[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