Re: Issue with PQdescribePortal to describe a select cursor

["Brijesh Shrivastav" <Bshrivastav(at)esri(dot)com>]

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, "Connection to 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, "Query Table 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 DOUBLE PRECISION, 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,"Error describing 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(at)sss(dot)pgh(dot)pa(dot)us]
> Sent: Monday, August 27, 2007 5:04 PM
> To: Brijesh Shrivastav
> Cc: pgsql-interfaces(at)postgresql(dot)org
> Subject: Re: [INTERFACES] Issue with PQdescribePortal to describe a
> select cursor 
> 
> 
> "Brijesh Shrivastav" <Bshrivastav(at)esri(dot)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
> 
>