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 <bnicolo...@siapcn.it> wrote: > From: Bartolomeo Nicolotti <bnicolo...@siapcn.it> > Subject: Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters > To: mono-devel-list@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: > > s...@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 > <bnicolo...@siapcn.it> wrote: > > > >> From: Bartolomeo Nicolotti > <bnicolo...@siapcn.it> > >> Subject: Re: [Mono-dev] unixOdbc System.Data.Obdc > and Output parameters > >> To: mono-devel-list@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: > >> > >> s...@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 > >> <bnicolo...@siapcn.it> wrote: > >> > > >> >> From: Bartolomeo Nicolotti > >> <bnicolo...@siapcn.it> > >> >> Subject: [Mono-dev] unixOdbc > System.Data.Obdc and > >> Output parameters > >> >> To: mono-devel-list@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 > >> <monodanm...@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 > >> >> <bnicolo...@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 > >> >> >> > >> >> >> s...@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: > >> >> >> > >> >> >> s...@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@lists.ximian.com > >> >> > >> > http://lists.ximian.com/mailman/listinfo/mono-devel-list > >> > > >> > > >> > > >> > > _______________________________________________ > >> > Mono-devel-list mailing list > >> > Mono-devel-list@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@lists.ximian.com > >> > http://lists.ximian.com/mailman/listinfo/mono-devel-list > > > > > > > > _______________________________________________ > > Mono-devel-list mailing list > > Mono-devel-list@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@lists.ximian.com > http://lists.ximian.com/mailman/listinfo/mono-devel-list _______________________________________________ Mono-devel-list mailing list Mono-devel-list@lists.ximian.com http://lists.ximian.com/mailman/listinfo/mono-devel-list