EM 431 ASA and the new SQLJ standards

1
EM 431ASA and the new SQLJ standards
Shannon White Software Developer iAnywhere
Solutions shannon.white_at_sybase.com
2
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

3
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

4
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

5
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

6
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

7
Mapping java classes to SQL types

• Install a java class using the install statement
• install java newupdate jar jar-name
• from file ltfile-namegt ltexprgt
• 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

8
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

9
Installing from a variable

• Example
• create variable _at_byte_code long binary
• set _at_byte_code 0xCAFEBABE
• install java update from _at_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

10
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

11
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

12
Out and inout parameters

• Not naturally a part of java
• Uses java arrays of unit length
• Example
• void myMethod( int myout ) myout0 123
• Maps to
• create variable out_var int
• call myInstance.myMethod( out_var )
• Value of out_var is 123

13
Returning SQL result sets

• Not naturally a part of java
• Uses arrays of java.sql.ResultSet
• Example
• void getRSet( ResultSet rset ) rset0
• Maps to
• select myInstance.getRSet()

14
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

15
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

16
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

17
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

18
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

19
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

20
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

21
File I/O

• Support for JDKs 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

22
Socket support

• Support for JDKs java.net package
• Provides classes for implementing network apps
• Access restricted by default
• Access granted via database option and
  SecurityManager

23
RPC

• Support for JDKs java.rmi package
• Provides classes for distributed programming
• JDK 1.3 includes support for CORBAs IIOP
• Access restricted by default
• Access granted via database option and
  SecurityManager

24
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 users access to resources accessible from
  java
• Default restricts access to file I/O and sockets

25
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

26
Future development

• Just-in-time (JIT) compiler
• Already in ASE 12.5
• Dynamic class loading
• JDK 1.4
• J2ME

27
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