[Mono-dev] unixOdbc System.Data.Obdc and Output parameters
Bartolomeo Nicolotti
bnicolotti at siapcn.it
Fri Feb 13 11:46:30 EST 2009
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-tp21953488p22000241.html
Sent from the Mono - Dev mailing list archive at Nabble.com.
More information about the Mono-devel-list
mailing list