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

Bartolomeo Nicolotti bnicolotti at siapcn.it
Mon Feb 16 09:59:49 GMT 2009




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-tp22002537p22034423.html
Sent from the unixODBC - Support mailing list archive at Nabble.com.



More information about the unixODBC-support mailing list