Re: Problems using SQLFetch on prepared INSERT with RETURNING clause - Mailing list pgsql-odbc

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