Thread: Insert binary data on postgre
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/
What data type did you use to define the binary column? bytea or LO?
On Thu, 2004-06-24 at 14:16, 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
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
>
>
>
>
>
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 > If you're using Java, check out http://doc.postgresintl.com/jdbc/ch08.html Cheers, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243
I am using bytea data type and Visual Basic 6.
I saw in another place, that I can insert byte values
in the Postgre with a double backslash, then I used a
function to translate the binary string to ANSI
string. Then I can use: \\001\\010\\047 ...
Somebody could say me if its right?
Eduardo
--- mike g <mike@thegodshalls.com> escreveu: > What
data type did you use to define the binary
> column? bytea or LO?
>
> On Thu, 2004-06-24 at 14:16, 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
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please
> send an appropriate
> subscribe-nomail command to
> majordomo@postgresql.org so that your
> message can get through to the mailing list
> cleanly
>
______________________________________________________________________
Yahoo! Mail - agora com 100MB de espaço, anti-spam e antivírus grátis!
http://br.info.mail.yahoo.com/
I forgot a little, or better, a big detail, to do this
translating from binary to ANSI of a file with 100Kb,
it will return something like 500000 bytes, and this
process is very, very and very slow. Then I need to
use another way.
I've tried to use the Replace native function from the
VB to put a backslash before the ' on the string, but
it hadn't worked, I think it don't interprets the
character like postgres. Somebody knows how to solve
it? How to identify the character exactly like the
postgres?
Thanks
Eduardo
--- "Eduardo S. Fontanetti" <dufuzzy@yahoo.com.br>
escreveu: > I am using bytea data type and Visual
Basic 6.
>
> I saw in another place, that I can insert byte
> values
> in the Postgre with a double backslash, then I used
> a
> function to translate the binary string to ANSI
> string. Then I can use: \\001\\010\\047 ...
>
> Somebody could say me if its right?
>
> Eduardo
>
> --- mike g <mike@thegodshalls.com> escreveu: > What
> data type did you use to define the binary
> > column? bytea or LO?
> >
> > On Thu, 2004-06-24 at 14:16, 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
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please
> > send an appropriate
> > subscribe-nomail command to
> > majordomo@postgresql.org so that your
> > message can get through to the mailing list
> > cleanly
> >
>
>
______________________________________________________________________
>
> 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 9: the planner will ignore your desire to choose
> an index scan if your
> joining column's datatypes do not match
>
______________________________________________________________________
Yahoo! Mail - agora com 100MB de espaço, anti-spam e antivírus grátis!
http://br.info.mail.yahoo.com/
Eduardo S. Fontanetti wrote: > I forgot a little, or better, a big detail, to do this > translating from binary to ANSI of a file with 100Kb, > it will return something like 500000 bytes, and this > process is very, very and very slow. Then I need to > use another way. > > I've tried to use the Replace native function from the > VB to put a backslash before the ' on the string, but > it hadn't worked, I think it don't interprets the > character like postgres. Somebody knows how to solve > it? How to identify the character exactly like the > postgres? > Check out http://www.postgresql.org/docs/7.4/interactive/libpq-exec.html#LIBPQ-EXEC-ESCAPE-BYTEA The escape functions are located in the libpq library (PostgreSQL's native C library), that has a Windows native port. You may either look at the code and implement that in VB (don't think it will perform very well though) or write a COM wrapper or something like that to make those functions available in VB. Best of luck, -- Radu-Adrian Popescu CSA, DBA, Developer Aldrapay MD Aldratech Ltd. +40213212243
On Fri, 2004-06-25 at 07:43, Eduardo S. Fontanetti wrote: > I forgot a little, or better, a big detail, to do this > translating from binary to ANSI of a file with 100Kb, > it will return something like 500000 bytes, and this > process is very, very and very slow. Then I need to > use another way. Have you considered uuencoding or binhexing the data so that you end up with a "clean" string you can commit? (It will still suck up twice the file size in space though). Regards, Frank
Attachment
Yes, I've tried, but it is very slow to uuencode. But I found another thing very better, the functions lo_import and lo_export. It solved my problem, really very good!! Thanks a lot for all the help! Eduardo --- Frank Knobbe <frank@knobbe.us> escreveu: > On Fri, 2004-06-25 at 07:43, Eduardo S. Fontanetti > wrote: > > I forgot a little, or better, a big detail, to do > this > > translating from binary to ANSI of a file with > 100Kb, > > it will return something like 500000 bytes, and > this > > process is very, very and very slow. Then I need > to > > use another way. > > Have you considered uuencoding or binhexing the data > so that you end up > with a "clean" string you can commit? (It will still > suck up twice the > file size in space though). > > Regards, > Frank > > > ATTACHMENT part 2 application/pgp-signature name=signature.asc ______________________________________________________________________ Yahoo! Mail - agora com 100MB de espaço, anti-spam e antivírus grátis! http://br.info.mail.yahoo.com/