[groovy-user] Please help with SQL PreparedStatement

[rawi]

Hi
I need to send to Postgresql the query:

/SELECT a, a_tsvector
FROM t1
WHERE a_tsvector @@ to_tsquery('aa:* & bb:* & ccc:* & dddd:*')
LIMIT 10 ;/

I am succeding with:

/def query2 = 'SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@
to_tsquery(\'aa:* & bb:* & ccc:* & dddd:*\') LIMIT 10 ; '
def rows = db.rows(query2)/

But I fail attempting a PreparedStatement with parameters, like this:

/def query1 = 'SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@
to_tsquery(\':a & :b & :c & :d\') LIMIT 10 ; '
def params = [ a: 'aa:*', b: 'bb:*', c: 'ccc:*', d: 'dddd:*' ]
def rows = db.rows(query1,params)/

Jun 20, 2013 1:13:26 PM groovy.sql.Sql$AbstractQueryCommand execute
WARNING: Failed to execute: SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@
to_tsquery(':a & :b & :c & :d') LIMIT 10 ; because: Can't infer the SQL
type to use for an instance of java.util.LinkedHashMap. Use setObject() with
an explicit Types value to specify the type to use.
Caught: org.postgresql.util.PSQLException: Can't infer the SQL type to use
for an instance of java.util.LinkedHashMap. Use setObject() with an explicit
Types value to specify the type to use.
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an
instance of java.util.LinkedHashMap. Use setObject() with an explicit Types
value to specify the type to use.
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1805)
at
org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:37)
at
org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:46)
at test_postgresql_fts.run(test_postgresql_fts.groovy:21)

Thank you very much in advance.

Regards, Rawi



--
View this message in context: http://groovy.329449.n5.nabble.com/Please-help-with-SQL-PreparedStatement-tp5715892.html
Sent from the groovy - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Tim Yates]

It's probably quote expansion on your parameters (as you've got them in escaped quotes already)

Does this work?

import static groovy.sql.Sql.expand as _

def params = [ a: 'aa:*', b: 'bb:*', c: 'ccc:*', d: 'dddd:*' ] 

def query1 = "SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@ to_tsquery( '${_(params.a)} & ${_(params.b)} & ${_(params.c)} & ${_(params.d)}\') LIMIT 10"

[Russel Winder]

On Thu, 2013-06-20 at 12:51 +0100, Tim Yates wrote:
[…]

> def params = [ a: 'aa:*', b: 'bb:*', c: 'ccc:*', d: 'dddd:*' ]
> def query1 = "SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@ to_tsquery(
> '${_(params.a)} & ${_(params.b)} & ${_(params.c)} & ${_(params.d)}\') LIMIT
> 10"

[…]

Nothing to do with the original question but…

Why should any Groovy programmer ever have to write any SQL? Groovy has
a MOP (like Python and C++) and so this should all be writeable as
Groovy (builder) code. As soon as string manipulation is involved things
get very dangerous.

--
Russel.
=============================================================================
Dr Russel Winder t: +44 20 7585 2200 voip: sip:russel...@ekiga.net
41 Buckmaster Road m: +44 7770 465 077 xmpp: rus...@winder.org.uk
London SW11 1EN, UK w: www.russel.org.uk skype: russel_winder

[Dinko Srkoč]

On 20 June 2013 13:35, rawi <only...@web.de> wrote:
> Hi
> I need to send to Postgresql the query:
>
> /SELECT a, a_tsvector
> FROM t1
> WHERE a_tsvector @@ to_tsquery('aa:* & bb:* & ccc:* & dddd:*')
> LIMIT 10 ;/

> [...]

