Thread: Problems using SQLFetch on prepared INSERT with RETURNING clause
Hi,
I am having some difficulties with a prepared INSERT statement with a returning clause. I made a small example that
reproducesthe problem:
One database: CREATE DATABASE my_test_db
One table: CREATE TABLE my_test_table(mycol integer)
The query I am attempting to use: INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint
First i use SQLPrepare to prepare my insert statement. After this, I use SQLBindCol to bind a 64-bit integer to the
resultthat would be xmin. Then I use SQLBindParam to bind a 32 bit integer variable for the input to the column.
Then I call SQLExecute and SQLFetch. The first time it works fine, and xmin is returned into the bound variable. Then I
callSQLMoreResults until all results are consumed.
After this I call SQLExecute and SQLFetch again, and here I would expect another row to be inserted, and the xmin for
thatrow to be returned on SQLFetch.
The new row is indeed inserted, but SQLFetch fails with SQLSTATE 07005, native error 15 and message "Null statement
resultin PGAPI_ExtendedFetch."
I am using the PostgreSQL Unicode(x64) driver version on Windows 7. I tried versions 9.03.02.10 and the just released
9.03.03.00.
Any ideas? I'll paste my complete test program (C++, uses boost unit test) below.
//---------------------------------------------------------------------
#include <boost/test/unit_test.hpp>
#include <sql.h>
namespace
{
SQLHENV henv = NULL;
SQLHDBC hdbc = NULL;
SQLHANDLE hstmt = NULL;
SQLWCHAR sqlstate[25];
SQLWCHAR message[255];
SQLINTEGER native_error;
//This test program will create a database named my_test_db with one table, my_test_table, containing a single
integercolumn named mycol.
SQLWCHAR crete_db_sql[] = L"CREATE DATABASE my_test_db";
SQLWCHAR create_table_sql[] = L"CREATE TABLE my_test_table(mycol integer)";
//It will attempt to prepare a query that inserts an integer, and returns the xmin value as a bigint.
//This will work fine the first time, but subsequent calls to SQLExec will fail on the SQLFetch that should fetch
xmin.
SQLWCHAR insert_sql[] = L"INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint";
SQLINTEGER input = 0;
SQLBIGINT xmin_output = 0;
std::wstring get_connection_string(const std::wstring& dbname)
{
return L"Driver={PostgreSQL Unicode(x64)};Server=127.0.0.1;Port=5432;Database=" + dbname +
L";Uid=postgres;Pwd=password;";
}
}
#define ODBC_CHECK(x) if (!SQL_SUCCEEDED(x)) { \
BOOST_CHECK_IMPL(false, BOOST_TEST_STRINGIZE(x), CHECK, CHECK_PRED); \
SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, &native_error, message, 255, nullptr); \
std::wcerr << L"SQLSTATE: " << sqlstate << L" NATIVE ERROR: " << native_error << std::endl << L"MESSAGE: " <<
message<< std::endl; }
BOOST_AUTO_TEST_CASE( aaa_postgres_odbc )
{
//------Boring setup code that creates the database etc.
BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv)));
BOOST_CHECK(SQL_SUCCEEDED(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
reinterpret_cast<void*>(SQL_OV_ODBC3), 0)));
BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc)));
BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"postgres").c_str()),
SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
ODBC_CHECK(SQLExecDirect(hstmt, crete_db_sql, SQL_NTS));
BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
//------Database should now be set up, some more boring code to create our table goes here
BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"my_test_db").c_str()),
SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
ODBC_CHECK(SQLExecDirect(hstmt, create_table_sql, SQL_NTS));
ODBC_CHECK(SQLPrepare(hstmt, insert_sql, SQL_NTS));
ODBC_CHECK(SQLBindCol(hstmt, 1, SQL_C_SBIGINT, &xmin_output, 0, nullptr));
ODBC_CHECK(SQLBindParam(hstmt, 1, SQL_C_SLONG, SQL_PARAM_INPUT, 0, 0, &input, nullptr));
//Execute first insert
++input;
ODBC_CHECK(SQLExecute(hstmt));
ODBC_CHECK(SQLFetch(hstmt));
BOOST_CHECK(xmin_output> 0);
while (SQL_SUCCEEDED(SQLMoreResults(hstmt)));
//Execute second insert
++input;
ODBC_CHECK(SQLExecute(hstmt));
ODBC_CHECK(SQLFetch(hstmt)); //This is where it fails
BOOST_CHECK(xmin_output> 0);
BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_ENV, henv)));
}
Hi again,
I'm not an ODBC expert, but I would think the below is an error, either in the ODBC driver or maybe even somewhere
else.If someone agrees that this should work, I can take a deeper look at fixing it.
----------------------------------------
> From: nukemd@hotmail.com
> To: pgsql-odbc@postgresql.org
> Subject: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
> Date: Sun, 18 May 2014 18:22:15 +0200
>
> Hi,
>
> I am having some difficulties with a prepared INSERT statement with a returning clause. I made a small example that
reproducesthe problem:
>
> One database: CREATE DATABASE my_test_db
> One table: CREATE TABLE my_test_table(mycol integer)
>
> The query I am attempting to use: INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint
>
> First i use SQLPrepare to prepare my insert statement. After this, I use SQLBindCol to bind a 64-bit integer to the
resultthat would be xmin. Then I use SQLBindParam to bind a 32 bit integer variable for the input to the column.
>
> Then I call SQLExecute and SQLFetch. The first time it works fine, and xmin is returned into the bound variable. Then
Icall SQLMoreResults until all results are consumed.
>
> After this I call SQLExecute and SQLFetch again, and here I would expect another row to be inserted, and the xmin for
thatrow to be returned on SQLFetch.
>
> The new row is indeed inserted, but SQLFetch fails with SQLSTATE 07005, native error 15 and message "Null statement
resultin PGAPI_ExtendedFetch."
>
> I am using the PostgreSQL Unicode(x64) driver version on Windows 7. I tried versions 9.03.02.10 and the just released
9.03.03.00.
>
> Any ideas? I'll paste my complete test program (C++, uses boost unit test) below.
>
> //---------------------------------------------------------------------
> #include <boost/test/unit_test.hpp>
> #include <sql.h>
>
> namespace
> {
> SQLHENV henv = NULL;
> SQLHDBC hdbc = NULL;
> SQLHANDLE hstmt = NULL;
>
> SQLWCHAR sqlstate[25];
> SQLWCHAR message[255];
> SQLINTEGER native_error;
>
> //This test program will create a database named my_test_db with one table, my_test_table, containing a single
integercolumn named mycol.
> SQLWCHAR crete_db_sql[] = L"CREATE DATABASE my_test_db";
> SQLWCHAR create_table_sql[] = L"CREATE TABLE my_test_table(mycol integer)";
> //It will attempt to prepare a query that inserts an integer, and returns the xmin value as a bigint.
> //This will work fine the first time, but subsequent calls to SQLExec will fail on the SQLFetch that should fetch
xmin.
> SQLWCHAR insert_sql[] = L"INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint";
>
> SQLINTEGER input = 0;
> SQLBIGINT xmin_output = 0;
>
> std::wstring get_connection_string(const std::wstring& dbname)
> {
> return L"Driver={PostgreSQL Unicode(x64)};Server=127.0.0.1;Port=5432;Database=" + dbname +
L";Uid=postgres;Pwd=password;";
> }
> }
>
> #define ODBC_CHECK(x) if (!SQL_SUCCEEDED(x)) { \
> BOOST_CHECK_IMPL(false, BOOST_TEST_STRINGIZE(x), CHECK, CHECK_PRED); \
> SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, &native_error, message, 255, nullptr); \
> std::wcerr << L"SQLSTATE: " << sqlstate << L" NATIVE ERROR: " << native_error << std::endl << L"MESSAGE: " << message
<<std::endl; }
>
> BOOST_AUTO_TEST_CASE( aaa_postgres_odbc )
> {
>
> //------Boring setup code that creates the database etc.
> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv)));
> BOOST_CHECK(SQL_SUCCEEDED(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
> reinterpret_cast<void*>(SQL_OV_ODBC3), 0)));
>
> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc)));
> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"postgres").c_str()),
> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>
> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
> ODBC_CHECK(SQLExecDirect(hstmt, crete_db_sql, SQL_NTS));
> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
> //------Database should now be set up, some more boring code to create our table goes here
>
> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"my_test_db").c_str()),
> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
> ODBC_CHECK(SQLExecDirect(hstmt, create_table_sql, SQL_NTS));
>
> ODBC_CHECK(SQLPrepare(hstmt, insert_sql, SQL_NTS));
>
> ODBC_CHECK(SQLBindCol(hstmt, 1, SQL_C_SBIGINT, &xmin_output, 0, nullptr));
> ODBC_CHECK(SQLBindParam(hstmt, 1, SQL_C_SLONG, SQL_PARAM_INPUT, 0, 0, &input, nullptr));
>
> //Execute first insert
> ++input;
> ODBC_CHECK(SQLExecute(hstmt));
> ODBC_CHECK(SQLFetch(hstmt));
> BOOST_CHECK(xmin_output> 0);
>
> while (SQL_SUCCEEDED(SQLMoreResults(hstmt)));
> //Execute second insert
> ++input;
> ODBC_CHECK(SQLExecute(hstmt));
> ODBC_CHECK(SQLFetch(hstmt)); //This is where it fails
> BOOST_CHECK(xmin_output> 0);
>
> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>
> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_ENV, henv)));
> }
>
> --
> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc
On 05/24/2014 06:47 PM, John Smith wrote:
> Hi again,
>
> I'm not an ODBC expert, but I would think the below is an error, either in the ODBC driver or maybe even somewhere
else.If someone agrees that this should work, I can take a deeper look at fixing it.
Yeah, it does look like a bug at a quick glance. Could you refactor your
test program into a regression test, like the ones in the psqlodbc's
test/src/* source tree, please? That would make it easier for me and
others to run and debug.
Does it make a difference if you use something else than "xmin" in the
test case? Like, just return a bigint constant in the RETURNING clause.
I would imagine it does not, but you never know..
> ----------------------------------------
>> From: nukemd@hotmail.com
>> To: pgsql-odbc@postgresql.org
>> Subject: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
>> Date: Sun, 18 May 2014 18:22:15 +0200
>>
>> Hi,
>>
>> I am having some difficulties with a prepared INSERT statement with a returning clause. I made a small example that
reproducesthe problem:
>>
>> One database: CREATE DATABASE my_test_db
>> One table: CREATE TABLE my_test_table(mycol integer)
>>
>> The query I am attempting to use: INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint
>>
>> First i use SQLPrepare to prepare my insert statement. After this, I use SQLBindCol to bind a 64-bit integer to the
resultthat would be xmin. Then I use SQLBindParam to bind a 32 bit integer variable for the input to the column.
>>
>> Then I call SQLExecute and SQLFetch. The first time it works fine, and xmin is returned into the bound variable.
ThenI call SQLMoreResults until all results are consumed.
>>
>> After this I call SQLExecute and SQLFetch again, and here I would expect another row to be inserted, and the xmin
forthat row to be returned on SQLFetch.
>>
>> The new row is indeed inserted, but SQLFetch fails with SQLSTATE 07005, native error 15 and message "Null statement
resultin PGAPI_ExtendedFetch."
>>
>> I am using the PostgreSQL Unicode(x64) driver version on Windows 7. I tried versions 9.03.02.10 and the just
released9.03.03.00.
>>
>> Any ideas? I'll paste my complete test program (C++, uses boost unit test) below.
>>
>> //---------------------------------------------------------------------
>> #include <boost/test/unit_test.hpp>
>> #include <sql.h>
>>
>> namespace
>> {
>> SQLHENV henv = NULL;
>> SQLHDBC hdbc = NULL;
>> SQLHANDLE hstmt = NULL;
>>
>> SQLWCHAR sqlstate[25];
>> SQLWCHAR message[255];
>> SQLINTEGER native_error;
>>
>> //This test program will create a database named my_test_db with one table, my_test_table, containing a single
integercolumn named mycol.
>> SQLWCHAR crete_db_sql[] = L"CREATE DATABASE my_test_db";
>> SQLWCHAR create_table_sql[] = L"CREATE TABLE my_test_table(mycol integer)";
>> //It will attempt to prepare a query that inserts an integer, and returns the xmin value as a bigint.
>> //This will work fine the first time, but subsequent calls to SQLExec will fail on the SQLFetch that should fetch
xmin.
>> SQLWCHAR insert_sql[] = L"INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint";
>>
>> SQLINTEGER input = 0;
>> SQLBIGINT xmin_output = 0;
>>
>> std::wstring get_connection_string(const std::wstring& dbname)
>> {
>> return L"Driver={PostgreSQL Unicode(x64)};Server=127.0.0.1;Port=5432;Database=" + dbname +
L";Uid=postgres;Pwd=password;";
>> }
>> }
>>
>> #define ODBC_CHECK(x) if (!SQL_SUCCEEDED(x)) { \
>> BOOST_CHECK_IMPL(false, BOOST_TEST_STRINGIZE(x), CHECK, CHECK_PRED); \
>> SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, &native_error, message, 255, nullptr); \
>> std::wcerr << L"SQLSTATE: " << sqlstate << L" NATIVE ERROR: " << native_error << std::endl << L"MESSAGE: " <<
message<< std::endl; }
>>
>> BOOST_AUTO_TEST_CASE( aaa_postgres_odbc )
>> {
>>
>> //------Boring setup code that creates the database etc.
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
>> reinterpret_cast<void*>(SQL_OV_ODBC3), 0)));
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"postgres").c_str()),
>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>> ODBC_CHECK(SQLExecDirect(hstmt, crete_db_sql, SQL_NTS));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>> //------Database should now be set up, some more boring code to create our table goes here
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"my_test_db").c_str()),
>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>> ODBC_CHECK(SQLExecDirect(hstmt, create_table_sql, SQL_NTS));
>>
>> ODBC_CHECK(SQLPrepare(hstmt, insert_sql, SQL_NTS));
>>
>> ODBC_CHECK(SQLBindCol(hstmt, 1, SQL_C_SBIGINT, &xmin_output, 0, nullptr));
>> ODBC_CHECK(SQLBindParam(hstmt, 1, SQL_C_SLONG, SQL_PARAM_INPUT, 0, 0, &input, nullptr));
>>
>> //Execute first insert
>> ++input;
>> ODBC_CHECK(SQLExecute(hstmt));
>> ODBC_CHECK(SQLFetch(hstmt));
>> BOOST_CHECK(xmin_output> 0);
>>
>> while (SQL_SUCCEEDED(SQLMoreResults(hstmt)));
>> //Execute second insert
>> ++input;
>> ODBC_CHECK(SQLExecute(hstmt));
>> ODBC_CHECK(SQLFetch(hstmt)); //This is where it fails
>> BOOST_CHECK(xmin_output> 0);
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_ENV, henv)));
>> }
>>
>> --
>> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-odbc
>
>
--
- Heikki
Hi Heikki,
Thanks for the reply. I tried returning a bigint constant instead, and got the same error. I haven't yet managed to set
upthe driver itself for debugging, but I did manage to refactor the test case to your existing kind. Here it is. (Not
sureif attachments work here, so I'll paste it below too).
/*
* Tests INSERT RETURNING twice on the same statement
*/
#include <stdio.h>
#include <stdlib.h>
#include "common.h"
int main(int argc, char** argv)
{
SQLRETURN rc;
HSTMT hstmt = SQL_NULL_HSTMT;
SQLCHAR *sql;
SQLINTEGER input = 0;
SQLBIGINT xmin_out = 0;
test_connect();
rc = SQLAllocHandle(SQL_HANDLE_STMT, conn, &hstmt);
if (!SQL_SUCCEEDED(rc))
{
print_diag("failed to allocate stmt handle", SQL_HANDLE_DBC, conn);
exit(1);
}
sql = "DROP TABLE IF EXISTS insertreturning_twice";
rc = SQLExecDirect(hstmt, sql, SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed while dropping previous table", hstmt);
sql = "CREATE TABLE insertreturning_twice (mycol int4)";
rc = SQLExecDirect(hstmt, sql, SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLExecDirect failed while creating table", hstmt);
sql = "INSERT INTO insertreturning_twice VALUES(?) RETURNING xmin::text::bigint";
rc = SQLPrepare(hstmt, sql, SQL_NTS);
CHECK_STMT_RESULT(rc, "SQLPrepare failed", hstmt);
rc = SQLBindCol(hstmt, 1, SQL_C_SBIGINT, &xmin_out, 0, NULL);
CHECK_STMT_RESULT(rc, "SQLBindCol failed", hstmt);
rc = SQLBindParam(hstmt, 1, SQL_C_SLONG, SQL_PARAM_INPUT, 0, 0, &input, NULL);
CHECK_STMT_RESULT(rc, "SQLBindParam failed", hstmt);
//Execute first insert
++input;
rc = SQLExecute(hstmt);
CHECK_STMT_RESULT(rc, "SQLExecute 1 failed", hstmt);
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch 1 failed", hstmt);
if (xmin_out == 0)
fprintf(stderr, "xmin 1 was not returned.");
xmin_out = 0;
while (SQL_SUCCEEDED(SQLMoreResults(hstmt)));
//Execute second insert
++input;
rc = SQLExecute(hstmt);
CHECK_STMT_RESULT(rc, "SQLExecute 2 failed", hstmt);
rc = SQLFetch(hstmt);
CHECK_STMT_RESULT(rc, "SQLFetch 2 failed", hstmt); //Failure
if (xmin_out == 0)
fprintf(stderr, "xmin 2 was not returned.");
test_disconnect();
}
----------------------------------------
> Date: Tue, 27 May 2014 17:41:30 +0300
> From: hlinnakangas@vmware.com
> To: nukemd@hotmail.com; pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
>
> On 05/24/2014 06:47 PM, John Smith wrote:
>> Hi again,
>>
>> I'm not an ODBC expert, but I would think the below is an error, either in the ODBC driver or maybe even somewhere
else.If someone agrees that this should work, I can take a deeper look at fixing it.
>
> Yeah, it does look like a bug at a quick glance. Could you refactor your
> test program into a regression test, like the ones in the psqlodbc's
> test/src/* source tree, please? That would make it easier for me and
> others to run and debug.
>
> Does it make a difference if you use something else than "xmin" in the
> test case? Like, just return a bigint constant in the RETURNING clause.
> I would imagine it does not, but you never know..
>
>> ----------------------------------------
>>> From: nukemd@hotmail.com
>>> To: pgsql-odbc@postgresql.org
>>> Subject: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
>>> Date: Sun, 18 May 2014 18:22:15 +0200
>>>
>>> Hi,
>>>
>>> I am having some difficulties with a prepared INSERT statement with a returning clause. I made a small example that
reproducesthe problem:
>>>
>>> One database: CREATE DATABASE my_test_db
>>> One table: CREATE TABLE my_test_table(mycol integer)
>>>
>>> The query I am attempting to use: INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint
>>>
>>> First i use SQLPrepare to prepare my insert statement. After this, I use SQLBindCol to bind a 64-bit integer to the
resultthat would be xmin. Then I use SQLBindParam to bind a 32 bit integer variable for the input to the column.
>>>
>>> Then I call SQLExecute and SQLFetch. The first time it works fine, and xmin is returned into the bound variable.
ThenI call SQLMoreResults until all results are consumed.
>>>
>>> After this I call SQLExecute and SQLFetch again, and here I would expect another row to be inserted, and the xmin
forthat row to be returned on SQLFetch.
>>>
>>> The new row is indeed inserted, but SQLFetch fails with SQLSTATE 07005, native error 15 and message "Null statement
resultin PGAPI_ExtendedFetch."
>>>
>>> I am using the PostgreSQL Unicode(x64) driver version on Windows 7. I tried versions 9.03.02.10 and the just
released9.03.03.00.
>>>
>>> Any ideas? I'll paste my complete test program (C++, uses boost unit test) below.
>>>
>>> //---------------------------------------------------------------------
>>> #include <boost/test/unit_test.hpp>
>>> #include <sql.h>
>>>
>>> namespace
>>> {
>>> SQLHENV henv = NULL;
>>> SQLHDBC hdbc = NULL;
>>> SQLHANDLE hstmt = NULL;
>>>
>>> SQLWCHAR sqlstate[25];
>>> SQLWCHAR message[255];
>>> SQLINTEGER native_error;
>>>
>>> //This test program will create a database named my_test_db with one table, my_test_table, containing a single
integercolumn named mycol.
>>> SQLWCHAR crete_db_sql[] = L"CREATE DATABASE my_test_db";
>>> SQLWCHAR create_table_sql[] = L"CREATE TABLE my_test_table(mycol integer)";
>>> //It will attempt to prepare a query that inserts an integer, and returns the xmin value as a bigint.
>>> //This will work fine the first time, but subsequent calls to SQLExec will fail on the SQLFetch that should fetch
xmin.
>>> SQLWCHAR insert_sql[] = L"INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint";
>>>
>>> SQLINTEGER input = 0;
>>> SQLBIGINT xmin_output = 0;
>>>
>>> std::wstring get_connection_string(const std::wstring& dbname)
>>> {
>>> return L"Driver={PostgreSQL Unicode(x64)};Server=127.0.0.1;Port=5432;Database=" + dbname +
L";Uid=postgres;Pwd=password;";
>>> }
>>> }
>>>
>>> #define ODBC_CHECK(x) if (!SQL_SUCCEEDED(x)) { \
>>> BOOST_CHECK_IMPL(false, BOOST_TEST_STRINGIZE(x), CHECK, CHECK_PRED); \
>>> SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, &native_error, message, 255, nullptr); \
>>> std::wcerr << L"SQLSTATE: " << sqlstate << L" NATIVE ERROR: " << native_error << std::endl << L"MESSAGE: " <<
message<< std::endl; }
>>>
>>> BOOST_AUTO_TEST_CASE( aaa_postgres_odbc )
>>> {
>>>
>>> //------Boring setup code that creates the database etc.
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
>>> reinterpret_cast<void*>(SQL_OV_ODBC3), 0)));
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"postgres").c_str()),
>>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>>> ODBC_CHECK(SQLExecDirect(hstmt, crete_db_sql, SQL_NTS));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>>> //------Database should now be set up, some more boring code to create our table goes here
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"my_test_db").c_str()),
>>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>>> ODBC_CHECK(SQLExecDirect(hstmt, create_table_sql, SQL_NTS));
>>>
>>> ODBC_CHECK(SQLPrepare(hstmt, insert_sql, SQL_NTS));
>>>
>>> ODBC_CHECK(SQLBindCol(hstmt, 1, SQL_C_SBIGINT, &xmin_output, 0, nullptr));
>>> ODBC_CHECK(SQLBindParam(hstmt, 1, SQL_C_SLONG, SQL_PARAM_INPUT, 0, 0, &input, nullptr));
>>>
>>> //Execute first insert
>>> ++input;
>>> ODBC_CHECK(SQLExecute(hstmt));
>>> ODBC_CHECK(SQLFetch(hstmt));
>>> BOOST_CHECK(xmin_output> 0);
>>>
>>> while (SQL_SUCCEEDED(SQLMoreResults(hstmt)));
>>> //Execute second insert
>>> ++input;
>>> ODBC_CHECK(SQLExecute(hstmt));
>>> ODBC_CHECK(SQLFetch(hstmt)); //This is where it fails
>>> BOOST_CHECK(xmin_output> 0);
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_ENV, henv)));
>>> }
>>>
>>> --
>>> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-odbc
>>
>>
>
>
> --
> - Heikki
Attachment
Greetings
I finally got the time to dig deeper into this, and the problem seems to be with SQLMoreResults.
In results.c, function PGAPI_MoreResults, around line 1921, there is this construct:
mylog("%s: entering...\n", func);
if (stmt && (res = SC_get_Curres(stmt)))
SC_set_Curres(stmt, res->next);
SC_set_Curres here causes the curres field of the statement to become NULL, and that is not liked when the next
SQLFetchis executed. Not sure why PGAPI_MoreResults would do this, though. Seems like an error, but I really can't say
foresure.
As I said before, I am not an ODBC expert, what I know I have gathered from bits and pieces around the internet, and it
seemscommon to recommend that SQLMoreResults is called to consume the results before you do something more with the
query.
It seems I can work around the problem by doing SQLFreeStmt(hstmt, SQL_CLOSE) instead of SQLMoreResults, but I will be
monitoringthe mailing list for opinions about this.
----------------------------------------
From: nukemd@hotmail.com
To: pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
Date: Tue, 27 May 2014 22:25:58 +0200
Hi Heikki,
Thanks for the reply. I tried returning a bigint constant instead, and got the same error. I haven't yet managed to set
upthe driver itself for debugging, but I did manage to refactor the test case to your existing kind. Here it is. (Not
sureif attachments work here, so I'll paste it below too).
----------------------------------------
> Date: Tue, 27 May 2014 17:41:30 +0300
> From: hlinnakangas@vmware.com
> To: nukemd@hotmail.com; pgsql-odbc@postgresql.org
> Subject: Re: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
>
> On 05/24/2014 06:47 PM, John Smith wrote:
>> Hi again,
>>
>> I'm not an ODBC expert, but I would think the below is an error, either in the ODBC driver or maybe even somewhere
else.If someone agrees that this should work, I can take a deeper look at fixing it.
>
> Yeah, it does look like a bug at a quick glance. Could you refactor your
> test program into a regression test, like the ones in the psqlodbc's
> test/src/* source tree, please? That would make it easier for me and
> others to run and debug.
>
> Does it make a difference if you use something else than "xmin" in the
> test case? Like, just return a bigint constant in the RETURNING clause.
> I would imagine it does not, but you never know..
>
>> ----------------------------------------
>>> From: nukemd@hotmail.com
>>> To: pgsql-odbc@postgresql.org
>>> Subject: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
>>> Date: Sun, 18 May 2014 18:22:15 +0200
>>>
>>> Hi,
>>>
>>> I am having some difficulties with a prepared INSERT statement with a returning clause. I made a small example that
reproducesthe problem:
>>>
>>> One database: CREATE DATABASE my_test_db
>>> One table: CREATE TABLE my_test_table(mycol integer)
>>>
>>> The query I am attempting to use: INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint
>>>
>>> First i use SQLPrepare to prepare my insert statement. After this, I use SQLBindCol to bind a 64-bit integer to the
resultthat would be xmin. Then I use SQLBindParam to bind a 32 bit integer variable for the input to the column.
>>>
>>> Then I call SQLExecute and SQLFetch. The first time it works fine, and xmin is returned into the bound variable.
ThenI call SQLMoreResults until all results are consumed.
>>>
>>> After this I call SQLExecute and SQLFetch again, and here I would expect another row to be inserted, and the xmin
forthat row to be returned on SQLFetch.
>>>
>>> The new row is indeed inserted, but SQLFetch fails with SQLSTATE 07005, native error 15 and message "Null statement
resultin PGAPI_ExtendedFetch."
>>>
>>> I am using the PostgreSQL Unicode(x64) driver version on Windows 7. I tried versions 9.03.02.10 and the just
released9.03.03.00.
>>>
>>> Any ideas? I'll paste my complete test program (C++, uses boost unit test) below.
>>>
>>> //---------------------------------------------------------------------
>>> #include <boost/test/unit_test.hpp>
>>> #include <sql.h>
>>>
>>> namespace
>>> {
>>> SQLHENV henv = NULL;
>>> SQLHDBC hdbc = NULL;
>>> SQLHANDLE hstmt = NULL;
>>>
>>> SQLWCHAR sqlstate[25];
>>> SQLWCHAR message[255];
>>> SQLINTEGER native_error;
>>>
>>> //This test program will create a database named my_test_db with one table, my_test_table, containing a single
integercolumn named mycol.
>>> SQLWCHAR crete_db_sql[] = L"CREATE DATABASE my_test_db";
>>> SQLWCHAR create_table_sql[] = L"CREATE TABLE my_test_table(mycol integer)";
>>> //It will attempt to prepare a query that inserts an integer, and returns the xmin value as a bigint.
>>> //This will work fine the first time, but subsequent calls to SQLExec will fail on the SQLFetch that should fetch
xmin.
>>> SQLWCHAR insert_sql[] = L"INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint";
>>>
>>> SQLINTEGER input = 0;
>>> SQLBIGINT xmin_output = 0;
>>>
>>> std::wstring get_connection_string(const std::wstring& dbname)
>>> {
>>> return L"Driver={PostgreSQL Unicode(x64)};Server=127.0.0.1;Port=5432;Database=" + dbname +
L";Uid=postgres;Pwd=password;";
>>> }
>>> }
>>>
>>> #define ODBC_CHECK(x) if (!SQL_SUCCEEDED(x)) { \
>>> BOOST_CHECK_IMPL(false, BOOST_TEST_STRINGIZE(x), CHECK, CHECK_PRED); \
>>> SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, &native_error, message, 255, nullptr); \
>>> std::wcerr << L"SQLSTATE: " << sqlstate << L" NATIVE ERROR: " << native_error << std::endl << L"MESSAGE: " <<
message<< std::endl; }
>>>
>>> BOOST_AUTO_TEST_CASE( aaa_postgres_odbc )
>>> {
>>>
>>> //------Boring setup code that creates the database etc.
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
>>> reinterpret_cast<void*>(SQL_OV_ODBC3), 0)));
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"postgres").c_str()),
>>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>>> ODBC_CHECK(SQLExecDirect(hstmt, crete_db_sql, SQL_NTS));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>>> //------Database should now be set up, some more boring code to create our table goes here
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"my_test_db").c_str()),
>>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>>> ODBC_CHECK(SQLExecDirect(hstmt, create_table_sql, SQL_NTS));
>>>
>>> ODBC_CHECK(SQLPrepare(hstmt, insert_sql, SQL_NTS));
>>>
>>> ODBC_CHECK(SQLBindCol(hstmt, 1, SQL_C_SBIGINT, &xmin_output, 0, nullptr));
>>> ODBC_CHECK(SQLBindParam(hstmt, 1, SQL_C_SLONG, SQL_PARAM_INPUT, 0, 0, &input, nullptr));
>>>
>>> //Execute first insert
>>> ++input;
>>> ODBC_CHECK(SQLExecute(hstmt));
>>> ODBC_CHECK(SQLFetch(hstmt));
>>> BOOST_CHECK(xmin_output> 0);
>>>
>>> while (SQL_SUCCEEDED(SQLMoreResults(hstmt)));
>>> //Execute second insert
>>> ++input;
>>> ODBC_CHECK(SQLExecute(hstmt));
>>> ODBC_CHECK(SQLFetch(hstmt)); //This is where it fails
>>> BOOST_CHECK(xmin_output> 0);
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>>>
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_ENV, henv)));
>>> }
>>>
>>> --
>>> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-odbc
>>
>>
>
>
> --
> - Heikki
--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc
Hi John,
Could you please try the attached patch?
regards,
Hiroshi Inoue
(2014/05/25 0:47), John Smith wrote:
> Hi again,
>
> I'm not an ODBC expert, but I would think the below is an error, either in the ODBC driver or maybe even somewhere
else.If someone agrees that this should work, I can take a deeper look at fixing it.
>
> ----------------------------------------
>> From: nukemd@hotmail.com
>> To: pgsql-odbc@postgresql.org
>> Subject: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
>> Date: Sun, 18 May 2014 18:22:15 +0200
>>
>> Hi,
>>
>> I am having some difficulties with a prepared INSERT statement with a returning clause. I made a small example that
reproducesthe problem:
>>
>> One database: CREATE DATABASE my_test_db
>> One table: CREATE TABLE my_test_table(mycol integer)
>>
>> The query I am attempting to use: INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint
>>
>> First i use SQLPrepare to prepare my insert statement. After this, I use SQLBindCol to bind a 64-bit integer to the
resultthat would be xmin. Then I use SQLBindParam to bind a 32 bit integer variable for the input to the column.
>>
>> Then I call SQLExecute and SQLFetch. The first time it works fine, and xmin is returned into the bound variable.
ThenI call SQLMoreResults until all results are consumed.
>>
>> After this I call SQLExecute and SQLFetch again, and here I would expect another row to be inserted, and the xmin
forthat row to be returned on SQLFetch.
>>
>> The new row is indeed inserted, but SQLFetch fails with SQLSTATE 07005, native error 15 and message "Null statement
resultin PGAPI_ExtendedFetch."
>>
>> I am using the PostgreSQL Unicode(x64) driver version on Windows 7. I tried versions 9.03.02.10 and the just
released9.03.03.00.
>>
>> Any ideas? I'll paste my complete test program (C++, uses boost unit test) below.
>>
>> //---------------------------------------------------------------------
>> #include <boost/test/unit_test.hpp>
>> #include <sql.h>
>>
>> namespace
>> {
>> SQLHENV henv = NULL;
>> SQLHDBC hdbc = NULL;
>> SQLHANDLE hstmt = NULL;
>>
>> SQLWCHAR sqlstate[25];
>> SQLWCHAR message[255];
>> SQLINTEGER native_error;
>>
>> //This test program will create a database named my_test_db with one table, my_test_table, containing a single
integercolumn named mycol.
>> SQLWCHAR crete_db_sql[] = L"CREATE DATABASE my_test_db";
>> SQLWCHAR create_table_sql[] = L"CREATE TABLE my_test_table(mycol integer)";
>> //It will attempt to prepare a query that inserts an integer, and returns the xmin value as a bigint.
>> //This will work fine the first time, but subsequent calls to SQLExec will fail on the SQLFetch that should fetch
xmin.
>> SQLWCHAR insert_sql[] = L"INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint";
>>
>> SQLINTEGER input = 0;
>> SQLBIGINT xmin_output = 0;
>>
>> std::wstring get_connection_string(const std::wstring& dbname)
>> {
>> return L"Driver={PostgreSQL Unicode(x64)};Server=127.0.0.1;Port=5432;Database=" + dbname +
L";Uid=postgres;Pwd=password;";
>> }
>> }
>>
>> #define ODBC_CHECK(x) if (!SQL_SUCCEEDED(x)) { \
>> BOOST_CHECK_IMPL(false, BOOST_TEST_STRINGIZE(x), CHECK, CHECK_PRED); \
>> SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, &native_error, message, 255, nullptr); \
>> std::wcerr << L"SQLSTATE: " << sqlstate << L" NATIVE ERROR: " << native_error << std::endl << L"MESSAGE: " <<
message<< std::endl; }
>>
>> BOOST_AUTO_TEST_CASE( aaa_postgres_odbc )
>> {
>>
>> //------Boring setup code that creates the database etc.
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
>> reinterpret_cast<void*>(SQL_OV_ODBC3), 0)));
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"postgres").c_str()),
>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>> ODBC_CHECK(SQLExecDirect(hstmt, crete_db_sql, SQL_NTS));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>> //------Database should now be set up, some more boring code to create our table goes here
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"my_test_db").c_str()),
>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>> ODBC_CHECK(SQLExecDirect(hstmt, create_table_sql, SQL_NTS));
>>
>> ODBC_CHECK(SQLPrepare(hstmt, insert_sql, SQL_NTS));
>>
>> ODBC_CHECK(SQLBindCol(hstmt, 1, SQL_C_SBIGINT, &xmin_output, 0, nullptr));
>> ODBC_CHECK(SQLBindParam(hstmt, 1, SQL_C_SLONG, SQL_PARAM_INPUT, 0, 0, &input, nullptr));
>>
>> //Execute first insert
>> ++input;
>> ODBC_CHECK(SQLExecute(hstmt));
>> ODBC_CHECK(SQLFetch(hstmt));
>> BOOST_CHECK(xmin_output> 0);
>>
>> while (SQL_SUCCEEDED(SQLMoreResults(hstmt)));
>> //Execute second insert
>> ++input;
>> ODBC_CHECK(SQLExecute(hstmt));
>> ODBC_CHECK(SQLFetch(hstmt)); //This is where it fails
>> BOOST_CHECK(xmin_output> 0);
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_ENV, henv)));
>> }
>>
>> --
>> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-odbc
Attachment
Hi Hiroshi,
Thank you for the patch. I can confirm that it solves the issue with both the test program and with my original
program.
----------------------------------------
Date: Mon, 2 Jun 2014 09:25:40 +0900
From: inoue@tpf.co.jp
To: nukemd@hotmail.com; pgsql-odbc@postgresql.org
Subject: Re: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
Hi John,
Could you please try the attached patch?
regards,
Hiroshi Inoue
(2014/05/25 0:47), John Smith wrote:
> Hi again,
>
> I'm not an ODBC expert, but I would think the below is an error, either in the ODBC driver or maybe even somewhere
else.If someone agrees that this should work, I can take a deeper look at fixing it.
>
> ----------------------------------------
>> From: nukemd@hotmail.com
>> To: pgsql-odbc@postgresql.org
>> Subject: [ODBC] Problems using SQLFetch on prepared INSERT with RETURNING clause
>> Date: Sun, 18 May 2014 18:22:15 +0200
>>
>> Hi,
>>
>> I am having some difficulties with a prepared INSERT statement with a returning clause. I made a small example that
reproducesthe problem:
>>
>> One database: CREATE DATABASE my_test_db
>> One table: CREATE TABLE my_test_table(mycol integer)
>>
>> The query I am attempting to use: INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint
>>
>> First i use SQLPrepare to prepare my insert statement. After this, I use SQLBindCol to bind a 64-bit integer to the
resultthat would be xmin. Then I use SQLBindParam to bind a 32 bit integer variable for the input to the column.
>>
>> Then I call SQLExecute and SQLFetch. The first time it works fine, and xmin is returned into the bound variable.
ThenI call SQLMoreResults until all results are consumed.
>>
>> After this I call SQLExecute and SQLFetch again, and here I would expect another row to be inserted, and the xmin
forthat row to be returned on SQLFetch.
>>
>> The new row is indeed inserted, but SQLFetch fails with SQLSTATE 07005, native error 15 and message "Null statement
resultin PGAPI_ExtendedFetch."
>>
>> I am using the PostgreSQL Unicode(x64) driver version on Windows 7. I tried versions 9.03.02.10 and the just
released9.03.03.00.
>>
>> Any ideas? I'll paste my complete test program (C++, uses boost unit test) below.
>>
>> //---------------------------------------------------------------------
>> #include <boost/test/unit_test.hpp>
>> #include <sql.h>
>>
>> namespace
>> {
>> SQLHENV henv = NULL;
>> SQLHDBC hdbc = NULL;
>> SQLHANDLE hstmt = NULL;
>>
>> SQLWCHAR sqlstate[25];
>> SQLWCHAR message[255];
>> SQLINTEGER native_error;
>>
>> //This test program will create a database named my_test_db with one table, my_test_table, containing a single
integercolumn named mycol.
>> SQLWCHAR crete_db_sql[] = L"CREATE DATABASE my_test_db";
>> SQLWCHAR create_table_sql[] = L"CREATE TABLE my_test_table(mycol integer)";
>> //It will attempt to prepare a query that inserts an integer, and returns the xmin value as a bigint.
>> //This will work fine the first time, but subsequent calls to SQLExec will fail on the SQLFetch that should fetch
xmin.
>> SQLWCHAR insert_sql[] = L"INSERT INTO my_test_table VALUES(?) RETURNING xmin::text::bigint";
>>
>> SQLINTEGER input = 0;
>> SQLBIGINT xmin_output = 0;
>>
>> std::wstring get_connection_string(const std::wstring& dbname)
>> {
>> return L"Driver={PostgreSQL Unicode(x64)};Server=127.0.0.1;Port=5432;Database=" + dbname +
L";Uid=postgres;Pwd=password;";
>> }
>> }
>>
>> #define ODBC_CHECK(x) if (!SQL_SUCCEEDED(x)) { \
>> BOOST_CHECK_IMPL(false, BOOST_TEST_STRINGIZE(x), CHECK, CHECK_PRED); \
>> SQLGetDiagRec(SQL_HANDLE_STMT, hstmt, 1, sqlstate, &native_error, message, 255, nullptr); \
>> std::wcerr << L"SQLSTATE: " << sqlstate << L" NATIVE ERROR: " << native_error << std::endl << L"MESSAGE: " <<
message<< std::endl; }
>>
>> BOOST_AUTO_TEST_CASE( aaa_postgres_odbc )
>> {
>>
>> //------Boring setup code that creates the database etc.
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION,
>> reinterpret_cast<void*>(SQL_OV_ODBC3), 0)));
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"postgres").c_str()),
>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>> ODBC_CHECK(SQLExecDirect(hstmt, crete_db_sql, SQL_NTS));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>> //------Database should now be set up, some more boring code to create our table goes here
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDriverConnect(hdbc, NULL,
const_cast<SQLWCHAR*>(get_connection_string(L"my_test_db").c_str()),
>> SQL_NTS, nullptr, 0, nullptr, SQL_DRIVER_NOPROMPT)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt)));
>> ODBC_CHECK(SQLExecDirect(hstmt, create_table_sql, SQL_NTS));
>>
>> ODBC_CHECK(SQLPrepare(hstmt, insert_sql, SQL_NTS));
>>
>> ODBC_CHECK(SQLBindCol(hstmt, 1, SQL_C_SBIGINT, &xmin_output, 0, nullptr));
>> ODBC_CHECK(SQLBindParam(hstmt, 1, SQL_C_SLONG, SQL_PARAM_INPUT, 0, 0, &input, nullptr));
>>
>> //Execute first insert
>> ++input;
>> ODBC_CHECK(SQLExecute(hstmt));
>> ODBC_CHECK(SQLFetch(hstmt));
>> BOOST_CHECK(xmin_output> 0);
>>
>> while (SQL_SUCCEEDED(SQLMoreResults(hstmt)));
>> //Execute second insert
>> ++input;
>> ODBC_CHECK(SQLExecute(hstmt));
>> ODBC_CHECK(SQLFetch(hstmt)); //This is where it fails
>> BOOST_CHECK(xmin_output> 0);
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_STMT, hstmt)));
>>
>> BOOST_CHECK(SQL_SUCCEEDED(SQLDisconnect(hdbc)));
>> BOOST_CHECK(SQL_SUCCEEDED(SQLFreeHandle(SQL_HANDLE_ENV, henv)));
>> }
>>
>> --
>> Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-odbc
--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc