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

Bartolomeo Nicolotti bnicolotti at siapcn.it
Fri Feb 13 18:47:19 GMT 2009


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.

-- 
View this message in context: http://www.nabble.com/inputOutput-parameter-problem-in-calling-stored-Procedure-tp22002537p22002537.html
Sent from the unixODBC - Support mailing list archive at Nabble.com.



More information about the unixODBC-support mailing list