Re: Problems using SQLFetch on prepared INSERT with RETURNING clause - Mailing list 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