How to Query map in Groovy with sql?

I am trying to query a map in Groovy using mysql using:

def dat = [["id": person[-1], "date" : appt]]          
dat.each{ db -> 
           sql.eachRow(
            "select * from ${Sql.expand(db)};", 
             { println "t$db ${it.mid}"} );

but I get an error:

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '=10886, date=19-01-2017}' at line 1

what seems to be the problem here?

************************************************* EDIT *****************************************************

I’m now trying to insert the map into a mysql, which I am then querying using:

  sql.execute '''DROP TABLE IF EXISTS EDSS'''

  sql.execute '''
  CREATE TABLE EDSS (
    id          INT,
    Clinic   VARCHAR(15),
    EDSS    VARCHAR(64),
    item     VARCHAR(64)
  );
  '''


  sql.withBatch("INSERT INTO EDSS (id, Clinic, EDSS, item) VALUES (?,?,?,?)"){ bt ->
      bt.addBatch(df)
  } 
  def res = sql.eachRow("select * from EDSS"){ row ->
      println "$row"
  }

the sql.eachRow part works fine i.e. I can select, but in the the insert statement I only seem to be inserting the first row within the map i.e.

println(df):

[1025386, 20-10-2017, null, ahddkw9d9c]
[10213446, 19-04-2017, 2.5, null]
[102382, 19-04-2017, null, null]
[1628466, 19-04-2017, null, 292jdmd02d]
[1111345, 18-09-2015, unchanged, null]

but:

println(res):

[1025386, 20-10-2017, null, ahddkw9d9c]

*********************************** Another EDIT *****************************************************

So trying to loop round all the values in the map, df, with:

sql.withBatch { stmt ->
            df.each { k, v, x, y ->
                stmt.addBatch("INSERT INTO EDSS (study_id, Clinic, EDSS, NHS) VALUES ('$k', '$v', '$x', '$y')")
            }
      }

results in:

groovy.lang.MissingMethodException: No signature of method: sql$_run_closure1$_closure2$_closure4.doCall() is applicable for argument types: (java.lang.String) values: [1025386]

I’m used to R where everything is nice and vectorized. If anyone can help at all that would be excellent!

Answer

What I observed here :

You are trying to insert the java.util.ArrayList [[val11, val12,...],[val21, val22,...]]. you almost close at what you are trying to do.

Note : in comment dagget already mentioned this answer. I am just trying to show in code.

Answer :

def df = [
[1025386, '20-10-2017', null, 'ahddkw9d9c'],
[10213446, '19-04-2017', 2.5, null],
[102382, '19-04-2017', null, null],
[1628466,'19-04-2017', null, '292jdmd02d'],
[1111345, '18-09-2015', 'unchanged', null]
]
sql.withBatch("INSERT INTO EDSS (id, Clinic, EDSS, item) VALUES (?,?,?,?)"){ bt ->                      
    df.each { row ->
            bt.addBatch (row) // <==== Answer Line
    }
} 
sql.eachRow("select * from EDSS"){ row ->
        println "$row"
}

Description :

The variable bt is actually BatchingPreparedStatementWrapper object. See the groovy document

So the addBatch() method can accept these types List<Object> or Object[].

Output :

[id:1025386, Clinic:20-10-2017, EDSS:[null], item:ahddkw9d9c]
[id:10213446, Clinic:19-04-2017, EDSS:2.5, item:[null]]
[id:102382, Clinic:19-04-2017, EDSS:[null], item:[null]]
[id:1628466, Clinic:19-04-2017, EDSS:[null], item:292jdmd02d]
[id:1111345, Clinic:18-09-2015, EDSS:unchanged, item:[null]]

Advertisement

Answer