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

Bartolomeo Nicolotti bnicolotti at siapcn.it
Mon Feb 16 10:15:37 GMT 2009


As you can see from here:

http://www.itjungle.com/mgo/mgo120503-story01.html

the code I've written in mono should be ok. The link above is an example in
visual basic .net on windows, I'm using mono on linux.



Bartolomeo Nicolotti wrote:
> 
> 
> 
> Martin Evans-4 wrote:
>> 
>> 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
>> _______________________________________________
>> unixODBC-support mailing list
>> unixODBC-support at mailman.unixodbc.org
>> http://mailman.unixodbc.org/mailman/listinfo/unixodbc-support
>> 
>> 
> 
> I'll repeat: the c# code I've written to test is:
> 
> 				string comandoAS2="{call QGPL.PROVACS('AA')}";
> 				comandoAS2="{CALL GIANLUCA.Provacs( ? )}";
> 				//string strcomando2="CALL "+ comandoAS2;
> 				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();
> 				System.Console.WriteLine("Ottengo:"+command2.Parameters[0].Value);
> 
> 
> The SQLBindParameter you say with mono is command2.Parameters.Add
> When command2.ExecuteNonQuery() is executed this method from the mono
> source is executed:
> 
> 		private int ExecuteNonQuery (bool freeHandle) 
> 		{
> 			System.Console.WriteLine("ExecuteNonQuery");
> 			int records = 0;
> 			if (Connection == null)
> 				throw new InvalidOperationException ("No open connection");
> 			if (Connection.State == ConnectionState.Closed)
> 				throw new InvalidOperationException ("Connection state is closed");
> 			// FIXME: a third check is mentioned in .NET docs
> 
> 			ExecSQL(CommandText);
> 
> and the ExecSQL is:
> 
> 		private void ExecSQL (string sql)
> 		{
> 			OdbcReturn ret;
> 			System.Console.WriteLine("ExecSQL.Parameters.Count:"+Parameters.Count);
> 			if (! prepared && Parameters.Count <= 0) {
> 				System.Console.WriteLine("ExecSQL not prepared || Parameters.Count >0
> ");
> 
> 				ReAllocStatment ();
> 				
> 				ret = libodbc.SQLExecDirect (hstmt, sql, libodbc.SQL_NTS);
> 				if ((ret != OdbcReturn.Success) && (ret != OdbcReturn.SuccessWithInfo)
> &&
> 				    (ret != OdbcReturn.NoData))
> 					throw connection.CreateOdbcException (OdbcHandleType.Stmt, hstmt);
> 				return;
> 			}
> 
> 			if (!prepared){
> 				System.Console.WriteLine( "Just before Prepare" );
> 				Prepare();
> 			}
> the Prepare is executed and in the Prepare we got an error from DB2
> iSeries (mind that this version of DB2 is different from the i386 version)
> 
> 		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!");
> 		}
> 
> I've switched on trace and the method called is:
> 
> 
> [ODBC][11917][SQLPrepareW.c][126]
> 		Entry:            
> 			Statement = 0x83df158            
> 			SQL = [{CALL GIANLUCA.Provacs( ? )}][length = 28]
> [ODBC][11917][SQLPrepareW.c][300]
> 		Exit:[SQL_ERROR]
> 		DIAG [42S02] [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0204 - PROVACS
> in GIANLUCA di tipo *N non trovato.
> 
> 
> the error means that the prepare is searching a PROVACS without parameters
> 
> As I've already said the CALL GIANLUCA.PROVACS('AA') works.
> 
> The problem is with the question mark ? that doesn't seem to be recognized
> as a parameter. this is strange as from jdbc we use ? with no problem
> 
> Do you know how can I use named parameters
> 
> Many thanks 
> 
> Best regards
> 
> 
> 

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



More information about the unixODBC-support mailing list