[unixODBC-support] inputOutput parameter problem in calling stored Procedure
Bartolomeo Nicolotti
bnicolotti at siapcn.it
Mon Feb 16 10:15:37 GMT 2009
As you can see from here:
http://www.itjungle.com/mgo/mgo120503-story01.html
the code I've written in mono should be ok. The link above is an example in
visual basic .net on windows, I'm using mono on linux.
Bartolomeo Nicolotti wrote:
>
>
>
> Martin Evans-4 wrote:
>>
>> Bartolomeo Nicolotti wrote:
>>> Hi,
>>>
>>> the code that does the prepare in Mono sourece tree is alreay in my
>>> e-mail:
>>>
>>> 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!");
>>> }
>>>
>>> It calls libodbc.SQLPrepare, and libodbc is only a wrapper for the
>>> unixodbc.
>>>
>>> Could you please tell me how to turn on ODBC tracing to see what mono is
>>> actually calling in the driver manager.
>>>
>>> Many thanks
>>>
>>> Best regards.
>>>
>>> Il giorno ven, 13/02/2009 alle 19.34 +0000, Nick Gorham ha scritto:
>>>> Bartolomeo Nicolotti wrote:
>>>>> Hi,
>>>>>
>>>>> I'm trying unixodbc with mono on ubuntu to call a stored procedure on
>>>>> a DB2
>>>>> for iseries (as/400):
>>>>>
>>>>>
>>>>> With this code (c#):
>>>>>
>>>>> 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 (i've modified the source code of mono
>>>>> System.Data to do debbungin printing only):
>>>>>
>>>>> 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
>>>>> Hello, Dunkel
>>>>> Eseguo:CALL QGPL.WBC022(9) <------ this is another call
>>>>> 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, but it has 1!
>>>>>
>>>>> If I use
>>>>>
>>>>> string comandoAS2="{CALL GIANLUCA.provacs( 'AA' )}";
>>>>>
>>>>> the program is executed correctly
>>>>>
>>>>> It seems that libodc.SQLPrepare statement can't say to db2 that
>>>>> there's a
>>>>> paramtere, could you please tell me where's the source of this method
>>>>> in
>>>>> unixodbc source tree?
>>>>>
>>>>> Many thanks
>>>>>
>>>>> Best regards.
>>>>>
>>>>>
>>>> Well, teh driver managers SQLPrepare is in the driver manager
>>>> unixODBC-2.x.x/DriverManager/SQLPrepare.c, but I think you need to look
>>>> in the mono code fro the method you mention.
>>>>
>>>> I would turn on ODBC tracing to see what mono is actually calling in
>>>> the
>>>> driver manager.
>>>>
>>>>
>>
>> You turn on tracing by adding something like the following to your
>> odbcinst.ini file:
>>
>> [ODBC]
>> Trace = yes
>> TraceFile = /tmp/unixodbc.log
>>
>> The code you show only does prepare and nothing else. Usually, if you
>> were calling a procedure that takes a parameter you would do something
>> like:
>>
>> SQLPrepare({ call myproc(?)})
>> optionally SQLNumParams but some drivers don't support it
>> SQLBindParameter(1, variable);
>> SQLExecute
>>
>> ">>> ExecSQL.Parameters.Count:1 " looked interesting - it suggested
>> someone knew there was a parameter. Guess you'll have to look closer at
>> Mono.
>>
>> Martin
>> --
>> Martin J. Evans
>> Easysoft Limited
>> http://www.easysoft.com
>> _______________________________________________
>> unixODBC-support mailing list
>> unixODBC-support at mailman.unixodbc.org
>> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
>>
>>
>
> I'll repeat: the c# code I've written to test is:
>
> string comandoAS2="{call QGPL.PROVACS('AA')}";
> comandoAS2="{CALL GIANLUCA.Provacs( ? )}";
> //string strcomando2="CALL "+ comandoAS2;
> 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();
> System.Console.WriteLine("Ottengo:"+command2.Parameters[0].Value);
>
>
> The SQLBindParameter you say with mono is command2.Parameters.Add
> When command2.ExecuteNonQuery() is executed this method from the mono
> source is executed:
>
> private int ExecuteNonQuery (bool freeHandle)
> {
> System.Console.WriteLine("ExecuteNonQuery");
> int records = 0;
> if (Connection == null)
> throw new InvalidOperationException ("No open connection");
> if (Connection.State == ConnectionState.Closed)
> throw new InvalidOperationException ("Connection state is closed");
> // FIXME: a third check is mentioned in .NET docs
>
> ExecSQL(CommandText);
>
> and the ExecSQL is:
>
> private void ExecSQL (string sql)
> {
> OdbcReturn ret;
> System.Console.WriteLine("ExecSQL.Parameters.Count:"+Parameters.Count);
> if (! prepared && Parameters.Count <= 0) {
> System.Console.WriteLine("ExecSQL not 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){
> System.Console.WriteLine( "Just before Prepare" );
> Prepare();
> }
> the Prepare is executed and in the Prepare we got an error from DB2
> iSeries (mind that this version of DB2 is different from the i386 version)
>
> 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!");
> }
>
> I've switched on trace and the method called is:
>
>
> [ODBC][11917][SQLPrepareW.c][126]
> Entry:
> Statement = 0x83df158
> SQL = [{CALL GIANLUCA.Provacs( ? )}][length = 28]
> [ODBC][11917][SQLPrepareW.c][300]
> Exit:[SQL_ERROR]
> DIAG [42S02] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - PROVACS
> in GIANLUCA di tipo *N non trovato.
>
>
> the error means that the prepare is searching a PROVACS without parameters
>
> As I've already said the CALL GIANLUCA.PROVACS('AA') works.
>
> The problem is with the question mark ? that doesn't seem to be recognized
> as a parameter. this is strange as from jdbc we use ? with no problem
>
> Do you know how can I use named parameters
>
> Many thanks
>
> Best regards
>
>
>
--
View this message in context: http://www.nabble.com/inputOutput-parameter-problem-in-calling-stored-Procedure-tp22002537p22034627.html
Sent from the unixODBC - Support mailing list archive at Nabble.com.
More information about the unixODBC-support
mailing list