Insert into SQL with Groovy using variable for values

user_8700309 0
Viewed 1916

I'm trying to use a variable for the values part of an insert SQL statement. What's odd is that if I copy the values of what I see in the debugger then the statement runs and inserts correctly. If I use the variable then it gives me a "Sorry, wrong number of values" error.

Here's my insert

db.execute("""
        INSERT INTO TABLE 
        (UNIT, ORD#, DISP, DATE, TIME, AMT, LAT, LONG, DRVR, OWNR, SETT, STAT) 
        values ${parsedOrderNumbers}
        """)

parsedOrderNumbers looks like in the debugger, but it might also have " " surrounding it since it's a String. I'm not sure and think that might be what is happening here with the error or maybe with how Groovy interpolates ${parsedOrderNumbers}

('12345', '1234567', '' ,'2020-04-11', '234', '35.00', '39.693702697753906', '-75.53226470947266', '', '', '', ''), ('20514', '9876543', '' ,'2020-04-12', '004', '24.00', '39.27902603149414', '-76.55120086669922', '', '', '', '')

If I copy this and replace ${parsedOrderNumbers} then the statement runs. Any ideas?

db2 groovy sql java
2 Answers
user_33558600

When you use ${} syntax together with GString version of execute() (Sql.execute(GString gstring)), groovy-sql will treat each ${} as a bind variable. You're getting the error Sorry, wrong number of values because groovy-sql only saw one bind variable when you're trying to insert 12 values.

What you're doing is actually not best practise, form both security (SQL injection) and performance (DB query cache) perspective.

If you really want to generate the VALUES clause upfront then use the String version of Sql.execute(String query) like this.

db.execute("""
        INSERT INTO TABLE 
        (UNIT, ORD#, DISP, DATE, TIME, AMT, LAT, LONG, DRVR, OWNR, SETT, STAT) 
        values ${parsedOrderNumbers}
        """.toString())

However, I recommend doing what @ou_ryperd suggested or something like this.

db.execute("""
        INSERT INTO TABLE 
        (UNIT, ORD#, DISP, DATE, TIME, AMT, LAT, LONG, DRVR, OWNR, SETT, STAT) 
        values ($unit, $orderNo, $disp, $date, $time, $amt, $lat, $long, $drvr, $ownr, $sett, $stat)
        """)
user_19101780
Related