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

Bartolomeo Nicolotti bnicolotti at siapcn.it
Fri Feb 13 18:42:53 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.



-- 
Bartolomeo Nicolotti
SIAP s.r.l.
www.siapcn.it
v.S.Albano 13 12049
Trinità(CN) Italy
ph:+39 0172 652553
centralino: +39 0172 652511
fax: +39 0172 652519
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.unixodbc.org/pipermail/unixodbc-support/attachments/20090213/b41f1577/attachment-0001.html>


More information about the unixODBC-support mailing list