Re: Problems using SQLFetch on prepared INSERT with RETURNING clause
От | John Smith |
---|---|
Тема | Re: Problems using SQLFetch on prepared INSERT with RETURNING clause |
Дата | |
Msg-id | DUB121-W204CF189C4B51F18678C75D3390@phx.gbl обсуждение исходный текст |
Ответ на | Problems using SQLFetch on prepared INSERT with RETURNING clause (John Smith <nukemd@hotmail.com>) |
Ответы |
Re: Problems using SQLFetch on prepared INSERT with RETURNING
clause
Re: Problems using SQLFetch on prepared INSERT with RETURNING clause |
Список | pgsql-odbc |
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
В списке pgsql-odbc по дате отправления: