Обсуждение: libpq: bind message supplies 2 parameters, but prepared statement requires 1

Hello,

these 2 statements work fine for me on the psql-prompt:

punbb=> select id, username, md5('deadbeef' || password) from users
where id = 7;
 id | username |               md5
----+----------+----------------------------------
  7 | Alex     | b962415469222eeb31e739c3afbc8a4a
(1 row)

punbb=> select username from users where id = 7 and md5('deadbeef' ||
password) = 'b962415469222eeb31e739c3afbc8a4a';
 username
----------
 Alex
(1 row)

However when I try to execute the latter query by my C-program,
then it fails, saying that my bind command supplies 2 arguments
(yes, that's true), but the prepared statement requires 1 argument
(why 1? I don't understand). I have prepared a separate simple
test case, could someone please explain what am I doing wrong?

#include <err.h>
#include <stdio.h>
#include <libpq-fe.h>

#define DB_CONN_STR             "host=/var/www/tmp user=punbb dbname=punbb"
#define SQL_FETCH_USERNAME      "select username from users " \
    "where id = $1 and md5('deadbeef' || password) = '$2'"

int
main(int argc, char *argv[])
{
        PGconn*         conn;
        PGresult*       res;
        const char      *args[2];
        char            username[201];

        if ((conn = PQconnectdb(DB_CONN_STR)) == NULL)
                err(1, "Connect to '%s' failed: out of memory", DB_CONN_STR);

        if (PQstatus(conn) != CONNECTION_OK)
                err(1, "Connect to '%s' failed: %s",
                    DB_CONN_STR, PQerrorMessage(conn));

        if ((res = PQprepare(conn, "sql_fetch_username",
            SQL_FETCH_USERNAME, 2, NULL)) == NULL)
                err(1, "Preparing statement '%s' failed: out of memory",
                    SQL_FETCH_USERNAME);

        if (PQresultStatus(res) != PGRES_COMMAND_OK)
                err(1, "Preparing statement '%s' failed: %s",
                     SQL_FETCH_USERNAME, PQerrorMessage(conn));

        PQclear(res);

        args[0] = "7";
        args[1] = "b962415469222eeb31e739c3afbc8a4a";

        if ((res = PQexecPrepared(conn, "sql_fetch_username",
            2, args, NULL, NULL, 0)) == NULL)
                err(1, "Executing statement '%s' failed: out of memory",
                    SQL_FETCH_USERNAME);

        if (PQresultStatus(res) != PGRES_TUPLES_OK)
                err(1, "Executing statement '%s' failed: %s",
                    SQL_FETCH_USERNAME, PQerrorMessage(conn));
                PQclear(res);

        PQfinish(conn);
        return 0;
}

And here is the error message I get:

laptop72:src {541} ./fetch-user
fetch-user: Executing statement 'select username from users where id =
$1 and md5('deadbeef' || password) = '$2'' failed: ERROR:  bind
message supplies 2 parameters, but prepared statement
"sql_fetch_username" requires 1
: No such file or directory

Thank you
Alex

PS: Using Postgresql 8.1.0 (from packages) on OpenBSD/386 -current

--
http://preferans.de

On Sun, Jul 02, 2006 at 11:17:12PM +0200, Alexander Farber wrote:

<snip>

> However when I try to execute the latter query by my C-program,
> then it fails, saying that my bind command supplies 2 arguments
> (yes, that's true), but the prepared statement requires 1 argument
> (why 1? I don't understand). I have prepared a separate simple
> test case, could someone please explain what am I doing wrong?
>
> #include <err.h>
> #include <stdio.h>
> #include <libpq-fe.h>
>
> #define DB_CONN_STR             "host=/var/www/tmp user=punbb dbname=punbb"
> #define SQL_FETCH_USERNAME      "select username from users " \
>    "where id = $1 and md5('deadbeef' || password) = '$2'"

You've got quotes around the $2, so it's seeing a string, not a
parameter.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.