Re: Problems using SQLFetch on prepared INSERT with RETURNING clause

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