Greetings from the Edge: Using javaobj in DATA Step

Greetings from the Edge:Using javaobj in DATA Step Richard A. DeVenezia

Java is ... • java.sun.com“The Java 2 Platform provides robust end-to-end solutions for networked applications as well as a trusted standard for embedded applications” • Widespread • Networked • Applications • Analysis and management

javaobj is ... • DATA Step • Experimental • Part of broader initiative • Component Interface • Object Dot Syntax

Component Interface is ... • support.sas.com/rnd/... “DATA Step Component Interface provides a mechanism for accessing predefined component objects from within a DATA Step program.” • Instantiate • Create an object • declare Type var, var = _new_ • Access • var.method()

Java development • Free kit • java.sun.com/j2se • compiler, packager and loaderjavac, jar, java • Runtime Environment • Online training • Tutorials • numerous web sites and publications

SAS session • CLASSPATH • Location of Java classes • Configuration options • Host preset environment variable • Command line-set CLASSPATH myPath;%CLASSPATH% • configuration file (sasv9.cfg)-set CLASSPATH “myPath;%CLASSPATH%”

Java coding pattern public class Class { private double variable; public double getVariable() { return this.variable; } public void setVariable(double variable) { this.variable = variable; } }

Declare statement declare javaobj var; var = _NEW_ javaobj ('Class' [,arg-1[,…[,arg-N]]) ; - or - declare javaobj var ('Class' [,arg-1[,…[,arg-N]]) ;

Signature public void setX (double X) { this.X = X } public void setX (String X) { try { this.X = Double.parseDouble(X); } catch (Exception e) { this.X = Double.Nan; } } • Pattern of argument types • Correspondence • DATA Step ERROR: ?

Accessing methods and fields • public Type Method (args…) {…} • obj.callTypeMethod ( ‘Method’, args…, return ); • fields: obj.[get|set]TypeField ( ‘field’, value );

HelloSAS.java public class HelloSAS { public HelloSAS () {} public String getMessage () { return "Hello SAS"; } }

HelloSAS.sas data _null_; declare javaobj j (’HelloSAS'); length message $200; j.callStringMethod ('getMessage', message); put message=; run; --- log -- message=Hello SAS

Gotchas • Classes are cached per SAS session • Java class recompiled ? • Restart SAS • Signature Types • pass double, String, Object (new) • return double, String

Example 8 - Enumeration import java.util.Enumeration; public class Example8 { private Enumeration e; public Example8 () { e = System.getProperties().propertyNames(); } public String getProperty () { if (e.hasMoreElements()) { String p = (String) e.nextElement(); return p + "=" + System.getProperty(p); } else { return null; } } }

Example 8 - DATA Step data _null_; dcl javaobj j ('Example8'); length s $200; j.callStringMethod ('getProperty', s); do while (s ne ''); put s; j.callStringMethod ('getProperty', s); end; run; --- log --- … java.vm.version=1.4.1_01-b01 java.vm.vendor=Sun Microsystems Inc.

Object Persistence • No • javaobj gone when DATA Step ends • obj.delete() recommended • Birdie: “An instantiated javaobj creates a JNI reference which will not be garbage collected. The reference needs to be explicitly deleted.”

A Case for Persistence • Creating SAS Table from Query • Query -> ResultSet • Requires Two SAS Passes • ResultSetMetaData • Read ResultsSet into Data Set • Same Query for each pass • not wanted

Databases • Commercial • Open source • Postgresql, mySQL • communities of devoted developers and users • JDBC • 109 drivers • trademark • not an acronym (just like ess-a-ess)

Persistence == RMI • Objects can persist outside SAS • in RMI server process • Obtaining reference and access • requires Java wrapper class

Gateway - an RMI Scheme • Server • DATA Step is client of server • via a wrapper • Server allocates resources • Returns handles • Methods

Gateway Implementation • Three Classes • An Interface • GatewayInterface • An Implementation • GatewayManager • A Server • GatewayServer

GatewayInterface • Declares methods public int getConnectionHandle ( String driverClass, String databaseURL, String username, String password )throws RemoteException; public int getStatementHandle (int cHandle) throws RemoteException; public int executeQuery (int handle, String sql) throws RemoteException;

GatewayManager • Implements the Interface public int getConnectionHandle ( String driverClass, String databaseURL, String username, String password)throws RemoteException{… try { System.out.println ("loading "+driverClass); Class.forName(driverClass); System.out.println ("connecting to "+databaseURL); con = DriverManager.getConnection (databaseURL, username, password); System.out.println ("connected");}…

GatewayServer • Hosts a GatewayManager protected static final String RMI_NAME = "JDBC-GATEWAY-MANAGER";public static void main(String args[]){…try { LocateRegistry.createRegistry(1099); GatewayManager manager = new GatewayManager (5); Naming.rebind (RMI_NAME, manager); System.out.println ( manager.getClass().getName() + " ready to manage " + 5 + " connections.");}…

GatewayServer • getReferenceToPersistentManager() • Convience method • Client starts immediately after server for (i=0;i<4;i++) { try { remote = Naming.lookup(RMI_NAME); } catch (java.rmi.NotBoundException e) { Thread.currentThread().sleep(250/*ms*/); }}

DataStepGatewayAdapter • Reimplements GatewayInterface • delegates everything to • performs typecasting where needed public class DataStepGatewayAdapter{ private GatewayInterface dbi; public DataStepGatewayAdapter() throws Exception { dbi = GatewayServer.getReferenceToPersistentManager ();} public int getStatementHandle (double cHandle) throws Exception{ return dbi.getStatementHandle ( (int) cHandle); }

SAS Macros • Facilitate use of Gateway • startServer • getConnectionHandle • getStatementHandle • jdbcLoadTable • jdbcQuery

Using the macros %let jdbc_server_policy = gateway.policy; %let jdbc_driver = org.postgresql.Driver; %let db_url = jdbc:postgresql://www.devenezia.com:5434/sesug03demo; %let username = sesug03demo ; %let password = D3m0oeoe; %let cHandle =; %getConnectionHandle ( driver = &jdbc_driver , url = &db_url , user = &username , pass = &password , cHandle_mv = cHandle );

Using the macros %jdbcLoadTable ( cHandle=&cHandle, data=sashelp.zipcode, obs=20); %jdbcQuery ( cHandle=&cHandle, sql=SELECT * FROM ZIPCODE, out=WORK.ZIPCODE);

JDBC connection pattern Class.forName(jdbcDriver); Connection con = DriverManager.getConnection ( URL, username, password ); • jdbcDriver = “org.postgresql.Driver” • URL = “jdbc:postgresql://www.devenezia.com:5434/sesug03demo”; • username = “sesug03demo” ; • password = “D3m0oeoe”;

Conclusion • Javaobj opens new horizons • Hybrid solutions • Combine best features of different technologies • Web www.devenezia.com/papers/sesug-2003

Greetings from the Edge: Using javaobj in DATA Step