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

[Nick Gorham]

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.


-- 
Nick