EM 431 ASA and the new SQLJ standards

1. EM 431ASA and the new SQLJ standards • Shannon White • Software Developer • iAnywhere Solutions • shannon.white@sybase.com

2. 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 What is SQLJ?

3. 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 Why SQLJ?

4. 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 SQLJ1 Overview

5. 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 SQLJ2 Overview

6. 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 Support for Java in the ASA database server

7. 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 Mapping java classes to SQL types

8. 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 file

9. 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 Installing from a variable

10. 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 Java methods and fields

11. 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 Calling a java method

12. Not naturally a part of java Uses java arrays of unit length Example: void myMethod( int[] myout ) { myout[0] = 123; } Maps to: createvariable out_var int; call myInstance.myMethod( out_var ); Value of out_var is 123 Out and inout parameters

13. Not naturally a part of java Uses arrays of java.sql.ResultSet Example: void getRSet( ResultSet[] rset ) { rset[0] = …; } Maps to: select myInstance.getRSet(); Returning SQL result sets

14. 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 Accessing SQL from java

15. 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 Persistence of java objects

16. 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 Java objects between client and server

17. 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 Is Java worth the trouble?

18. 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 Java cons

19. 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 More than just a better language

20. 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 Multi-threading

21. 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 File I/O

22. Support for JDK’s java.net package Provides classes for implementing network apps Access restricted by default Access granted via database option and SecurityManager Socket support

23. 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 RPC

24. 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 Security

25. 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 JDK 1.3 support

26. Just-in-time (JIT) compiler Already in ASE 12.5 Dynamic class loading JDK 1.4 J2ME Future development

27. 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 Conclusion