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