Neoview Guide to Stored Procedures in Java (R2.2)

Those java.sql.Connection objects are portable to other database management systems
(DBMSs).
Neoview SQL controls default connections in the SPJ environment and closes default connections
when they are no longer needed. Therefore, you do not need to use the close() method in an
SPJ method to explicitly close a default connection when the connection is no longer needed.
If an SPJ method returns result sets, do not explicitly close the default connection. Neoview SQL
closes the connection used to return result sets after it finishes processing the result sets. If an
SPJ method closes the connection on which the result sets are being returned, those result sets
will be lost, and the calling application will not be able to process them.
A default connection that is acquired when an SPJ method executes is not guaranteed to remain
open for future invocations of the SPJ method. Therefore, do not store default connections in
static variables for future use.
The default connection URL, "jdbc:default:connection", is invalid outside a DBMS, such
as when you execute a Java method in an application. To write an SPJ method that operates in
a DBMS, in an application, or both, without having to change and recompile the code, use the
sqlj.defaultconnection system property:
String s = System.property("sqlj.defaultconnection");
if (s == null) {
s = other-url;
}
Connection c = DriverManager.getConnection(s);
The value of sqlj.defaultconnection is "jdbc:default:connection" in a DBMS and
null outside a DBMS.
Using JDBC Method Calls
To perform SQL operations on a Neoview database, use JDBC method calls in the SPJ method.
You do not have to explicitly load the JDBC driver before establishing a connection to the Neoview
database. Neoview SQL automatically loads the JDBC driver when the SPJ is called.
Here is an example of an SPJ method, adjustSalary(), that uses JDBC method calls to adjust
an employee’s salary in the EMPLOYEE table:
public class Payroll {
public static void adjustSalary(BigDecimal empNum,
double percent,
BigDecimal[] newSalary)
throws SQLException
{
Connection conn = DriverManager.getConnection("jdbc:default:connection");
PreparedStatement setSalary =
conn.prepareStatement("UPDATE persnl.employee " +
"SET salary = salary * (1 + (? / 100)) " +
"WHERE empnum = ?");
PreparedStatement getSalary =
conn.prepareStatement("SELECT salary " +
"FROM persnl.employee " +
"WHERE empnum = ?");
setSalary.setDouble(1, percent);
setSalary.setBigDecimal(2, empNum);
setSalary.executeUpdate();
getSalary.setBigDecimal(1, empNum);
ResultSet rs = getSalary.executeQuery();
rs.next();
newSalary[0] = rs.getBigDecimal(1);
rs.close();
conn.close();
30 Developing SPJ Methods