[soci-users] Fwd: Retrieving multiple rows from a single query in Postgresql

[Deane Yang]

I was able to get this to work with sqlite, but am having difficulty doing
this with postgresql. I've confirmed that SOCI is successfully connecting
to the database. When I ask for the number of rows in the table "trades",
it returns the correct number.

The code that isn't working looks like this:

statement get_trades =
(sql.prepare <<
"select * from trades where trade_date=20090111",
into(record));
get_trades.execute();
while (get_trades.fetch())
{
...
}

An exception is thrown when executing "get_grades.fetch()" and return the
following message:

ERROR: prepared statement "st_1" does not exist

Could someone help me with this?

[Mateusz Loskot]

Post by Deane Yang
I was able to get this to work with sqlite, but am having difficulty doing
this with postgresql. I've confirmed that SOCI is successfully connecting to
the database. When I ask for the number of rows in the table "trades", it
returns the correct number.
statement get_trades =
(sql.prepare <<
"select * from trades where trade_date=20090111",

This query is missing quotes around the date, so it causes
statement preparation failure. Try this:

"select * from trades where trade_date=\'20090111\'"

Post by Deane Yang
An exception is thrown when executing "get_grades.fetch()" and return the
ERROR: prepared statement "st_1" does not exist

This error is misleading, indeed and you should see error like this:

ERROR: operator does not exist: date = integer
LINE 1: select * from trades where trade_date=20090111

The misleading error is due to a slight bug in PostgreSQL backend:
- PQprepare is fails due to invalid query
--- clean_up() is called and statement object deallocated
---- deallocation does not check if there is statement exists
------ DEALLOCATE command fails with exception

Finally, the exception is reported for DEALLOCATE failure instead of
for the original cause of error, statement preparation failure.

A tentative fix is to take these two lines from
postgresql_statement_backend dtor:

https://github.com/SOCI/soci/blob/684be04/src/backends/postgresql/statement.cpp#L42-L43

and move into postgresql_statement_backend::clean_up:

https://github.com/SOCI/soci/blob/684be04/src/backends/postgresql/statement.cpp#L55

This may not work, as result_ is related to statement
execution, not statement preparation.
I'll work on proper fix.

Deane, thanks for reporting this!

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Deane Yang]

Many, many thanks for the extremely helpful and fast reply.

Post by Deane Yang

Post by Deane Yang
I was able to get this to work with sqlite, but am having difficulty

doing

Post by Deane Yang
this with postgresql. I've confirmed that SOCI is successfully

connecting to

Post by Deane Yang
the database. When I ask for the number of rows in the table "trades", it
returns the correct number.
statement get_trades =
(sql.prepare <<
"select * from trades where trade_date=20090111",

This query is missing quotes around the date, so it causes
"select * from trades where trade_date=\'20090111\'"

Post by Deane Yang
An exception is thrown when executing "get_grades.fetch()" and return the
ERROR: prepared statement "st_1" does not exist

ERROR: operator does not exist: date = integer
LINE 1: select * from trades where trade_date=20090111
- PQprepare is fails due to invalid query
--- clean_up() is called and statement object deallocated
---- deallocation does not check if there is statement exists
------ DEALLOCATE command fails with exception
Finally, the exception is reported for DEALLOCATE failure instead of
for the original cause of error, statement preparation failure.
A tentative fix is to take these two lines from
https://github.com/SOCI/soci/blob/684be04/src/backends/postgresql/statement.cpp#L42-L43
https://github.com/SOCI/soci/blob/684be04/src/backends/postgresql/statement.cpp#L55
This may not work, as result_ is related to statement
execution, not statement preparation.
I'll work on proper fix.
Deane, thanks for reporting this!
Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Mateusz Loskot]

Post by Deane Yang
Many, many thanks for the extremely helpful and fast reply.

You're welcome!

It won't be forgotten https://github.com/SOCI/soci/issues/116

Best regards,

[Mateusz Loskot]

Post by Mateusz Loskot

Post by Deane Yang
Many, many thanks for the extremely helpful and fast reply.

You're welcome!
It won't be forgotten https://github.com/SOCI/soci/issues/116

Fixed and will be included in upcoming 3.2.1 bugfix release.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Deane Yang]

Post by Mateusz Loskot

Post by Mateusz Loskot

Post by Deane Yang
Many, many thanks for the extremely helpful and fast reply.

You're welcome!
It won't be forgotten https://github.com/SOCI/soci/issues/116

Fixed and will be included in upcoming 3.2.1 bugfix release.

Thanks again. In any case, SOCI is working beautifully for me right now.

Post by Mateusz Loskot
Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net

[Mateusz Loskot]

Post by Deane Yang

Post by Mateusz Loskot

Post by Mateusz Loskot

Post by Deane Yang
Many, many thanks for the extremely helpful and fast reply.

You're welcome!
It won't be forgotten https://github.com/SOCI/soci/issues/116

Fixed and will be included in upcoming 3.2.1 bugfix release.

Thanks again. In any case, SOCI is working beautifully for me right now.

Glad to hear.

Best regards,
--
Mateusz Loskot, http://mateusz.loskot.net