Skip to content

Instantly share code, notes, and snippets.

@greghelton

greghelton/StoredProcWriter.java

Last active November 28, 2023 00:06
Show Gist options
  • Save greghelton/2318885 to your computer and use it in GitHub Desktop.
Save greghelton/2318885 to your computer and use it in GitHub Desktop.
Download ZIP
Java that writes Java code that calls an AS400 stored procedure. This program reads the SYSPARMS table and gets the number and types of the stored procedure's parms and writes the connecting Java code. This code requires a properties file named AS400DEV.properties and this file must have values for system, userid and password.
Raw
StoredProcWriter.java
import java.sql.PreparedStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.Vector;
import java.util.Enumeration;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.sql.SQLException;
import java.io.IOException;
/**
* Generate Code to call AS400 Stored Procedures
*
*/
public class StoredProcWriter {
static boolean DEBUG = true;
static Connection conn;
static String schema, proc, javaFileName;
static FileOutputStream javaSourceFile;
static final String SQLBIGINT = "BIGINT";
static final String SQLBLOB = "BINARY LARGE OBJECT";
static final String SQLCHAR = "CHARACTER";
static final String SQLCLOB = "CHARACTER LARGE OBJECT";
static final String SQLVARCHAR = "CHARACTER VARYING";
static final String SQLDATE = "DATE";
static final String SQLDEC = "DECIMAL";
static final String SQLDISTINCT = "DISTINCT";
static final String SQLDBLBYTECLOB = "DOUBLE-BYTE CHARACTER LARGE OBJECT";
static final String SQLDOUBLE = "DOUBLE PRECISION";
static final String SQLINT = "INTEGER";
static final String SQLNUM = "NUMERIC";
static final String SQLREAL = "REAL";
static final String SQLROWID = "ROWID";
static final String SQLSMALLINT = "SMALLINT";
static final String SQLTIMESTAMP = "TIMESTAMP";
static final String SQLTIME = "TIME";
/**
*
* find stored proc attributes
*
*/
public static Vector getStoredProcData(String lib, String storedProc)
throws SQLException
{
String attrib;
Vector parms;
Vector endResult = new Vector(0,1);
String query = " SELECT substr(SPECSCHEMA,1,10) Lib," +
" substr(SPECNAME,1,50) Proc," +
" PARMNO, PARMMODE," +
" substr(PARMNAME,1,12) Parm, DATA_TYPE," +
" ifnull(SCALE,0), ifnull(PRECISION,0), ifnull(CHARLEN,0) FROM QSYS2/sysparms" +
" where specname=? and specschema=?";
PreparedStatement stmt = StoredProcWriter.conn.prepareStatement(query);
stmt.setString(1, storedProc.trim().toUpperCase());
stmt.setString(2, lib.trim().toUpperCase());
//ProgramPauser.pauseProgram("wait");
ResultSet rs = stmt.executeQuery();
while (rs.next()) {
parms = new Vector(0,1);
parms.addElement(rs.getString(1));
parms.addElement(rs.getString(2));
parms.addElement(Integer.toString(rs.getInt(3)));
parms.addElement(rs.getString(4));
parms.addElement(rs.getString(5));
parms.addElement(rs.getString(6));
parms.addElement(rs.getString(7));
parms.addElement(rs.getString(8));
parms.addElement(rs.getString(9));
endResult.add(parms);
}
return endResult;
}
/**
*
* find stored proc attributes
*
*/
public static void writeJdbcCode(String lib, String storedProc, FileOutputStream outFile)
throws SQLException, ClassNotFoundException, java.io.IOException
{
String endResult;
// BOILERPLATE
outFile.write("import java.sql.CallableStatement;\n".getBytes());
outFile.write("import java.sql.Connection;\n".getBytes());
outFile.write("import java.sql.ResultSet;\n".getBytes());
outFile.write("import java.sql.ResultSetMetaData;\n".getBytes());
outFile.write("import java.sql.Types;\n".getBytes());
outFile.write("import java.sql.SQLException;\n".getBytes());
outFile.write("import java.sql.DriverManager;\n".getBytes());
outFile.write("import java.sql.Date;\n".getBytes());
outFile.write("import java.io.FileInputStream;\n".getBytes());
outFile.write("import java.io.IOException;\n".getBytes());
outFile.write(("\npublic class " + javaFileName + " {\n\n").getBytes());
outFile.write("\n".getBytes());
outFile.write("\n static Connection conn;".getBytes());
outFile.write("\n public static void setConn()".getBytes());
outFile.write("\n throws SQLException, ClassNotFoundException, IOException \n {\n".getBytes());
outFile.write("\n Class.forName(\"com.ibm.as400.access.AS400JDBCDriver\");\n".getBytes());
outFile.write("\n // getConnection starts client access job on AS400\n".getBytes());
//
outFile.write("\n java.util.Properties myProperties = new java.util.Properties();".getBytes());
outFile.write("\n myProperties.load(new FileInputStream(\"AS400DEV.properties\"));".getBytes());
outFile.write("\n String AS400SYSTEM = myProperties.getProperty(\"system\"); ".getBytes());
outFile.write("\n String AS400USERID = myProperties.getProperty(\"userid\");".getBytes());
outFile.write("\n String AS400PASSWORD = myProperties.getProperty(\"password\");".getBytes());
//
outFile.write("\n conn = DriverManager.getConnection(\"jdbc:as400://\" \n".getBytes());
outFile.write(" + AS400SYSTEM \n".getBytes());
outFile.write((" + \";naming=system;libraries=GHELTON,VPAYBRDDTA;prompt=false\",\n").getBytes());
outFile.write(" AS400USERID, \n".getBytes());
outFile.write(" AS400PASSWORD);\n".getBytes());
outFile.write(" }\n".getBytes());
outFile.write("\n\n public static void callProc() {\n".getBytes());
//
endResult = " CallableStatement cs1=null;\n";
outFile.write((endResult).getBytes());
endResult = " String query = \"{call " + storedProc.trim() + "(";
Vector data = StoredProcWriter.getStoredProcData(lib, storedProc);
for (int j = 1; j<=data.size(); j++) {
endResult = endResult + "?,";
}
endResult = endResult.substring(0, endResult.length()-1) + ")}\";\n\n";
outFile.write(endResult.getBytes());
endResult = " try {\n cs1 = conn.prepareCall(query);\n";
outFile.write(endResult.getBytes());
int k = 1;
for (Enumeration e = data.elements();e.hasMoreElements();k++) {
Vector v = (Vector)e.nextElement();
String[] s = new String[9];
for (int j=0; j<9; j++) {
s[j] = (String)v.elementAt(j);
}
//System.out.println("\n3=" + s[3] + " 4=" + s[4] + " 5=" + s[5] + " 6=" + s[6] + " 7=" + s[7] + " 8=" + s[8] + " " + "\n");
if ("IN".equals(s[3].trim())) {
outFile.write((" " + getInVariableStatement(k, s[4], s[5], s[6], s[7], s[8])).getBytes());
} else if ("OUT".equals(s[3].trim())) {
outFile.write((" " + getOutVariableStatement(k, s[4], s[5], s[6], s[7], s[8])).getBytes());
} else if ("INOUT".equals(s[3].trim())) {
outFile.write((" " + getInVariableStatement(k, s[4], s[5], s[6], s[7], s[8])).getBytes());
outFile.write((" " + getOutVariableStatement(k, s[4], s[5], s[6], s[7], s[8])).getBytes());
}
}
//
outFile.write("\n ResultSet rs = cs1.executeQuery();".getBytes());
outFile.write("\n ResultSetMetaData rsmd = rs.getMetaData();".getBytes());
outFile.write("\n int nbrColumns = rsmd.getColumnCount();".getBytes());
outFile.write("\n while (rs.next()) { ".getBytes());
outFile.write("\n for (int col = 1; col <= nbrColumns; col++)".getBytes());
outFile.write("\n System.out.println(rsmd.getColumnName(col) + \":\" + rs.getString(col)); ".getBytes());
outFile.write("\n }".getBytes());
outFile.write("\n System.out.println();".getBytes());
outFile.write("\n } catch(SQLException e){e.printStackTrace();}\n".getBytes());
outFile.write("\n finally ".getBytes());
outFile.write("\n {".getBytes());
outFile.write("\n try {cs1.close();} catch(Exception e){e.printStackTrace();}".getBytes());
outFile.write("\n try {conn.close();} catch(Exception e){e.printStackTrace();}".getBytes());
outFile.write("\n }".getBytes());
//
outFile.write("\n }\n public static void main(String args[]) {".getBytes());
outFile.write("\n try {".getBytes());
outFile.write(("\n " + javaFileName + ".setConn();").getBytes());
outFile.write(("\n //ProgramPauser.pauseProgram(\"waiting\");").getBytes());
outFile.write(("\n " + javaFileName + ".callProc();").getBytes());
outFile.write("\n }\n catch(Exception e) {e.printStackTrace();}\n }\n\n}".getBytes());
//
}
/**
*
*/
static String getOutVariableStatement(int parmNumber, String name, String dataType,
String scale, String precision, String charLength)
{
String endResult=null;
if (SQLBIGINT.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.BIGINT);\n";
else
if (SQLBLOB.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.BLOB);\n";
else
if (SQLCHAR.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.CHAR);\n";
else
if (SQLCLOB.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.CLOB);\n";
else
if (SQLVARCHAR.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.VARCHAR);\n";
else
if (SQLDATE.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.DATE);\n ";
else
if (SQLDISTINCT.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.DISTINCT);\n ";
else
if (SQLDOUBLE.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.DOUBLE);\n";
else
if (SQLDBLBYTECLOB.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.CLOB);\n";
else
if (SQLDEC.equals(dataType.trim()) || SQLNUM.equals(dataType.trim()))
{
if ("0".equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.INTEGER);\n";
else
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.DECIMAL);\n";
}
else
if (SQLINT.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.INTEGER);\n";
else
if (SQLROWID.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.INTEGER);\n";
else
if (SQLREAL.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.FLOAT);\n";
else
if (SQLSMALLINT.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.SMALLINT);\n";
else
if (SQLTIMESTAMP.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.TIMESTAMP);\n ";
if (SQLTIME.equals(dataType.trim()))
endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.TIME);\n ";
System.out.println(endResult);
return endResult;
}
/**
*
*/
static String getInVariableStatement(int parmNumber, String name, String dataType, String scale, String precision, String charLength)
{
String endResult=null;
int spoofInt = 0;
double spoofDouble=0.0;
String spoofString = "\"\"";
String spoofDate = "java.sql.Date.valueOf(\"2003-01-01\")";
String spoofTime = "java.sql.Time.valueOf(\"23:59:01\")";
String spoofTimeStamp = "java.sql.Timestamp.valueOf(\"2003-01-01 12:01:59.000000001\")";
if (SQLCHAR.equals(dataType.trim())
|| SQLVARCHAR.equals(dataType.trim())
|| SQLCLOB.equals(dataType.trim()))
endResult = " cs1.setString(" + parmNumber + ", " + spoofString + ");\n";
else if (SQLDEC.equals(dataType.trim())
|| SQLREAL.equals(dataType.trim())
|| SQLNUM.equals(dataType.trim())
|| SQLDOUBLE.equals(dataType.trim()))
{
if ("0".equals(dataType.trim()))
endResult = " cs1.setInt(" + parmNumber + ", " + spoofInt + ");\n";
else
endResult = " cs1.setDouble(" + parmNumber + ", " + spoofDouble + ");\n";
}
else if (SQLINT.equals(dataType.trim())
|| SQLBIGINT.equals(dataType.trim())
|| SQLSMALLINT.equals(dataType.trim()))
endResult = " cs1.setInt(" + parmNumber + ", " + spoofInt + ");\n";
else if (SQLDATE.equals(dataType.trim()))
endResult = " cs1.setDate(" + parmNumber + ", " + spoofDate + ");\n";
else if (SQLTIME.equals(dataType.trim()))
endResult = " cs1.setDate(" + parmNumber + ", " + spoofTime + ");\n";
else if (SQLTIMESTAMP.equals(dataType.trim()))
endResult = " cs1.setTimestamp(" + parmNumber + ", " + spoofTimeStamp + ");\n";
System.out.println(endResult + "\n");
return endResult;
}
/**
*
*/
public static void setConn()
throws SQLException, ClassNotFoundException, IOException
{
java.util.Properties myProperties = new java.util.Properties();
myProperties.load(new FileInputStream("AS400DEV.properties"));
String AS400SYSTEM = myProperties.getProperty("system");
String AS400USERID = myProperties.getProperty("userid");
String AS400PASSWORD = myProperties.getProperty("password");
// System.out.println("\n" + AS400SYSTEM + "\n" + AS400USERID + "\n" + AS400PASSWORD+ "\n");
Class.forName("com.ibm.as400.access.AS400JDBCDriver");
// getConnection starts client access job on AS400
conn = DriverManager.getConnection("jdbc:as400://"
+ AS400SYSTEM
+ ";naming=system;prompt=false",
AS400USERID,
AS400PASSWORD);
}
/**
*
*/
public static void main(String[] args) throws Exception {
try {
System.out.println("Schema = " + args[0]);
System.out.println("Stored Proc = " + args[1]);
System.out.println("Generated file = " + args[2]);
StoredProcWriter.schema = args[0];
StoredProcWriter.proc = args[1];
StoredProcWriter.javaFileName = args[2];
StoredProcWriter.javaSourceFile = new FileOutputStream(StoredProcWriter.javaFileName+".java", true);
StoredProcWriter.setConn();
StoredProcWriter.writeJdbcCode(StoredProcWriter.schema, StoredProcWriter.proc, StoredProcWriter.javaSourceFile);
}
finally {
try {conn.close();conn=null;}
catch(Exception e){}
}
}
}
Raw
terminal commands
1. Please note that in the generated Java code, you will want to change the libraries on
the connection string and the values in the arguments passed to the stored procedure.
2. The command line arguments (shown below) on the call to the StoredProcedureWriter class are
(a.) the library of the stored procedure
(b.) the stored procedure name
(c.) the name of the Java file (sans extension) to be generated.
The following steps show how to easily create the necessary properties file from the
Windows command propmpt. To enter the ^Z character, it is necessary to press F6.
Alternatively, you can use notepad to enter the three required properties.
==========================================================================
copy con AS400DEV.properties
system=DEV
userid=GHELTON
password=password
^Z
1 file(s) copied.
==========================================================================
The following commands first compile and run the StoredProcWriter code then compile
and run the generated class.
==========================================================================
javac StoredProcWriter.java
java -cp .;C:\Users\admin\dev\lib\jt400\jt400.jar StoredProcWriter GHLIB UPDATEPHONE UpdatePhone
Schema = GHLIB
Stored Proc = UPDATEPHONE
Generated file = UpdatePhone
cs1.setInt(1, 0);
cs1.setString(2, "");
cs1.setString(3, "");
javac UpdatePhone.java
java -cp .;C:\Users\Ghelton\dev\lib\jt400\jt400.jar UpdatePhone
@dancarlosgabriel
Copy link

awesome Greg, way to go...

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment