EM 431 ASA and the new SQLJ standards

cavalcadejewelSoftware and s/w Development

Nov 18, 2013 (4 years and 1 month ago)

110 views

EM 431

ASA and the new SQLJ
standards

Shannon White

Software Developer

iAnywhere Solutions

shannon.white@sybase.com

What is SQLJ?


SQLJ1: ANSI NCITS 331.1
-
2000


Calling java methods from SQL


SQLJ2: ANSI NCITS 331.2
-
2000


Mapping java types to SQL types


To be merged in SQL/JRT for SQL/2000


Not covered here:


SQLJ0: The Java language bindings

Why SQLJ?


SQL PSM (Persistent Stored Modules)


Not object
-
oriented


Small standard library


Only usable within a database server


Small developer community


Vendor specific variations


Java


Object
-
oriented


Large library of standard packages


Ubiquitous from client tier to server tier


Large developer community

SQLJ1 Overview


Java classes in SQL


Installing/updating/removing classes


Name resolution path within java classes


Privileges for accessing java classes


Calling java static methods from SQL


Returning out and inout parameters


Returning SQL result sets from java

SQLJ2 Overview


Syntax for mapping SQL types to java classes:


Creating/dropping of mapping


Instantiation of java objects from SQL


Calling methods on java objects from SQL


Ordering of java objects referenced from SQL


Persistence of java objects


Java class must implement Serializable or
SQLData

Support for Java in the ASA
database server


Java supported in the database server since ASA 6.0


Java VM runs in
-
process


Java support fully integrated throughout


Access to java from SQL


Access to SQL through JDBC


Persistence of java object data in the database


Transfer of java objects between client and server


Support for JDK 1.3 in upcoming ASA 8.0 release

Mapping java classes to SQL
types


Install a java class using the install statement:



install java {new|update} [jar ‘jar
-
name’]



from {file <file
-
name> | <expr>}



Corresponding SQL type(s) automatically created


SQL type name is same as fully qualified java name


Ex.


Java class
BigDecimal

in package
java.math


Corresponding SQL type:

java.math.BigDecimal

Installing from a file


Install a class from a file



install java new



from file

‘d:
\
\
java
\
\
myClass.class’



Creates the SQL type
myClass



Install a class from a jar file



install java new jar

‘testjar’



from file

‘d:
\
\
mytest.jar’



Creates a SQL type for each class in
mytest.jar

Installing from a variable

Example:


create variable

@byte_code
long binary
;

set

@byte_code = 0xCAFEBABE…;

install java update from

@byte_code;



Useful in several circumstances:


Class file not accessible from server


Class byte code not stored in file


Used in ASA unload/reload, dbtran and SQL Remote

Java methods and fields


Java static and instance methods callable from SQL


Java static and instance fields SQL
-
accessible


Method and field names map to same name in SQL


Syntax for accessing static methods and fields:


The java SQL type name prefixes the method or field


Accessing instance methods and fields:


Java instance reference prefixes the method or field


Instance references include


Java
-
typed column references


Java
-
typed SQL variables


Java
-
typed expressions

Calling a java method


Examples:


Converting from decimal to hexadecimal



select

java.lang.Integer.toString( 90, 16 )



produces row:




5a


Setting a standard scale



create variable

bd java.math.BigDecimal;



set

bd = new java.math.BigDecimal( ‘123.456’ );



select

bd.setScale( 1, ROUND_UP );



produces row:




123.5

Out and inout parameters


Not naturally a part of java


Uses java arrays of unit length

Example:


void myMethod( int[] myout ) { myout[0] = 123; }


Maps to:


create

variable

out_var
int
;

call

myInstance.myMethod( out_var );


Value of

out_var
is

123

Returning SQL result sets


Not naturally a part of java


Uses arrays of java.sql.ResultSet

Example:


void getRSet( ResultSet[] rset ) { rset[0] = …; }


Maps to:


select

myInstance.getRSet();

Accessing SQL from java


Server
-
side JDBC 2.0 driver


Full access to power of SQL


Prepared statements


Batch statements


Result sets


Scrollable cursors


Updateable cursors


Database and result set meta data

Persistence of java objects


Java objects stored in Java
-
typed table columns


Live across server shutdown and restart


Compact binary representation


create table

ttt ( bd java.math.BigDecimal );

insert into

ttt
values


(new java.math.BigDecimal(‘123.456’));

select

bd.negate()
from

ttt;


Produces row:


-
123.456

Java objects between client
and server


JDBC


ASA JDBC driver JConnect


setObject() and getObject() methods


ODBC/OLEDB, OpenClient, DBLIB


Objects sent as long binary SQL type


Object must be java.io.Serializable


Sun serialization


SQLRemote


Objects sent as long binary


Serialization used is more compact

Is Java worth the trouble?

Is it worth switching development from SQL to java?


Java is the considered “better” language


Object
-
oriented, standard


Exploding development community


Large pre
-
existing code base

Is that enough to justify a switch?


Switch costs


Acquiring java programmer experience


Maintenance of legacy SQL procedures

Java cons


Starting the java VM


Can take a couple seconds to create first VM


Garbage collection


Can occur at inopportune times


Can make it difficult to predict execution time


Java is all unicode


Unicode to/from native character set costs


Sun serialization is slow and bulky


Persisting java object usually slower than storing
constituent data

More than just a better
language

The ASA support provides dramatic new capabilities not
required by SQLJ and not available in SQL


ASA 7.0


Multi
-
threading within a connection


Access to the file system (java.io.*)


Access to the internet (java.net.*)


ASA 8.0


Remote procedure calls (java.rmi.*)


Enhanced security features (ASA 8.0)


JDK 1.3 support


Multi
-
threading


Use standard java.lang.Thread API


Java threads can be spawned, interrupted, and killed


Threads execute concurrently on the same connection


Only single
-
threaded access to JDBC


Child threads killed when parent thread exits VM


Daemon threads not allowed

File I/O


Support for JDK’s java.io package


Access to file system from perspective of server


Features abstraction of file and pathnames


Access restricted by default


Access granted via database option and
SecurityManager

Socket support


Support for JDK’s java.net package


Provides classes for implementing network apps


Access restricted by default


Access granted via database option and
SecurityManager


RPC


Support for JDK’s java.rmi package


Provides classes for distributed programming


JDK 1.3 includes support for CORBA’s IIOP


Access restricted by default


Access granted via database option and
SecurityManager

Security


SecurityManager


Java object that governs access to permission
-
sensitive resources


Ex. file I/O, sockets


Extend to enterprise
-
specific resources


DBA can associate user with a SecurityManager


Limits user’s access to resources accessible from java


Default restricts access to file I/O and sockets

JDK 1.3 support


Previous support based on JDK 1.1.6


ASA 8.0 beta provides JDK1.3.0 support


ASA 8.0 release will likely provide JDK 1.3.1 support


New capabilities


Collections framework


JDBC 2.0


Reference Objects


JNDI


RMI over IIOP


Reflection enhancements

Future development


Just
-
in
-
time (JIT) compiler


Already in ASE 12.5


Dynamic class loading


JDK 1.4


J2ME

Conclusion


Java in the database server can be useful


Has to be more than a “better” language


Must provide capabilities not available in SQL


ASA provides java in the database server


Multi
-
threading


I/O, sockets, RPC


JDK 1.3