[groovy-user] Groovy SQL to byte[]

[Sean McPoland]

Hi,

 

Having a problem with groovy SQL to byte[]. 

 

I have documents stored in a Postgresql database as bytea.

 

My code is:

 

import groovy.sql.Sql;

conn1 = BonitaSql.newInstance ("jdbc:postgresql://localhost:5432/database","user", "password", new org.postgresql.Driver());

def reqdColName = "document";

def reqdDocument = "documentName.docx";

def query1 = "SELECT $reqdColName FROM documents WHERE documentname = '$reqdDocument'";

 

and have tried each of the following

 

def documentBytes = conn1.executeQuery(query1).getArray(1);

def documentBytes = conn1.executeQuery(query1).getBytes(1);

bytes[] documentBytes = conn1.executeQuery(query1).getBytes(1);

def documentBytes = conn1.execute(query1);

 

They all fail with either Boolean or ResultSet closed.

 

How do I get my variable documentBytes to be byte[], which is what I need for following code?

 

Thanks in advance

Seán

 

 

PS My original Java code was as follows, but I need this to be in groovy.

 

PreparedStatement ps = conn1.prepareStatement("SELECT document FROM documents WHERE documentname = ?");

ps.setString(1, "dingbatdocument.docx");

ResultSet rs = ps.executeQuery();

while (rs.next()) {

    byte[] documentBytes = rs.getBytes(1);

}

 

[Dinko Srkoč]

On 25 February 2015 at 06:26, Sean McPoland <sean.m...@gubernare.com> wrote:
> Hi,
>
> Having a problem with groovy SQL to byte[].

I don't think the problem is with the 'byte[]' part, as it looks like
you haven't reached that yet. ;-)

> [...]

> def query1 = "SELECT $reqdColName FROM documents WHERE documentname = '$reqdDocument'";

> [...]

> and have tried each of the following
>
> def documentBytes = conn1.executeQuery(query1).getArray(1);
>
> def documentBytes = conn1.executeQuery(query1).getBytes(1);
>
> bytes[] documentBytes = conn1.executeQuery(query1).getBytes(1);
>
> def documentBytes = conn1.execute(query1);
>
>
>
> They all fail with either Boolean or ResultSet closed.

`execute` returns boolean. From the docs:
"""
Returns:
true if the first result is a ResultSet object; false if it is an
update count or there are no results
"""

`executeQuery` method is protected, not meant to be used unless you
are extending the Sql class.

>
> How do I get my variable documentBytes to be byte[], which is what I need
> for following code?

If you want to work with ResultSet directly (as using `executeQuery`
seems to imply), you could use the `query` method:

conn1.query(query1 as String) { rs ->

while (rs.next()) {
byte[] documentBytes = rs.getBytes(1)
}
}

Note that I converted `query1` to String because its type is, in fact,
GString. Method `Sql.query(GString, Closure)` (unlike its overloaded
variant `Sql.query(String, Closure)` that I used above) expects the
GString argument to be query with parameters, as it will use
PreparedStatement under the hood. `query1`, as written, would fail.
Proper GString query might look like this:

def query1 = """
SELECT ${Sql.expand(reqdColName)} FROM documents
WHERE documentname = $reqdDocument
"""

It can then be used without casting to String:

conn1.query(query1) { rs -> ...

There are other Sql methods that you could use, e.g. `firstRow` if
only one row interests you, or `rows` to fetch the list of rows. You
would then be insulated from the ResultSet.

Finally, the documentation for the Sql class is rather extensive.
Might I suggest that you go through it (if you haven't done so
already):

http://docs.groovy-lang.org/docs/latest/html/gapi/groovy/sql/Sql.html

Cheers,
Dinko

>
>
>
> Thanks in advance
>
> Seán
>
>
>
>
>
> PS My original Java code was as follows, but I need this to be in groovy.
>
>
>
> PreparedStatement ps = conn1.prepareStatement("SELECT document FROM
> documents WHERE documentname = ?");
>
> ps.setString(1, "dingbatdocument.docx");
>
> ResultSet rs = ps.executeQuery();
>
> while (rs.next()) {
>
> byte[] documentBytes = rs.getBytes(1);
>
> }
>
>

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

http://xircles.codehaus.org/manage_email