> But I fail attempting a PreparedStatement with parameters, like this:
>
> /def query1 = 'SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@
> to_tsquery(\':a & :b & :c & :d\') LIMIT 10 ; '
> def params = [ a: 'aa:*', b: 'bb:*', c: 'ccc:*', d: 'dddd:*' ]
> def rows = db.rows(query1,params)/

I would venture to guess that this is because you only have one
parameter (`to_tsquery( one-parameter-here )`, if I read correctly),
but are trying to use four. Furthermore, you should not put the
parameters inside quotes.

If you wanted to use PreparedStatement, you might try something along
the lines of:
[untested]

def params = [a:'aa:*', b:'bb:*', c:'ccc:*', d:'dddd:*']*.value.join(' & ')

def query1 = 'SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@

to_tsquery(?) LIMIT 10;'
def rows = db.rows(query1, params)

Cheers,
Dinko

[Dinko Srkoč]

On 20 June 2013 15:04, Dinko Srkoč <dinko...@gmail.com> wrote:
> If you wanted to use PreparedStatement, you might try something along
> the lines of:
> [untested]
>
> def params = [a:'aa:*', b:'bb:*', c:'ccc:*', d:'dddd:*']*.value.join(' & ')
> def query1 = 'SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@
> to_tsquery(?) LIMIT 10;'
> def rows = db.rows(query1, params)

or rather

def rows = db.rows(query1, [params])

as `params` is in fact just a String, not a collection.

- Dinko

[Tim Yates]

Agreed, a builder would be great, but wouldn't taking in all possible sql edge-cases (such as the Postgres text searching query above) be a Herculean task?

[Russel Winder]

On Thu, 2013-06-20 at 14:21 +0100, Tim Yates wrote:
> Agreed, a builder would be great, but wouldn't taking in all possible sql
> edge-cases (such as the Postgres text searching query above) be
> a Herculean task?

I suspect not. Well not completely. cf. SQLAlchemy.

From what I can tell groovy-sql just handles connections, cursors and
transactions. SQLAlchemy has this layer as it is rather essential. But
then SQLALchemy has an expression language layer on this to allow people
to write Python and have SQLAlchemy create the necessary SQL. Jez and
later someone else had a go at trying to create something along these
lines for Groovy. Sometime back I salvaged their work into a Git
repository: https://github.com/russel/GSQL At the time I had been going
to do some work with SQL which is why I was doing this. My problem is
that I have very little knowledge of SQL.

--
Russel.
=============================================================================
Dr Russel Winder t: +44 20 7585 2200 voip: sip:russel...@ekiga.net
41 Buckmaster Road m: +44 7770 465 077 xmpp: rus...@winder.org.uk
London SW11 1EN, UK w: www.russel.org.uk skype: russel_winder

[David Durham]

I started something having to do with data warehouse reporting.  My focus was really just on having a way to define relationships between tables and have the library deal with the issues of creating an optimized statement.

http://code.google.com/p/groovy-sql-mapper/

Here's a look at some of my tests.

http://code.google.com/p/groovy-sql-mapper/source/browse/groovy-sql-mapper/src/test/groovy/sql/QueryBuilderTest.groovy

Anyway, there hasn't really been a lot of interest in this project and I'm no longer having to deal with much SQL.

[Russel Winder]

On Thu, 2013-06-20 at 16:19 -0500, David Durham wrote:
> I started something having to do with data warehouse reporting. My focus
> was really just on having a way to define relationships between tables and
> have the library deal with the issues of creating an optimized statement.
>
> http://code.google.com/p/groovy-sql-mapper/
>
> Here's a look at some of my tests.
>
> http://code.google.com/p/groovy-sql-mapper/source/browse/groovy-sql-mapper/src/test/groovy/sql/QueryBuilderTest.groovy
>
> Anyway, there hasn't really been a lot of interest in this project and I'm
> no longer having to deal with much SQL.

Looks like we have a bit of a Catch-22 situation: those people doing
SQL-related things with Groovy are either using GORM or happy to write
SQL in strings, and those people who think Groovy could do with an SQL
expression language are no longer that interested in SQL!

SQLAlchemy is forging ahead down this road, making Python the language
of choice for this sort of stuff, leaving Groovy far behind in its wake.
It's a pity.

Interestingly (or not :-) your schema specification approach is almost
the same as that of SQLAlchemy. I guess there is an element of "form
follows function", so it should be no surprise there is a lot of
commonality of expression.

Thanks for mentioning your Git repository, I have filed the URL away in
case a day comes when SQL and Groovy become an issue for me again.
Hopefully by then someone will have picked up your and Jez's work and
moulded into something everyone working with SQL in Groovy is using.

[Paul King]

Most people that want more than String queries but less than GORM
are probably using Groovy's datasets, e.g. from the GroovyDoc:

def person = new DataSet(db, 'Person')
def janFrequentBuyers = person.findAll { it.purchaseCount > 10 && it.lastName == "January" }
def sortedPeopleOfInterest = janFrequentBuyers.
findAll{ it.lastName < 'Zulu' || it.lastName > 'Alpha' }.
findAll{ it.age < 99 }.
findAll{ it.age > 5 }.
sort{ it.firstName }.reverse().
findAll{ it.firstName != 'Bert' }.
sort{ it.age }

There's no need for SQL text fragments there. It has it's limitations
(as per a couple of Jira issues you have raised) but is workable for
simple cases. Agreed though thatschema specification approach in
David's groovy-sql-mapper (or something like it) might be an interesting
path forward.

Paul.

[rawi]

FIRST OF ALL, thank you very much for your help and interest in this
question!

@ TIM YATES

/import static groovy.sql.Sql.expand as _

def params = [ a: 'aa:*', b: 'bb:*', c: 'ccc:*', d: 'dddd:*' ]
def query1 = "SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@ to_tsquery(
'${_(params.a)} & ${_(params.b)} & ${_(params.c)} & ${_(params.d)}\') LIMIT
10"

def rows = db.rows(query,params)/

This produces almost the same error as I had first:

/Jun 21, 2013 9:37:55 AM groovy.sql.Sql$AbstractQueryCommand execute
WARNING: Failed to execute: SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@
to_tsquery( 'groovy.sql.Sql$35@6c0f34ef & groovy.sql.Sql$35@5a5c6e55 &
groovy.sql.Sql$35@6a4fbccb & groovy.sql.Sql$35@18f3e9f4') LIMIT 10 because:

Can't infer the SQL type to use for an instance of java.util.LinkedHashMap.
Use setObject() with an explicit Types value to specify the type to use.
Caught: org.postgresql.util.PSQLException: Can't infer the SQL type to use
for an instance of java.util.LinkedHashMap. Use setObject() with an explicit
Types value to specify the type to use.
org.postgresql.util.PSQLException: Can't infer the SQL type to use for an
instance of java.util.LinkedHashMap. Use setObject() with an explicit Types
value to specify the type to use.
at
org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1805)
at
org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:37)
at
org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:46)

