Re: Performing COPY Command

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(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
>
>

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