[Mono-dev] unixOdbc System.Data.Obdc and Output parameters

Daniel Morgan monodanmorg at yahoo.com
Fri Feb 13 11:18:03 EST 2009 

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

More information about the Mono-devel-list mailing list