Assistance with libpq

[Jesse Johnson]

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

[Albe Laurenz]

I have an issue that is driving me crazy. I am using libpq on linux in C. The insert code below

Your code as you posted it is not complete (no #includes, undefined "exit_nicely").
This makes it harder to help you.

PGresult* put_data_to_test2(PGconn* conn, int key_size, const char* const key, int data_size, const
char* const data)
{

[...]

const int paramLenghts[] = {key_size, data_size};

[...]

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[])
{

[...]

res = put_data_to_test2
(
conn,
sizeof(key),
key,
sizeof(data),
data
);

[...]

This is clearly wrong.
sizeof(key) is sizeof(char *), but you mean something
like strlen(key) (read below for caveats).

Did you try to query the database after your program ran?
The inserted values will be truncated at 4 or 8 bytes,
depending on your architecture.

PGresult* get_data_from_test2(PGconn* conn, int key_size, const char* const key)
{

[...]

const int paramLenghts[] = {key_size};

[...]

result = PQexecParams(conn, "select * from test2 where data = $1::bytea", nParams, NULL, paramValues,
paramLenghts, paramFormats, resultFormat);
return result;
}
int
main(int argc, char* argv[])
{

[...]

res = get_data_from_test2(conn, sizeof(key), key);

[...]

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.

This program has the same problem, sizeof() instead of strlen().
So you should actually get back the truncated values
that the first program has inserted.

Looks like you inserted several key strings whose first couple
of bytes are identical.

Have you considered the possibility that some of the binary
strings you want to insert might contain byte zero?
If that is the case, strlen(), which I suggested above,
would not measure the length correctly.
You'd have to track the length in some other way.

Yours,
Laurenz Albe