Re: Calling a stored procedure

["Dave Wall" <dawall@xxxxxxxxxx>]

What you are doing seems reasonable and should work.  I tried the following
and it worked for me.  Do you see anything different between what you did
and what I am doing?  In my case the procedure is in library 'dawjdbc',
which is after the other two in the library list.

      try
      {
         DriverManager.registerDriver(new
com.ibm.as400.access.AS400JDBCDriver());

         connection = DriverManager.getConnection
("jdbc:as400://xxxxx;naming=system;errors=full;user=xxx;password=xxxx;");
         statement  = connection.createStatement();
         runCommand(connection, "ADDLIBLE DAWJDBC");
         runCommand(connection, "ADDLIBLE FRED");
         runCommand(connection, "ADDLIBLE DAW");

         clblStmt = connection.prepareCall( "{CALL TEST_SP2(?, ?, ?, ?, ?)
}" );
         clblStmt.setInt(1, 123);
         clblStmt.setNull(2, Types.INTEGER);
         clblStmt.setNull(3, Types.INTEGER);
         clblStmt.setNull(4, Types.INTEGER);
         clblStmt.setString(5, "Hi Mom");
         rs = clblStmt.executeQuery();

         if (rs.next())
            System.out.println(rs.getString(1));

      }
      catch (Exception e) { e.printStackTrace(); }


   static void runCommand(Connection connection, String command)
                          throws SQLException
   {
      Statement statement = connection.createStatement();

      // We run commands via the QCMDEXC stored procedure.  That procedure
      // requires the length of the command be included with the command
      // specified in precision 15, scale 5 format.  That is,
      // "CALL QSYS.QCMDEXC('command-to-run', 000000nnnn.00000)"
      String commandLength = "0000000000" + command.length();
             commandLength = commandLength.substring(commandLength.length()
- 10) +
                               ".00000";

      String SQLCommand = "CALL QSYS/QCMDEXC('" + command  +
                                 "', " + commandLength + ")";

      statement.executeUpdate(SQLCommand);
      statement.close();
   }



David Wall
AS/400 Toolbox for Java



                                                                                
                             
                    "DUCRET Gilles                                              
                             
                    (GVA)"                    To:     "JAVA400-L (E-mail)" 
<JAVA400-L@midrange.com>          
                    <Gilles.DUCRET@Lloy       cc:                               
                             
                    dsBank.ch>                Subject:     Calling a stored 
procedure                        
                    Sent by:                                                    
                             
                    owner-java400-l@mid                                         
                             
                    range.com                                                   
                             
                                                                                
                             
                                                                                
                             
                    07/27/2001 03:27 AM                                         
                             
                    Please respond to                                           
                             
                    JAVA400-L                                                   
                             
                                                                                
                             
                                                                                
                             



Hi,

I have an unexpected behaviour when i call my (RPG) stored procedure on the

AS400. I am using the AS400 toolbox.

I have a pool of connections.
For each connection i set 3 library lists in java. (i do an execute command

"AddLibl")
I know that the last lib that is added will be used first.

When i call the stored procedure i don't prefix the call with name of the
lib.
(e.g CALL EVALUATE_ACCOUNT instead of CALL MYLIB.EVALUATE_ACCOUNT)

I am surprised to see that if the stored procedure is not in the first
library, it is not
found (searched?) in the other libraries.

This is a bit annoying since i have some stored proc in my developpement
environment,
but some others are already in my integration environment.

I did'nt expected such a behaviour.
I use the naming = system convention.

Any one can help. I pretty sure it can search the stored proc in the other
libraries.
We do that with the datasource of websphere. I don't expect something
different
with classic JDBC.
All the examples i can find in the IBM document use something like
MYLIBNAME.MYPROCEDURE

I have tried that, also specifying libraries in the connection URL (say
ANOTHERLIB)
If MYPROCEDURE is not found in MYLIBNAME it is not found/searched in
ANOTHERLIB.

Should i set something special on my profile on the AS400?

Many thanks

Gilles


Gilles Ducret
Wealth Management Division
IT Architect
Lloyds TSB Bank
Tel: + 41 22 307 31 50
Mob: +41 79 217 21 41




+---
| This is the JAVA/400 Mailing List!
| To submit a new message, send your mail to JAVA400-L@midrange.com.
| To subscribe to this list send email to JAVA400-L-SUB@midrange.com.
| To unsubscribe from this list send email to JAVA400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner: joe@zappie.net
+---