AppletTalk.com

..::WojT::.. · Guest

Hello.
I hav big problem with storing binary data into bytea (postgres) using plain
text insert.
I need to generate sql script (in fact plpgsql script) which inserts byte
array into bytea column i my table.
This byte array is serialized object.
There is way i am doing it right now:

{
.....
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
ObjectOutputStream stream= new ObjectOutputStream(outputStream);
// serialize object
stream.writeObject(model);
// get bytes of object
byte[] modelBytes = outputStream.toByteArray();
ByteArrayOutputStream arrayOutputStream = new ByteArrayOutputStream();
DataOutputStream dataOutputStream = new DataOutputStream(arrayOutputStream);
// convert to UTF8
dataOutputStream.writeUTF(new String(modelBytes));
StringBuffer buffer = new StringBuffer();
byte[] aaa = arrayOutputStream.toByteArray();
for (int i = 0; i < aaa.length; i++) {
buffer.append(getEscapedByte(aaa[i]));
}

// here i generate insert...

String sqlInsert = "insert blablabvlalbal values ('" + buffer.toString() +
"')";

}

.....
-- the method which escapes bytes as plpgsql need them.

private String getEscapedByte(byte b) {
int c = (int)b & 0xFF;
// special chars
switch (c){
// ZERO byte
case 0:
return "\\000";
// apostroph '
case 39:
return "\\047";
// backslash
case 92:
return "\\134";
}
// non-printable characters
if (c < 32 || c > 126){
String cS = Integer.toString(c, Cool

;
if (cS.length() == 1){
return "\00" + cS;
}
if (cS.length() == 2){
return "\0" + cS;
}
return "\" + cS;
}
// printable characters
return "" + (char)c;
}

After executing generated plpgsql function i get error:
psql:WojT test_1.0.6.txt:27: ERROR: invalid input syntax for type bytea

The second problem is, that generated byte String is little different than
string generated in the database when i am inserting this by using
preparedStattemnt and setBytes() method.
When i insert generated string using pgadmin - everything is ok, but when i
try to deserialize object i get error:
java.io.StreamCorruptedException: invalid stream header

Do you know where i am doing something wrong ?
Maybe somebody have done something like this ?
The major is INSERT A BYTE ARRAY INTO BYTEA FIELD INTO POSTGRES USING
PLPGSQL FUNCTION.
Please help and thank you for any help.
I have lost four days on this problem...

...::WojT::..