Skip site navigation (1) Skip section navigation (2)

Re: Performing COPY Command

From: "M(dot) Bastin" <marcbastin(at)mindspring(dot)com>
To: "Rajan Bhide" <rbhide(at)nulinkinc(dot)com>
Cc: <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Performing COPY Command
Date: 2004-02-12 11:12:11
Message-ID: a06020401bc510c249bb7@[192.168.0.101] (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-novice
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


In response to

pgsql-novice by date

Next:From: Marcus Andree S. MagalhaesDate: 2004-02-12 13:43:11
Subject: function accessing other db
Previous:From: Bruno LEVEQUEDate: 2004-02-12 10:54:49
Subject: Re: Performing COPY Command

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group