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

Bartolomeo Nicolotti bnicolotti at siapcn.it
Mon Feb 16 08:35:07 GMT 2009


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.
> 
> 
-- 
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



More information about the unixODBC-support mailing list