PHP: (10020) ODBC Function(s) executed out of allowable sequence., SQL state S1010

e596297

PHP Warning odbc_execute(): SQL error: [unixODBC][Vertica][ODBC] (10020) ODBC Function(s) executed out of allowable sequence., SQL state S1010 in SQLDescribeParameter Test case, settings, logs, traces is in file https://mega.co.nz/#!QAswBQyJ!KUPd90XjZTMZ6mwnd6Z6vnnek9tAqqDBcrZH7Ws3GiM

[Deleted User]

Hi Isaac, Well, the error that you've received seems to indicate that you are calling functions in an order that is not permitted with the Vertica driver. Do you have a simple reproducer (that you could paste inline) that shows what you're trying to do? And are you sure that you're using the functions in question correctly? (My browser is unable to open the link above; it just hangs.) Adam

e596297

Ok. I'm using lightly modified php sample from https://my.vertica.com/docs/CE/6.0.1/HTML/index.htm#16519.htm here it is:

$ cat test.php    

run using cli: php test.php

e596297

Here is interesting trace part (last odbc_execute() and latter):

  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::ClearCancel: +++++ enter +++++  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::GetNumParams: +++++ enter +++++  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::GetNumParams: +++++ enter +++++  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::GetNumParams: +++++ enter +++++  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::FinalizePushedParamData: +++++ enter +++++  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::Execute: +++++ enter +++++  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::BeginStreamingExecution: +++++ enter +++++  Jun 12 20:24:24 INFO  -1194014944 VQueryExecutor::BeginStreamingExecution: Sending load files message  Jun 12 20:24:24 INFO  -1194014944 VQueryExecutor::Execute: Received STDIN initiate message  Jun 12 20:24:24 INFO  -1194014944 VQueryExecutor::Execute: Sending header for 7 parameters  Jun 12 20:24:24 DEBUG -1194014944 VQueryExecutor::ProcessParams: sqltype=-5, Oidtype=6, Len=1, Value=3  Jun 12 20:24:24 DEBUG -1194014944 VQueryExecutor::ProcessParams: sqltype=6, Oidtype=7, Len=4, Value=2.28  Jun 12 20:24:24 DEBUG -1194014944 VQueryExecutor::ProcessParams: sqltype=-9, Oidtype=9, Len=20, Value=abcdefg1234567890406  Jun 12 20:24:24 DEBUG -1194014944 VQueryExecutor::ProcessParams: sqltype=91, Oidtype=10, Len=10, Value=1901-01-01  Jun 12 20:24:24 DEBUG -1194014944 VQueryExecutor::ProcessParams: sqltype=92, Oidtype=11, Len=8, Value=23:12:34  Jun 12 20:24:24 DEBUG -1194014944 VQueryExecutor::ProcessParams: sqltype=93, Oidtype=12, Len=19, Value=1901-01-01 09:00:09  Jun 12 20:24:24 DEBUG -1194014944 VQueryExecutor::ProcessParams: sqltype=-7, Oidtype=5, Len=1, Value=t  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::Execute: AUTOCOMMIT property on, finishing COPY  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::FinishStreamingExecution: +++++ enter +++++  Jun 12 20:24:24 INFO  -1194014944 VQueryExecutor::FinishStreamingExecution: Sending end of load message  Jun 12 20:24:24 INFO  -1194014944 VQueryExecutor::FinishStreamingExecution: Clearing streaming QE  Jun 12 20:24:24 TRACE -1194014944 VQueryExecutor::GetResults: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 VQueryExecutor::GetResults: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 Connection::SQLFreeStmt: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 ConnectionState5::SQLFreeStmt: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 Statement::SQLFreeStmt: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 StatementStateExecuted::SQLFreeStmt: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 StatementState::SQLFreeStmt: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 Statement::SQLNumResultCols: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 StatementState::SQLNumResultCols: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 Connection::SQLFreeHandle: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 Statement::SQLDescribeParam: +++++ enter +++++  Jun 12 20:24:26 TRACE -1194014944 StatementState::SQLDescribeParam: +++++ enter +++++  Jun 12 20:24:26 ERROR -1194014944 Statement::SQLDescribeParam: [Vertica][ODBC] (10020) ODBC Function(s) executed out of allowable sequence.  ...

e596297

It seems than I found the source of problem: (quote from https://my.vertica.com/docs/CE/6.0.1/PDF/Programmer's%20Guide.pdf ) ODBC Feature Support The ODBC driver for Vertica supports the most of the features defined in the Microsoft ODBC 3.5 specifications. The following features are not supported:  Updatable result sets  Backwards scrolling cursors  Cursor attributes  !!! More than one open statement per connection. __For example you cannot execute a new statement while another statement has a result set open. If you need to execute multiple statements at once, open multiple database connections.__ WAT OK. It is about multiple statements in one connection. But I want ONE statement in ONE connection. But I want to execute it multiple times. ODBC is slow enough, but the HP developers decide to make inserts to vertica as slow as it possible! Good plan is: odbc_connect($dsn); odbc_prepare($SQL_WITH_BINDS); foreach( $data as $element){ odbc_execute($element) } ++ one time SQL parsing. No need to send anything except data. ++ SPEEED!!!111 -- NOT WORKING Bad plan (as it works for now): odbc_connect($dsn); foreach( $data as $element){ odbc_prepare($SQL_WITH_BINDS); odbc_execute($element) } OR even odbc_connect($dsn); foreach( $data as $element){ odbc_exec($SQL_WITH_DATA_INCLUDED); } -- parse every request -- extra data send -- 1 req/sec (3 nodes of Xeon E3-1220 V2 @ 3.10GHz, 8Gb RAM) ++ Working

[Deleted User]

Hi Isaac, Vertica certainly does support prepared INSERTs over ODBC; they yield dramatically faster performance than doing one statement at a time. There are a number of posts in these forums of people who are successfully doing what you are trying to do here. So you may want to look around at their examples, ask them some questions about their code, etc. Out of curiosity: Does this script work for you if you comment out the line?:

     echo "[+] Result = " . print_r(odbc_fetch_array($statement)) . PHP_EOL;  

Adam

Jaka_Jančar

I'm getting the same problem.

odbc_prepare() is ok, and first odbc_execute() runs fine. The second one returns the "ODBC Function(s) executed out of allowable sequence., SQL state S1010 in SQLDescribeParameter" error.

Dev_1

Me too, in order to make this worked I had to execute prepare before each insert.
any estimation on fixing this?