Обсуждение: Assistance with libpq
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 */
{
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;
}
return result;
}
int
main (int argc, char* argv[])
{
PGconn* conn;
PGresult* res;
int rec_count;
int row;
int col;
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);
}
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 */
{
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;
}
return result;
}
int
main(int argc, char* argv[])
{
PGconn* conn;
PGresult* res;
int rec_count;
int row;
int col;
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);
}
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
--
Jesse
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