How to perform COPY from memory instead of files?
Any examples/pointers would be greatly appreciated.

Thanks in advanced
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\\0120b0c0d0e0f1011121314 15161718191a1b1c1d1e1f20212223242526'28292a2b2c2d2 e2f303132333435363738393a3b3c3d3e3f404142434445464 748494a4b4c4d4e4f505152535455565758595a5b\\5d5e5f6 06162636465666768696a6b6c6d6e6f7071727374757677787 97a7b7c7d7e7f808182838485868788898a8b8c8d8e8f90919 2939495969798999a9b9c9d9e9fa0a1a2a3a4a5a6a7a8a9aaa bacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfc0c1c2c3c 4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6d7d8d9dadbdcd ddedfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f 6f7f8f9fafbfcfdfeff
\.


Basically its the hexsequence from 0X00-0XFF.

Thanks,
Rajan Bhide


-----Original Message-----
From: Michael Glaesemann [mailto:grzm (AT) myrealbox (DOT) 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:

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 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 (AT) postgresql (DOT) 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\\0120b0c0d0e0f1011121314 15161718191a1b1c1d1e1f20212223242526'28292a2b2c2d2 e2f303132333435363738393a3b3c3d3e3f404142434445464 748494a4b4c4d4e4f505152535455565758595a5b\\5d5e5f6 06162636465666768696a6b6c6d6e6f7071727374757677787 97a7b7c7d7e7f808182838485868788898a8b8c8d8e8f90919 2939495969798999a9b9c9d9e9fa0a1a2a3a4a5a6a7a8a9aaa bacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfc0c1c2c3c 4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6d7d8d9dadbdcd ddedfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f 6f7f8f9fafbfcfdfeff
\.


Basically its the hexsequence from 0X00-0XFF.

Thanks,
Rajan Bhide


-----Original Message-----
From: Michael Glaesemann [mailto:grzm (AT) myrealbox (DOT) 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:

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

Hi,

Maybe you need to protect your data (bytea).
With a base64_encode.

Bruno

On Thu, 12 Feb 2004, Rajan Bhide wrote:

Bruno LEVEQUE
System Engineer
SARL NET6D
bruno.leveque (AT) net6d (DOT) com
http://www.net6d.com

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

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:

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend