Mapping Java and SQL datatypes

When you create a stored procedure or function that references a Java method, the datatypes of input and output parameters or result sets must not conflict when values are converted from the SQL environment to the Java environment and back again. The rules for how this mapping takes place are consistent with the JDBC standard implementation. They are shown below and in Table 5-1.

Each SQL parameter and its corresponding Java parameter must be mappable. SQL and Java datatypes are mappable in these ways:

• A SQL datatype and a primitive Java datatype are simply mappable if so specified in Table 5-1.

• A SQL datatype and a non-primitive Java datatype are object mappable if so specified in Table 5-1.

• A SQL abstract datatype (ADT) and a non-primitive Java datatype are ADT mappable if both are the same class or interface.

• A SQL datatype and a Java datatype are output mappable if the Java datatype is an array and the SQL datatype is simply mappable, object mappable, or ADT mappable to the Java datatype. For example, character and String[] are output mappable.

• A Java datatype is result-set mappable if it is an array of the result set-oriented class: java.sql.ResultSet.

In general, a Java method is mappable to SQL if each of its parameters is mappable to SQL and its result set parameters are result-set mappable and the return type is either mappable (functions) or void or int (procedures).

Support for int return types for SQLJ stored procedures is a Sybase extension of the SQLJ Part 1 standard.

Specifying Java method signatures explicitly or implicitly

When you create a SQLJ function or stored procedure, you typically specify a Java method signature. You can also allow Adaptive Server to infer the Java method signature from the routine’s SQL signature according to standard JDBC datatype correspondence rules described earlier in this section and in Table 5-1.

Sybase recommends that you include the Java method signature as this practise ensures that all datatype translations are handled as specified.

You can allow Adaptive Server to infer the method signature for datatypes that are:

• Simply mappable

• ADT mappable

• Output mappable

• Result-set mappable

For example, if you want Adaptive Server to infer the method signature for correct_states, the create procedure statement is:

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

Adaptive Server infers a Java method signature of java.lang.String and java.lang.String. If you explicitly add the Java method signature, the create procedure statement looks like this:

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)’

You must explicitly specify the Java method signature for datatypes that are object mappable. Otherwise, Adaptive Server infers the primitive, simply mappable datatype.

For example, the SQLJExamples.job method contains a parameter of type int. (See “Handling null argument values”.) When creating a function referencing that method, Adaptive Server infers a Java signature of int, and you need not specify it.

However, suppose the parameter of SQLJExamples.job was Java Integer, which is the object-mappable type. For example:

public class SQLJExamples {
	public static String job(Integer jc)
			throws SQLException ...

Then, you must specify the Java method signature when you create a function that references it:

create function job_of(jc integer)
...
external name
	'SQLJExamples.job(java.lang.Integer)'

Returning result sets and method overloading

When you create a SQLJ stored procedure that returns result sets, you specify the maximum number of result sets that can be returned.

If you specify a Java method signature, Adaptive Server looks for the single method that matches the method name and signature. For example:

create procedure ranked_emps(region integer)
	dynamic result sets 1
	language java parameter style java
	external name 'SQLJExamples.orderedEmps
		(int, java.sql.ResultSet[])'

In this case, Adaptive Server resolves parameter types using normal Java overloading conventions.

Suppose, however, that you do not specify the Java method signature:

create procedure ranked_emps(region integer)
	dynamic result sets 1
	language java parameter style java
	external name 'SQLJExamples.orderedEmps'

If two methods exist, one with a signature of int, RS[ ], the other with a signature of int, RS[ ], RS[ ], Application Server cannot distinguish between the two methods and the procedure fails. If you allow Adaptive Server to infer the Java method signature when returning result sets, make sure that only one method satisfies the inferred conditions.

Ensuring signature validity

If an installed class has been modified, Adaptive Server checks to make sure that the method signature is valid when you invoke a SQLJ procedure or function that references that class. If the signature of a modified method is still valid, the execution of the SQLJ routine succeeds.

---

Copyright © 2001. Sybase Inc. All rights reserved.

View this book as PDF