Example using Java toolbox driver and naming = sql

Example using Java toolbox driver and naming = sql

//	define parameters for the JDBC code

	String jdbcDriverName = "com.ibm.as400.access.AS400JDBCDriver";
	String naming = "sql";

	String dataSourceName = "//S103X7TM";
	String aUser = "jem";	
	String aPassword = "jem";
	String tableName = "EMPLOYEE";
	String schemaName = "CCBS";		

//	load the JDBC driver class, create statement and set connection properties

	Class.forName(jdbcDriverName).newInstance();
	String aUrl = "jdbc:as400:" + dataSourceName;
	String stmt = "SELECT * FROM " + tableName;
	String cmdStmt = "SET SCHEMA = " + schemaName;

	java.util.Properties properties = new java.util.Properties(); 
  	properties.put("user", aUser);  	properties.put("password", aPassword);
	properties.put("language", "Java");
	properties.put("naming", naming);
	if (naming.equals("sql")) {
		aUrl = aUrl + "/"+ schemaName;
	} else {
		properties.put("naming", naming);
	};

	StringBuffer sb = new StringBuffer();
	sb.append("n	report for SQL: " + stmt + "   from " + aUrl + "	naming = " + naming + "n");
	int colCount = 0;	int rowCount = 0;
	String aColName;	boolean metaDataDefined = false;
	java.sql.Statement aQryStmt = null;
	java.sql.Connection aDbConnection =	null;

	try {
//	"connect to url"
	java.sql.Driver aDriver = java.sql.DriverManager.getDriver(aUrl);	
	aDbConnection =	aDriver.connect(aUrl, properties); 

//	"create a select query statement"
	aQryStmt = ((java.sql.Statement) aDbConnection.createStatement());
	java.sql.ResultSet resultSet =	aQryStmt.executeQuery(stmt);

//	"access rows"
	while (resultSet.next()) {

//	START: do this to list column headings on selected columns in result set

		if ( ! metaDataDefined ) {
			colCount = resultSet.getMetaData().getColumnCount();
			sb.append("n");

			for ( int i = 1; i <= colCount; i++) {
				sb.append("	" + resultSet.getMetaData().getColumnName(i));
			};
			sb.append("n");
			metaDataDefined = true;
			};
//	END: do this to list column headings on selected columns in result set

		sb.append("n");
		for ( int i = 1; i <= colCount; i++) {
			sb.append("	" + resultSet.getObject(i));
			};
		};
	} catch (Exception ex) {
		sb.append("n ERROR: " + ex);
		sb.append("n SQL WARNINGS: " + aDbConnection.getWarnings());
		ex.printStackTrace();
	} finally {
		aQryStmt.close();
		aDbConnection.close();
		System.out.println(sb.toString());
	};
	System.out.println(sb.toString())

Sample output:

report for SQL: SELECT * FROM EMPLOYEE   from jdbc:as400:192.168.1.15/CCBS	naming = sql

	EMPNO       FIRSTNME    MIDINIT     LASTNAME    WORKDEPT    PHONENO
	000010      CHRISTINE     I                 HAAS                 A00               3978
	000020      MICHAEL         L                THOMPSON    B01               122
	000030      SALLY              A                 KWAN              C01               4738
	000050      JOHN               B                GEYER             E01               6789
	000060      IRVING             F                STERN             D11               6423

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close