Обсуждение: RE: [INTERFACES] binary insert with JDBC
Ok, which version of Postgres are you using? To use Blobs (Binary Large Objects) currently you need to use the oid type. Take a look at the example.blobtest and example.ImageViewer classes as these deal with Blobs. -- Peter T Mount, peter@maidstone.gov.uk, peter@retep.org.uk Please note that this is from my works email. If you reply, please cc my home address. -----Original Message----- From: owner-pgsql-interfaces@hub.org [mailto:owner-pgsql-interfaces@hub.org]On Behalf Of Sean Reilly Sent: Monday, May 11, 1998 11:09 PM To: interfaces postgres Subject: [INTERFACES] binary insert with JDBC Can anyone tell me how to insert binary data into a postgres table using JDBC? What type field should I use? Most importantly, how do I create an SQL statement with binary data? I thought this could be done with a PreparedStatement and setBytes() but it doesn't seem to be implemented in postgres.PreparedStatement. Is there some way to encode the binary value into the SQL string (like hex encoding or something)? I looked through the docs but couldn't find anything related to this, but it seems like it might be a common problem. Thanks, Sean
Hello, Thanks for taking the time to reply =) I'm using version 6.3.2. I just tried using blobs and it seems to take an unusually long time to retrieve the blobs (after the record has been retrieved). Since I know my data will be under 512 bytes, is there any way I can store it directly in a field in the table so I can avoid the extra lookup? If not, is there any way that I can speed up the retrieval of the blob (insertion speed doesn't matter)? I've already tried creating an index for the oid, but it didn't help much. Thanks, Sean Peter Mount wrote: > > Ok, which version of Postgres are you using? > > To use Blobs (Binary Large Objects) currently you need to use the oid type. > > Take a look at the example.blobtest and example.ImageViewer classes as these > deal with Blobs. > > -- > Peter T Mount, peter@maidstone.gov.uk, peter@retep.org.uk > Please note that this is from my works email. If you reply, please cc my > home address. > > -----Original Message----- > From: owner-pgsql-interfaces@hub.org > [mailto:owner-pgsql-interfaces@hub.org]On Behalf Of Sean Reilly > Sent: Monday, May 11, 1998 11:09 PM > To: interfaces postgres > Subject: [INTERFACES] binary insert with JDBC > > Can anyone tell me how to insert binary data into > a postgres table using JDBC? What type field should I use? > Most importantly, how do I create an SQL statement with > binary data? I thought this could be done with a > PreparedStatement and setBytes() but it doesn't seem to > be implemented in postgres.PreparedStatement. Is there > some way to encode the binary value into the SQL string > (like hex encoding or something)? > > I looked through the docs but couldn't find anything > related to this, but it seems like it might be a common > problem. > > Thanks, > Sean -- Sean Reilly | Corporation for National Research Initiatives Programmer | sreilly@cnri.reston.va.us 703.620.8990 x275 | http://www.cnri.reston.va.us
On Wed, 13 May 1998, Sean Reilly wrote: > Hello, > Thanks for taking the time to reply =) > > > I'm using version 6.3.2. I just tried using blobs and it seems to > take an unusually long time to retrieve the blobs (after the record > has been retrieved). Since I know my data will be under 512 bytes, > is there any way I can store it directly in a field in the table so > I can avoid the extra lookup? If not, is there any way that I can > speed up the retrieval of the blob (insertion speed doesn't matter)? > I've already tried creating an index for the oid, but it didn't help > much. There is some overhead with BLOBS: On the first call, the driver has several queries to perform to set itself up. After that, accessing a blob is a minimum of three fastpath calls (lo_open, lo_read, lo_close). There isn't a way (yet) to store directly into a table. There was some discussion a few weeks ago about this, and I have started to look at this, but it wont be available until 6.4 -- Peter T Mount peter@retep.org.uk or petermount@earthling.net Main Homepage: http://www.retep.org.uk ************ Someday I may rebuild this signature completely ;-) ************ Work Homepage: http://www.maidstone.gov.uk Work EMail: peter@maidstone.gov.uk
At 1:08 +0300 on 14/5/98, Sean Reilly wrote:
> I'm using version 6.3.2. I just tried using blobs and it seems to
> take an unusually long time to retrieve the blobs (after the record
> has been retrieved). Since I know my data will be under 512 bytes,
> is there any way I can store it directly in a field in the table so
> I can avoid the extra lookup? If not, is there any way that I can
> speed up the retrieval of the blob (insertion speed doesn't matter)?
> I've already tried creating an index for the oid, but it didn't help
> much.
The simplest way would be to encode the binary data into text and save it
in a "Text" attribute - assuming you don't want to rely on conversion of
binary data into Unicode characters, which is another way of doing the same
thing without taking much space. It may be unreliable when it comes to
8-bit data, though, so keep that in mind.
The simplest way - which keeps you well in the 7bit realm - is to encode
using straightforward hex, which would mean your attribute will take
exactly twice as much space as it is originally. Let's suppose your binary
data is stored in a byte array named binData.
byte [] binData = new byte [512];
... // Data filled in the array
StringBuffer conversionBuffer = new StringBuffer( binData.length * 2 );
for (int i = 0; i < binData.length; i++ ) {
conversionBuffer.append(
Integer.toHexString( 0x100 | binData[i] ).substring(1) );
}
... // Make a prepared statement for the insert.
stmt.setString( 1, conversionBuffer.toString() );
The thing there with the 0x100 | binData[i] is meant to make sure you have
two digits even if the number was smaller than 0x10. It gives you a hex
string between 100 and 1ff, and then you substring the last two digits.
This is appended to the conversion buffer, and eventually, you put it in a
field in pgSQL. The field is to be defined as Text, of course.
To decode this, you can use:
.. // Make a statement and select the string into the variable hexData
byte [] binData = new byte [ hexData.length() / 2];
for (int i=0; i< hexData.length(); i += 2 ) {
binData[i/2] = Byte.parseByte( hexData.substring( i, i+2 ), 16);
}
Disclaimer: I haven't actually tested the code above. Try it on small
amounts of data, displaying them instead of putting to the database. Once
you're satisfied that my indexes into the strings and all that are correct
(that is, an array of {128, 8} gives you really 8008 and vice versa).
Herouth