at fts.run(fts.groovy:17)/


@ DINKO SRKOC

> I would venture to guess that this is because you only have one parameter
> (`to_tsquery( one-parameter-here )`

Yes Sir! That's right! I was blinded by the fact, that I will get a couple
of query parameter (which I wanted to SQL-Escape securely).
I suppose I can (equally secure) build first a concatenated string (made of
the 'aa:*' + ' & ' + 'bbb:*') and give it as parameter to the
PreparedStatement...

/def params = [a:'aa:*', b:'bb:*', c:'ccc:*', d:'dddd:*']*.value.join(' & ')
def query = 'SELECT a, a_tsvector FROM t2 WHERE a_tsvector @@ to_tsquery(?)
LIMIT 10;'
def rows = db.rows(query, params)/

_works_
...and as params-collection

/def rows = db.rows(query, [params])/

_works_ equally good!

Wonderful! Thanks again!

Regards, Rawi




--
View this message in context: http://groovy.329449.n5.nabble.com/Please-help-with-SQL-PreparedStatement-tp5715892p5715908.html

Sent from the groovy - user mailing list archive at Nabble.com.

[David Durham]

On Fri, Jun 21, 2013 at 2:49 AM, Paul King <pa...@asert.com.au> wrote:

Most people that want more than String queries but less than GORM
are probably using Groovy's datasets, e.g. from the GroovyDoc:

def person = new DataSet(db, 'Person')
def janFrequentBuyers = person.findAll { it.purchaseCount > 10 && it.lastName == "January" }
def sortedPeopleOfInterest = janFrequentBuyers.
    findAll{ it.lastName < 'Zulu' || it.lastName > 'Alpha' }.
    findAll{ it.age < 99 }.
    findAll{ it.age > 5 }.
    sort{ it.firstName }.reverse().
    findAll{ it.firstName != 'Bert' }.
    sort{ it.age }

There's no need for SQL text fragments there. It has it's limitations
(as per a couple of Jira issues you have raised) but is workable for
simple cases. Agreed though thatschema specification approach in
David's groovy-sql-mapper (or something like it) might be an interesting
path forward.

I thought one thing I might consider adding a feature to build the schema from database metadata.  This would cover a lot of uses.  Again, I'm kind of borrowing some of these ideas from htsql

http://htsql.org/

-Dave