Re: Performing COPY Command - Mailing list pgsql-novice

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