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, "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
>
>