Using input and output parameters

Java methods do not support output parameters. When you wrap a Java method in SQL, however, you can take advantage of Sybase SQLJ capabilities that allow input, output, and input/output parameters for SQLJ stored procedures.

When you create a SQLJ procedure, you identify the mode for each parameter as in, out, or inout.

For input parameters, use the in keyword to qualify the parameter. in is the default; Adaptive Server assumes an input parameter if you do not enter a parameter mode.
For output parameters, use the out keyword.
For parameters that can pass values both to and from the referenced Java method, use the inout keyword.

You create Transact-SQL stored procedures using only the in and out keywords. The out keyword corresponds to the SQLJ inout keyword. See the create procedure reference pages in the Adaptive Server Reference Manual for more information.

To create a SQLJ stored procedure that defines output parameters, you must:

Define the output parameter(s) using either the out or inout option when you create the SQLJ stored procedure.
Declare those parameters as Java arrays in the Java method. SQLJ uses arrays as containers for the method’s output parameter values.

For example, if you want an Integer parameter to return a value to the caller, you must specify the parameter type as Integer[] (an array of Integer) in the method.

The array object for an out or inout parameter is created implicitly by the system. It has a single element. The input value (if any) is placed in the first (and only) element of the array before the Java method is called. When the Java method returns, the first element is removed and assigned to the output variable. Typically, this element will be assigned a new value by the called method.

The following examples illustrate the use of output parameters using a Java method bestTwoEmps() and a stored procedure best2 that references that method.

Writing the Java method

The SQLJExamples.bestTwoEmps() method returns the name, ID, region, and sales of the two employees with the highest sales performance records. The first eight parameters are output parameters requiring a containing array. The ninth parameter is an input parameter and does not require an array.

	public static void bestTwoEmps(String[] n1,
		String[] id1, int[] r1, 
		BigDecimal[] s1, String[] n2,
		String[] id2, int[] r2, BigDecimal[] s2,
		int regionParm) throws SQLException {

	n1[0] = “****”; 	
	id1[0] = ““; 
	r1[0] = 0;
	s1[0] = new BigDecimal(0):
	n2[0] = “****”;
	id2[0] = ““;
	r2[0] = 0;
	s2[0] = new BigDecimal(0);


	try {
		Connection conn = DriverManager.getConnection
			(“jdbc:default:connection”);
		java.sql.PreparedStatement stmt =
			conn.prepareStatement(“SELECT name, id,”
			+ “region_of(state) as region, sales FROM”
			+ “sales_emps WHERE”
			+ “region_of(state)>? AND”
			+ “sales IS NOT NULL ORDER BY sales DESC”);
		stmt.setInteger(1, regionParm);
		ResultSet r = stmt.executeQuery();

		if(r.next()) {
			n1[0] = r.getString(“name”);
			id1[0] = r.getString(“id”);
			r1[0] = r.getInt(“region”);

			s1[0] = r.getBigDecimal(“sales”);
		}
		else return;

		if(r.next()) {
			n2[0] = r.getString(“name”);
			id2[0] = r.getString(“id”);
			r2[0] = r.getInt(“region”);
			s2[0] = r.getBigDecimal(“sales”);
		}
		else return;
	} 
	catch (SQLException e) {
		System.err.println(“SQLException: “ +
			e.getErrorCode() + e.getMessage());
		}
	}

Creating the SQLJ procedure

Create a SQL name for the bestTwoEmps method. The first eight parameters are output parameters; the ninth is an input parameter.

create procedure best2
	(out n1 varchar(50), out id1 varchar(5), 
	out s1 decimal(6,2), out r1 integer, 
	out n2 varchar(50), out id2 varchar(50), 
	out r2 integer, out s2 decimal(6,2), 
	in region integer)
	language java 
	parameter style java 
	external name
		'SQLJExamples.bestTwoEmps (java.lang.String,
		java.lang.String, int, java.math.BigDecimal,
		java.lang.String, java.lang.String, int,
		java.math.BigDecimal, int)'

The SQL procedure signature for best2 is: varchar(20), varchar(5), decimal (6,2) and so on. The Java method signature is String, String, int, BigDecimal and so on.

Calling the procedure

After the method is installed in the database and the SQLJ procedure referencing the method has been created, you can call the SQLJ procedure.

At runtime, the SQL system:

Creates the needed arrays for the out and inout parameters when the SQLJ procedure is called.
Copies the contents of the parameter arrays into the out and inout target variables when returning from the SQLJ procedure.

The following example calls the best2 procedure from isql. The value for the region input parameter specifies the region number.

declare @n1 varchar(50), @id1 varchar(5), 
	@s1 decimal (6,2), @r1 integer, @n2 varchar(50), 
	@id2 varchar(50), @r2 integer, @s2 decimal(6,2),
	@region integer
select @region = 3
execute best2 @n1 out, @id1 out, @s1 out, @r1 out,
	@n2 out, @id2 out, @r2 out, @s2 out, @region

Adaptive Server calls SQLJ stored procedures exactly as it calls Transact-SQL stored procedures. Thus, when using isql or any other non-Java client, you must precede parameter names by the @ sign.