Assistance with libpq - Mailing list pgsql-admin

Hello,

 

I have an issue that is driving me crazy. I am using libpq on linux in C. The insert code below inserts one key/data pair record with as both as BYTEA into database test2:

 

PGresult* put_data_to_test2(PGconn* conn, int key_size, const char* const key, int data_size, const char* const data)
{
 PGresult* result;
 const char* const paramValues[] = { key, data };
 const int nParams = sizeof(paramValues) / sizeof(paramValues[0]);
 const int paramLenghts[] = {key_size, data_size};
 const int paramFormats[] = {1, 1}; /* binary */
 const int resultFormat = 0; /* text */

 result = PQexecParams(conn, "insert into test2 (key, data) values ($1::bytea, $2::bytea)", nParams, NULL, /* Types of parameters, unused as casts will define types */ paramValues, paramLenghts, paramFormats, resultFormat);
 return result;
}

int
main (int argc, char* argv[])
{
 PGconn*  conn;
 PGresult* res;
 int  rec_count;
 int  row;
 int  col;

 conn = PQconnectdb("dbname=postgres host=localhost user=tc port=3333");

 if (PQstatus(conn) == CONNECTION_BAD) {
  puts("We were unable to connect to the database");
  exit(0);
 }
 char* key = "This is the key";
 char* data = "This is the data";
 res = put_data_to_test2
 (
 conn,
 sizeof(key),
 key,
 sizeof(data),
 data
 );
 if ((!res) || (PQresultStatus(res) != PGRES_COMMAND_OK)){
  fprintf(stderr, "INSERT INTO command failed\n");
  PQclear(res);
  exit_nicely(conn);
 }
 PQfinish(conn);
 exit(0);
}

 

The select code in which I use a invalid key value returns all records in the database:

 

PGresult* get_data_from_test2(PGconn* conn, int key_size, const char* const key)
{
 PGresult* result;
 const char* const paramValues[] = {key};
 const int nParams = 1;
 const int paramLenghts[] = {key_size};
 const int paramFormats[] = {1}; /* binary */
 const int resultFormat = 0; /* text */

 result = PQexecParams(conn, "select * from test2 where data = $1::bytea", nParams, NULL, paramValues, paramLenghts, paramFormats, resultFormat);
 return result;
}

int
main(int argc, char* argv[])
{
 PGconn*  conn;
 PGresult* res;
 int  rec_count;
 int  row;
 int  col;

 conn = PQconnectdb("dbname=postgres host=localhost user=tc port=3333");

 if (PQstatus(conn) == CONNECTION_BAD) {
  puts("We were unable to connect to the database");
  exit(0);
 }
 char* key = "This is not the key";
 res = get_data_from_test2(conn, sizeof(key), key);
 if ((PQresultStatus(res) == PGRES_EMPTY_QUERY)){
  printf("Result returned no results\n");
  PQclear(res);
  exit_nicely(conn);
 }
 if ((!res) || (PQresultStatus(res) != PGRES_TUPLES_OK)){
  fprintf(stderr, "SELECT FROM command failed\n");
  fprintf(stderr, PQresultErrorMessage(res));
  PQclear(res);
  exit_nicely(conn);
 }
 printf("Numbers of rows = %d\n", PQntuples(res));
 PQclear(res);
 PQfinish(conn);
 exit(0);
}

 

This code should return no results, yet I get all records and no errors. My endstate in a key/data pair database that takes data as marshalled binaries and returns them in there original inserted format.

 

Thank you for any insites,

Jesse



--
Jesse