Thread: BUG #1234: prepared statements and libpq don't work as expected

The following bug has been logged online:

Bug reference:      1234
Logged by:          Sven Riedel

Email address:      sr@gimp.org

PostgreSQL version: 8.0 Beta

Operating system:   Linux, Kernel 2.6

Description:        prepared statements and libpq don't work as expected

Details:

Hi,
I'm trying to set up and invoke a prepared query over the C API. In short
(code below): I set up the prepared query with PQexec() without having an
error returned, and subsequent calls to PQexecPrepared() fails. The server
writes to it's logs that the name of the called prepared query is unknown,
when PQexecPrepared() is invoked.

The code in question looks like this:

static const char *prepareuserquery =
   "PREPARE UserQuery (text,text) AS SELECT UserID FROM Users WHERE Name=$1
AND Password=$2";
static const char *connectinfo = "host=/usr/local/pgsql/socket port=5432
dbname=mydb user=myuser password=mypassword connect_timeout=10";

char *username = safe_alloc( 160 * sizeof(char) );
char *password = safe_alloc( 160 * sizeof(char) );
PGresult *result = NULL;
char *parameters[2] = {username, password };
int param_format[2] = {0, 0};
long int uid;


if( (sqlhandle = PQconnectdb( connectinfo ) ) == NULL ) {
      fprintf( stderr, "Cannot allocate sql handle\n" );
      exit(0);
}

if( PQstatus( sqlhandle ) != CONNECTION_OK ) {
      fprintf( stderr, "Connection to db failed: %s\n", PQerrorMessage(
sqlhandle ) );
      exit(0);
}

if( ( result = PQexec( sqlhandle, prepareuserquery ) ) == NULL ) {
      fprintf( stderr, "Cannot prepare statement. Error: %s\n",
PQerrorMessage( sqlhandle) );
      exit(0);
}
switch( PQresultStatus( result ) ) {
      case PGRES_COMMAND_OK: fprintf( stderr, "Prepared query ok\n" );break;
      case PGRES_TUPLES_OK: break;
      default: fprintf( stderr, "Something funny happened while preparing
statement!\n" );
         exit(0);
}

while( !done ) {
      fprintf( stderr, "Debug: %s\n", ((const char* const *)parameters)[0]
);
      if( ( result = PQexecPrepared( sqlhandle, "UserQuery", 2, (const char*
const *)parameters, NULL, param_format, 1 ) ) == NULL ) {
         fprintf( stderr, "Resultset of query is NULL!\n" );
      }

      if( PQntuples( result ) == 0 ) {
         uid = -1;
      } else {
         uid = *(long long int*)PQgetvalue( result, 0, 0 );
      }

      PQclear( result );
      result = NULL;
}

"PostgreSQL Bugs List" <pgsql-bugs@postgresql.org> writes:
> The code in question looks like this:

> static const char *prepareuserquery =
>    "PREPARE UserQuery (text,text) AS SELECT UserID FROM Users WHERE Name=$1
> AND Password=$2";

>       if( ( result = PQexecPrepared( sqlhandle, "UserQuery", 2, (const char*
> const *)parameters, NULL, param_format, 1 ) ) == NULL ) {

You've got an identifier-case problem.  The statement name argument to
PQexecPrepared is taken literally (as if double-quoted), but the name
appearing in the PREPARE command will have been effectively folded to
lower case.

            regards, tom lane