From: Andrew Biagioni <andrew.biagioni@e-greek.net>
To: Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
Cc: pgsql-admin@postgresql.org
Subject: Re: Insert binary data on postgre
Date: Fri, 25 Jun 2004 00:06:23 -0400
Message-ID: <40DBA4BF.9040601@e-greek.net> (raw)
In-Reply-To: <20040624191640.84897.qmail@web40707.mail.yahoo.com>
References: <20040624191640.84897.qmail@web40707.mail.yahoo.com>
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
>
>
>
>
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-admin@postgresql.org
Cc: andrew.biagioni@e-greek.net, dufuzzy@yahoo.com.br
Subject: Re: Insert binary data on postgre
In-Reply-To: <40DBA4BF.9040601@e-greek.net>
References: <20040624191640.84897.qmail@web40707.mail.yahoo.com> <40DBA4BF.9040601@e-greek.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox