[egenix-users]
Impossible to correct execute stored procedure using cursors
Sirio Capizzi
sirio.capizzi at prometeia.it
Wed Nov 5 09:50:50 CET 2008
Hi all,
we have a problem using egenix mxODBC with a stored procedure using
cursors. The scenario is the following: a Windows XP based client tries
to call a stored procedure on a Microsoft SQL Server 2000 using python
2.5 and mxODBC 3.0.2. The procedure simply copies all the rows of a
table to another one using cursors. The call succeeds but a random numer
of rows are copied instead of the full table. We have tried with
execute, executedirect and callproc. Execute and executedirect complete
but only a random number of rows are copied. Callproc crashes and a
windows error report dialog pops up saying that an error occured in
mxodbc.pyd. We also tried
.setconnectoption(SQL.CURSOR_TYPE,SQL.CURSOR_DYNAMIC) but with no results.
Someone can help us? Thank you in advance.
The stored procedure looks like the following, attaced you will find the
odbc connection trace:
USE [db3]
GO
/****** Oggetto: StoredProcedure [user].[CursorTest] Data script:
11/05/2008 08:44:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [user].[CursorTest] as
truncate table db3.user.CursorTest_test
declare @codicePaese varchar(100), @assetClass varchar(100),
@nomeSocieta varchar(100), @description varchar(100), @codicePaesePrec
varchar(100), @assetClassPrec varchar(100)
set @codicePaesePrec = 'aaa'
set @assetClassPrec = 'bbb'
DECLARE cursore CURSOR
FOR
select CodicePaese, AssetClass, NomeSocieta, [Description]
from db3..sourceTable
order by codicepaese, assetclass, nomesocieta
OPEN cursore
FETCH NEXT FROM cursore
INTO @codicePaese, @assetClass, @nomeSocieta, @description
WHILE @@FETCH_STATUS = 0
BEGIN
IF @codicePaese + '.' + @assetclass = @codicePaesePrec + '.' +
@assetclassPrec
BEGIN
set @codicePaesePrec = @codicePaese
set @assetclassPrec = @assetclass
END
ELSE
BEGIN
insert into db3.user.CursorTest_test (codicepaese, assetclass,
nomesocieta, [description])
values(@codicePaese, @assetClass, @nomeSocieta, @description)
set @codicePaesePrec = @codicePaese
set @assetclassPrec = @assetclass
END
FETCH NEXT FROM cursore
INTO @codicePaese, @assetClass, @nomeSocieta, @description
END
CLOSE cursore
DEALLOCATE cursore
--
SIRIO CAPIZZI
Prometeia SpA
Via G. Marconi, 43
40122 Bologna
Italy
-------------- next part --------------
provaodbc 1608-161c ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 0098E984
provaodbc 1608-161c EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 00000000
SQLHANDLE * 0x0098E984 ( 0x009a1788)
provaodbc 1608-161c ENTER SQLSetEnvAttr
SQLHENV 009A1788
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003
SQLINTEGER 0
provaodbc 1608-161c EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)
SQLHENV 009A1788
SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>
SQLPOINTER 0x00000003 (BADMEM)
SQLINTEGER 0
provaodbc 1608-161c ENTER SQLAllocHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 009A1788
SQLHANDLE * 00A967A8
provaodbc 1608-161c EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 009A1788
SQLHANDLE * 0x00A967A8 ( 0x009a1830)
provaodbc 1608-161c ENTER SQLConnectW
HDBC 009A1830
WCHAR * 0x009A1938 [ -3] "db3\ 0"
SWORD -3
WCHAR * 0x745E4EC8 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x745E4EC8 [ -3] "******\ 0"
SWORD -3
provaodbc 1608-161c EXIT SQLConnectW with return code 1 (SQL_SUCCESS_WITH_INFO)
HDBC 009A1830
WCHAR * 0x009A1938 [ -3] "db3\ 0"
SWORD -3
WCHAR * 0x745E4EC8 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x745E4EC8 [ -3] "******\ 0"
SWORD -3
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Il contesto di database è stato sostituito con 'db3'. (5701)
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (5703)
provaodbc 1608-161c ENTER SQLErrorW
HENV 009A1788
HDBC 009A1830
HSTMT 00000000
WCHAR * 0x0021FA74 (NYI)
SDWORD * 0x0021FAC0
WCHAR * 0x0021F674
SWORD 511
SWORD * 0x0021FAC4
provaodbc 1608-161c EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 009A1788
HDBC 009A1830
HSTMT 00000000
WCHAR * 0x0021FA74 (NYI)
SDWORD * 0x0021FAC0 (5701)
WCHAR * 0x0021F674 [ 110] "[Microsoft][ODBC SQL Server Driver][SQL Server]Il contesto di database \ff stato sostituito con 'db3'."
SWORD 511
SWORD * 0x0021FAC4 (110)
provaodbc 1608-161c ENTER SQLErrorW
HENV 009A1788
HDBC 009A1830
HSTMT 00000000
WCHAR * 0x0021FA74 (NYI)
SDWORD * 0x0021FAC0
WCHAR * 0x0021F674
SWORD 511
SWORD * 0x0021FAC4
provaodbc 1608-161c EXIT SQLErrorW with return code 0 (SQL_SUCCESS)
HENV 009A1788
HDBC 009A1830
HSTMT 00000000
WCHAR * 0x0021FA74 (NYI)
SDWORD * 0x0021FAC0 (5703)
WCHAR * 0x0021F674 [ 86] "[Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english."
SWORD 511
SWORD * 0x0021FAC4 (86)
provaodbc 1608-161c ENTER SQLErrorW
HENV 009A1788
HDBC 009A1830
HSTMT 00000000
WCHAR * 0x0021FA74 (NYI)
SDWORD * 0x0021FAC0
WCHAR * 0x0021F674
SWORD 511
SWORD * 0x0021FAC4
provaodbc 1608-161c EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)
HENV 009A1788
HDBC 009A1830
HSTMT 00000000
WCHAR * 0x0021FA74 (NYI)
SDWORD * 0x0021FAC0
WCHAR * 0x0021F674
SWORD 511
SWORD * 0x0021FAC4
provaodbc 1608-161c ENTER SQLGetInfoW
HDBC 009A1830
UWORD 17 <SQL_DBMS_NAME>
PTR 0x009A2328
SWORD 2048
SWORD * 0x0021F50C
provaodbc 1608-161c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 17 <SQL_DBMS_NAME>
PTR 0x009A2328 [ 40] "Microsoft SQL Server"
SWORD 2048
SWORD * 0x0021F50C (40)
provaodbc 1608-161c ENTER SQLGetInfoW
HDBC 009A1830
UWORD 18 <SQL_DBMS_VER>
PTR 0x009A2270
SWORD 2048
SWORD * 0x0021F0E8
provaodbc 1608-161c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 18 <SQL_DBMS_VER>
PTR 0x009A2270 [ 20] "08.00.2039"
SWORD 2048
SWORD * 0x0021F0E8 (20)
provaodbc 1608-161c ENTER SQLGetInfoW
HDBC 009A1830
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x009A2270
SWORD 2048
SWORD * 0x0021F0E8
provaodbc 1608-161c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 6 <SQL_DRIVER_NAME>
PTR 0x009A2270 [ 24] "SQLSRV32.DLL"
SWORD 2048
SWORD * 0x0021F0E8 (24)
provaodbc 1608-161c ENTER SQLGetInfoW
HDBC 009A1830
UWORD 7 <SQL_DRIVER_VER>
PTR 0x009A2270
SWORD 2048
SWORD * 0x0021F0E8
provaodbc 1608-161c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 7 <SQL_DRIVER_VER>
PTR 0x009A2270 [ 20] "03.85.1132"
SWORD 2048
SWORD * 0x0021F0E8 (20)
provaodbc 1608-161c ENTER SQLGetFunctions
HDBC 009A1830
UWORD 1016
UWORD * 0x0021F504
provaodbc 1608-161c EXIT SQLGetFunctions with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 1016
UWORD * 0x0021F504 (1)
provaodbc 1608-161c ENTER SQLGetFunctions
HDBC 009A1830
UWORD 1020
UWORD * 0x0021F504
provaodbc 1608-161c EXIT SQLGetFunctions with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 1020
UWORD * 0x0021F504 (1)
provaodbc 1608-161c ENTER SQLGetFunctions
HDBC 009A1830
UWORD 58
UWORD * 0x0021F504
provaodbc 1608-161c EXIT SQLGetFunctions with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 58
UWORD * 0x0021F504 (1)
provaodbc 1608-161c ENTER SQLGetFunctions
HDBC 009A1830
UWORD 1021
UWORD * 0x0021F504
provaodbc 1608-161c EXIT SQLGetFunctions with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 1021
UWORD * 0x0021F504 (1)
provaodbc 1608-161c ENTER SQLGetInfoW
HDBC 009A1830
UWORD 81 <SQL_GETDATA_EXTENSIONS>
PTR 0021F510
SWORD 1024
SWORD * 0x0021F508
provaodbc 1608-161c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 81 <SQL_GETDATA_EXTENSIONS>
PTR 0021F510
SWORD 1024
SWORD * 0x0021F508 (4)
provaodbc 1608-161c ENTER SQLGetInfoW
HDBC 009A1830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0021F510
SWORD 1024
SWORD * 0x0021F508
provaodbc 1608-161c EXIT SQLGetInfoW with return code 0 (SQL_SUCCESS)
HDBC 009A1830
UWORD 46 <SQL_TXN_CAPABLE>
PTR 0x0021F510 (2)
SWORD 1024
SWORD * 0x0021F508 (2)
provaodbc 1608-161c ENTER SQLSetConnectAttr
SQLHDBC 009A1830
SQLINTEGER 102 <SQL_ATTR_AUTOCOMMIT>
SQLPOINTER 0x00000000
SQLINTEGER 0
provaodbc 1608-161c EXIT SQLSetConnectAttr with return code 0 (SQL_SUCCESS)
SQLHDBC 009A1830
SQLINTEGER 102 <SQL_ATTR_AUTOCOMMIT>
SQLPOINTER 0x00000000
SQLINTEGER 0
provaodbc 1608-161c ENTER SQLGetConnectAttr
SQLHDBC 009A1830
SQLINTEGER 102 <SQL_ATTR_AUTOCOMMIT>
SQLPOINTER 0x0021F4E0
SQLINTEGER 4
SQLINTEGER * 0x0021F4E4
provaodbc 1608-161c EXIT SQLGetConnectAttr with return code 0 (SQL_SUCCESS)
SQLHDBC 009A1830
SQLINTEGER 102 <SQL_ATTR_AUTOCOMMIT>
SQLPOINTER 0x0021F4E0
SQLINTEGER 4
SQLINTEGER * 0x0021F4E4 (4)
provaodbc 1608-161c ENTER SQLSetConnectAttr
SQLHDBC 009A1830
SQLINTEGER 6 <SQL_ATTR_CURSOR_TYPE>
SQLPOINTER 0x00000002
SQLINTEGER 0
provaodbc 1608-161c EXIT SQLSetConnectAttr with return code 0 (SQL_SUCCESS)
SQLHDBC 009A1830
SQLINTEGER 6 <SQL_ATTR_CURSOR_TYPE>
SQLPOINTER 0x00000002 (BADMEM)
SQLINTEGER 0
provaodbc 1608-161c ENTER SQLAllocHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 009A1830
SQLHANDLE * 00B19EEC
provaodbc 1608-161c EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 009A1830
SQLHANDLE * 0x00B19EEC ( 0x009a2298)
provaodbc 1608-161c ENTER SQLFreeStmt
HSTMT 009A2298
UWORD 3 <SQL_RESET_PARAMS>
provaodbc 1608-161c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 009A2298
UWORD 3 <SQL_RESET_PARAMS>
provaodbc 1608-161c ENTER SQLFreeStmt
HSTMT 009A2298
UWORD 0 <SQL_CLOSE>
provaodbc 1608-161c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 009A2298
UWORD 0 <SQL_CLOSE>
provaodbc 1608-161c ENTER SQLExecDirect
HSTMT 009A2298
UCHAR * 0x00B57BE8 [ 11] "CursorTest"
SDWORD 11
provaodbc 1608-161c EXIT SQLExecDirect with return code 0 (SQL_SUCCESS)
HSTMT 009A2298
UCHAR * 0x00B57BE8 [ 11] "CursorTest"
SDWORD 11
provaodbc 1608-161c ENTER SQLNumResultCols
HSTMT 009A2298
SWORD * 0x0021FCEC
provaodbc 1608-161c EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)
HSTMT 009A2298
SWORD * 0x0021FCEC (0)
provaodbc 1608-161c ENTER SQLGetStmtAttr
SQLHSTMT 009A2298
SQLINTEGER 14 <SQL_ROW_NUMBER>
SQLPOINTER 0x0021FCD4
SQLINTEGER 4
SQLINTEGER * 0x0021FCD8
provaodbc 1608-161c EXIT SQLGetStmtAttr with return code -1 (SQL_ERROR)
SQLHSTMT 009A2298
SQLINTEGER 14 <SQL_ROW_NUMBER>
SQLPOINTER 0x0021FCD4
SQLINTEGER 4
SQLINTEGER * 0x0021FCD8
DIAG [24000] [Microsoft][Driver Manager ODBC] Stato del cursore non valido. (0)
provaodbc 1608-161c ENTER SQLFreeStmt
HSTMT 009A2298
UWORD 3 <SQL_RESET_PARAMS>
provaodbc 1608-161c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)
HSTMT 009A2298
UWORD 3 <SQL_RESET_PARAMS>
provaodbc 1608-161c ENTER SQLFreeHandle
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 009A2298
provaodbc 1608-161c EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 3 <SQL_HANDLE_STMT>
SQLHANDLE 009A2298
provaodbc 1608-161c ENTER SQLEndTran
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 009A1830
SQLSMALLINT 0
provaodbc 1608-161c EXIT SQLEndTran with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 009A1830
SQLSMALLINT 0
provaodbc 1608-161c ENTER SQLDisconnect
HDBC 009A1830
provaodbc 1608-161c EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)
HDBC 009A1830
provaodbc 1608-161c ENTER SQLFreeHandle
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 009A1830
provaodbc 1608-161c EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 2 <SQL_HANDLE_DBC>
SQLHANDLE 009A1830
provaodbc 1608-161c ENTER SQLFreeHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 009A1788
provaodbc 1608-161c EXIT SQLFreeHandle with return code 0 (SQL_SUCCESS)
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 009A1788
More information about the egenix-users
mailing list