Re: Insert binary data on postgre - Mailing list pgsql-admin

Eduardo,

your problem is the way you are submitting the string.  Since it's a
binary, it can contain any "character" value (from 0 to 255) and
PostgreSQL treats certain characters as special characters.  The main
example you're running into is the quote character.

If you try to submit a string like this:

    I had a 'great' time

and you try to execute:

    INSERT INTO mytable ( myfield ) VALUES ( 'I had a 'great' time' );

the parser will of course see a second quote right before "great" and
terminate the string.  Then it will be very confused by  "great' time'" !!!

The correct insert statement would ESCAPE (i.e., precede with a back
slash) the quotes IN the string, as follows:

    INSERT INTO mytable ( myfield ) VALUES ( 'I had a \'great\' time' );

or:

    INSERT INTO mytable ( myfield ) VALUES ( 'I had a ''great'' time' );

The parser knows that \' and '' are a character in a string
corresponding to a single quote, NOT an actual quote indicating the
beginning and the end of a string.  The string that will be added to the
database will actually contain a single quote, not the escaped sequence.

Note that, depending on your code, you may need to do more when you read
back the string.

There is at least one other character that must be escaped:  the actual
backslash, since it's expected to be part of an escape sequence, so you
replace "\" with "\\" which tells the parser, "this is really a
backslash" and it inserts a SINGLE backslash in the string.  Note that
otherwise, the parser will try to do something with the character that
follows the backslash;  if you're lucky, you'll just lose the backslash.

PLEASE NOTE:  Depending on what you are using to connect to PostgreSQL,
there may be different syntaxes;  some drivers expose function calls
with parameters, so you could create a Function called "InsertBinary (
text, text, text )" in PostgreSQL and call it from your code as

    String rslt = ConnP.ExecuteFunction ( "InsertBinary", "fotossocios",
"arquivo", myString );

The assumption here is that ExecuteFunction will cause a function to be
called ($1), with parameters $2, $3, and $4, and returning a String with
the result of the function execution.

Sorry, I don't know of a driver out there that offers this, but it's the
concept of "stored procedure calls" with Oracle, and I would suspect
that PostgreSQL somewhere has something similar.

             Andrew


Eduardo S. Fontanetti wrote:

>How can I do to put a file into the PostgreSQL ? I
>have got the contents of file in binary yet and put on
>a String on my APP, but when I try to insert it on
>postgre, it returns to me:
>
>ERROR: parser: unterminated quoted string at or near
>"BMæ»
' at character 90
>
>My string:
>
>ConnP.Execute "insert into fotossocios(arquivo)
>values('" & Conteudo & "')"
>
>Could somebody help me how to construct the right
>string ?
>
>Thanks
>Eduardo
>
>______________________________________________________________________
>
>Yahoo! Mail - agora com 100MB de espaço, anti-spam e antivírus grátis!
>http://br.info.mail.yahoo.com/
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>               http://www.postgresql.org/docs/faqs/FAQ.html
>
>
>
>
>