Modifying SQL data

You can use a SQLJ stored procedure to modify information in the database. The method referenced by the SQLJ procedure must be either:

A method of type void, or
A method with an int return type (incorporation of the int return type is a Sybase extension of the SQLJ standard).

Writing the Java method

The method SQLJExamples.correctStates() performs a SQL update statement to correct the spelling of state codes. Input parameters specify the old and new spellings. correctStates() is a void method; no value is returned to the caller.

public static void correctStates(String oldSpelling, 				String newSpelling) throws SQLException {

	Connection conn = null;
	PreparedStatement pstmt = null;
	try {
		Class.forName(”sybase.asejdbc.ASEDriver”);
		conn = DriverManager.getConnection
			(“jdbc:default:connection”);	
	} 
	catch (Exception e) {
		System.err.println(e.getMessage() + 
			“:error in connection”);
	}
	try {
		pstmt = conn.prepareStatement
			(“UPDATE sales_emps SET state = ? 
			WHERE state = ?”);
		pstmt.set.String(1, newSpelling);
		pstmt.set.String(2, oldSpelling);
		pstmt.executeUpdate();
	}
	catch (SQLException e) {
		System.err.println(“SQLException: “ +
		e.getErrorCode() + e.getMessage());
	}
	return;
}

Creating the stored procedure

Before you can call a Java method with a SQL name, you must create the SQL name for it using the SQLJ create procedure command. The modifies sql data clause is optional.

create procedure correct_states(old char(20), 
		not_old char(20))
	modifies sql data
	language java parameter style java
	external name
		'SQLJExamples.correctStates
		(java.lang.String, java.lang.String)'

The correct_states procedure has a SQL procedure signature of char(20), char(20). The Java method signature is java.lang.String, java.lang.String.

Calling the stored procedure

You can execute the SQLJ procedure exactly as you would a Transact-SQL procedure. In this example, the procedure executes from isql:

execute correct_states 'GEO', 'GA'