Re: Performing COPY Command

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(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\\0120b0c0d0e0f101112131415161718191a1b1c1d1e1f20212223242526'28292a2b2c2d2e2f303132333435363738393a3b3c3d3e3f404142434445464748494a4b4c4d4e4f505152535455565758595a5b\\5d5e5f606162636465666768696a6b6c6d6e6f707172737475767778797a7b7c7d7e7f808182838485868788898a8b8c8d8e8f909192939495969798999a9b9c9d9e9fa0a1a2a3a4a5a6a7a8a9aaabacadaeafb0b1b2b3b4b5b6b7b8b9babbbcbdbebfc0c1c2c3c4c5c6c7c8c9cacbcccdcecfd0d1d2d3d4d5d6d7d8d9dadbdcdddedfe0e1e2e3e4e5e6e7e8e9eaebecedeeeff0f1f2f3f4f5f6f7f8f9fafbfcfdfeff
>\.
>
>
>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:
>
>> 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