Re: Assistance with libpq

Jesse Johnson wrote:
> 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:

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.

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