× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



David,



The basic Java program I use to test stored procedures is shown below.



Notes:

SYSTEMNAME = name of system to connect to

USERID/PASSWORD = your userid and password

LIB1:LIB2:LIB3 = library list to use for connection

PROCNAME = name of stored procedure

(?,?,?) following PROCNAME ? each ? represents a parameter



For each input parameter you would use



stmt.setXXXXX(parm#, value);



XXXXX corresponds to parameter type: String, Int, etc.





For each output parameter you would use



stmt.registerOutParameter(parm#, Types.XXXXX);



XXXXX corresponds to parameter type (CHAR, INTEGER);



Let me know if you have any questions. This same process can be used for
stored procedures that return a result set.



If you get stuck on the parameter related stuff, let me know how your
procedure is defined and I can give you the corresponding Java statements.



Have fun!

Richard



===================================================================

import java.sql.*;



public class TestSP {



public static void main(String[] args) {



Connection con = null;

CallableStatement stmt = null;



try {



// Connect to system


Class.forName("com.ibm.as400.access.AS400JDBCDriver").newInstance();

con = DriverManager.getConnection(


"jdbc:as400://SYSTEMNAME;prompt=false;naming=system;libraries=LIB1:LIB2:LIB3
",

"USERID", "PASSWORD");



// Define procedure

String callProc = "CALL PROCNAME(?,?,?)";

stmt = con.prepareCall(callProc);

stmt.setString(1, "parm1"); // Parameter 1 -
input string

stmt.setInt(2, 2); // Parameter 2 -
input integer

stmt.registerOutParameter(3, Types.CHAR); // Parameter 3 -
output string



// Call procedure

stmt.execute();



// Display output parameters

System.out.println("Parameter 3 = " + stmt.getString(3));



// Close connection

con.close();



} catch (Exception e) {

System.out.println("Error: " + e);

System.exit(1);

}



System.exit(0);

};

}

===================================================================









From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Tuesday, December 07, 2010 4:18 AM
To: Midrange Systems Technical Discussion
Subject: RE: Basic stored procedure questions



Richard, I would indeed and I'm sure others would be interested. In my case
the Java would have to be very simple!


-----Message d'origine-----
De : midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] De la part de Richard Casey
Envoyé : lundi 6 décembre 2010 22:45
À : 'Midrange Systems Technical Discussion'
Objet : RE: Basic stored procedure questions

David,



I've written small Java programs to test stored procedures
that had output parameters and/or result sets. They work
great. Let me know if you want to try that route and I can
post an example.



Richard Casey



From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of David FOXWELL
Sent: Thursday, December 02, 2010 8:41 AM
To: Midrange Systems Technical Discussion
Subject: RE: Basic stored procedure questions



Vern,

I was trying to keep it as simple as possible, I don't see
how it could be simpler as I am no expert with stored
procedures. By call to a service program I meant to a
procedure in that program. I just recompiled to try to
reproduce the problem, and it worked this time. So, I'm
currently calling one procedure with input parameters only
from the sql session and it works fine. Will have to find
another way of exploiting a procedure with output parameters.



As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.