Thread: New COPY commands in libpq

I read in the manual that the libpq functions PQputline(conn, cmd) and
PQendcopy(conn) have been deprecated and that PQputCopyData(conn, cmd,
sizeof(cmd)) and PQputCopyEnd(conn, msg) are the replacements.

I'm trying to convert a program that works just fine with the old
functions. I assume I'm missing a step here:

old C++ code:
       CString cmd;              res = PQexec(conn,  "COPY segmentvalues FROM STDIN;");if (PQresultStatus (res) !=
PGRES_COPY_IN){   PQclear (res);    return ns_DATABASEERROR;}else    PQclear(res);
 
       cmd.Format("1\t\2\t{3,4,5}\n");PQputline(conn, cmd);cmd.Format("\\.\n");PQputline(conn, cmd);
       PQendcopy(conn);


New C++ code:
       CString cmd, msg;              res = PQexec(conn,  "COPY segmentvalues FROM STDIN;");if (PQresultStatus (res) !=
PGRES_COPY_IN){   PQclear (res);    return ns_DATABASEERROR;}else    PQclear(res);
 
       cmd.Format("1\t\2\t{3,4,5}\n");
*    PQputCopyData(conn, cmd, sizeof(cmd));       cmd.Format("\\.\n");
*       PQputCopyData(conn, cmd, sizeof(cmd));
*       PQputCopyEnd(conn, msg);


Old C++ code works, new stuff doesn't. Only line that have changed are
*'d.

The serverlog gives me the error: "ERROR: Copy from STDIN failed:
CONTEXT: COPY segmentvalues, line 1: "1        " ERROR: current
transaction is aborted, commands ignored until the end of transaction
block."

Can anyone point me in the right direction? There aren't any examples
of how to use the new code in the manual. I thought I was following
the written instructions correctly (but apparently am not).

Thanks.
-Tony

p.s. Running PostgreSQL 7.4.2 server on Fedora Linux. Client is a
Windows XP MS Visual C++ .NET using libpq.

On Fri, Apr 30, 2004 at 06:12:35AM -0700, Tony Reina wrote:

>         CString cmd, msg;
>         cmd.Format("1\t\2\t{3,4,5}\n");
> *    PQputCopyData(conn, cmd, sizeof(cmd));
>         cmd.Format("\\.\n");
> *       PQputCopyData(conn, cmd, sizeof(cmd));
> *       PQputCopyEnd(conn, msg);
> Old C++ code works, new stuff doesn't. Only line that have changed are
> *'d.
I'm not surprised.  CString is clearly a class, and cmd is an object of
that class.  Apparently that class has an implicit conversion operator to
char * (which is a striking example of a Bad Idea--kids, don't try this
at home!) but nonetheless, sizeof() should still give you the size of the
object, *not* the size of the string it represents!

You might try porting your code to libpqxx, which is C++-native and should
make large swathes of this sort of code unnecessary.


Jeroen

reina_ga@hotmail.com (Tony Reina) writes:
> *    PQputCopyData(conn, cmd, sizeof(cmd));

I'm a bit rusty on C++ string mashing, but surely sizeof() is not the
correct way to determine the number of bytes presently stored in a
variable-length string?

> *       PQputCopyEnd(conn, msg);

You want to pass NULL for the second argument.
        regards, tom lane

> I'm a bit rusty on C++ string mashing, but surely sizeof() is not the
> correct way to determine the number of bytes presently stored in a
> variable-length string?
> 
> > *       PQputCopyEnd(conn, msg);

Yes, I've had several people mention this and that did the trick.
Thanks to all that replied. Looks like a simple strlen() works fine,
but I'll have to look into the UNICODE concerns that some people had
about Windows and MFC.

As I was looking into finding some sample code, I found that
/src/bin/psql/copy.c uses the older, deprecated commands of PQputline
and PQendcopy.

-Tony

> You might try porting your code to libpqxx, which is C++-native and should
> make large swathes of this sort of code unnecessary.
> 

I've seriously considered it (along with the npgsql library), but am
not really sure as to what the advantage(s) would be considering that
(with the corrections suggested) my code works now with libpq.

Would there be any improvement in speed with libpqxx? Does libpqxx
make use of the SSL encryption? How hard is it to link to the SSL
connections for Postgres commands?

-Tony

On Sat, May 01, 2004 at 02:25:01AM -0700, Tony Reina wrote:
> > You might try porting your code to libpqxx, which is C++-native and should
> > make large swathes of this sort of code unnecessary.
> I've seriously considered it (along with the npgsql library), but am
> not really sure as to what the advantage(s) would be considering that
> (with the corrections suggested) my code works now with libpq.
It depends.  It takes a lot of debugging out of your hands because the
boiler-plate stuff like error checking goes into the library, rather than
being rewritten all over the place in the applications.  Of course if
your current code works just fine, there's no reason to change anything
so drastic.


> Would there be any improvement in speed with libpqxx? Does libpqxx
> make use of the SSL encryption? How hard is it to link to the SSL
> connections for Postgres commands?

Don't expect any speed improvements per se; libpqxx is a layer on top of
libpq.  OTOH some performance features like COPY and limited non-blocking
access (no support for select()ing on multiple file descriptors yet) become
much more easily accessible.

Frankly I don't recall ATM just how libpq deals with SSL.  Don't have the
opportunity to look it up just now.


Jeroen