Although the IBM i database supports physical files with multiple members, SQL (and consequently JDBC) is designed to access only the first member (which is usually the member with the same name as the file/table). You can do this by creating an SQL Alias. Suppose that you have a physical file MYLIB/SALES with twelve members (one member for each month of the year). To access JANUARY, you must first create an alias with the following SQL command:
CREATE ALIAS MYLIB/SALES_JANUARY FOR MYLIB/SALES(JANUARY)
Sample Java program to access multiple member Physical file
package com.as400samplecode; import java.io.FileInputStream; import java.sql.Connection; import java.sql.DriverManager; import java.sql.PreparedStatement; import java.sql.ResultSet; import java.util.Properties; public class As400MultiFormatJava { static Properties props; public static void main(String[] args) { String db2FileName = ""; String db2MemberName = ""; if (args.length < 2) { As400MultiFormatJava as400MultiFormatJava = new As400MultiFormatJava(); System.err.println("Usage: java "+ as400MultiFormatJava.getClass().getName()+ " Db2_File_Name Db2_File_Member_Name"); System.exit(1); } db2FileName = args[0].trim(); db2MemberName = args[1].trim(); AccessMultiFormatFile(db2FileName, db2MemberName); } private static void AccessMultiFormatFile(String db2FileName, String db2MemberName){ try { props = new Properties(); props.load(new FileInputStream("properties/dataconnection.properties")); String DRIVER = "com.ibm.as400.access.AS400JDBCDriver"; String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full"; Connection conn = null; String sql = null; //Connect to iSeries Class.forName(DRIVER); conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim()); //Create alias if other than the default member is accessed if(db2MemberName.equalsIgnoreCase("") == false){ sql = "Create ALIAS QTEMP/" + db2FileName + "_" + db2MemberName + " for " + db2FileName + "(" + db2MemberName + ")"; System.out.println("SQL: " + sql); PreparedStatement stmt = conn.prepareStatement(sql); stmt.executeUpdate(); sql = "Select * from QTEMP/" + db2FileName + "_" + db2MemberName; } else { sql = "Select * from " + db2FileName; } System.out.println("SQL: " + sql); //Database PreparedStatement stmt = conn.prepareStatement(sql); ResultSet rs = stmt.executeQuery(); while (rs.next()) { System.out.println("Order#: ----> " + rs.getString("X1ORD")); System.out.println("Order Date: " + rs.getString("X1ORDT")); System.out.println("Customer: " + rs.getString("X1CUST")); System.out.println("PO Number: " + rs.getString("X1PO")); System.out.println("Order Total: " + rs.getString("X1OTOT")); } rs.close(); stmt.close(); conn.close(); } catch (Exception e) { System.out.println(e); } } }
No comments:
Post a Comment
NO JUNK, Please try to keep this clean and related to the topic at hand.
Comments are for users to ask questions, collaborate or improve on existing.