BUG #3244: problem with PREPARE - Mailing list pgsql-bugs

The following bug has been logged online:

Bug reference:      3244
Logged by:          William Lawrance
Email address:      bill.lawrance@bull.com
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);
}