Re: Issue with PQdescribePortal to describe a select cursor

Tom,

Please see the test program below that creates a table and then
queries it using DECLARE CURSOR statement. It only describes the
cursor and doesn't really fetch any data. It appears to me I
may be forced to execute the query before describe that will
not be possible for me in our current application (in many
cases I need input bind parameter data from client application
before executing the query).

Thanks,
Brijesh


#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>

#  include <process.h>
#  include <windows.h>
#include "libpq-fe.h"

#define TEST_TABLE  "pgtest"
#define SE_SUCCESS  0
#define EOS  '\0'

//Prototypes

LONG createTable(PGconn *conn);
LONG queryTable(PGconn *conn);

int main(int argc, char* argv[])
{
 char         host[32],database[32],              user[32],password[32],errmsg[256]; int          rc; PGconn
*conn;
 if (argc < 5)   printf("incorrect arguments: provide <server> <database> <user> <password>  \n"); else {
strcpy(host,argv[1]);  strcpy(database,argv[2]);   strcpy(user,argv[3]);   strcpy(password,argv[4]); } 
  conn = PQsetdbLogin (host,                                     NULL,                                     NULL,
                            NULL,                                     database,
user,                                    password); 
 /* Check to see that the  connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) {   fprintf(stderr,
"Connectionto postgres failed.\n");   sprintf(errmsg,"%s",PQerrorMessage(conn));   fprintf(stderr,"ERRMSG:
%s\n",errmsg);} 
 //Create test table rc = createTable(conn); if (rc != SE_SUCCESS) {   fprintf(stderr, "Create Table Failed(%d)\n",rc);
  PQfinish(conn);   exit(1); }    //Query test table rc =  queryTable(conn); if (rc != SE_SUCCESS) {   fprintf(stderr,
"QueryTable Failed(%d):\n",rc);    PQfinish(conn);   exit(1); }  
 PQfinish(conn);

}

LONG createTable(PGconn *conn)
{
 char    sql[512],*msg; PGresult       *res;
 //Drop table if one already exists
 sprintf(sql," DROP TABLE %s ", TEST_TABLE);
 res = PQexec(conn, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) {       msg = PQresultErrorMessage(res);
if(msg != NULL && msg[0] != EOS  )               fprintf(stderr,"Error dropping table: %s\n",msg); 
 }
 sprintf(sql," CREATE TABLE %s (shortcol smallint, intcol INTEGER, int64col bigint, realcol real,  "   " doublecol
DOUBLEPRECISION, strcol VARCHAR(256),  blobcol BYTEA, timecol timestamp)", TEST_TABLE); 
 res = PQexec(conn, sql); if (PQresultStatus(res) != PGRES_COMMAND_OK) {       msg = PQresultErrorMessage(res);
if(msg != NULL && msg[0] != EOS  )               fprintf(stderr,"Error dropping table: %s\n",msg);       return(-1); 
 } else     printf("Successfully created %s table.\n",TEST_TABLE);
 return(SE_SUCCESS);
}




LONG queryTable(PGconn *conn)
{
 int                   numOutputCols; char                *col_name,sql[512],*cursorName =
"pgcursor",*msg,*sqlstate,*stmt= "pgstmt"; int                  num_rows = 10,column_index,dbtype; PGresult       *res; 
 // Start transaction res = PQexec(conn,"BEGIN");
 sprintf(sql," DECLARE %s CURSOR FOR SELECT  shortcol, intcol, int64col, realcol,  "   " doublecol, timecol,
blobcol,strcol FROM  %s", cursorName,TEST_TABLE); 
  res = PQprepare(conn, stmt,sql, 0 ,NULL);   if (PQresultStatus(res) != PGRES_COMMAND_OK &&       PQresultStatus(res)
!=PGRES_TUPLES_OK)   {       sqlstate = PQresultErrorField(res, PG_DIAG_SQLSTATE);       msg =
PQresultErrorMessage(res);      if (msg != NULL && msg[0] != EOS  )         fprintf(stderr,"Error preparing select
statement:%s(%s)\n",msg,sqlstate? sqlstate:"");       return(-1); 
   }

   res = PQdescribePortal (conn,cursorName);   if (PQresultStatus(res) != PGRES_COMMAND_OK &&       PQresultStatus(res)
!=PGRES_TUPLES_OK)   {       msg = PQresultErrorMessage(res);       if (msg != NULL && msg[0] != EOS  )
fprintf(stderr,"Errordescribing select statement: %s\n",msg);       return(-1); 
   }
   numOutputCols  = PQnfields(res);
   // Get column information   for (column_index = 0; (column_index < numOutputCols) ; column_index++)   {     LONG
colmod=0;       /* Get the column name. */     col_name = PQfname(res,column_index);       dbtype =
PQftype(res,column_index);     if (col_name)         fprintf(stderr,"%s - %d \n ",col_name,dbtype);  } 
 res = PQexec(conn,"END");
 printf("Successfully queried and described %s.\n",TEST_TABLE); return(SE_SUCCESS);

}






> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: Monday, August 27, 2007 5:04 PM
> To: Brijesh Shrivastav
> Cc: pgsql-interfaces@postgresql.org
> Subject: Re: [INTERFACES] Issue with PQdescribePortal to describe a
> select cursor
>
>
> "Brijesh Shrivastav" <Bshrivastav@esri.com> writes:
> > Is there any known issue with using PQdescribePortal() against
> > a declared select server side cursor?
>
> Should work.  Can you provide a self-contained example of
> your problem?
>
>             regards, tom lane
>
>