Error: [IBM][CLI Driver] CLI0125E Function sequence error. SQLSTATE=S1010 while using ADO

[PA]

Hi All,

Thanks for your attention..I appreciate that.

-----Problem description -----------Start----------------------

Database : DB2 UDB 8.2 on Windows 2003
Client: VB6/ADO 2.7 on Windows 2003

1) I am opening a ADO 2.7 recordset with following settings

SQL Source : "select * from <<sometable>>" //
<<sometable>>
has a PK
CursorLocation : adUseClient
CursorType : KeySet
LockType : BatchOptimistic or Optimistic
UpdateMethod : Update() or UpdateBatch()

2) After opening the recordset
2.1) Add a record using AddNew method
2.2) Assign Values
2.3) Call Update Or UpdateBatch()
2.4) I can see the recordcount incremented by 1 ...
2.5) I close the recordset
2.6) I assign Nothing to RecordSet.Active Connection property.

3) I Go in command Editor and tried to see my added record using
"select *
from <<sometable>>" and the Command Editor times out
with following error : Error: [IBM][CLI Driver] CLI0125E Function
sequence
error. SQLSTATE=S1010

4) I now must stop and start db2 service to execute "select * from
<<sometable>>" successfully again..however record added in step 2 is
not
there.

-----Problem description -----------END----------------------

My Research :
-----------------------
I tried DB2 documentation and S1010 does not exist in db2 documentation
CLI0125E has very general message that sequence of function call is
wrong.

Anyway I tried more on internet and following interesting
cases/suggested
solutions ...though nothing are recent and deal with UDB 8.2 and
nothing
worked in my case

1) changing CURSORHOLD property to 0. Db2 documentation explanation
makes
sense of this solution that by default all cursor open with Db2 kept
open
until Rolled back. Setting CURSORHOLD to 0 will change this behavior. I
did
that but no change

2) Set Autocommit to False ..tried with Both AutoCommit to true and
false
while CursorHOLD was set to 0 ..did not work.

3) Use IBM OLEDB driver or better still use MS OLE DB provider for ODBC
driver...tried that and did not worked.

Please help.

-TIA

[Jan M. Nelken]

PA wrote:

Hi All,

Thanks for your attention..I appreciate that.

-----Problem description -----------Start----------------------

Database : DB2 UDB 8.2 on Windows 2003
Client: VB6/ADO 2.7 on Windows 2003

1) I am opening a ADO 2.7 recordset with following settings

SQL Source : "select * from <<sometable>>" //
<<sometable>>
has a PK
CursorLocation : adUseClient
CursorType : KeySet
LockType : BatchOptimistic or Optimistic
UpdateMethod : Update() or UpdateBatch()

2) After opening the recordset
2.1) Add a record using AddNew method
2.2) Assign Values
2.3) Call Update Or UpdateBatch()
2.4) I can see the recordcount incremented by 1 ...
2.5) I close the recordset
2.6) I assign Nothing to RecordSet.Active Connection property.

3) I Go in command Editor and tried to see my added record using
"select *
from <<sometable>>" and the Command Editor times out
with following error : Error: [IBM][CLI Driver] CLI0125E Function
sequence
error. SQLSTATE=S1010

4) I now must stop and start db2 service to execute "select * from
<<sometable>>" successfully again..however record added in step 2 is
not
there.

-----Problem description -----------END----------------------

My Research :
-----------------------
I tried DB2 documentation and S1010 does not exist in db2 documentation
CLI0125E has very general message that sequence of function call is
wrong.

Anyway I tried more on internet and following interesting
cases/suggested
solutions ...though nothing are recent and deal with UDB 8.2 and
nothing
worked in my case

1) changing CURSORHOLD property to 0. Db2 documentation explanation
makes
sense of this solution that by default all cursor open with Db2 kept
open
until Rolled back. Setting CURSORHOLD to 0 will change this behavior. I
did
that but no change

2) Set Autocommit to False ..tried with Both AutoCommit to true and
false
while CursorHOLD was set to 0 ..did not work.

3) Use IBM OLEDB driver or better still use MS OLE DB provider for ODBC
driver...tried that and did not worked.

Please help.

-TIA

Post a smallest complete sample code illustrating this problem - or capture and
post CLI trace.

Jan M. Nelken

[PA]

Hi.
I am posting the CLI trace here...now I am facing the same problem but
at a different time. Initially My opening ADO recordset was successful
step 2.0...now even that doesnt work and I get the same problem ... I
also observed that this time..db2 was unable to open the recordset
usng my requested cursor type which was keyset..the CLI trace describe
this problem too.. I am only posting relevent SQL_ERROR traces and
avoiding SQL_SUCCESS

=====================CLI traces start=====================

SQLSetStmtAttrW( hStmt=1:1, fOption=Unknown value 1227,
pvParam=&00000001, uiStrLen=-6 )

SQLSetStmtAttrW( )
<--- SQL_ERROR
// I dont know what this is...somehow db2 is unable to retrieve value
for some option 1227
-----------------------------------------------------------------
SQLSetStmtAttrW( hStmt=1:1, fOption=Unknown value 1228,
pvParam=&00000001, uiStrLen=-6 )
( Unretrieved error message="[IBM][CLI Driver] CLI0133E Option type
out of range. SQLSTATE=S1092" )

SQLSetStmtAttrW( )
<--- SQL_ERROR

SQLGetStmtAttrW( hStmt=1:1, fOption=SQL_ATTR_CURSOR_TYPE,
pvParam=&0012ed38, cbParamMax=-5, pcbParam=<NULL pointer> )
( Unretrieved error message="[IBM][CLI Driver] CLI0133E Option type
out of range. SQLSTATE=S1092" )

// I also dont know what this is...somehow db2 is unable to retrieve
value for some option 1228

-----------------------------------------------------------------

SQLExecDirectW( hStmt=1:1, pszSqlStr="Select * from ", cbSqlStr=29 )
( StmtOut="Select * from ADE_RULE_GROUPS" )
( Package="SYSSN200 ", Section=4 )
( Requested Cursor Attributes=1 )
( Reply Cursor Attributes=524296 )
( Actual Cursor Attributes=524297 )

// This is interesting ...see how the requested cursor (1=keyset) is
different from actual cursor (static)
-------------------------------------------------------------------------

SQLExtendedFetch( hStmt=1:1, fFetchType=SQL_FETCH_NEXT, iRow=0,
pcRow=&0012eb28, rgfRowStatus=&02ded720 )

SQLExtendedFetch( )
<--- SQL_ERROR

SQLGetDiagRecW( fHandleType=SQL_HANDLE_STMT, hHandle=1:1, iRecNumber=1,
pszSqlState=&0012eacc, pfNativeError=&0012e6c0, pszErrorMsg=&0012e6cc,
cbErrorMsgMax=512, pcbErrorMsg=&0012e6b8 )
( iRowNumber=-2, iColumnNumber=-2 )

// Extended fetch failing...

=====================CLI traces END=====================

-TIA