Re: [INTERFACES] binary insert with JDBC

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