[Mono-dev] unixOdbc System.Data.Obdc and Output parameters
Bartolomeo Nicolotti
bnicolotti at siapcn.it
Fri Feb 13 12:16:51 EST 2009
The problem should be the version of the iseries access I'm using
http://www-01.ibm.com/support/docview.wss?uid=swg21342488
B2 ODBC Errors in Framework Manager: SQL7967 - PREPARE of statement QADBKFLD
completed
Technote (troubleshooting)
Problem(Abstract)
The errors below are being returned when trying to import tables using the
Framework Manager Metadata Wizard.
Symptom
RQP-DEF-0177 An error occurred while performing operation 'sqlOpen'
status='-69'.
UDA-SQL-0043 The underlying database detected an error during processing the
SQL request.
[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7967 - PREPARE of statement
QADBKFLD completed.
Cause
The version of the DB2 ODBC driver or client did not match that of the DB2
server.
Environment
IBM DB2 iSeries Access ODBC Driver
Resolving the problem
Ensure that the iSeries ODBC driver on the Framework Manager computer is one
that matches the DB2 server.
For example, if the DB2 server is AS400 V5R4 then a V5R4 driver should be
installed on the Cognos reporting servers and computers that have Framework
Manager installed on them.
Related information
KB 102832: Error reading data using iSeries Access ODBC Driver:
DS-DBMS-E400, UDA-SQL-0144
Historical Number
1036951
Bartolomeo Nicolotti wrote:
>
> Hi,
>
> yes, I'm trying that way, adding some debugging System.Console.WriteLine
> in the OdbcCommand.cs and I got:
>
> siap at LxPC54:~/dwn/src/test$ mono helloODBCCommand.exe
> Hello, Dunkel
> Eseguo:CALL QGPL.WBC022(9)
> ExecuteNonQuery
> ExecSQL.Parameters.Count:0
> ExecSQL not prepared || Parameters.Count >0
> Eseguo:CALL GIANLUCA.PROVACS(?)
> ExecuteNonQuery
> ExecSQL.Parameters.Count:1
> Just before Prepare
> OdbcCommand.Prepare: begins!
> OdbcCommand.Prepare: Just before libodbc.SQLPrepare
> OdbcCommand.Prepare: Just after libodbc.SQLPrepare
> OdbcCommand.Prepare: Throwing connection.CreateOdbcException
> System.Data.Odbc.OdbcException: ERROR [42S02] [unixODBC][IBM][iSeries
> Access ODBC Driver][DB2 UDB]SQL7967 - PREPARAZIONE dell'istruzione
> QZ905CB2A97A726000 completata.
> at System.Data.Odbc.OdbcCommand.Prepare () [0x00000]
> at (wrapper remoting-invoke-with-check)
> System.Data.Odbc.OdbcCommand:Prepare ()
> at System.Data.Odbc.OdbcCommand.ExecSQL (System.String sql) [0x00000]
> at System.Data.Odbc.OdbcCommand.ExecuteNonQuery (Boolean freeHandle)
> [0x00000]
> at System.Data.Odbc.OdbcCommand.ExecuteNonQuery () [0x00000]
> at (wrapper remoting-invoke-with-check)
> System.Data.Odbc.OdbcCommand:ExecuteNonQuery ()
> at helloODBC.Main (System.String[] args) [0x00000]
>
>
> the message :
>
> PREPARAZIONE dell'istruzione QZ905CB2A97A726000 completata
>
> translated in English is:
>
> PREPARING of instruction QZ905CB2A97A726000 completed
>
> really strange ... the exception is thrown in OdbcCommand.cs, with some
> debug WriteLine:
>
> void Prepare()
> {
> System.Console.WriteLine("OdbcCommand.Prepare: begins!");
> ReAllocStatment ();
>
> OdbcReturn ret;
>
> System.Console.WriteLine("OdbcCommand.Prepare: Just before
> libodbc.SQLPrepare");
> ret = libodbc.SQLPrepare(hstmt, CommandText, CommandText.Length);
> System.Console.WriteLine("OdbcCommand.Prepare: Just after
> libodbc.SQLPrepare");
>
> if ((ret!=OdbcReturn.Success) && (ret!=OdbcReturn.SuccessWithInfo)){
> System.Console.WriteLine("OdbcCommand.Prepare: Throwing
> connection.CreateOdbcException");
> throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
> }
> prepared = true;
> System.Console.WriteLine("OdbcCommand.Prepare: ends!");
> }
>
>
>
>
>
>
>
>
> Daniel Morgan-3 wrote:
>>
>> Maybe System.Data.Odbc should be modified to use the ODBC call procedure
>> syntax when calling a stored procedure using the CommandType of
>> StoredProcedure.
>>
>> The ODBC call procedure syntax is enclosed with curly braces. It has the
>> keyword call, the procedure name, parentheses, and commas separating the
>> parameters. Of course, a question mark and equals signs indicates a
>> return parameter.
>>
>> {? = call usp_TestParameters (?, ?)}
>>
>> http://support.microsoft.com/kb/310130
>>
>>
>> --- On Fri, 2/13/09, Bartolomeo Nicolotti <bnicolotti at siapcn.it> wrote:
>>
>>> From: Bartolomeo Nicolotti <bnicolotti at siapcn.it>
>>> Subject: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters
>>> To: mono-devel-list at lists.ximian.com
>>> Date: Friday, February 13, 2009, 9:24 AM
>>> Hi,
>>>
>>> I'm trying unixodbc with System.Data.ODBC
>>>
>>> I can do select, see this file
>>> http://www.nabble.com/file/p21997273/helloODBC.cs
>>> helloODBC.cs , but I've
>>> some problems with out parameters of stored procedueres.
>>>
>>> With this file
>>> http://www.nabble.com/file/p21997273/helloODBCCommand.cs
>>> helloODBCCommand.cs I get:
>>>
>>> Hello, Dunkel
>>> Eseguo:CALL QGPL.WBC022(9)
>>> Eseguo:CALL GIANLUCA.PROVACS
>>> System.Data.Odbc.OdbcException: ERROR [42000]
>>> [unixODBC][IBM][iSeries Access
>>> ODBC Driver][DB2 UDB]SQL0104 - Token GIANLUCA non valido.
>>> Token validi: (
>>> END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER.
>>>
>>>
>>> It seems that setting the CommandType to StoredProcedure
>>> has no effect, as
>>> one can also see from the source of mono (I've
>>> installed mono from source)
>>>
>>> /home/siap/dwn/src/mono/mono-2.2/mcs/class/System.Data/System.Data.Odbc/OdbcCommand
>>>
>>> the ExecuteNonQuery member function call ExecSQL that
>>>
>>>
>>> private int ExecuteNonQuery (bool freeHandle)
>>> {
>>> ...
>>> ExecSQL(CommandText);
>>>
>>> ExecSQL is:
>>>
>>> private void ExecSQL (string sql)
>>> {
>>> OdbcReturn ret;
>>> if (! prepared && Parameters.Count <= 0) {
>>>
>>> ReAllocStatment ();
>>>
>>> ret = libodbc.SQLExecDirect (hstmt, sql,
>>> libodbc.SQL_NTS);
>>> if ((ret != OdbcReturn.Success) && (ret !=
>>> OdbcReturn.SuccessWithInfo)
>>> &&
>>> (ret != OdbcReturn.NoData))
>>> throw connection.CreateOdbcException
>>> (OdbcHandleType.Stmt, hstmt);
>>> return;
>>> }
>>>
>>> if (!prepared)
>>> Prepare();
>>>
>>> BindParameters ();
>>> ret = libodbc.SQLExecute (hstmt);
>>> if (ret != OdbcReturn.Success && ret !=
>>> OdbcReturn.SuccessWithInfo)
>>> throw connection.CreateOdbcException
>>> (OdbcHandleType.Stmt, hstmt);
>>> }
>>>
>>> Should I use prepared to BindParameters?
>>>
>>> How can I get the output parameter of a Stored Procedure?
>>>
>>> Many thanks
>>>
>>> Best regards
>>>
>>>
>>>
>>>
>>>
>>> Bartolomeo Nicolotti wrote:
>>> >
>>> > Da: Daniel Morgan <monodanmorg at yahoo.com>
>>> >
>>> > Have you considered using ODBC provider instead?
>>> >
>>> > System.Data.Odbc namespace is included in System.Data
>>> assembly.
>>> >
>>> > You can use iodbc or unixodbc on linux. There's
>>> commercial odbc solutions
>>> > for linux too.
>>> >
>>> > http://mono-project.com/ODBC
>>> >
>>> > http://www.unixodbc.com/doc/db2.html
>>> >
>>> > Novell maintains System.Data.Odbc; however, I do not
>>> think anyone is
>>> > maintaining IBM.Data.DB2 in Mono.
>>> >
>>> >
>>> > --- On Wed, 2/11/09, Bartolomeo Nicolotti
>>> <bnicolotti at siapcn.it> wrote:
>>> >
>>> >
>>> >
>>> > Bartolomeo Nicolotti wrote:
>>> >>
>>> >> IBM.Data.DB2.DB2Exception: Unable to allocate
>>> statement handle
>>> >>
>>> >> by Bartolomeo Nicolotti :: Rate this Message:
>>> >>
>>> >> Reply | Reply to Author | View Threaded | Show
>>> Only this Message
>>> >> Hello,
>>> >>
>>> >> I've installed mono, and xsp (not yet
>>> mod_mono) on ubuntu following the
>>> >> instruction here:
>>> >>
>>> >> http://ubuntuforums.org/showthread.php?t=803743
>>> >>
>>> >> in view of using mod_mono together with php on
>>> ubuntu server 8.04, to
>>> >> migrate a web service that access a db2/as400
>>> database.
>>> >>
>>> >> I've also installed db2exc from ubuntu
>>> repository as said here:
>>> >>
>>> >> http://www.ubuntu.com/partners/ibm/db2
>>> >>
>>> >> I can compile a test program that does a query to
>>> the db:
>>> >>
>>> >> http://www.nabble.com/file/p21953488/helloDB2.cs
>>> helloDB2.cs
>>> >>
>>> >> siap at LxPC54:~/src/test$ gmcs
>>> -r:/usr/lib/mono/1.0/IBM.Data.DB2.dll
>>> >> -r:/usr/lib/mono/2.0/System.Data.dll helloDB2.cs
>>> >>
>>> >> but when I execute it:
>>> >>
>>> >> siap at LxPC54:~/src/test$ sudo MONO_LOG_LEVEL=debug
>>> mono helloDB2.exe bart
>>> >>
>>> >> ....
>>> >>
>>> >> Mono-INFO: Assembly Ref addref System.Data
>>> 0x8362e10 -> System.Xml
>>> >> 0x83719d8: 2
>>> >>
>>> >> Hello, bart
>>> >> Mono-INFO: DllImport attempting to load:
>>> 'libdb2'.
>>> >> Mono-INFO: DllImport loading location:
>>> 'libdb2.so'.
>>> >> Mono-INFO: Searching for 'SQLAllocHandle'.
>>> >> Mono-INFO: Probing 'SQLAllocHandle'.
>>> >> Mono-INFO: Found as 'SQLAllocHandle'.
>>> >> Mono-INFO: DllImport attempting to load:
>>> 'libdb2'.
>>> >> Mono-INFO: DllImport loading location:
>>> 'libdb2.so'.
>>> >> Mono-INFO: Searching for 'SQLAllocHandle'.
>>> >> Mono-INFO: Probing 'SQLAllocHandle'.
>>> >> Mono-INFO: Found as 'SQLAllocHandle'.
>>> >> Bart
>>> >> not useLibCli
>>> >> Bart
>>> >> not useLibCli
>>> >> Mono-INFO: DllImport attempting to load:
>>> 'libdb2'.
>>> >> Mono-INFO: DllImport loading location:
>>> 'libdb2.so'.
>>> >> Mono-INFO: Searching for
>>> 'SQLDriverConnectW'.
>>> >> Mono-INFO: Probing 'SQLDriverConnectWW'.
>>> >> Mono-INFO: Probing 'SQLDriverConnectWW'.
>>> >> Mono-INFO: Probing 'SQLDriverConnectW'.
>>> >> Mono-INFO: Found as 'SQLDriverConnectW'.
>>> >> Mono-INFO: DllImport attempting to load:
>>> 'libdb2'.
>>> >> Mono-INFO: DllImport loading location:
>>> 'libdb2.so'.
>>> >> Mono-INFO: Searching for
>>> 'SQLDriverConnectW'.
>>> >> Mono-INFO: Probing 'SQLDriverConnectWW'.
>>> >> Mono-INFO: Probing 'SQLDriverConnectWW'.
>>> >> Mono-INFO: Probing 'SQLDriverConnectW'.
>>> >> Mono-INFO: Found as 'SQLDriverConnectW'.
>>> >> Mono-INFO: DllImport attempting to load:
>>> 'libdb2'.
>>> >> Mono-INFO: DllImport loading location:
>>> 'libdb2.so'.
>>> >> Mono-INFO: Searching for 'SQLGetInfoW'.
>>> >> Mono-INFO: Probing 'SQLGetInfoWW'.
>>> >> Mono-INFO: Probing 'SQLGetInfoWW'.
>>> >> Mono-INFO: Probing 'SQLGetInfoW'.
>>> >> Mono-INFO: Found as 'SQLGetInfoW'.
>>> >> Mono-INFO: DllImport attempting to load:
>>> 'libdb2'.
>>> >> Mono-INFO: DllImport loading location:
>>> 'libdb2.so'.
>>> >> Mono-INFO: Searching for 'SQLGetInfoW'.
>>> >> Mono-INFO: Probing 'SQLGetInfoWW'.
>>> >> Mono-INFO: Probing 'SQLGetInfoWW'.
>>> >> Mono-INFO: Probing 'SQLGetInfoW'.
>>> >> Mono-INFO: Found as 'SQLGetInfoW'.
>>> >> Mono-INFO: DllImport attempting to load:
>>> 'libdb2'.
>>> >> Mono-INFO: DllImport loading location:
>>> 'libdb2.so'.
>>> >> Mono-INFO: Searching for 'SQLGetDiagRec'.
>>> >> Mono-INFO: Probing 'SQLGetDiagRec'.
>>> >> Mono-INFO: Found as 'SQLGetDiagRec'.
>>> >> Mono-INFO: DllImport attempting to load:
>>> 'libdb2'.
>>> >> Mono-INFO: DllImport loading location:
>>> 'libdb2.so'.
>>> >> Mono-INFO: Searching for 'SQLGetDiagRec'.
>>> >> Mono-INFO: Probing 'SQLGetDiagRec'.
>>> >> Mono-INFO: Found as 'SQLGetDiagRec'.
>>> >> Bart
>>> >> not useLibCli
>>> >>
>>> >> Unhandled Exception: IBM.Data.DB2.DB2Exception:
>>> ERROR [08003] [IBM][CLI
>>> >> Driver] CLI0106E Connection is closed.
>>> SQLSTATE=08003
>>> >> InternalExecuteNonQuery: Unable to allocate
>>> statement handle.
>>> >> at IBM.Data.DB2.DB2Command.AllocateStatement
>>> (System.String location)
>>> >> [0x00000]
>>> >> at
>>> IBM.Data.DB2.DB2Command.ExecuteNonQueryInternal
>>> (CommandBehavior
>>> >> behavior) [0x00000]
>>> >> at IBM.Data.DB2.DB2Command.ExecuteReader
>>> (CommandBehavior behavior)
>>> >> [0x00000]
>>> >> at IBM.Data.DB2.DB2Command.ExecuteReader ()
>>> [0x00000]
>>> >> at (wrapper remoting-invoke-with-check)
>>> >> IBM.Data.DB2.DB2Command:ExecuteReader ()
>>> >> at HelloWorldDb2.Main (System.String[] args)
>>> [0x00000]
>>> >>
>>> >
>>> >
>>>
>>> --
>>> View this message in context:
>>> http://www.nabble.com/IBM.Data.DB2.DB2Exception%3A-Unable-to-allocate-statement-handle-tp21953488p21997273.html
>>> Sent from the Mono - Dev mailing list archive at
>>> Nabble.com.
>>>
>>> _______________________________________________
>>> Mono-devel-list mailing list
>>> Mono-devel-list at lists.ximian.com
>>> http://lists.ximian.com/mailman/listinfo/mono-devel-list
>>
>>
>>
>> _______________________________________________
>> Mono-devel-list mailing list
>> Mono-devel-list at lists.ximian.com
>> http://lists.ximian.com/mailman/listinfo/mono-devel-list
>>
>>
>
>
--
View this message in context: http://www.nabble.com/IBM.Data.DB2.DB2Exception%3A-Unable-to-allocate-statement-handle-tp21953488p22000837.html
Sent from the Mono - Dev mailing list archive at Nabble.com.
More information about the Mono-devel-list
mailing list