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:

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.

Table 5-1: Simply and object mappable SQL and Java datatypes

SQL datatype

Corresponding Java datatypes

Simply mappable

Object mappable

char/unichar

java.lang.String

nchar

java.lang.String

varchar/univarchar

java.lang.String

nvarchar

java.lang.String

text

java.lang.String

numeric

java.math.BigDecimal

decimal

java.math.BigDecimal

money

java.math.BigDecimal

smallmoney

java.math.BigDecimal

bit

boolean

Boolean

tinyint

byte

Integer

smallint

short

Integer

integer

int

Integer

real

float

Float

float

double

Double

double precision

double

Double

binary

byte[]

varbinary

byte[]

datetime

java.sql.Timestamp

smalldatetime

java.sql.Timestamp

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:

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.

NoteThe number of dynamic result sets specified only affects the maximum number of results that can be returned. It does not affect method overloading.

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.