[Mono-dev] unixOdbc System.Data.Obdc and Output parameters
Daniel Morgan
monodanmorg at yahoo.com
Fri Feb 13 12:52:27 EST 2009
Did you forget the curly braces surrounding the call?
cmd.CommandText = "{? = call usp_TestParameters (?, ?)}";
You also have to manually add your own parameters.
--- On Fri, 2/13/09, Bartolomeo Nicolotti <bnicolotti at siapcn.it> wrote:
> From: Bartolomeo Nicolotti <bnicolotti at siapcn.it>
> Subject: Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters
> To: mono-devel-list at lists.ximian.com
> Date: Friday, February 13, 2009, 11:46 AM
> 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.
>
> _______________________________________________
> Mono-devel-list mailing list
> Mono-devel-list at lists.ximian.com
> http://lists.ximian.com/mailman/listinfo/mono-devel-list
More information about the Mono-devel-list
mailing list