Skip site navigation (1) Skip section navigation (2)

Re: Insert binary data on postgre

From: Andrew Biagioni <andrew(dot)biagioni(at)e-greek(dot)net>
To: "Eduardo S(dot) Fontanetti" <dufuzzy(at)yahoo(dot)com(dot)br>
Cc: pgsql-admin(at)postgresql(dot)org
Subject: Re: Insert binary data on postgre
Date: 2004-06-25 04:06:23
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-admin

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' );


    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.


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 ?
>Yahoo! Mail - agora com 100MB de espaço, anti-spam e antivírus grátis!
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?

In response to

pgsql-admin by date

Next:From: Jan Norman PedersenDate: 2004-06-25 07:16:58
Subject: Re: rules
Previous:From: mike gDate: 2004-06-25 03:28:04
Subject: Re: Insert binary data on postgre

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group