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

[Martin Evans]

Bartolomeo Nicolotti wrote:

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

> 
> I'll repeat: the c# code I've written to test is:

and I'll repeat, the code needs to call SQLPrepare, SQLBindParameter and 
SQLExecute in that order - how Mono does it I don't know but looking at 
the code you provide suggests:

> 
> 				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 it calls this SQLExecDirect it is wrong or you have called the wrong 
methods in Mono - I don't use Mono, I was just trying to help you.


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

It should be ok to call SQLPrepare like this but I see all the examples 
on IBMs web site do not use the ODBC syntax for calling procs i.e., they do:

SQLPrepare('call gianluca.provacs(?)')

> 
> the error means that the prepare is searching a PROVACS without parameters

When I looked up SQL0204 on IBMs site it suggests this error means 
provacs is undefined in gianluca.

> As I've already said the CALL GIANLUCA.PROVACS('AA') works.

I understood that. I wonder if you have overloaded provacs? Have you got 
more than one version of provacs that take different arguments?

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

Martin
-- 
Martin J. Evans
Easysoft Limited
http://www.easysoft.com