blobs in groovy sql

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

blobs in groovy sql

bguenthe
Hello,

I want to insert binary data into a databse blob field (derby db) with grooy sql. I have no idea ho to do this. Has anybody an idea how this can be done?

Thanks

Bernd
Reply | Threaded
Open this post in threaded view
|

Re: blobs in groovy sql

Christof Vollrath
Just tried it out, the problem is, that sql returns a java.sql.Blob
object and you have to use the getBytes() method to access the bytes of
the blob. Also getBytes has some strange parameters, the first parameter
is long and is the start position of the bytes counting from 1, while
the next is the length of bytes as int - see my example.
Hope this helps,
tof

import groovy.sql.*

def jdbcUrl = 'jdbc:derby:E:/Christof/Labor/GroovyJPA/db;create=true'
def sql = Sql.newInstance(jdbcUrl, '', '',
                           'org.apache.derby.jdbc.EmbeddedDriver')
sql.execute('CREATE TABLE testBlob (bits BLOB)')
byte[] bits = 'Alles in Butter'.getBytes()
sql.execute('INSERT INTO testBlob (bits) VALUES (?)', [bits])
sql.eachRow('SELECT bits FROM testBlob') {
   println 'bits.class: ' + it.bits.class
   byte[] readBits = it.bits.getBytes(1L, it.bits.length()as int)
   println 'bits: ' + new String(readBits)
}

bguenthe schrieb:
> Hello,
>
> I want to insert binary data into a databse blob field (derby db) with grooy
> sql. I have no idea ho to do this. Has anybody an idea how this can be done?
>
> Thanks
>
> Bernd

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

    http://xircles.codehaus.org/manage_email

Reply | Threaded
Open this post in threaded view
|

Re: blobs in groovy sql

bguenthe
Hey,

