Re: PQexecParams and CURSOR

["Laurent Marzullo" <laurent.marzullo@xxxxxxxxxxxxxx>]

Hello,

Ok so here is the full TEST program I try to run:
(It's a cut/paste + modification of program from PostgreSQL doc)

Thanks for any further help.
Laurent Marzullo

===================================================================
// vim:set ts=4 sts=4 sw=4 expandtab:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"

static void
exit_nicely(PGconn *conn)
{
        PQfinish(conn);
        exit(1);
}

int
main(int argc, char **argv)
{
        const char *conninfo;
        PGconn     *conn;
        PGresult   *res;
        const char *paramValues[1];
        int                     i,
                                j;
        int                     i_fnum,
                                t_fnum,
                                b_fnum;

        if (argc > 1)
                conninfo = argv[1];
        else
                conninfo = "dbname = grgl";

        /* Make a connection to the database */
        conn = PQconnectdb(conninfo);

        /* Check to see that the backend connection was successfully made */
        if (PQstatus(conn) != CONNECTION_OK)
        {
                fprintf(stderr, "Connection to database '%s' failed.\n",
PQdb(conn));
                fprintf(stderr, "%s", PQerrorMessage(conn));
                exit_nicely(conn);
        }

        /* Here is our out-of-line parameter value */
        paramValues[0] = "2";

        PQexec( conn , "BEGIN" );
        res = PQexecParams(conn,
                "DECLARE my_cursor CURSOR FOR SELECT * FROM
grgl.range_modifier WHERE weapon_id = $1",
                1,
                NULL,
                paramValues,
                NULL,
                NULL,
                0);
        if (PQresultStatus(res) != PGRES_COMMAND_OK)
        {
                fprintf(stderr, "DECLARE failed: %s", PQerrorMessage(conn));
                PQclear(res);
                exit_nicely(conn);
        }
        //PQclear(res);

        // res = PQexec( conn , "FETCH 1 FROM MY_CURSOR" );
        /*
        res = PQexecParams(conn,
                "FETCH 1 FROM MY_CURSOR",
                0,
                NULL,
                NULL,
                NULL,
                NULL,
                0);
        */
        res = PQexecParams(conn,
                "FETCH 1 FROM MY_CURSOR",
                1,
                NULL,
                paramValues,
                NULL,
                NULL,
                0);
        if (PQresultStatus(res) != PGRES_TUPLES_OK)
        {
                fprintf(stderr, "FETCH failed: %s", PQerrorMessage(conn));
                PQclear(res);
                exit_nicely(conn);
        }

        /* Use PQfnumber to avoid assumptions about field order in result */
        i_fnum = PQfnumber(res, "weapon_id");
        t_fnum = PQfnumber(res, "lower_bound");
        b_fnum = PQfnumber(res, "upper_bound");

        for (i = 0; i < PQntuples(res); i++)
        {
                char       *iptr;
                char       *tptr;
                char       *bptr;
                int                     blen;
                uint64_t                ival;

                /* Get the field values (we ignore possibility they are
null!) */
                iptr = PQgetvalue(res, i, i_fnum);
                tptr = PQgetvalue(res, i, t_fnum);
                bptr = PQgetvalue(res, i, b_fnum);

                ival = strtoll(iptr , 0 , 10 );
                blen = PQgetlength(res, i, b_fnum);

                printf("tuple %d: got\n", i);
                printf(" i = (%d bytes) %d\n",
                           PQgetlength(res, i, i_fnum), ival);
                printf(" t = (%d bytes) '%s'\n",
                           PQgetlength(res, i, t_fnum), tptr);
                printf(" b = (%d bytes) '%s'\n",
                           PQgetlength(res, i, b_fnum), bptr);
                printf("\n\n");
        }

        PQclear(res);

        /* close the connection to the database and cleanup */
        PQfinish(conn);

        return 0;
}




-----Original Message-----
From: pgsql-general-owner@xxxxxxxxxxxxxx
[mailto:pgsql-general-owner@xxxxxxxxxxxxxx]On Behalf Of Michael Fuhr
Sent: Sunday, January 16, 2005 1:32 AM
To: Laurent Marzullo
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re:  PQexecParams and CURSOR


On Fri, Jan 14, 2005 at 12:31:24PM +0100, Laurent Marzullo wrote:

> paramValues[0] = "2";	// This is the parameter for the query
>
> res = PQexecParams( conn ,
>   "DECLARE MY_CURSOR FOR "
>   "SELECT * FROM GRGL.RANGE_MODIFIER "
>   "WHERE WEAPON_ID = $1",
>   1,
>   NULL,
>   paramValues,
>   NULL,
>   NULL,
>   0);

Are you checking the result status of this command?  If so, what
does your error checking code look like?  In 8.0.0rc5 this command
fails due to the missing keyword CURSOR before FOR.

> res = PQexec( conn , "FETCH 1 FROM MY_CURSOR" );
>
> => Error
> FETCH failed: ERROR:  no value found for parameter 1

Again, what does your error checking code look like?  If I correct
the syntax error in the DECLARE statement above then this query
succeeds.

> res = PQexecParams( conn ,
>   "FETCH 1 FROM MY_CURSOR",
>   1,
>   NULL,
>   paramValues,
>   NULL,
>   NULL,
>   0);
>
> => Error:
> FETCH failed: ERROR:  bind message supplies 1 parameters, but prepared
> statement "" requires 0

The error tells you what's wrong: you're supplying parameters that
the SQL statement isn't looking for.  Change the nParams value from
1 to 0 and pass NULL instead of paramValues, or use PQexec() instead
of PQexecParams() since you're not passing any parameters.

If you still have trouble then please post a minimal but complete
program so we can see everything you're doing.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend