Article

Example using Java toolbox driver and naming = sql

Jim Mason

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: