API tools faq
paste
Guest User

Untitled

a guest
Sep 24th, 2018
94
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 18.55 KB | None | 0 0
raw download clone embed print report
  1.  
  2. import java.sql.CallableStatement;
  3. import java.sql.PreparedStatement;
  4. import java.sql.Connection;
  5. import java.sql.ResultSet;
  6. import java.sql.DriverManager;
  7.  
  8. import java.util.Vector;
  9. import java.util.List;
  10.  
  11. import java.util.Enumeration;
  12. import java.io.PrintStream;
  13. import java.io.FileInputStream;
  14. import java.io.FileOutputStream;
  15.  
  16. import java.sql.SQLException;
  17. import java.io.FileNotFoundException;
  18. import java.io.IOException;
  19.  
  20.  
  21. /**
  22. * Generate Code to call AS400 Stored Procedures
  23. *
  24. */
  25.  
  26. public class StoredProcWriter {
  27. static boolean DEBUG = true;
  28. static Connection conn;
  29. static String schema, proc, javaFileName;
  30. static FileOutputStream javaSourceFile;
  31.  
  32. static final String SQLBIGINT = "BIGINT";
  33. static final String SQLBLOB = "BINARY LARGE OBJECT";
  34. static final String SQLCHAR = "CHARACTER";
  35. static final String SQLCLOB = "CHARACTER LARGE OBJECT";
  36. static final String SQLVARCHAR = "CHARACTER VARYING";
  37. static final String SQLDATE = "DATE";
  38. static final String SQLDEC = "DECIMAL";
  39. static final String SQLDISTINCT = "DISTINCT";
  40. static final String SQLDBLBYTECLOB = "DOUBLE-BYTE CHARACTER LARGE OBJECT";
  41. static final String SQLDOUBLE = "DOUBLE PRECISION";
  42. static final String SQLINT = "INTEGER";
  43. static final String SQLNUM = "NUMERIC";
  44. static final String SQLREAL = "REAL";
  45. static final String SQLROWID = "ROWID";
  46. static final String SQLSMALLINT = "SMALLINT";
  47. static final String SQLTIMESTAMP = "TIMESTAMP";
  48. static final String SQLTIME = "TIME";
  49.  
  50. /**
  51. *
  52. * find stored proc attributes
  53. *
  54. */
  55. public static Vector getStoredProcData(String lib, String storedProc)
  56. throws SQLException
  57. {
  58. String attrib;
  59. Vector parms;
  60. Vector endResult = new Vector(0,1);
  61.  
  62. String query = " SELECT substr(SPECSCHEMA,1,10) Lib," +
  63. " substr(SPECNAME,1,50) Proc," +
  64. " PARMNO, PARMMODE," +
  65. " substr(PARMNAME,1,12) Parm, DATA_TYPE," +
  66. " ifnull(SCALE,0), ifnull(PRECISION,0), ifnull(CHARLEN,0) FROM QSYS2/sysparms" +
  67. " where specname=? and specschema=?";
  68.  
  69. PreparedStatement stmt = StoredProcWriter.conn.prepareStatement(query);
  70.  
  71. stmt.setString(1, storedProc.trim().toUpperCase());
  72. stmt.setString(2, lib.trim().toUpperCase());
  73.  
  74. //ProgramPauser.pauseProgram("wait");
  75. ResultSet rs = stmt.executeQuery();
  76.  
  77. while (rs.next()) {
  78. parms = new Vector(0,1);
  79.  
  80. parms.addElement(rs.getString(1));
  81.  
  82. parms.addElement(rs.getString(2));
  83.  
  84. parms.addElement(Integer.toString(rs.getInt(3)));
  85.  
  86. parms.addElement(rs.getString(4));
  87.  
  88. parms.addElement(rs.getString(5));
  89.  
  90. parms.addElement(rs.getString(6));
  91.  
  92. parms.addElement(rs.getString(7));
  93.  
  94. parms.addElement(rs.getString(8));
  95.  
  96. parms.addElement(rs.getString(9));
  97.  
  98. endResult.add(parms);
  99. }
  100. return endResult;
  101. }
  102.  
  103. /**
  104. *
  105. * find stored proc attributes
  106. *
  107. */
  108. public static void writeJdbcCode(String lib, String storedProc, FileOutputStream outFile)
  109. throws SQLException, ClassNotFoundException, java.io.IOException
  110. {
  111. String endResult;
  112.  
  113. // BOILERPLATE
  114. outFile.write("import java.sql.CallableStatement;\n".getBytes());
  115. outFile.write("import java.sql.Connection;\n".getBytes());
  116. outFile.write("import java.sql.ResultSet;\n".getBytes());
  117. outFile.write("import java.sql.ResultSetMetaData;\n".getBytes());
  118. outFile.write("import java.sql.Types;\n".getBytes());
  119. outFile.write("import java.sql.SQLException;\n".getBytes());
  120. outFile.write("import java.sql.DriverManager;\n".getBytes());
  121. outFile.write("import java.sql.Date;\n".getBytes());
  122. outFile.write("import java.io.FileInputStream;\n".getBytes());
  123. outFile.write("import java.io.IOException;\n".getBytes());
  124. outFile.write(("\npublic class " + javaFileName + " {\n\n").getBytes());
  125. outFile.write("\n".getBytes());
  126. outFile.write("\n static Connection conn;".getBytes());
  127. outFile.write("\n public static void setConn()".getBytes());
  128. outFile.write("\n throws SQLException, ClassNotFoundException, IOException \n {\n".getBytes());
  129. outFile.write("\n Class.forName(\"com.ibm.as400.access.AS400JDBCDriver\");\n".getBytes());
  130. outFile.write("\n // getConnection starts client access job on AS400\n".getBytes());
  131. //
  132. outFile.write("\n java.util.Properties myProperties = new java.util.Properties();".getBytes());
  133. outFile.write("\n myProperties.load(new FileInputStream(\"AS400DEV.properties\"));".getBytes());
  134. outFile.write("\n String AS400SYSTEM = myProperties.getProperty(\"system\"); ".getBytes());
  135. outFile.write("\n String AS400USERID = myProperties.getProperty(\"userid\");".getBytes());
  136. outFile.write("\n String AS400PASSWORD = myProperties.getProperty(\"password\");".getBytes());
  137. //
  138. outFile.write("\n conn = DriverManager.getConnection(\"jdbc:as400://\" \n".getBytes());
  139. outFile.write(" + AS400SYSTEM \n".getBytes());
  140. outFile.write((" + \";naming=system;prompt=false\",\n").getBytes());
  141. outFile.write(" AS400USERID, \n".getBytes());
  142. outFile.write(" AS400PASSWORD);\n".getBytes());
  143. outFile.write(" }\n".getBytes());
  144. outFile.write("\n\n public static void callProc() {\n".getBytes());
  145.  
  146. //
  147. endResult = " CallableStatement cs1=null;\n";
  148. outFile.write((endResult).getBytes());
  149.  
  150. endResult = " String query = \"{call " + storedProc.trim() + "(";
  151.  
  152. Vector data = StoredProcWriter.getStoredProcData(lib, storedProc);
  153. for (int j = 1; j<=data.size(); j++) {
  154. endResult = endResult + "?,";
  155. }
  156. endResult = endResult.substring(0, endResult.length()-1) + ")}\";\n\n";
  157. outFile.write(endResult.getBytes());
  158.  
  159. endResult = " try {\n cs1 = conn.prepareCall(query);\n";
  160. outFile.write(endResult.getBytes());
  161.  
  162. int k = 1;
  163. for (Enumeration e = data.elements();e.hasMoreElements();k++) {
  164. Vector v = (Vector)e.nextElement();
  165. String[] s = new String[9];
  166. for (int j=0; j<9; j++) {
  167. s[j] = (String)v.elementAt(j);
  168. }
  169. //System.out.println("\n3=" + s[3] + " 4=" + s[4] + " 5=" + s[5] + " 6=" + s[6] + " 7=" + s[7] + " 8=" + s[8] + " " + "\n");
  170. if ("IN".equals(s[3].trim())) {
  171. outFile.write((" " + getInVariableStatement(k, s[4], s[5], s[6], s[7], s[8])).getBytes());
  172. } else if ("OUT".equals(s[3].trim())) {
  173. outFile.write((" " + getOutVariableStatement(k, s[4], s[5], s[6], s[7], s[8])).getBytes());
  174. } else if ("INOUT".equals(s[3].trim())) {
  175. outFile.write((" " + getInVariableStatement(k, s[4], s[5], s[6], s[7], s[8])).getBytes());
  176. outFile.write((" " + getOutVariableStatement(k, s[4], s[5], s[6], s[7], s[8])).getBytes());
  177. }
  178. }
  179.  
  180. //
  181. outFile.write("\n ResultSet rs = cs1.executeQuery();".getBytes());
  182. outFile.write("\n ResultSetMetaData rsmd = rs.getMEtaData()".getBytes());
  183. outFile.write("\n int nbrColumns = rsmd.getColumnCount();".getBytes());
  184. outFile.write("\n while (rs.next() { ".getBytes());
  185. outFile.write("\n for (int col = 0; col < nbrColumns; col++)".getBytes());
  186. outFile.write("\n System.out.print(rs.getString(col)); ".getBytes());
  187. outFile.write("\n }".getBytes());
  188. outFile.write("\n System.out.println();".getBytes());
  189. outFile.write("\n } catch(SQLException e){e.printStackTrace();}\n".getBytes());
  190. outFile.write("\n finally ".getBytes());
  191. outFile.write("\n {".getBytes());
  192. outFile.write("\n try {cs1.close();} catch(Exception e){e.printStackTrace();}".getBytes());
  193. outFile.write("\n try {conn.close();} catch(Exception e){e.printStackTrace();}".getBytes());
  194. outFile.write("\n }".getBytes());
  195. //
  196. outFile.write("\n }\n public static void main(String args[]) {".getBytes());
  197. outFile.write("\n try {".getBytes());
  198. outFile.write(("\n " + javaFileName + ".setConn();").getBytes());
  199. outFile.write(("\n //ProgramPauser.pauseProgram(\"waiting\");").getBytes());
  200. outFile.write(("\n " + javaFileName + ".callProc();").getBytes());
  201. outFile.write("\n }\n catch(Exception e) {e.printStackTrace();}\n }\n\n}".getBytes());
  202. //
  203. }
  204.  
  205. /**
  206. *
  207. */
  208. static String getOutVariableStatement(int parmNumber, String name, String dataType,
  209. String scale, String precision, String charLength)
  210. {
  211. String endResult=null;
  212. if (SQLBIGINT.equals(dataType.trim()))
  213. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.BIGINT);\n";
  214. else
  215. if (SQLBLOB.equals(dataType.trim()))
  216. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.BLOB);\n";
  217. else
  218. if (SQLCHAR.equals(dataType.trim()))
  219. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.CHAR);\n";
  220. else
  221. if (SQLCLOB.equals(dataType.trim()))
  222. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.CLOB);\n";
  223. else
  224. if (SQLVARCHAR.equals(dataType.trim()))
  225. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.VARCHAR);\n";
  226. else
  227. if (SQLDATE.equals(dataType.trim()))
  228. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.DATE);\n ";
  229. else
  230. if (SQLDISTINCT.equals(dataType.trim()))
  231. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.DISTINCT);\n ";
  232. else
  233. if (SQLDOUBLE.equals(dataType.trim()))
  234. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.DOUBLE);\n";
  235. else
  236. if (SQLDBLBYTECLOB.equals(dataType.trim()))
  237. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.CLOB);\n";
  238. else
  239. if (SQLDEC.equals(dataType.trim()) || SQLNUM.equals(dataType.trim()))
  240. {
  241. if ("0".equals(dataType.trim()))
  242. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.INTEGER);\n";
  243. else
  244. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.DECIMAL);\n";
  245. }
  246. else
  247. if (SQLINT.equals(dataType.trim()))
  248. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.INTEGER);\n";
  249. else
  250. if (SQLROWID.equals(dataType.trim()))
  251. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.INTEGER);\n";
  252. else
  253. if (SQLREAL.equals(dataType.trim()))
  254. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.FLOAT);\n";
  255. else
  256. if (SQLSMALLINT.equals(dataType.trim()))
  257. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.SMALLINT);\n";
  258. else
  259. if (SQLTIMESTAMP.equals(dataType.trim()))
  260. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.TIMESTAMP);\n ";
  261. if (SQLTIME.equals(dataType.trim()))
  262. endResult = " cs1.registerOutParameter(" + parmNumber + ", java.sql.Types.TIME);\n ";
  263.  
  264. System.out.println(endResult);
  265. return endResult;
  266. }
  267.  
  268. /**
  269. *
  270. */
  271. static String getInVariableStatement(int parmNumber, String name, String dataType, String scale, String precision, String charLength)
  272. {
  273. String endResult=null;
  274. int spoofInt = 0;
  275. double spoofDouble=0.0;
  276. String spoofString = "\"\"";
  277. String spoofDate = "java.sql.Date.valueOf(\"2003-01-01\")";
  278. String spoofTime = "java.sql.Time.valueOf(\"23:59:01\")";
  279. String spoofTimeStamp = "java.sql.Timestamp.valueOf(\"2003-01-01 12:01:59.000000001\")";
  280.  
  281. if (SQLCHAR.equals(dataType.trim())
  282. || SQLVARCHAR.equals(dataType.trim())
  283. || SQLCLOB.equals(dataType.trim()))
  284. endResult = " cs1.setString(" + parmNumber + ", " + spoofString + ");\n";
  285. else if (SQLDEC.equals(dataType.trim())
  286. || SQLREAL.equals(dataType.trim())
  287. || SQLNUM.equals(dataType.trim())
  288. || SQLDOUBLE.equals(dataType.trim()))
  289. {
  290. if ("0".equals(dataType.trim()))
  291. endResult = " cs1.setInt(" + parmNumber + ", " + spoofInt + ");\n";
  292. else
  293. endResult = " cs1.setDouble(" + parmNumber + ", " + spoofDouble + ");\n";
  294. }
  295. else if (SQLINT.equals(dataType.trim())
  296. || SQLBIGINT.equals(dataType.trim())
  297. || SQLSMALLINT.equals(dataType.trim()))
  298. endResult = " cs1.setInt(" + parmNumber + ", " + spoofInt + ");\n";
  299. else if (SQLDATE.equals(dataType.trim()))
  300. endResult = " cs1.setDate(" + parmNumber + ", " + spoofDate + ");\n";
  301. else if (SQLTIME.equals(dataType.trim()))
  302. endResult = " cs1.setDate(" + parmNumber + ", " + spoofTime + ");\n";
  303. else if (SQLTIMESTAMP.equals(dataType.trim()))
  304. endResult = " cs1.setTimestamp(" + parmNumber + ", " + spoofTimeStamp + ");\n";
  305. System.out.println(endResult + "\n");
  306.  
  307. return endResult;
  308. }
  309.  
  310.  
  311. /**
  312. *
  313. */
  314.  
  315. public static void setConn()
  316. throws SQLException, ClassNotFoundException, IOException
  317. {
  318. java.util.Properties myProperties = new java.util.Properties();
  319. myProperties.load(new FileInputStream("AS400DEV.properties"));
  320. String AS400SYSTEM = myProperties.getProperty("system");
  321. String AS400USERID = myProperties.getProperty("userid");
  322. String AS400PASSWORD = myProperties.getProperty("password");
  323. // System.out.println("\n" + AS400SYSTEM + "\n" + AS400USERID + "\n" + AS400PASSWORD+ "\n");
  324.  
  325.  
  326. Class.forName("com.ibm.as400.access.AS400JDBCDriver");
  327. // getConnection starts client access job on AS400
  328. conn = DriverManager.getConnection("jdbc:as400://"
  329. + AS400SYSTEM
  330. + ";naming=system;prompt=false",
  331. AS400USERID,
  332. AS400PASSWORD);
  333. }
  334.  
  335. /**
  336. *
  337. */
  338. public static void main(String[] args) throws Exception {
  339. try {
  340. System.out.println("Schema = " + args[0]);
  341. System.out.println("Stored Proc = " + args[1]);
  342. System.out.println("Generated file = " + args[2]);
  343. StoredProcWriter.schema = args[0];
  344. StoredProcWriter.proc = args[1];
  345. StoredProcWriter.javaFileName = args[2];
  346. StoredProcWriter.javaSourceFile = new FileOutputStream(StoredProcWriter.javaFileName+".java", true);
  347. StoredProcWriter.setConn();
  348. StoredProcWriter.writeJdbcCode(StoredProcWriter.schema, StoredProcWriter.proc, StoredProcWriter.javaSourceFile);
  349. }
  350.  
  351. finally {
  352. try {conn.close();conn=null;}
  353. catch(Exception e){}
  354. }
  355. }
  356. }
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!