The following bug has been logged online:
Bug reference: 3244
Logged by: William Lawrance
Email address: [EMAIL PROTECTED]
PostgreSQL version: cvs HEAD
Operating system: Linux
Description: problem with PREPARE
Details:
This program that does "PQprepare" and then
"PQexecPrepared" has worked previously, but doesn't
work now. The error message is"
ERROR: bind message supplies 1 parameters, but
prepared statement "stmtopen" requires 0
The table is defined with 1 row of content:
create table tprep ( cola character(3) );
insert into tprep values('aaa');
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"
int pg_beginTx(PGconn *conn);
int pg_displayResRows(PGresult *res);
int pg_commit(PGconn *conn);
/***********************************************
* main
***********************************************/
int main(int argc, char **argv)
{
PGconn *conn;
PGresult *res;
long resultSts;
const char *conninfo;
char openStmt[100];
const char *paramValues[10];
char p1str[10];
//--- connect to the database
conninfo = "dbname = test";
conn = PQconnectdb(conninfo);
if(PQstatus(conn) != CONNECTION_OK)
{
fprintf(stderr, "Connection to database failed\n");
fprintf(stderr, " %s\n", PQerrorMessage(conn));
exit(1);
}
//--- begin transaction
pg_beginTx(conn);
//--- prepare the declare/open statement
strcpy(openStmt, "declare C1 cursor for select cola"
" from tprep"
" where cola = $1");
res = PQprepare(conn, "stmtopen", openStmt, 0, 0);
resultSts = PQresultStatus(res);
if(resultSts != PGRES_COMMAND_OK)
{
fprintf(stderr, "**** error preparing stmt, sts = %ld\n",
resultSts);
fprintf(stderr, "prepare OPEN failed: %s\n", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
exit(1);
}
PQclear(res);
//---- execute the declare/open statement
strcpy(p1str, "aaa"); paramValues[0] = p1str;
res = PQexecPrepared(conn, "stmtopen", 1, paramValues,
NULL, /* don't need param lengths since text */
NULL, /* default to all text params */
0); /* ask for text results */
resultSts = PQresultStatus(res);
if(resultSts != PGRES_COMMAND_OK)
{
fprintf(stderr, "**** error executing prepared statement, sts =
%ld\n", resultSts);
fprintf(stderr, " %s\n", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
exit(1);
}
PQclear(res);
//---- fetch
res = PQexecParams(conn, "FETCH C1",
0, /* 0 params */
0,
paramValues,
NULL,
NULL,
0);
resultSts = PQresultStatus(res);
if(resultSts != PGRES_TUPLES_OK)
{
fprintf(stderr, "**** error FETCHing\n");
fprintf(stderr, "resultSts = %ld\n", resultSts);
fprintf(stderr, " %s\n", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
exit(1);
}
pg_displayResRows(res);
PQclear(res);
//---- close cursor
res = PQexecParams(conn, "CLOSE C1",
0, /* 0 params */
0,
paramValues,
NULL,
NULL,
0);
resultSts = PQresultStatus(res);
if(resultSts != PGRES_COMMAND_OK)
{
fprintf(stderr, "**** error CLOSEing\n");
fprintf(stderr, "resultSts = %ld\n", resultSts);
fprintf(stderr, " %s\n", PQerrorMessage(conn));
PQclear(res);
PQfinish(conn);
exit(1);
}
//---- commit
pg_commit(conn);
//---- disconnect
PQfinish(conn);
//---- done
exit(0);
}
/***********************************************
* display result rows
***********************************************/
int pg_displayResRows(PGresult *res)
{
int noTuples, rowNo, noCols, colNo, colType, colLeng;
long tblOID, colFormat;
char *colName, *colValue;
noTuples = PQntuples(res);
noCols = PQnfields(res);
for(colNo = 0; colNo < noCols; ++colNo)
{
colName = PQfname(res, colNo);
tblOID = PQftable(res, colNo);
colFormat = PQfformat(res, colNo);
colType = PQftype(res, colNo);
}
for(rowNo = 0; rowNo < noTuples; ++rowNo)
{
printf(" #%d --------\n", rowNo);
for(colNo = 0; colNo < noCols; ++colNo)
{
colValue = PQgetvalue(res, rowNo, colNo);
colLeng = PQgetlength(res, rowNo, colNo);
printf(" name=%s, leng=%d, value='%s'\n", colName, colLeng,
colValue);
}
}
return(0);
}
/***********************************************
* begin transaction
***********************************************/
int pg_beginTx(PGconn *conn)
{
PGresult *res;
res = PQexecParams(conn, "BEGIN",
0, /* no params */
NULL, /* let the backend deduce param
type */
0,
NULL, /* don't need param lengths since
text */
NULL, /* default to all text params */
0); /* ask for text results */
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "BEGIN failed: %s", PQerrorMessage(conn));
PQclear(res);
exit(1);
}
PQclear(res);
return(0);
}
/***********************************************
* commit transaction
***********************************************/
int pg_commit(PGconn *conn)
{
PGresult *res;
res = PQexecParams(conn, "COMMIT",
0, /* no params */
NULL, /* let the backend deduce param
type */
0,
NULL, /* don't need param lengths since
text */
NULL, /* default to all text params */
0); /* ask for text results */
if(PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "COMMIT failed: %s", PQerrorMessage(conn));
PQclear(res);
exit(1);
}
PQclear(res);
return(0);
}
---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
