Thread: code example for PQgetCopyData

code example for PQgetCopyData

From

Dave Huber

Date:

03 December 2009, 16:24:11

Does anybody have a snippet where they use PQgetCopyData? I must be calling it wrong as it keep crashing my program. I’ve attached my code below. I am writing this for a Code Interface Node in LabVIEW.

Thanks,

Dave

MgErr CINRun(LStrHandle conninfo, LStrHandle copystr, TD1Hdl resultValues) {

MgErr err = noErr;

PGconn *pConn;

PGresult* pResult = NULL;

char* szCopyStr = NULL;

char* errormsg = NULL;

char** buffer = NULL; // for retrieving the data

int nLen; // length of returned data

// connect to the database

char* szConnInfo = new char[LHStrLen(conninfo)+1];

LToCStr((*(conninfo)), (CStr)szConnInfo);

pConn = PQconnectdb(szConnInfo);

delete [] szConnInfo;

// check for errors connecting to database

if (PQstatus(pConn) != CONNECTION_OK)

{

DbgPrintf("Connection to database failed: %s", PQerrorMessage(pConn));

}

else

{

// start the copy command

szCopyStr = new char[LHStrLen(copystr)+1];

LToCStr((*(copystr)), (CStr)szCopyStr);

pResult = PQexec(pConn, szCopyStr);

delete [] szCopyStr;

// get the data

int i = 0;

while (nLen = PQgetCopyData(pConn, buffer, false) > 0)

{

if (err = SetCINArraySize((UHandle)resultValues, 2, ++i))

goto out;

if (err = NumericArrayResize(uB, 1L, (UHandle*)(&(*resultValues)->elt[i-1]), nLen-1))

goto out;

LStrLen(*(*resultValues)->elt[i-1]) = nLen-1; // set the Labview String size

MoveBlock(*buffer, LStrBuf(*(*resultValues)->elt[i-1]), nLen-1); // copy the data to a new string

PQfreemem(*buffer); // free the memory from getCopy

}

(*resultValues)->dimSize = i;

out:

PQclear(pResult);

// see if there were errors

if (nLen == -2)

{

DbgPrintf("Copy Out failed: %s", PQerrorMessage(pConn));

}

// close the connection

PQfinish(pConn);

return err;

}

This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message, together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use, printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email message to the sender and delete all copies of this message.
THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similar import, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachments are an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies, Inc., or any of its subsidiaries, affiliates, or any other person or entity.
WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.

Re: code example for PQgetCopyData

From

"Bret"

Date:

03 December 2009, 16:58:12

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Dave Huber
Sent: Thursday, December 03, 2009 9:18 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] code example for PQgetCopyData

Does anybody have a snippet where they use PQgetCopyData? I must be calling it wrong as it keep crashing my program. I’ve attached my code below. I am writing this for a Code Interface Node in LabVIEW.

Thanks,
Dave

MgErr CINRun(LStrHandle conninfo, LStrHandle copystr, TD1Hdl resultValues) {
      MgErr err = noErr;
      PGconn *pConn;
      PGresult* pResult = NULL;
      char* szCopyStr = NULL;
      char* errormsg = NULL;
      char** buffer = NULL;               // for retrieving the data
      int nLen;                                 // length of returned data

      // connect to the database
      char* szConnInfo = new char[LHStrLen(conninfo)+1];
      LToCStr((*(conninfo)), (CStr)szConnInfo);
      pConn = PQconnectdb(szConnInfo);
      delete [] szConnInfo;

      // check for errors connecting to database
      if (PQstatus(pConn) != CONNECTION_OK)
      {
            DbgPrintf("Connection to database failed: %s", PQerrorMessage(pConn));
      }
      else
      {
            // start the copy command
            szCopyStr = new char[LHStrLen(copystr)+1];
            LToCStr((*(copystr)), (CStr)szCopyStr);

            pResult = PQexec(pConn, szCopyStr);
            delete [] szCopyStr;

            // get the data
            int i = 0;
            while (nLen = PQgetCopyData(pConn, buffer, false) > 0)
            {

                  if (err = SetCINArraySize((UHandle)resultValues, 2, ++i))
                        goto out;
                  if (err = NumericArrayResize(uB, 1L, (UHandle*)(&(*resultValues)->elt[i-1]), nLen-1))
                        goto out;
                  LStrLen(*(*resultValues)->elt[i-1]) = nLen-1;                                 // set the Labview String size
                  MoveBlock(*buffer, LStrBuf(*(*resultValues)->elt[i-1]), nLen-1); // copy the data to a new string

                  PQfreemem(*buffer);                                                                             // free the memory from getCopy

            }
            (*resultValues)->dimSize = i;

out:
            PQclear(pResult);

            // see if there were errors
            if (nLen == -2)
            {
                  DbgPrintf("Copy Out failed: %s", PQerrorMessage(pConn));
            }

      }
      // close the connection
      PQfinish(pConn);

      return err;
}

Where is it blowing up?

This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message, together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use, printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email message to the sender and delete all copies of this message.
THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similar import, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachments are an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies, Inc., or any of its subsidiaries, affiliates, or any other person or entity.
WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.

Re: code example for PQgetCopyData

From

Tom Lane

Date:

03 December 2009, 18:30:14

