[Mono-dev] unixOdbc System.Data.Obdc and Output parameters
Daniel Morgan
monodanmorg at yahoo.com
Fri Feb 13 14:13:24 EST 2009
ODBC can use named parameters.
Does the IBM DB2 odbc driver support named parameters?
If yes, then try using a named parameter instead of a question mark?
--- 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, 1:16 PM
> With this code:
>
> string comandoAS2="{CALL GIANLUCA.provacs( ?
> )}";
> OdbcCommand command2 = conn.CreateCommand();
> command2.CommandText= comandoAS2;
> command2.CommandType=CommandType.StoredProcedure;
>
> OdbcParameter param1 =
> command2.Parameters.Add("@PAR1", OdbcType.Char,
> 2);
> param1.Direction= ParameterDirection.InputOutput;
> param1.Value="AA";
>
> System.Console.WriteLine("Eseguo:"+comandoAS2);
> command2.ExecuteNonQuery();
>
> and this OdbcCommand.Prepare:
>
> 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!");
> }
>
> when running I get:
>
> siap at LxPC54:~/dwn/src/test$ mono helloODBCCommand.exe
> Dunkel siapbn siapbn75
> GIANLUCA
> 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]SQL0204 - PROVACS in GIANLUCA di tipo
> *N non trovato.
> 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 means that libodbc.SQLPrepare searches for a no
> parameters
> PROVACS stored procedure
>
> If I use
>
> string comandoAS2="{CALL GIANLUCA.provacs(
> 'AA' )}";
>
> the program is executed correctly
>
>
> Daniel Morgan-3 wrote:
> >
> > 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
> >
> >
> >
> > _______________________________________________
> > 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-tp21953488p22002053.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