Обсуждение: Performing COPY Command
How to perform COPY from memory instead of files?
Any examples/pointers would be greatly appreciated.
Any examples/pointers would be greatly appreciated.
Thanks in advanced
Rajan
Rajan
Hi,
Can u provide me the 'C' code snippet to execute it (with libpq).(Specially to PIPE stdin to psql server)
I tried with following but no success:
void performInsert()
{
char query_string[2048]; /* holds constructed SQL query */
PGconn *conn; /* holds database connection */
PGresult *res; /* holds query result */
/* connect to the database */
conn = PQconnectdb(DB_CONN_PARAM_STR);
/* connect to the database */
if (PQstatus(conn) == CONNECTION_BAD) /* did the connection fail? */
{
fprintf(stderr, "Connection to database failed.\n");
fprintf(stderr, "%s\n",PQerrorMessage(conn));
return;
}
else
{
fprintf(stderr, "Connection successful\n");
}
sprintf(query_string, "COPY testtable FROM stdin DELIMITERS ','");
fprintf(stdin,"123,");
fprintf(stdin,"abc");
fprintf(stdin,"\.");
fprintf(stderr,"QueryStr : %s\n",query_string);
res = PQexec(conn, query_string); /* send the query */
if (atoi(PQcmdTuples(res)) == 0)
{
fprintf(stderr, "Insert failed : %s\n",PQerrorMessage(conn));
break;
}
PQclear(res); /* free result */
PQfinish(conn); /* disconnect from the database */
}
Table structure:
Psql=>\d testtable
Column | Type | Modifiers
--------+---------+--------------------------------------------------
seqno | integer |
data | bytea |
I could succeed fetching the data from file,
but my code fails if the file contains non-printable characters saying "Bad input string for type bytea".
My contents from the file are:
1,\\000010203040506070809\\0120b0c0d0e0f101112131415161718191a1b1c1d1e1f20212223242526'28292a2b2c2d2e2f303132333435363738393a3b3c3d3e3f404142434445464748494a4b4c4d4e4f505152535455565758595a5b\\5d5e5f606162636465666768696a6b6c6d6e6f707172737475767778797a7b7c7d7e7f808182838485868788898a8b8c8d8e8f909192939495969798999a9b9c9d9e9fa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfc0c1c2c3c4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6d7d8d9dadbdcdddedfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f6f7f8f9fafbfcfdfeff
\.
Basically its the hexsequence from 0X00-0XFF.
Thanks,
Rajan Bhide
-----Original Message-----
From: Michael Glaesemann [mailto:grzm@myrealbox.com]
Sent: Tuesday, February 10, 2004 2:37 PM
To: Rajan Bhide
Subject: Re: [NOVICE] Performing COPY Command
Hi Rajan
On Feb 10, 2004, at 2:41 PM, Rajan Bhide wrote:
> How to perform COPY from memory instead of files?
> Any examples/pointers would be greatly appreciated.
I believe COPY works from STDIN if no file is specified, so if you can
pipe the data to STDIN, you should be golden. And the docs might help:
<http://www.postgresql.org/docs/current/static/sql-copy.html>
Does this help?
Michael Glaesemann
grzm myrealbox com
Hi All,
I need an urgent soln, so u expert guys, plz help out or plz provide some pointers to look on for example code.
Thanks,
Rajan Bhide
-----Original Message-----
From: Rajan Bhide
Sent: Tuesday, February 10, 2004 7:45 PM
To: Michael Glaesemann; pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Performing COPY Command
Hi,
Can u provide me the 'C' code snippet to execute it (with libpq).(Specially to PIPE stdin to psql server)
I tried with following but no success:
void performInsert()
{
char query_string[2048]; /* holds constructed SQL query */
PGconn *conn; /* holds database connection */
PGresult *res; /* holds query result */
/* connect to the database */
conn = PQconnectdb(DB_CONN_PARAM_STR);
/* connect to the database */
if (PQstatus(conn) == CONNECTION_BAD) /* did the connection fail? */
{
fprintf(stderr, "Connection to database failed.\n");
fprintf(stderr, "%s\n",PQerrorMessage(conn));
return;
}
else
{
fprintf(stderr, "Connection successful\n");
}
sprintf(query_string, "COPY testtable FROM stdin DELIMITERS ','");
fprintf(stdin,"123,");
fprintf(stdin,"abc");
fprintf(stdin,"\.");
fprintf(stderr,"QueryStr : %s\n",query_string);
res = PQexec(conn, query_string); /* send the query */
if (atoi(PQcmdTuples(res)) == 0)
{
fprintf(stderr, "Insert failed : %s\n",PQerrorMessage(conn));
break;
}
PQclear(res); /* free result */
PQfinish(conn); /* disconnect from the database */
}
Table structure:
Psql=>\d testtable
Column | Type | Modifiers
--------+---------+--------------------------------------------------
seqno | integer |
data | bytea |
I could succeed fetching the data from file,
but my code fails if the file contains non-printable characters saying "Bad input string for type bytea".
My contents from the file are:
1,\\000010203040506070809\\0120b0c0d0e0f101112131415161718191a1b1c1d1e1f20212223242526'28292a2b2c2d2e2f303132333435363738393a3b3c3d3e3f404142434445464748494a4b4c4d4e4f505152535455565758595a5b\\5d5e5f606162636465666768696a6b6c6d6e6f707172737475767778797a7b7c7d7e7f808182838485868788898a8b8c8d8e8f909192939495969798999a9b9c9d9e9fa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfc0c1c2c3c4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6d7d8d9dadbdcdddedfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f6f7f8f9fafbfcfdfeff
\.
Basically its the hexsequence from 0X00-0XFF.
Thanks,
Rajan Bhide
-----Original Message-----
From: Michael Glaesemann [mailto:grzm@myrealbox.com]
Sent: Tuesday, February 10, 2004 2:37 PM
To: Rajan Bhide
Subject: Re: [NOVICE] Performing COPY Command
Hi Rajan
On Feb 10, 2004, at 2:41 PM, Rajan Bhide wrote:
> How to perform COPY from memory instead of files?
> Any examples/pointers would be greatly appreciated.
I believe COPY works from STDIN if no file is specified, so if you can
pipe the data to STDIN, you should be golden. And the docs might help:
<http://www.postgresql.org/docs/current/static/sql-copy.html>
Does this help?
Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Hi,
Maybe you need to protect your data (bytea).
With a base64_encode.
Bruno
On Thu, 12 Feb 2004, Rajan Bhide wrote:
> Hi All,
>
> I need an urgent soln, so u expert guys, plz help out or plz provide some pointers to look on for example code.
>
> Thanks,
> Rajan Bhide
>
> -----Original Message-----
> From: Rajan Bhide
> Sent: Tuesday, February 10, 2004 7:45 PM
> To: Michael Glaesemann; pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Performing COPY Command
>
>
> Hi,
>
> Can u provide me the 'C' code snippet to execute it (with libpq).(Specially to PIPE stdin to psql server)
> I tried with following but no success:
>
>
> void performInsert()
> {
> char query_string[2048]; /* holds constructed SQL query */
> PGconn *conn; /* holds database connection */
> PGresult *res; /* holds query result */
> /* connect to the database */
> conn = PQconnectdb(DB_CONN_PARAM_STR);
> /* connect to the database */
>
> if (PQstatus(conn) == CONNECTION_BAD) /* did the connection fail? */
> {
> fprintf(stderr, "Connection to database failed.\n");
> fprintf(stderr, "%s\n",PQerrorMessage(conn));
> return;
> }
> else
> {
> fprintf(stderr, "Connection successful\n");
> }
>
>
> sprintf(query_string, "COPY testtable FROM stdin DELIMITERS ','");
> fprintf(stdin,"123,");
> fprintf(stdin,"abc");
> fprintf(stdin,"\.");
> fprintf(stderr,"QueryStr : %s\n",query_string);
>
> res = PQexec(conn, query_string); /* send the query */
> if (atoi(PQcmdTuples(res)) == 0)
> {
> fprintf(stderr, "Insert failed : %s\n",PQerrorMessage(conn));
> break;
> }
>
> PQclear(res); /* free result */
> PQfinish(conn); /* disconnect from the database */
> }
>
> Table structure:
> Psql=>\d testtable
> Column | Type | Modifiers
> --------+---------+--------------------------------------------------
> seqno | integer |
> data | bytea |
>
>
> I could succeed fetching the data from file,
> but my code fails if the file contains non-printable characters saying "Bad input string for type bytea".
>
> My contents from the file are:
>
>
1,\\000010203040506070809\\0120b0c0d0e0f101112131415161718191a1b1c1d1e1f20212223242526'28292a2b2c2d2e2f303132333435363738393a3b3c3d3e3f404142434445464748494a4b4c4d4e4f505152535455565758595a5b\\5d5e5f606162636465666768696a6b6c6d6e6f707172737475767778797a7b7c7d7e7f808182838485868788898a8b8c8d8e8f909192939495969798999a9b9c9d9e9fa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfc0c1c2c3c4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6d7d8d9dadbdcdddedfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f6f7f8f9fafbfcfdfeff
> \.
>
>
> Basically its the hexsequence from 0X00-0XFF.
>
> Thanks,
> Rajan Bhide
>
>
> -----Original Message-----
> From: Michael Glaesemann [mailto:grzm@myrealbox.com]
> Sent: Tuesday, February 10, 2004 2:37 PM
> To: Rajan Bhide
> Subject: Re: [NOVICE] Performing COPY Command
>
>
> Hi Rajan
>
> On Feb 10, 2004, at 2:41 PM, Rajan Bhide wrote:
>
> > How to perform COPY from memory instead of files?
> > Any examples/pointers would be greatly appreciated.
>
> I believe COPY works from STDIN if no file is specified, so if you can
> pipe the data to STDIN, you should be golden. And the docs might help:
<http://www.postgresql.org/docs/current/static/sql-copy.html>
>
> Does this help?
>
> Michael Glaesemann
> grzm myrealbox com
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>
Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque@net6d.com
http://www.net6d.com
Hi Rajan, I can't really help you with C, but if you want to check out pgSQL4RB then you can build a client with this functionality in about 3 minutes and there I could be of assistance. <http://aliacta.com/products> I don't know if you need this for part of a larger project in C in which you have already invested much time or wether you only want a small utility to do this particular import. If it is the latter, or if you are only at the beginning of your project and you can still switch from C, then pgSQL4RB will definitely save you a huge amount of time. When you download pgSQL4RB there's a sample project that comes with it that implements COPY from STDIN so you could just compile that and merrily do your import without doing any purchase, if that's the only thing you need to do. pgSQL4RB can properly escape bytea data as well if that is part of the problem. (I must confess this is a plug and I'm the writer of pgSQL4RB, but it's a honest one aimed at helping you out since you seem stuck.) Marc At 3:14 PM +0530 2/12/04, Rajan Bhide wrote: >Hi All, > >I need an urgent soln, so u expert guys, plz help out or plz provide >some pointers to look on for example code. > >Thanks, >Rajan Bhide > >-----Original Message----- >From: Rajan Bhide >Sent: Tuesday, February 10, 2004 7:45 PM >To: Michael Glaesemann; pgsql-novice@postgresql.org >Subject: Re: [NOVICE] Performing COPY Command > > >Hi, > >Can u provide me the 'C' code snippet to execute it (with >libpq).(Specially to PIPE stdin to psql server) >I tried with following but no success: > > >void performInsert() >{ > char query_string[2048]; /* holds constructed SQL query */ > PGconn *conn; /* holds >database connection */ > PGresult *res; /* holds >query result */ > /* connect to the database */ > conn = PQconnectdb(DB_CONN_PARAM_STR); > /* connect to the database */ > > if (PQstatus(conn) == CONNECTION_BAD) /* did the >connection fail? */ > { > fprintf(stderr, "Connection to database failed.\n"); > fprintf(stderr, "%s\n",PQerrorMessage(conn)); > return; > } > else > { > fprintf(stderr, "Connection successful\n"); > } > > > sprintf(query_string, "COPY testtable FROM stdin DELIMITERS ','"); > fprintf(stdin,"123,"); > fprintf(stdin,"abc"); > fprintf(stdin,"\."); > fprintf(stderr,"QueryStr : %s\n",query_string); > > res = PQexec(conn, query_string); /* send the query */ > if (atoi(PQcmdTuples(res)) == 0) > { > fprintf(stderr, "Insert failed : %s\n",PQerrorMessage(conn)); > break; > } > > PQclear(res); /* free result */ > PQfinish(conn); /* >disconnect from the database */ >} > >Table structure: >Psql=>\d testtable >Column | Type | Modifiers >--------+---------+-------------------------------------------------- > seqno | integer | > data | bytea | > > >I could succeed fetching the data from file, >but my code fails if the file contains non-printable characters >saying "Bad input string for type bytea". > >My contents from the file are: > >1,\\000010203040506070809\\0120b0c0d0e0f101112131415161718191a1b1c1d1e1f20212223242526'28292a2b2c2d2e2f303132333435363738393a3b3c3d3e3f404142434445464748494a4b4c4d4e4f505152535455565758595a5b\\5d5e5f606162636465666768696a6b6c6d6e6f707172737475767778797a7b7c7d7e7f808182838485868788898a8b8c8d8e8f909192939495969798999a9b9c9d9e9fa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfc0c1c2c3c4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6d7d8d9dadbdcdddedfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f6f7f8f9fafbfcfdfeff >\. > > >Basically its the hexsequence from 0X00-0XFF. > >Thanks, >Rajan Bhide > > >-----Original Message----- >From: Michael Glaesemann [mailto:grzm@myrealbox.com] >Sent: Tuesday, February 10, 2004 2:37 PM >To: Rajan Bhide >Subject: Re: [NOVICE] Performing COPY Command > > >Hi Rajan > >On Feb 10, 2004, at 2:41 PM, Rajan Bhide wrote: > >> How to perform COPY from memory instead of files? >> Any examples/pointers would be greatly appreciated. > >I believe COPY works from STDIN if no file is specified, so if you can >pipe the data to STDIN, you should be golden. And the docs might >help: <http://www.postgresql.org/docs/current/static/sql-copy.html> > >Does this help? > >Michael Glaesemann >grzm myrealbox com > > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > > > > >---------------------------(end of broadcast)--------------------------- >TIP 8: explain analyze is your friend