Dave Huber <DHuber@letourneautechnologies.com> writes:
> Does anybody have a snippet where they use PQgetCopyData? I must be calling it wrong as it keep crashing my program.
I'veattached my code below. I am writing this for a Code Interface Node in LabVIEW. 

One thing you're missing is that you should check that the result from
the PQexec actually shows successful entry into COPY_OUT state.
But I think the crash is because you're confused about the indirection
level.  buffer should be char *, not char **, and the argument ought to
be &buffer so that the function can assign to buffer.

            regards, tom lane

Re: code example for PQgetCopyData

From

Dave Huber

Date:

03 December 2009, 18:35:21

> Where is it blowing up?

I’m sorry, I wasn’t clear. It bombs on the PQgetCopyData call. If I comment out the entire while loop, the program runs fine. If I simply comment out the contents of the while loop…kablooey!

Dave

Re: code example for PQgetCopyData

From

"Bret"

Date:

03 December 2009, 18:55:27

Looks like fun.

Tom added some comments on the PQgetCopyData function.

If your environment allows, put a breapoint in on the line below and look at the vars

while (nLen = PQgetCopyData(pConn, buffer, false) > 0)

perhaps this will get you working

while (nLen = PQgetCopyData(pConn, &buffer, false) > 0)

here's the only sample I found

while (!copydone)
{
! ret = PQgetCopyData(g_conn, &copybuf, false);
! switch (ret) {
! case -1:
! copydone = true;
! break;
! case 0:
! case -2:
! write_msg(NULL, "SQL command to dump the contents of table \"%s\" failed: PQgetCopyData() failed.\n", classname);
! write_msg(NULL, "Error message from server: %s", PQerrorMessage(g_conn));
! write_msg(NULL, "The command was: %s\n", q->data);
! exit_nicely();
! break;
! default:
! archputs(copybuf, fout);
! PQfreemem(copybuf);
! break;
}

From: Dave Huber [mailto:DHuber@letourneautechnologies.com]
Sent: Thursday, December 03, 2009 11:35 AM
To: 'bret_stern@machinemanagement.com'; pgsql-general@postgresql.org
Subject: RE: [GENERAL] code example for PQgetCopyData

> Where is it blowing up?

I’m sorry, I wasn’t clear. It bombs on the PQgetCopyData call. If I comment out the entire while loop, the program runs fine. If I simply comment out the contents of the while loop…kablooey!

Dave

This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This message, together with any attachment, is confidential and may contain privileged information. Any unauthorized review, use, printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message in error, please immediately advise the sender by reply email message to the sender and delete all copies of this message.
THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of similar import, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any attachments are an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau Technologies, Inc., or any of its subsidiaries, affiliates, or any other person or entity.
WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments.

Re: code example for PQgetCopyData

From

Dave Huber

Date:

03 December 2009, 19:54:20

Tom,

Thanks for the help. Setting buffer to a char * fixed the crashing problem.

Now, I have a different issue. The result from PQgetCopyData is always 1 for every row of data returned. Does this not
workfor return data "WITH BINARY"? If I issue the same copy command to a file instead of STDOUT and examine the file
contents,there is most definitely data for each row. The command going to the PQexec function is: 

COPY (SELECT * FROM event_log_table) TO STDOUT WITH BINARY
Or
COPY (SELECT * FROM event_log_table) TO E'C:\\testfile' WITH BINARY

To reiterate, nLen in the following code always == 1:


                pResult = PQexec(pConn, szCopyStr);
                delete [] szCopyStr;

                // make sure we are in the copy out state before reading                        if (PGRES_COPY_OUT ==
PQresultStatus(pResult))
                {
                        // get the data
                        int i = 0;
                        while (nLen = PQgetCopyData(pConn, &buffer, false) > 0)
                        {
                ...


Thanks again.

Dave


Tom wrote:

One thing you're missing is that you should check that the result from
the PQexec actually shows successful entry into COPY_OUT state.
But I think the crash is because you're confused about the indirection
level.  buffer should be char *, not char **, and the argument ought to
be &buffer so that the function can assign to buffer.

                        regards, tom lane



This electronic mail message is intended exclusively for the individual(s) or entity to which it is addressed. This
message,together with any attachment, is confidential and may contain privileged information. Any unauthorized review,
use,printing, retaining, copying, disclosure or distribution is strictly prohibited. If you have received this message
inerror, please immediately advise the sender by reply email message to the sender and delete all copies of this
message.
THIS E-MAIL IS NOT AN OFFER OR ACCEPTANCE: Notwithstanding the Uniform Electronic Transactions Act or any other law of
similarimport, absent an express statement to the contrary contained in this e-mail, neither this e-mail nor any
attachmentsare an offer or acceptance to enter into a contract, and are not intended to bind the sender, LeTourneau
Technologies,Inc., or any of its subsidiaries, affiliates, or any other person or entity. 
WARNING: Although the company has taken reasonable precautions to ensure no viruses are present in this email, the
companycannot accept responsibility for any loss or damage arising from the use of this email or attachments.

Re: code example for PQgetCopyData

From

Tom Lane

Date:

03 December 2009, 20:28:51

Dave Huber <DHuber@letourneautechnologies.com> writes:
> Now, I have a different issue. The result from PQgetCopyData is always
> 1 for every row of data returned. Does this not work for return data
> "WITH BINARY"?

Weird, it should be the row length in bytes.  Are you maybe testing on
empty rows?

            regards, tom lane