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.

NoteYou 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:

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
		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( {
			n1[0] = r.getString(“name”);
			id1[0] = r.getString(“id”);
			r1[0] = r.getInt(“region”);
			s1[0] = r.getBigDecimal(“sales”);
		else return;

		if( {
			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:

  1. Creates the needed arrays for the out and inout parameters when the SQLJ procedure is called.

  2. 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 

NoteAdaptive 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.