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

Bartolomeo Nicolotti bnicolotti at siapcn.it
Mon Feb 16 11:24:18 GMT 2009


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

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

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

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



More information about the unixODBC-support mailing list