|
midrange.com
My apologies... seems I forgot one important detail before... the code example.
<blush>
I will offer up some information on this as I have done a fair amount of playing
with SQL Stored Procedures and Java. Hope the following helps:
The following example program shows:
1) The syntax of creating an SQL Stored Procedure that uses serveral different
types of parameters.
2) How to compile this procedure using Java/JDBC
3) How to call the stored procedure, setting/registering parameters for it
4) How to get an output parameter from the stored procedure.
5) How to get rid of the stored procedure.
-----------------------------
import java.util.Properties;
import java.sql.*;
import java.math.BigDecimal;
public class SPExample {
public static void main(java.lang.String[] args) {
try {
try {
Class.forName("com.ibm.db2.jdbc.app.DB2Driver");
} catch (ClassNotFoundException cnf) {
System.out.println("This example should be run on an AS/400. " +
"The native JDBC driver should be in the system
classpath " +
" by default.");
}
Properties properties = new Properties ();
properties.put ("user", "");
properties.put ("password", "");
Connection connection =
DriverManager.getConnection("jdbc:db2://*LOCAL", properties);
Statement s = connection.createStatement ();
StringBuffer buffer = new StringBuffer ();
buffer.append ("CREATE PROCEDURE QGPL.TESTSP"
+ " (INOUT P_SMALLINT SMALLINT," //
1
+ " INOUT P_INTEGER INTEGER," //
2
+ " INOUT P_REAL REAL," //
3
+ " INOUT P_FLOAT FLOAT," //
4
+ " INOUT P_DOUBLE DOUBLE," //
5
+ " INOUT P_DECIMAL_50 DECIMAL(5,0)," //
6
+ " INOUT P_DECIMAL_105 DECIMAL(10,5)," //
7
+ " INOUT P_NUMERIC_50 NUMERIC(5,0)," //
8
+ " INOUT P_NUMERIC_105 NUMERIC(10,5)," //
9
+ " INOUT P_CHAR_1 CHAR," //
10
+ " INOUT P_CHAR_50 CHAR(50)," //
11
+ " INOUT P_VARCHAR_50 VARCHAR(50)," //
12
+ " INOUT P_BINARY_20 CHAR(20) FOR BIT DATA," //
13
+ " INOUT P_VARBINARY_20 VARCHAR(20) FOR BIT DATA,"//
14
+ " INOUT P_DATE DATE," //
15
+ " INOUT P_TIME TIME," //
16
+ " INOUT P_TIMESTAMP TIMESTAMP"); //
17
buffer.append (") LANGUAGE SQL SPECIFIC QGPL.TESTSP"
+ " MYSP: BEGIN"
+ " SET P_SMALLINT = P_SMALLINT + 23;"
+ " SET P_INTEGER = P_INTEGER * -2;"
+ " SET P_REAL = -P_REAL;"
+ " SET P_FLOAT = P_FLOAT + 543.2;"
+ " SET P_DOUBLE = P_DOUBLE - 54.54;"
+ " SET P_DECIMAL_50 = P_DECIMAL_50 - 3;"
+ " SET P_DECIMAL_105 = P_DECIMAL_105 +
30000.00003;"
+ " SET P_NUMERIC_50 = P_NUMERIC_50 + 1;"
+ " SET P_NUMERIC_105 = P_NUMERIC_105 - 1;"
+ " SET P_CHAR_1 = 'C';"
+ " SET P_CHAR_50 = 'Cujo';"
+ " SET P_VARCHAR_50 = 'Love ' || P_VARCHAR_50;"
+ " SET P_BINARY_20 = 'Change ';"
+ " SET P_VARBINARY_20 = 'Change ' ||
P_VARBINARY_20;"
+ " SET P_DATE = '1998-04-15';"
+ " SET P_TIME = '08.42.30';"
+ " SET P_TIMESTAMP =
'2001-11-18-13.42.22.123456';");
buffer.append (" END MYSP");
String sql = buffer.toString ();
s.executeUpdate (sql);
s.close ();
sql = "CALL QGPL.TESTSP (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?, ?)";
CallableStatement cs = connection.prepareCall (sql);
// Set all the input parameters.
cs.setShort (1, (short) 1);
cs.setInt (2, 51);
cs.setFloat (3, 3.3f);
cs.setFloat (4, 4.4f);
cs.setDouble (5, 5.5);
cs.setBigDecimal (6, new BigDecimal (6));
cs.setBigDecimal (7, new BigDecimal ("777.777"));
cs.setBigDecimal (8, new BigDecimal (8888));
cs.setBigDecimal (9, new BigDecimal ("-9999.99999"));
cs.setString (10, "H");
cs.setString (11, "Hola");
cs.setString (12, "JDBC");
cs.setBytes (13, new byte[] { (byte) 'J'});
cs.setBytes (14, new byte[] { (byte) 'B', (byte) 'o', (byte) 'n',
(byte) 'j', (byte) 'o', (byte) 'u',
(byte) 'r'});
cs.setDate (15, new Date (121));
cs.setTime (16, new Time (2123));
cs.setTimestamp (17, new Timestamp (32342));
// Register all the output parameters.
cs.registerOutParameter (1, Types.SMALLINT);
cs.registerOutParameter (2, Types.INTEGER);
cs.registerOutParameter (3, Types.REAL);
cs.registerOutParameter (4, Types.DOUBLE);
cs.registerOutParameter (5, Types.DOUBLE);
cs.registerOutParameter (6, Types.DECIMAL);
cs.registerOutParameter (7, Types.DECIMAL);
cs.registerOutParameter (8, Types.NUMERIC);
cs.registerOutParameter (9, Types.NUMERIC);
cs.registerOutParameter (10, Types.CHAR);
cs.registerOutParameter (11, Types.CHAR);
cs.registerOutParameter (12, Types.VARCHAR);
cs.registerOutParameter (13, Types.BINARY);
cs.registerOutParameter (14, Types.VARBINARY);
cs.registerOutParameter (15, Types.DATE);
cs.registerOutParameter (16, Types.TIME);
cs.registerOutParameter (17, Types.TIMESTAMP);
// Execute.
cs.execute ();
// Get one of the values back and test it.
String p = cs.getString (12);
if (p.equals("Love JDBC"))
System.out.println("Works.");
else
System.out.println("Doesn't work." + p + " " + "Love JDBC");
cs.close();
// OK, lets clean up because someone is going to try to run
// this again, right?
s = connection.createStatement ();
s.executeUpdate ("DROP PROCEDURE QGPL.TESTSP");
s.close ();
connection.close();
} catch (SQLException e) {
System.out.println("Something failed... wonder what?");
e.printStackTrace();
}
}
}
----------------------------
This program has been tested on a v4r4 system and is designed to use the AS/400
Developer Kit for java "native" JDBC driver. There could be some issues with it
as it was thrown together from pieced of code from various places. It seems to
be fully funtional to the point that it needs to be however.
I will try to get together an example that shows the use of multiple result sets
from an SQL Stored Procedure and put that out here as well.
As far as the control flow structures for SQL Procedures, I assume they are
pretty self-explanitory.
Regards,
Richard D. Dettinger
AS/400 Native JDBC Driver Team
"I don't see much sense in that," said Rabbit.
"No," said Pooh humbly, "there isn't. But there was going to be when I began
it. It's just that something happened to it along the way."
enord@ORIENTAL.COM on 07/22/99 12:48:30 PM
Please respond to JAVA400-L@midrange.com
To: JAVA400-L@midrange.com
cc:
Subject: Stored Procedures
We are currently writing are main system in Java. We have SQL statements inside
our class files. We would like to create pure SQL stored procedures on the 400.
Has anyone created these and could someone please give me an example of the
code. I have found a little on IBM's web site, but I don't think it is very
good. What should the source type be and what are the steps to compile this.
Thank you in advance for all you help
Erik Nord
Oriental Trading Company
Omaha, NE
+---
| 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/operator: david@midrange.com
+---
+---
| 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/operator: david@midrange.com
+---
As an Amazon Associate we earn from qualifying purchases.
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.
