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

Martin Evans martin.evans at easysoft.com
Mon Feb 16 11:45:08 GMT 2009


Bartolomeo Nicolotti wrote:
> I'd like to thank you for your help, but the code I've written should be ok,
> as you can see from here:

I was not suggesting /your/ code was wrong.

> 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.
>>
>>  
>>
> 
> this is a response from Nick Gorham, the problem seems that the driver
> doesn't accept prepare before binding, I'll ask iseries access forum. Many
> thanks, best regards

Yes, I saw the response from Nick and I fully understand that SQLPrepare 
has failed but if I were you I'd still:

1. try without the ODBC syntax
2. check if you've overloaded your procedure with different types for 
the parameter or perhaps even different numbers of arguments. When you 
call myproc('AA') the procedure is 'myproc' which takes one string 
argument - when you call myproc(?) the database cannot be certain which 
procedure you mean if myproc was overloaded until a parameter is bound. 
If you had overloaded myproc then the error your quote makes perfect sense.

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

> Ok, but the log shows a error coming back from the driver where one 
> should not occur, so I suggest the problem lies with the driver. All 
> unixODBC is doing at this point is passing the call from the app to the 
> driver.
> 
> Try executing the same call in isql.
> 
> {CALL GIANLUCA.Provacs( ? )}
> 
> it should fail to SQLExecute it, but if the driver is working as 
> expected, the SQLPrepare should succeed. It may be that your DB2 driver 
> requires the parameters to a SQLPrepare on a stored procedure to be 
> bound before calling prepare, but if it does, and the mono code only 
> binds after calling SQLPrepare then all you can do is change the code in 
> mono, or use a different driver.
> 
> 
> 
> 
> Martin Evans-4 wrote:
>> 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


More information about the unixODBC-support mailing list