Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters

2009-02-16 Thread Bartolomeo Nicolotti 

Hi,

it seems that IBM iseries access odbc driver doesn't support named
parameters:

http://www.windows-tech.info/15/20db2419a40d67ff.php

at least in a version.

Could you please give me an example of named parameter?

Many thanks,

Best regards



Daniel Morgan-3 wrote:
 
 ODBC can use named parameters.  
 Does the IBM DB2 odbc driver support named parameters?
 
 If yes, then try using a named parameter instead of a question mark?
 
 
 --- On Fri, 2/13/09, Bartolomeo Nicolotti bnicolo...@siapcn.it wrote:
 
 From: Bartolomeo Nicolotti bnicolo...@siapcn.it
 Subject: Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters
 To: mono-devel-list@lists.ximian.com
 Date: Friday, February 13, 2009, 1:16 PM
 With this code:
 
  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:
 
  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:
 
 s...@lxpc54:~/dwn/src/test$ mono helloODBCCommand.exe
 Dunkel siapbn siapbn75
 GIANLUCA
 Hello, Dunkel
 Eseguo:CALL QGPL.WBC022(9)
 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 () [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:Prepare ()
   at System.Data.Odbc.OdbcCommand.ExecSQL (System.String
 sql) [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery (Boolean
 freeHandle)
 [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery ()
 [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:ExecuteNonQuery ()
   at helloODBC.Main (System.String[] args) [0x0] 
 
 The message means that libodbc.SQLPrepare searches for a no
 parameters
 PROVACS stored procedure
 
 If I use
 
 string comandoAS2={CALL GIANLUCA.provacs(
 'AA' )};
 
 the program is executed correctly
 
 
 Daniel Morgan-3 wrote:
  
  Did you forget the curly braces surrounding the call?
  
  cmd.CommandText = {? = call usp_TestParameters
 (?, ?)};
  
  You also have to manually add your own parameters.
  
  
  
  
  --- On Fri, 2/13/09, Bartolomeo Nicolotti
 bnicolo...@siapcn.it wrote:
  
  From: Bartolomeo Nicolotti
 bnicolo...@siapcn.it
  Subject: Re: [Mono-dev] unixOdbc System.Data.Obdc
 and Output parameters
  To: mono-devel-list@lists.ximian.com
  Date: Friday, February 13, 2009, 11:46 AM
  Hi,
  
  yes, I'm trying that way, adding some
 debugging
  System.Console.WriteLine in
  the OdbcCommand.cs and I got:
  
  s...@lxpc54:~/dwn/src/test$ mono
 helloODBCCommand.exe 
  Hello, Dunkel
  Eseguo:CALL QGPL.WBC022(9)
  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

Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters

2009-02-13 Thread Daniel Morgan 
Maybe System.Data.Odbc should be modified to use the ODBC call procedure syntax 
when calling a stored procedure using the CommandType of StoredProcedure.

The ODBC call procedure syntax is enclosed with curly braces.  It has the 
keyword call, the procedure name, parentheses, and commas separating the 
parameters.  Of course, a question mark and equals signs indicates a return 
parameter.

{? = call usp_TestParameters (?, ?)}

http://support.microsoft.com/kb/310130


--- On Fri, 2/13/09, Bartolomeo Nicolotti bnicolo...@siapcn.it wrote:

 From: Bartolomeo Nicolotti bnicolo...@siapcn.it
 Subject: [Mono-dev]  unixOdbc System.Data.Obdc and Output parameters
 To: mono-devel-list@lists.ximian.com
 Date: Friday, February 13, 2009, 9:24 AM
 Hi,
 
 I'm trying unixodbc with System.Data.ODBC
 
 I can do select, see this file 
 http://www.nabble.com/file/p21997273/helloODBC.cs
 helloODBC.cs , but I've
 some problems with out parameters of stored procedueres.
 
 With this file 
 http://www.nabble.com/file/p21997273/helloODBCCommand.cs
 helloODBCCommand.cs  I get:
 
 Hello, Dunkel
 Eseguo:CALL QGPL.WBC022(9)
 Eseguo:CALL GIANLUCA.PROVACS
 System.Data.Odbc.OdbcException: ERROR [42000]
 [unixODBC][IBM][iSeries Access
 ODBC Driver][DB2 UDB]SQL0104 - Token GIANLUCA non valido.
 Token validi: (
 END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER.
 
 
 It seems that setting the CommandType to StoredProcedure
 has no effect, as
 one can also see from the source of mono (I've
 installed mono from source)
 
 /home/siap/dwn/src/mono/mono-2.2/mcs/class/System.Data/System.Data.Odbc/OdbcCommand
 
 the ExecuteNonQuery member function call ExecSQL that
 
 
 private int ExecuteNonQuery (bool freeHandle) 
   {
 ...
   ExecSQL(CommandText);
 
 ExecSQL is:
 
   private void ExecSQL (string sql)
   {
   OdbcReturn ret;
   if (! 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)
   Prepare();
 
   BindParameters ();
   ret = libodbc.SQLExecute (hstmt);
   if (ret != OdbcReturn.Success  ret !=
 OdbcReturn.SuccessWithInfo)
   throw connection.CreateOdbcException
 (OdbcHandleType.Stmt, hstmt);
   }
 
 Should I use prepared to BindParameters?
 
 How can I get the output parameter of a Stored Procedure?
 
 Many thanks
 
 Best regards
 
 
 
 
 
 Bartolomeo Nicolotti wrote:
  
  Da: Daniel Morgan monodanm...@yahoo.com
  
  Have you considered using ODBC provider instead?
  
  System.Data.Odbc namespace is included in System.Data
 assembly.
  
  You can use iodbc or unixodbc on linux.  There's
 commercial odbc solutions
  for linux too.
  
  http://mono-project.com/ODBC
  
  http://www.unixodbc.com/doc/db2.html
  
  Novell maintains System.Data.Odbc; however, I do not
 think anyone is
  maintaining IBM.Data.DB2 in Mono.
  
  
  --- On Wed, 2/11/09, Bartolomeo Nicolotti
 bnicolo...@siapcn.it wrote:
  
  
  
  Bartolomeo Nicolotti wrote:
  
  IBM.Data.DB2.DB2Exception: Unable to allocate
 statement handle
  
  by Bartolomeo Nicolotti :: Rate this Message:
  
  Reply | Reply to Author | View Threaded | Show
 Only this Message
  Hello,
  
  I've installed mono, and xsp (not yet
 mod_mono) on ubuntu following the
  instruction here:
  
  http://ubuntuforums.org/showthread.php?t=803743
  
  in view of using mod_mono together with php on
 ubuntu server 8.04, to
  migrate a web service that access a db2/as400
 database.
  
  I've also installed db2exc from ubuntu
 repository as said here:
  
  http://www.ubuntu.com/partners/ibm/db2
  
  I can compile a test program that does a query to
 the db:
  
   http://www.nabble.com/file/p21953488/helloDB2.cs
 helloDB2.cs 
  
  s...@lxpc54:~/src/test$ gmcs
 -r:/usr/lib/mono/1.0/IBM.Data.DB2.dll
  -r:/usr/lib/mono/2.0/System.Data.dll helloDB2.cs
  
  but when I execute it:
  
  s...@lxpc54:~/src/test$ sudo MONO_LOG_LEVEL=debug
 mono helloDB2.exe bart
  
  
  
  Mono-INFO: Assembly Ref addref System.Data
 0x8362e10 - System.Xml
  0x83719d8: 2
  
  Hello, bart
  Mono-INFO: DllImport attempting to load:
 'libdb2'.
  Mono-INFO: DllImport loading location:
 'libdb2.so'.
  Mono-INFO: Searching for 'SQLAllocHandle'.
  Mono-INFO: Probing 'SQLAllocHandle'.
  Mono-INFO: Found as 'SQLAllocHandle'.
  Mono-INFO: DllImport attempting to load:
 'libdb2'.
  Mono-INFO

Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters

2009-02-13 Thread Bartolomeo Nicolotti 

Hi,

yes, I'm trying that way, adding some debugging System.Console.WriteLine in
the OdbcCommand.cs and I got:

s...@lxpc54:~/dwn/src/test$ mono helloODBCCommand.exe 
Hello, Dunkel
Eseguo:CALL QGPL.WBC022(9)
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]SQL7967 - PREPARAZIONE dell'istruzione
QZ905CB2A97A726000 completata.
  at System.Data.Odbc.OdbcCommand.Prepare () [0x0] 
  at (wrapper remoting-invoke-with-check)
System.Data.Odbc.OdbcCommand:Prepare ()
  at System.Data.Odbc.OdbcCommand.ExecSQL (System.String sql) [0x0] 
  at System.Data.Odbc.OdbcCommand.ExecuteNonQuery (Boolean freeHandle)
[0x0] 
  at System.Data.Odbc.OdbcCommand.ExecuteNonQuery () [0x0] 
  at (wrapper remoting-invoke-with-check)
System.Data.Odbc.OdbcCommand:ExecuteNonQuery ()
  at helloODBC.Main (System.String[] args) [0x0] 


the message :

PREPARAZIONE dell'istruzione QZ905CB2A97A726000 completata

translated in English is:

PREPARING of instruction QZ905CB2A97A726000 completed

really strange ... the exception is thrown in OdbcCommand.cs, with some
debug WriteLine:

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!);
}








Daniel Morgan-3 wrote:
 
 Maybe System.Data.Odbc should be modified to use the ODBC call procedure
 syntax when calling a stored procedure using the CommandType of
 StoredProcedure.
 
 The ODBC call procedure syntax is enclosed with curly braces.  It has the
 keyword call, the procedure name, parentheses, and commas separating the
 parameters.  Of course, a question mark and equals signs indicates a
 return parameter.
 
 {? = call usp_TestParameters (?, ?)}
 
 http://support.microsoft.com/kb/310130
 
 
 --- On Fri, 2/13/09, Bartolomeo Nicolotti bnicolo...@siapcn.it wrote:
 
 From: Bartolomeo Nicolotti bnicolo...@siapcn.it
 Subject: [Mono-dev]  unixOdbc System.Data.Obdc and Output parameters
 To: mono-devel-list@lists.ximian.com
 Date: Friday, February 13, 2009, 9:24 AM
 Hi,
 
 I'm trying unixodbc with System.Data.ODBC
 
 I can do select, see this file 
 http://www.nabble.com/file/p21997273/helloODBC.cs
 helloODBC.cs , but I've
 some problems with out parameters of stored procedueres.
 
 With this file 
 http://www.nabble.com/file/p21997273/helloODBCCommand.cs
 helloODBCCommand.cs  I get:
 
 Hello, Dunkel
 Eseguo:CALL QGPL.WBC022(9)
 Eseguo:CALL GIANLUCA.PROVACS
 System.Data.Odbc.OdbcException: ERROR [42000]
 [unixODBC][IBM][iSeries Access
 ODBC Driver][DB2 UDB]SQL0104 - Token GIANLUCA non valido.
 Token validi: (
 END GET SET CALL DROP FREE HOLD LOCK OPEN WITH ALTER.
 
 
 It seems that setting the CommandType to StoredProcedure
 has no effect, as
 one can also see from the source of mono (I've
 installed mono from source)
 
 /home/siap/dwn/src/mono/mono-2.2/mcs/class/System.Data/System.Data.Odbc/OdbcCommand
 
 the ExecuteNonQuery member function call ExecSQL that
 
 
 private int ExecuteNonQuery (bool freeHandle) 
  {
 ...
  ExecSQL(CommandText);
 
 ExecSQL is:
 
  private void ExecSQL (string sql)
  {
  OdbcReturn ret;
  if (! 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

Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters

2009-02-13 Thread Bartolomeo Nicolotti 

The problem should be the version of the iseries access I'm using

http://www-01.ibm.com/support/docview.wss?uid=swg21342488

B2 ODBC Errors in Framework Manager: SQL7967 - PREPARE of statement QADBKFLD
completed
 Technote (troubleshooting)
 
Problem(Abstract)
The errors below are being returned when trying to import tables using the
Framework Manager Metadata Wizard.
 
Symptom
RQP-DEF-0177 An error occurred while performing operation 'sqlOpen'
status='-69'.

UDA-SQL-0043 The underlying database detected an error during processing the
SQL request.

[IBM][iSeries Access ODBC Driver][DB2 UDB]SQL7967 - PREPARE of statement
QADBKFLD completed.
 
Cause
The version of the DB2 ODBC driver or client did not match that of the DB2
server.
 
Environment
IBM DB2 iSeries Access ODBC Driver
 
Resolving the problem
Ensure that the iSeries ODBC driver on the Framework Manager computer is one
that matches the DB2 server.

For example, if the DB2 server is AS400 V5R4 then a V5R4 driver should be
installed on the Cognos reporting servers and computers that have Framework
Manager installed on them.
 
Related information
KB 102832: Error reading data using iSeries Access ODBC Driver:
DS-DBMS-E400, UDA-SQL-0144
 
 
Historical Number
1036951 

Bartolomeo Nicolotti wrote:
 
 Hi,
 
 yes, I'm trying that way, adding some debugging System.Console.WriteLine
 in the OdbcCommand.cs and I got:
 
 s...@lxpc54:~/dwn/src/test$ mono helloODBCCommand.exe 
 Hello, Dunkel
 Eseguo:CALL QGPL.WBC022(9)
 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]SQL7967 - PREPARAZIONE dell'istruzione
 QZ905CB2A97A726000 completata.
   at System.Data.Odbc.OdbcCommand.Prepare () [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:Prepare ()
   at System.Data.Odbc.OdbcCommand.ExecSQL (System.String sql) [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery (Boolean freeHandle)
 [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery () [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:ExecuteNonQuery ()
   at helloODBC.Main (System.String[] args) [0x0] 
 
 
 the message :
 
 PREPARAZIONE dell'istruzione QZ905CB2A97A726000 completata
 
 translated in English is:
 
 PREPARING of instruction QZ905CB2A97A726000 completed
 
 really strange ... the exception is thrown in OdbcCommand.cs, with some
 debug WriteLine:
 
   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!);
   }
 
 
 
 
 
 
 
 
 Daniel Morgan-3 wrote:
 
 Maybe System.Data.Odbc should be modified to use the ODBC call procedure
 syntax when calling a stored procedure using the CommandType of
 StoredProcedure.
 
 The ODBC call procedure syntax is enclosed with curly braces.  It has the
 keyword call, the procedure name, parentheses, and commas separating the
 parameters.  Of course, a question mark and equals signs indicates a
 return parameter.
 
 {? = call usp_TestParameters (?, ?)}
 
 http://support.microsoft.com/kb/310130
 
 
 --- On Fri, 2/13/09, Bartolomeo Nicolotti bnicolo...@siapcn.it wrote:
 
 From: Bartolomeo Nicolotti bnicolo...@siapcn.it
 Subject: [Mono-dev]  unixOdbc System.Data.Obdc and Output parameters
 To: mono-devel-list@lists.ximian.com
 Date: Friday, February 13, 2009, 9:24 AM
 Hi,
 
 I'm trying unixodbc with System.Data.ODBC
 
 I can do select, see this file 
 http://www.nabble.com/file/p21997273/helloODBC.cs
 helloODBC.cs , but I've
 some problems with out parameters of stored procedueres.
 
 With this file 
 http://www.nabble.com/file/p21997273/helloODBCCommand.cs
 helloODBCCommand.cs  I get:
 
 Hello, Dunkel
 Eseguo:CALL QGPL.WBC022(9

Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters

2009-02-13 Thread Daniel Morgan 
Did you forget the curly braces surrounding the call?

cmd.CommandText = {? = call usp_TestParameters (?, ?)};

You also have to manually add your own parameters.




--- On Fri, 2/13/09, Bartolomeo Nicolotti bnicolo...@siapcn.it wrote:

 From: Bartolomeo Nicolotti bnicolo...@siapcn.it
 Subject: Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters
 To: mono-devel-list@lists.ximian.com
 Date: Friday, February 13, 2009, 11:46 AM
 Hi,
 
 yes, I'm trying that way, adding some debugging
 System.Console.WriteLine in
 the OdbcCommand.cs and I got:
 
 s...@lxpc54:~/dwn/src/test$ mono helloODBCCommand.exe 
 Hello, Dunkel
 Eseguo:CALL QGPL.WBC022(9)
 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]SQL7967 - PREPARAZIONE
 dell'istruzione
 QZ905CB2A97A726000 completata.
   at System.Data.Odbc.OdbcCommand.Prepare () [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:Prepare ()
   at System.Data.Odbc.OdbcCommand.ExecSQL (System.String
 sql) [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery (Boolean
 freeHandle)
 [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery ()
 [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:ExecuteNonQuery ()
   at helloODBC.Main (System.String[] args) [0x0] 
 
 
 the message :
 
 PREPARAZIONE dell'istruzione QZ905CB2A97A726000
 completata
 
 translated in English is:
 
 PREPARING of instruction QZ905CB2A97A726000 completed
 
 really strange ... the exception is thrown in
 OdbcCommand.cs, with some
 debug WriteLine:
 
   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!);
   }
 
 
 
 
 
 
 
 
 Daniel Morgan-3 wrote:
  
  Maybe System.Data.Odbc should be modified to use the
 ODBC call procedure
  syntax when calling a stored procedure using the
 CommandType of
  StoredProcedure.
  
  The ODBC call procedure syntax is enclosed with curly
 braces.  It has the
  keyword call, the procedure name, parentheses, and
 commas separating the
  parameters.  Of course, a question mark and equals
 signs indicates a
  return parameter.
  
  {? = call usp_TestParameters (?, ?)}
  
  http://support.microsoft.com/kb/310130
  
  
  --- On Fri, 2/13/09, Bartolomeo Nicolotti
 bnicolo...@siapcn.it wrote:
  
  From: Bartolomeo Nicolotti
 bnicolo...@siapcn.it
  Subject: [Mono-dev]  unixOdbc System.Data.Obdc and
 Output parameters
  To: mono-devel-list@lists.ximian.com
  Date: Friday, February 13, 2009, 9:24 AM
  Hi,
  
  I'm trying unixodbc with System.Data.ODBC
  
  I can do select, see this file 
  http://www.nabble.com/file/p21997273/helloODBC.cs
  helloODBC.cs , but I've
  some problems with out parameters of stored
 procedueres.
  
  With this file 
 
 http://www.nabble.com/file/p21997273/helloODBCCommand.cs
  helloODBCCommand.cs  I get:
  
  Hello, Dunkel
  Eseguo:CALL QGPL.WBC022(9)
  Eseguo:CALL GIANLUCA.PROVACS
  System.Data.Odbc.OdbcException: ERROR [42000]
  [unixODBC][IBM][iSeries Access
  ODBC Driver][DB2 UDB]SQL0104 - Token GIANLUCA non
 valido.
  Token validi: (
  END GET SET CALL DROP FREE HOLD LOCK OPEN WITH
 ALTER.
  
  
  It seems that setting the CommandType to
 StoredProcedure
  has no effect, as
  one can also see from the source of mono (I've
  installed mono from source)
  
 
 /home/siap/dwn/src/mono/mono-2.2/mcs/class/System.Data/System.Data.Odbc/OdbcCommand
  
  the ExecuteNonQuery member function call ExecSQL
 that
  
  
  private int ExecuteNonQuery (bool freeHandle) 
 {
  ...
 ExecSQL(CommandText);
  
  ExecSQL is:
  
 private void ExecSQL (string sql

Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters

2009-02-13 Thread Bartolomeo Nicolotti 

With this code:

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:

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:

s...@lxpc54:~/dwn/src/test$ mono helloODBCCommand.exe Dunkel siapbn siapbn75
GIANLUCA
Hello, Dunkel
Eseguo:CALL QGPL.WBC022(9)
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 () [0x0] 
  at (wrapper remoting-invoke-with-check)
System.Data.Odbc.OdbcCommand:Prepare ()
  at System.Data.Odbc.OdbcCommand.ExecSQL (System.String sql) [0x0] 
  at System.Data.Odbc.OdbcCommand.ExecuteNonQuery (Boolean freeHandle)
[0x0] 
  at System.Data.Odbc.OdbcCommand.ExecuteNonQuery () [0x0] 
  at (wrapper remoting-invoke-with-check)
System.Data.Odbc.OdbcCommand:ExecuteNonQuery ()
  at helloODBC.Main (System.String[] args) [0x0] 

The message means that libodbc.SQLPrepare searches for a no parameters
PROVACS stored procedure

If I use

string comandoAS2={CALL GIANLUCA.provacs( 'AA' )};

the program is executed correctly


Daniel Morgan-3 wrote:
 
 Did you forget the curly braces surrounding the call?
 
 cmd.CommandText = {? = call usp_TestParameters (?, ?)};
 
 You also have to manually add your own parameters.
 
 
 
 
 --- On Fri, 2/13/09, Bartolomeo Nicolotti bnicolo...@siapcn.it wrote:
 
 From: Bartolomeo Nicolotti bnicolo...@siapcn.it
 Subject: Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters
 To: mono-devel-list@lists.ximian.com
 Date: Friday, February 13, 2009, 11:46 AM
 Hi,
 
 yes, I'm trying that way, adding some debugging
 System.Console.WriteLine in
 the OdbcCommand.cs and I got:
 
 s...@lxpc54:~/dwn/src/test$ mono helloODBCCommand.exe 
 Hello, Dunkel
 Eseguo:CALL QGPL.WBC022(9)
 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]SQL7967 - PREPARAZIONE
 dell'istruzione
 QZ905CB2A97A726000 completata.
   at System.Data.Odbc.OdbcCommand.Prepare () [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:Prepare ()
   at System.Data.Odbc.OdbcCommand.ExecSQL (System.String
 sql) [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery (Boolean
 freeHandle)
 [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery ()
 [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:ExecuteNonQuery ()
   at helloODBC.Main (System.String[] args) [0x0] 
 
 
 the message :
 
 PREPARAZIONE dell'istruzione QZ905CB2A97A726000

Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters

2009-02-13 Thread Daniel Morgan 
ODBC can use named parameters.  
Does the IBM DB2 odbc driver support named parameters?

If yes, then try using a named parameter instead of a question mark?


--- On Fri, 2/13/09, Bartolomeo Nicolotti bnicolo...@siapcn.it wrote:

 From: Bartolomeo Nicolotti bnicolo...@siapcn.it
 Subject: Re: [Mono-dev] unixOdbc System.Data.Obdc and Output parameters
 To: mono-devel-list@lists.ximian.com
 Date: Friday, February 13, 2009, 1:16 PM
 With this code:
 
   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:
 
   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:
 
 s...@lxpc54:~/dwn/src/test$ mono helloODBCCommand.exe
 Dunkel siapbn siapbn75
 GIANLUCA
 Hello, Dunkel
 Eseguo:CALL QGPL.WBC022(9)
 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 () [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:Prepare ()
   at System.Data.Odbc.OdbcCommand.ExecSQL (System.String
 sql) [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery (Boolean
 freeHandle)
 [0x0] 
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery ()
 [0x0] 
   at (wrapper remoting-invoke-with-check)
 System.Data.Odbc.OdbcCommand:ExecuteNonQuery ()
   at helloODBC.Main (System.String[] args) [0x0] 
 
 The message means that libodbc.SQLPrepare searches for a no
 parameters
 PROVACS stored procedure
 
 If I use
 
 string comandoAS2={CALL GIANLUCA.provacs(
 'AA' )};
 
 the program is executed correctly
 
 
 Daniel Morgan-3 wrote:
  
  Did you forget the curly braces surrounding the call?
  
  cmd.CommandText = {? = call usp_TestParameters
 (?, ?)};
  
  You also have to manually add your own parameters.
  
  
  
  
  --- On Fri, 2/13/09, Bartolomeo Nicolotti
 bnicolo...@siapcn.it wrote:
  
  From: Bartolomeo Nicolotti
 bnicolo...@siapcn.it
  Subject: Re: [Mono-dev] unixOdbc System.Data.Obdc
 and Output parameters
  To: mono-devel-list@lists.ximian.com
  Date: Friday, February 13, 2009, 11:46 AM
  Hi,
  
  yes, I'm trying that way, adding some
 debugging
  System.Console.WriteLine in
  the OdbcCommand.cs and I got:
  
  s...@lxpc54:~/dwn/src/test$ mono
 helloODBCCommand.exe 
  Hello, Dunkel
  Eseguo:CALL QGPL.WBC022(9)
  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]SQL7967 - PREPARAZIONE
  dell'istruzione
  QZ905CB2A97A726000 completata.
at System.Data.Odbc.OdbcCommand.Prepare ()
 [0x0