CODEWITHSUNDEEP
sqlgroovy
Bhanuchander Udhayakumar
Bhanuchander Udhayakumar

Reputation: 1621

Why this error happens with groovy sql jdbc builder?

Why this code does not get the string for sql.execute("$y")?

    import groovy.sql.Sql
    def sql = Sql.newInstance("jdbc:mysql://localhost", "root","password", "com.mysql.jdbc.Driver")
    def y= "select * from table"
    table(sql,y)
    def table(sql,x){
        println ("$x")
        sql.execute("$x")
    }

The Output:

'select * from table'
Sep 02, 2017 3:49:39 PM groovy.sql.Sql$AbstractQueryCommand execute
WARNING: Failed to execute: ? because: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''select * from table'' at line 1
Caught: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''select * from table'' at line 1
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''select * from table'' at line 1

Upvotes: 0

Views: 871

Answers (2)

Bhanuchander Udhayakumar
Bhanuchander Udhayakumar

Reputation: 1621

This problem solved by the method shown below.

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:mysql://localhost", "root","password", "com.mysql.jdbc.Driver")
def y= "select * from tablename"
table(sql,y)
def table(sql,x){
    println (x)
    sql.execute(x)
}

"select * from table" query can't work. Because table is a keyword in sql.

This simple change works without any errors. Thanks for your responses.

Upvotes: 0

daggett
daggett

Reputation: 28564

sql.execute("$x")

in groovy double-quoted string with $expression inside is actually a groovy.lang.GString

so you are calling this method: Sql.execute(Gstring query)

this method replaces all $expressions in groovy string with ?

creates prepared statement and pass all $expressions as parameters of this prepared statement

in your case "$x" converted to "?" and executed.

Mysql tries to parse this query "?" and gives you an error:

MySQLSyntaxErrorException: You have an error in your SQL syntax

if you change your code to this:

sql.execute("$x" as String)

You'll beat this problem but you will face another one: you can't select rows with method Sql.execute(...)

examples with parameters

The following commands are equivalent:

def rows = sql.rows("select * from mytable where fieldA = $value")

def rows = sql.rows("select * from mytable where fieldA = ?", [value] )

def parms = [VALUE: value]
def rows = sql.rows(parms, "select * from mytable where fieldA = :VALUE")

all of them will be executed as a prepared statement "select * from mytable where fieldA = ?"

Upvotes: 1

Related Questions