thats a great idea for all data that can easily tranformed into a byte array. But what I have in mind was to put large amouts of binary data into a blob/clob column (e.g a Picture). The derby documentation describes this process als follows (java code, if you have jdk1.6 installed it's in the file <jdk-installation\db\demo\programs\toursdb\insertMaps.java>)

Connection connCS = null;

System.out.println("Loading the Cloudscape jdbc driver...");
Class.forName(CSdriver).newInstance();

System.out.println("Getting Cloudscape database connection...");
connCS = DriverManager.getConnection(dbURLCS);
System.out.println("Successfully got the Cloudscape database connection...");

PreparedStatement ps = null;

ps = connCS.prepareStatement
("insert into maps (map_name, region, area, photo_format, picture) values (?,?,?,?,?)");

ps.setString(1,"BART");
ps.setString(2,"Bay Area");
ps.setBigDecimal(3, new BigDecimal("1776.11"));
ps.setString(4,"gif");
File file = new File ("BART.gif");
InputStream fileIn = new FileInputStream(file);
ps.setBinaryStream(5, fileIn, (int)file.length());
int numrows = ps.executeUpdate();

So this works with an InputStream (and with an output stream to retrieve the data from the database). The question is . It this also possible in groovy, without embedding java code?

thx

Christof Vollrath wrote
Just tried it out, the problem is, that sql returns a java.sql.Blob
object and you have to use the getBytes() method to access the bytes of
the blob. Also getBytes has some strange parameters, the first parameter
is long and is the start position of the bytes counting from 1, while
the next is the length of bytes as int - see my example.
Hope this helps,
tof

import groovy.sql.*

def jdbcUrl = 'jdbc:derby:E:/Christof/Labor/GroovyJPA/db;create=true'
def sql = Sql.newInstance(jdbcUrl, '', '',
                           'org.apache.derby.jdbc.EmbeddedDriver')
sql.execute('CREATE TABLE testBlob (bits BLOB)')
byte[] bits = 'Alles in Butter'.getBytes()
sql.execute('INSERT INTO testBlob (bits) VALUES (?)', [bits])
sql.eachRow('SELECT bits FROM testBlob') {
   println 'bits.class: ' + it.bits.class
   byte[] readBits = it.bits.getBytes(1L, it.bits.length()as int)
   println 'bits: ' + new String(readBits)
}

bguenthe schrieb:
> Hello,
>
> I want to insert binary data into a databse blob field (derby db) with grooy
> sql. I have no idea ho to do this. Has anybody an idea how this can be done?
>
> Thanks
>
> Bernd

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

    http://xircles.codehaus.org/manage_email
Reply | Threaded
Open this post in threaded view
|

Re: blobs in groovy sql

Christof Vollrath
Oh, I see. The problem is, that one should pass a Blob as a parameter,
but a Blob is only an interface and can not be instanciated.
The only solution I found is to prepare statement explicitly, which then
is much like the Java code but still a shorter.
This looks like this:

import groovy.sql.*

def jdbcUrl = 'jdbc:derby:E:/Christof/Labor/GroovyJPA/db;create=true'
def sql = Sql.newInstance(jdbcUrl, '', '',
                           'org.apache.derby.jdbc.EmbeddedDriver')
sql.execute('CREATE TABLE testBlob (bits BLOB)')
def is = new ByteArrayInputStream('Alles in Butter'.getBytes())
def ps = sql.connection.prepareStatement(
                'INSERT INTO testBlob (bits) VALUES (?)')
ps.setBinaryStream(1, is)
ps.executeUpdate()
sql.eachRow('SELECT bits FROM testBlob') {
   println 'bits.class: ' + it.bits.class
   byte[] readBits = it.bits.getBytes(1L, it.bits.length()as int)
   println 'bits: ' + new String(readBits)
}

Maybe some more experienced Groovy SQL expert knows a better solution...
Bye,
tof

bguenthe schrieb:

> Hey,
>
> thats a great idea for all data that can easily tranformed into a byte
> array. But what I have in mind was to put large amouts of binary data into a
> blob/clob column (e.g a Picture). The derby documentation describes this
> process als follows (java code, if you have jdk1.6 installed it's in the
> file <jdk-installation\db\demo\programs\toursdb\insertMaps.java>)
>
> Connection connCS = null;
>
> System.out.println("Loading the Cloudscape jdbc driver...");
> Class.forName(CSdriver).newInstance();
>
> System.out.println("Getting Cloudscape database connection...");
> connCS = DriverManager.getConnection(dbURLCS);
> System.out.println("Successfully got the Cloudscape database
> connection...");
>
> PreparedStatement ps = null;
>
> ps = connCS.prepareStatement
> ("insert into maps (map_name, region, area, photo_format, picture) values
> (?,?,?,?,?)");
>
> ps.setString(1,"BART");
> ps.setString(2,"Bay Area");
> ps.setBigDecimal(3, new BigDecimal("1776.11"));
> ps.setString(4,"gif");
> File file = new File ("BART.gif");
> InputStream fileIn = new FileInputStream(file);
> ps.setBinaryStream(5, fileIn, (int)file.length());
> int numrows = ps.executeUpdate();
>
> So this works with an InputStream (and with an output stream to retrieve the
> data from the database). The question is . It this also possible in groovy,
> without embedding java code?
>
> thx
>
>
> Christof Vollrath wrote:
>> Just tried it out, the problem is, that sql returns a java.sql.Blob
>> object and you have to use the getBytes() method to access the bytes of
>> the blob. Also getBytes has some strange parameters, the first parameter
>> is long and is the start position of the bytes counting from 1, while
>> the next is the length of bytes as int - see my example.
>> Hope this helps,
>> tof
>>
>> import groovy.sql.*
>>
>> def jdbcUrl = 'jdbc:derby:E:/Christof/Labor/GroovyJPA/db;create=true'
>> def sql = Sql.newInstance(jdbcUrl, '', '',
>>                            'org.apache.derby.jdbc.EmbeddedDriver')
>> sql.execute('CREATE TABLE testBlob (bits BLOB)')
>> byte[] bits = 'Alles in Butter'.getBytes()
>> sql.execute('INSERT INTO testBlob (bits) VALUES (?)', [bits])
>> sql.eachRow('SELECT bits FROM testBlob') {
>>    println 'bits.class: ' + it.bits.class
>>    byte[] readBits = it.bits.getBytes(1L, it.bits.length()as int)
>>    println 'bits: ' + new String(readBits)
>> }
>>
>> bguenthe schrieb:
>>> Hello,
>>>
>>> I want to insert binary data into a databse blob field (derby db) with
>>> grooy
>>> sql. I have no idea ho to do this. Has anybody an idea how this can be
>>> done?
>>>
>>> Thanks
>>>
>>> Bernd
>> ---------------------------------------------------------------------
>> To unsubscribe from this list please visit:
>>
>>     http://xircles.codehaus.org/manage_email
>>
>>
>>
>

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

    http://xircles.codehaus.org/manage_email