CODEWITHSUNDEEP
grailsgroovy
sp123
sp123

Reputation: 45

java.sql.SQLException: Invalid column index in groovy service class

I have a code as per: 

def private prepare(params) {
    groovy.sql.Sql sql = new groovy.sql.Sql(dataSource);
    String variables = params.variables

    if(variables != null && variables != "") {
        def Paths = variables.split("\\|")
        List conceptList = new ArrayList()

        Paths.each { cPath ->
            def row = sql.rows(""" SELECT firstname FROM person WHERE lastname like '${cPath}%' """).each { row ->
                conceptList.add(PersonHelperService.getLastnameFromKey(row.firstname))
            }
        }

        String[] conceptArray = conceptList.toArray()
        jobDataMap.put("c_cds", conceptArray);
    }
}

I do get the error while running the application: java.sql.SQLException: Invalid column index

I have in variable Paths the list of certain paths which are already separated by function split. I am pretty sure that I am getting error while calling sql.rows. If I comment that part out along with its each function everything seems to work. But I do need that sql statement to get the list of rows which satisfies the condition. What part of the code I am doing wrong?

Upvotes: 0

Views: 657

Answers (3)

sp123
sp123

Reputation: 45

The below worked, thank you everyone: 

def source= cPath+'%';
            def row = sql.rows(""" SELECT firstname FROM person WHERE lastname like 'source' """)

Upvotes: -2

Fernando
Fernando

Reputation: 2189

Something that I experienced before is that sql.rows method have trouble with GString.

When you use string interpolation, that is not a real string, in fact it is a GString. They are mostly interchangeable, but sometimes they are not.

You can convert a GString into a regular String with the toString() method. My suggestion for you is to re-write your query like:

sql.rows("SELECT firstname FROM person WHERE lastname like '${cPath}%'".toString())

Another, maybe cleaner, solution would be to assign the query to a String variable first.

String query = "SELECT firstname FROM person WHERE lastname like '${cPath}%'"
sql.rows(query).each { row -> ... }

Good luck!

Upvotes: 1

tim_yates
tim_yates

Reputation: 171084

I believe your call to rows should be

def row = sql.rows("SELECT firstname FROM person WHERE lastname like ${cPath + '%'}").each {

Upvotes: 2

Related Questions