[Mono-dev] unixOdbc System.Data.Obdc and Output parameters
Bartolomeo Nicolotti
bnicolotti at siapcn.it
Fri Feb 13 13:16:45 EST 2009
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.
More information about the Mono-devel-list
mailing list