[unixODBC-support] inputOutput parameter problem in calling stored Procedure

[Martin Evans]

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