Re: Problems using SQLFetch on prepared INSERT with RETURNING clause

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(at)hotmail(dot)com
> To: pgsql-odbc(at)postgresql(dot)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 reproduces the 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 result that 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 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 for that 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 result in 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 integer column 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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-odbc