pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedInsert binary data on postgre
9+ messages / 5 participants
[nested] [flat]
* Insert binary data on postgre
@ 2004-06-24 19:16 Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 03:28 ` Re: Insert binary data on postgre mike g <mike@thegodshalls.com>
2004-06-25 04:06 ` Re: Insert binary data on postgre Andrew Biagioni <andrew.biagioni@e-greek.net>
2004-06-25 09:11 ` Re: Insert binary data on postgre Radu-Adrian Popescu <radu.popescu@aldratech.com>
0 siblings, 3 replies; 9+ messages in thread
From: Eduardo S. Fontanetti @ 2004-06-24 19:16 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Insert binary data on postgre
2004-06-24 19:16 Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
@ 2004-06-25 03:28 ` mike g <mike@thegodshalls.com>
2004-06-25 12:17 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2 siblings, 1 reply; 9+ messages in thread
From: mike g @ 2004-06-25 03:28 UTC (permalink / raw)
To: Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Insert binary data on postgre
2004-06-24 19:16 Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 03:28 ` Re: Insert binary data on postgre mike g <mike@thegodshalls.com>
@ 2004-06-25 12:17 ` Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 12:43 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
0 siblings, 1 reply; 9+ messages in thread
From: Eduardo S. Fontanetti @ 2004-06-25 12:17 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Insert binary data on postgre
2004-06-24 19:16 Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 03:28 ` Re: Insert binary data on postgre mike g <mike@thegodshalls.com>
2004-06-25 12:17 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
@ 2004-06-25 12:43 ` Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 14:24 ` Re: Insert binary data on postgre Radu-Adrian Popescu <radu.popescu@aldratech.com>
2004-06-28 04:51 ` Re: Insert binary data on postgre Frank Knobbe <frank@knobbe.us>
0 siblings, 2 replies; 9+ messages in thread
From: Eduardo S. Fontanetti @ 2004-06-25 12:43 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Insert binary data on postgre
2004-06-24 19:16 Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 03:28 ` Re: Insert binary data on postgre mike g <mike@thegodshalls.com>
2004-06-25 12:17 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 12:43 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
@ 2004-06-25 14:24 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
1 sibling, 0 replies; 9+ messages in thread
From: Radu-Adrian Popescu @ 2004-06-25 14:24 UTC (permalink / raw)
To: Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Insert binary data on postgre
2004-06-24 19:16 Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 03:28 ` Re: Insert binary data on postgre mike g <mike@thegodshalls.com>
2004-06-25 12:17 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 12:43 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
@ 2004-06-28 04:51 ` Frank Knobbe <frank@knobbe.us>
2004-06-28 13:50 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
1 sibling, 1 reply; 9+ messages in thread
From: Frank Knobbe @ 2004-06-28 04:51 UTC (permalink / raw)
To: Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>; +Cc: pgsql-admin
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
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Insert binary data on postgre
2004-06-24 19:16 Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 03:28 ` Re: Insert binary data on postgre mike g <mike@thegodshalls.com>
2004-06-25 12:17 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 12:43 ` Re: Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-28 04:51 ` Re: Insert binary data on postgre Frank Knobbe <frank@knobbe.us>
@ 2004-06-28 13:50 ` Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
0 siblings, 0 replies; 9+ messages in thread
From: Eduardo S. Fontanetti @ 2004-06-28 13:50 UTC (permalink / raw)
To: pgsql-admin
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/
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Insert binary data on postgre
2004-06-24 19:16 Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
@ 2004-06-25 04:06 ` Andrew Biagioni <andrew.biagioni@e-greek.net>
2 siblings, 0 replies; 9+ messages in thread
From: Andrew Biagioni @ 2004-06-25 04:06 UTC (permalink / raw)
To: Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>; +Cc: 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
>
>
>
>
>
^ permalink raw reply [nested|flat] 9+ messages in thread
* Re: Insert binary data on postgre
2004-06-24 19:16 Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
@ 2004-06-25 09:11 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
2 siblings, 0 replies; 9+ messages in thread
From: Radu-Adrian Popescu @ 2004-06-25 09:11 UTC (permalink / raw)
To: Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 9+ messages in thread
end of thread, other threads:[~2004-06-28 13:50 UTC | newest]
Thread overview: 9+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-06-24 19:16 Insert binary data on postgre Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 03:28 ` mike g <mike@thegodshalls.com>
2004-06-25 12:17 ` Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 12:43 ` Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 14:24 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
2004-06-28 04:51 ` Frank Knobbe <frank@knobbe.us>
2004-06-28 13:50 ` Eduardo S. Fontanetti <dufuzzy@yahoo.com.br>
2004-06-25 04:06 ` Andrew Biagioni <andrew.biagioni@e-greek.net>
2004-06-25 09:11 ` Radu-Adrian Popescu <radu.popescu@aldratech.com>
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