Re: Problem with inserting bytea into postgresql

...::WojT::.. wrote:
> 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:

Just a few comments. I didn't look at your code in a lot of detail:

> {
> ....
> 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));

You should have stopped at "byte[] modelBytes = ..." instead of
continuing with the ensuing nonsense. What your code after that line
does is:

1. Convert your binary data into character data using the platform
default encoding. Since the platform default encoding is very possibly
unable to represent all of the Unicode code points below 256, the
process will convert many of your bytes into '?' characters, causing you
to become unable to recover the original data.

2. Convert the resulting mangled character stream back into a byte
array, except this time there's a 16-bit length count appended to the
beginning, and the characters are encoded via UTF-8.

> private String getEscapedByte(byte b) {

This seems to have lots of problems, such as doubling backslash
characters that shouldn't be, and so forth. I'm not going to sort
through your code... but here's the code from the latest PostgreSQL JDBC
driver to perform that task:

public static String toPGString(byte[] p_buf) throws SQLException
{
if (p_buf == null) return null;
StringBuffer l_strbuf = new StringBuffer(2 * p_buf.length);
for (int i = 0; i < p_buf.length; i++)
{
int l_int = (int)p_buf[i];
if (l_int < 0)
{
l_int = 256 + l_int;
}

if (l_int < 040 || l_int > 0176)
{
l_strbuf.append("\\");
l_strbuf.append((char)(((l_int >> 6) & 0x3) + 48));
l_strbuf.append((char)(((l_int >> 3) & 0x7) + 48));
l_strbuf.append((char)((l_int & 0x07) + 48));
}
else if (p_buf[i] == (byte)'\\')
{
l_strbuf.append("\\\\");
}
else
{
//other characters are left alone
l_strbuf.append((char)p_buf[i]);
}
}
return l_strbuf.toString();
}

If you have the JDBC drivers in your classpath, you need not copy the
code... just use

String str = PGbytea.toPGString(modelBytes);

PGbytea should be imported from org.postgresql.util.

If you fix those problems and still need more help, let us know.

--
www.designacourse.com
The Easiest Way To Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation
.

Relevant Pages

  • Re: sending echo to all clients
    ... I did initialize it up properly, ... There is only one array and that is an array of pollfd structures named ... as an array of characters only but then I can't because sendsends bytes ... you receive C-style strings, so there's really no point to doing it. ...
    (comp.unix.programmer)
  • Re: test if handle exists? How???
    ... > ishandlegives an array of nine 0s ... does not work because 'handles.c' is a literal character vector of 9 characters and that character vector is not an empty array. ... If what you are trying to do is find out whether there is a graphics object with the tag 'abcdef' then use ...
    (comp.soft-sys.matlab)
  • Re: easy parsing problem in C for beginner
    ... As far as I can tell you're getting an array of time slot priorities ... so I went with the WHILE loops and my own counter. ... char prio = PA_GetArrayElementAtIndex; ... characters are in that array. ...
    (comp.sys.mac.programmer.help)
  • Re: Subquery Confusion
    ... Then I got this crazy idea that an Array can only contain a maximum ... number of characters, ... Then I decide that maybe I'm completely wrong with my query, ... it out of Excel VBA and spit it into Microsoft SQL Server Management ...
    (microsoft.public.excel.programming)
  • Re: test if handle exists? How???
    ... The problem is with the script but even replicating it on the command line ... ishandlegives an array of nine 0s ... does not work because 'handles.c' is a literal character vector of 9 characters and that character vector is not an empty array. ... If what you are trying to do is find out whether there is a graphics object with the tag 'abcdef' then use ...
    (comp.soft-sys.matlab)