Nonstop Database, HiRDB Version 9 UAP Development Guide
This subsection explains how to use JDBC and SQLJ together.
You can convert an SQLJ iterator into a JDBC result set (ResultSet object) and use the JDBC API to obtain the retrieval result. For the native interface edition, JDBC result sets cannot be obtained.
To obtain a JDBC result set, use the getResultSet method for the iterator class (ResultSetiterator). This method returns a JDBC result set as a return value. After executing the next method for the iterator, do not invoke the getResultSet method.
After you have used the getResultSet method to convert an SQLJ iterator into a JDBC result set, do not receive a retrieval result using the original iterator.
An example follows:
public void showEmployeeName() throws SQLException { sqlj.runtime.ResultSetIterator iter; #sql iter = { SELECT ename FROM rmp } ; ResultSet rs = iter.getResultSet(); while(rs.next()){ System.out.println("employee name: " + rs.getString(1)); } iter.close(); } |
The JDBC result set (ResultSet) that was created using the JDBC API is converted with the CAST statement and read as a result set of the SQLJ cursor.
The coding example follows:
#sql iterator Employees(String ename, double sal); Statement stmt=conn.createStatement(); String query="SELECT pname, pcode FROM stock WHERE pcode > 1000"; ResultSet rs=stmt.executeQuery(query); Employees emps; #sql emps ={CAST :rs }; |
The SQLJ connection context defines a constructor for generating an object from a JDBC connection. Using this constructor, you can convert a JDBC connection into an SQLJ connection context. Note that JDBC connection is transferred as an argument of the constructor. You can also use both types of connection together.
An example follows:
java.sql.Connection jdbcConCtx =java.sql.DriverManager.getConnection(...); #sql context Inventory; Inventory sljConCtx = new Inventory(jdbcConCtx); |
You can use the getConnection method to get the JDBC connection from an SQLJ connection. You can also use both types of connection together.
With the native interface edition, an SQLJ connection cannot be converted into a JDBC connection. To use the same connection as JDBC, you must create a connection in JDBC beforehand, and then convert the connection into an SQLJ connection context.
An example follows:
#sql context Inventory; Inventory sljConCtx = new Inventory(url); java.sql.Connection jdbcConCtx = sqljConCtx.getConnection(); |
SQLJ can describe only static SQL statements. Therefore, to execute a dynamic SQL statement, you must use the JDBC API.
A dynamic SQL statement is executed using a PreparedStatement object in JDBC.
When the prepareStatement method for the connection context is executed using the SQL as an argument, a PreparedStatement object is returned as a return value.
To set a parameter in a dynamic SQL statement, use the set method of PreparedStatement. To execute the dynamic SQL statement, use the execute method of the PreparedStatement object.
An example of dynamic SQL execution follows:
java.sql.PreparedStatement pstmt = con.prepareStatement( "INSERT INTO FOO_TABLE VALUES(?, ?)"); pstmt.setInt(1, 100); pstmt.setString(2, "test"); pstmt.execute(); |
Only static cursors can be used in SQLJ. Therefore, to use a dynamic cursor, you must use the JDBC API.
When the prepareStatement method for the connection context is executed for a character string that indicates a SELECT statement, a PreparedStatement object is returned as a return value.
To set a parameter, use the set method of PreparedStatement. To execute the SQL statement, use the executeQuery method of the PreparedStatement object. The executeQuery method returns the JDBC result set.
To receive a retrieval result, use the get method for result sets.
An example of retrieval using a dynamic cursor follows:
java.sql.PreparedStatement pstmt = con.prepareStatement( "SELECT NAME, POINT FROM FOO_TABLE WHERE BAR=100"); ResultSet rs = pstmt.executeQuery(); String name; Integer point; rs.next(); name = pstmt.getString(1); point = pstmt.getInteger(2); |
To determine the column name and data type of each retrieval item of a dynamic cursor, use a ResultSetMetaData object. You can obtain a ResultSetMetaData object from the getMetaData object of the result set.
You can also use the getColumnClassName method of the ResultSetMetaData object to obtain the character string that indicates the data type of each retrieval item.
You can use the getColumnName method to obtain column names.
Specify the items to be retrieved using numbers (beginning with 1). You can use the getColumnCount method to obtain the number of columns.
An example of executing the DESCRIBE statement follows:
java.sql.PreparedStatement pstmt = con.prepareStatement( "SELECT * FROM FOO_TABLE"); java.sql.ResultSetMetaData aMeta = pstmt.getMetaData(); int columCount = aMeta.getColumnCount(); Vector nameList = new Vector(); Vector classLis = new Vector(); for(int i = 1; i <= columnCount; i++){ nameList.addElement(aMeta.getColumnName(i)); classList.addElement(a.Meta.getColumnClassName(i)); } Vector dataList = new Vector(); rs.next(); for(int i = 1; i <= columnCount; i++){ dataList.addElement(rs.getObject(i)); } |
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.