Thread: PQexecParams and CURSOR
Hello,
I Could not achieve to use CURSOR with PQexecParams ! How to you do ?
Here what I've done:
1)------------------------------------
paramValues[0] = "2"; // This is the parameter for the query
res = PQexec( conn , "DECLARE MY_CURSOR FOR "
"SELECT * FROM GRGL.RANGE_MODIFIER "
"WHERE WEAPON_ID = $1" );
=> ERROR
DECLARE failed: ERROR: There is no parameter 1
2)------------------------------------
then
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);
res = PQexec( conn , "FETCH 1 FROM MY_CURSOR" );
=> Error
FETCH failed: ERROR: no value found for parameter 1
3)--------------------------------------
then
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); );
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
PLEASE, COULD YOU HELP ? THANKS.
Laurent Marzullo
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/
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@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Michael Fuhr
Sent: Sunday, January 16, 2005 1:32 AM
To: Laurent Marzullo
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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
On Mon, Jan 17, 2005 at 11:28:57AM +0100, Laurent Marzullo wrote: > // res = PQexec( conn , "FETCH 1 FROM MY_CURSOR" ); The above should work if you uncomment it and comment out or remove the other two attempts to execute FETCH. > /* > res = PQexecParams(conn, > "FETCH 1 FROM MY_CURSOR", > 0, > NULL, > NULL, > NULL, > NULL, > 0); > */ The above should also work if you uncomment it and comment out or remove the other two. > res = PQexecParams(conn, > "FETCH 1 FROM MY_CURSOR", > 1, > NULL, > paramValues, > NULL, > NULL, > 0); This call fails because you're passing a parameter that the FETCH statement doesn't need (you're passing 1 as the nParams argument and the parameter list as paramValues). Use one of the other two methods, either PQexec() or PQexecParams() with nParams set to 0 and pass NULL instead of paramValues. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Mon, Jan 17, 2005 at 11:28:57AM +0100, Laurent Marzullo wrote: > > // res = PQexec( conn , "FETCH 1 FROM MY_CURSOR" ); > The above should work if you uncomment it and comment out or remove > the other two attempts to execute FETCH. On my machine (2.6.7-gentoo-r9) and postgreSQL (postmaster --version) : 7.4.6 the command above gave: FETCH failed: ERROR: no value found for parameter 1 > > /* > > res = PQexecParams(conn, > > "FETCH 1 FROM MY_CURSOR", > > 0, > > NULL, > > NULL, > > NULL, > > NULL, > > 0); > > */ > The above should also work if you uncomment it and comment out or > remove the other two. This one alose gave the same result as the preceding one: FETCH failed: ERROR: no value found for parameter 1 My I install a version greater than 7.4.6 ? -- Michael Fuhr http://www.fuhr.org/~mfuhr/
"Laurent Marzullo" <laurent.marzullo@atosorigin.com> writes:
> Ok so here is the full TEST program I try to run:
I think you are running into an issue that was fixed during the 8.0
development cycle. From the CVS logs:
2004-08-01 21:30 tgl
Allow DECLARE CURSOR to take parameters from the portal in which it
is executed. Previously, the DECLARE would succeed but subsequent
FETCHes would fail since the parameter values supplied to DECLARE
were not propagated to the portal created for the cursor. In
support of this, add type Oids to ParamListInfo entries, which
seems like a good idea anyway since code that extracts a value can
double-check that it got the type of value it was expecting.
Oliver Jowett, with minor editorialization by Tom Lane.
So what you are trying to do will work in 8.0 but not in 7.4.
regards, tom lane
On Mon, Jan 17, 2005 at 04:44:50PM +0100, Laurent Marzullo wrote: > > > > > > // res = PQexec( conn , "FETCH 1 FROM MY_CURSOR" ); > > > > The above should work if you uncomment it and comment out or remove > > the other two attempts to execute FETCH. > > On my machine (2.6.7-gentoo-r9) and postgreSQL (postmaster --version) : > 7.4.6 > > the command above gave: > FETCH failed: ERROR: no value found for parameter 1 The problem appears to be in the 7.4.6 backend. I did some tests with 8.0.0rc5 and 7.4.6 and got the following results: good 8.0.0rc5 backend, 8.0.0rc5 client good 8.0.0rc5 backend, 7.4.6 client bad 7.4.6 backend, 8.0.0rc5 client bad 7.4.6 backend, 7.4.6 client If I set log_error_verbosity to "verbose" then the 7.4.6 server logs the following: ERROR: 42704: no value found for parameter 1 LOCATION: ExecEvalParam, execQual.c:518 I think the following message to pgsql-commiters announces the fix that was applied to the development branch, which will soon be released as 8.0.0: http://archives.postgresql.org/pgsql-committers/2004-08/msg00028.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/
ok. Thanks all for you help. I will take a look to 8.0 Laurent Marzullo -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Michael Fuhr Sent: Monday, January 17, 2005 5:59 PM To: Laurent Marzullo Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PQexecParams and CURSOR On Mon, Jan 17, 2005 at 04:44:50PM +0100, Laurent Marzullo wrote: > > > > > > // res = PQexec( conn , "FETCH 1 FROM MY_CURSOR" ); > > > > The above should work if you uncomment it and comment out or remove > > the other two attempts to execute FETCH. > > On my machine (2.6.7-gentoo-r9) and postgreSQL (postmaster --version) : > 7.4.6 > > the command above gave: > FETCH failed: ERROR: no value found for parameter 1 The problem appears to be in the 7.4.6 backend. I did some tests with 8.0.0rc5 and 7.4.6 and got the following results: good 8.0.0rc5 backend, 8.0.0rc5 client good 8.0.0rc5 backend, 7.4.6 client bad 7.4.6 backend, 8.0.0rc5 client bad 7.4.6 backend, 7.4.6 client If I set log_error_verbosity to "verbose" then the 7.4.6 server logs the following: ERROR: 42704: no value found for parameter 1 LOCATION: ExecEvalParam, execQual.c:518 I think the following message to pgsql-commiters announces the fix that was applied to the development branch, which will soon be released as 8.0.0: http://archives.postgresql.org/pgsql-committers/2004-08/msg00028.php -- Michael Fuhr http://www.fuhr.org/~mfuhr/ ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)