Greetings from the Edge:
Using JavaObj in DATA Step

by Richard A. DeVenezia, Back to Home

You are viewing Gateway SAS Code

javaobj requires SAS version 9. javaobj is experimental in the first release of version 9.

ToggleIntroduction

These macro utilize javaobjs and facilitate acccess to the Gateway Manager by implementing task level behaviour.

ToggleGateway Macros

These macros will let you access the Gateway in a nearly transparent way. Enter only a few parameters about the database you are connecting to and off you go.

Many of the macros cause a DATA Step boundary and 'pass' back the handles received from the gateway in macro variables (of callees choosing). These DATA Steps use a javaobj variable with name ji. ji is an indicator that the variable is an instance of DataStepGatewayAdapter (the adapter from this side of things looks like an interface).

The two macros that would be used at a task level are jdbcLoadTable and jdbcQuery. jdbcLoadTable moves data from your SAS session to the remote database in one pass of your SAS table. jdbcQuery moves data from the remote database to your SAS session; it requires two DATA Steps, but only one query against the remote database (thanks to the GatewayManager!)

The source code has been marked as follows; where macros are defined, the macro is boxed and large. The invocation of a macro is in a white box and if clicked will show you the macro definition. The invocation of a java method is in a light-yellow box and if clicked will take you to the method definition.

gateway-macros.sas 
%*------------------------------------------------;
%macro startServer (policy=);
  %local restore;
  %let restore
  = %sysfunc(getoption(xsync))
    %sysfunc(getoption(xwait))
    %sysfunc(getoption(xmin))
  ;
  options noxsync noxwait xmin;
  %sysexec start "Jdbc Gateway Server" java -Djava.security.policy=&policy GatewayServer ;
  options &restore;
%mend;
%*------------------------------------------------;
%macro getConnectionHandle (driver=,url=,user=,pass=,cHandle_mv=);
  data _null_;
    declare javaobj ji ("DataStepGatewayAdapter");
    ji.callIntMethod (
      "getConnectionHandle"
    , "&driver"
    , "&url"
    , "&user"
    , "&pass"
    , handle
    );
    ji.delete();
    put "Connection " handle "established.";
    call symput ("&cHandle_mv", trim(left(put(handle,best12.))));
  run;
%mend;
%*------------------------------------------------;
%macro getStatementHandle (cHandle=, sHandle_mv=);
  data _null_;
    declare javaobj ji ("DataStepGatewayAdapter");
    ji.callIntMethod (
      "getStatementHandle"
    , &cHandle
    , handle
    );
    ji.delete();
    put "Statement " handle "obtained.";
    call symput ("&sHandle_mv", trim(left(put(handle,best12.))));
  run;
%mend;
%*------------------------------------------------;
%macro closeConnection (cHandle=);
  data _null_;
    declare javaobj ji ("DataStepGatewayAdapter");
    ji.callVoidMethod (
      "closeConnectionHandle"
    , &cHandle
    );
    ji.delete();
  run;
%mend;
%*------------------------------------------------;
%macro closeStatement (sHandle=);
  data _null_;
    declare javaobj ji ("DataStepGatewayAdapter");
    ji.callVoidMethod (
      "closeStatementHandle"
    , &sHandle
    );
    ji.delete();
  run;
%mend;
%*------------------------------------------------;
%macro closeResultSet (rHandle=);
  data _null_;
    declare javaobj ji ("DataStepGatewayAdapter");
    ji.callVoidMethod (
      "closeResultSetHandle"
    , &rHandle
    );
    ji.delete()
  run;
%mend;
%*------------------------------------------------;
%macro tlp(varname,format);
  trim(left(put(&varname,&format)))
%mend;
%*------------------------------------------------;
%macro checkException(handle, msgVar, action=);
 ji.callStringMethod("getExceptionMessage",&handle,&msgVar);
 if &msgVar ne "" then do;
    lfat = index (&msgVar, '0A'x);
    do while (lfat or &msgVar ne '');

      if lfat = 0 then
        _line = &msgVar;
      else
      if lfat = 1 then
        _line = '';
      else
        _line = substr (&msgVar,1,lfat-1);

      put _line;

      if lfat = 0 or lfat = length (&msgVar)
        then &msgVar = '';
        else &msgVar = substr (&msgVar, lfat+1);

      lfat = index (&msgVar, '0A'x);
    end;
    %if (%bquote(&action) ne ) %then %str(&action;);
  end;
%mend;
%*------------------------------------------------;
%macro jdbcLoadTable (cHandle=NONE, data=, obs=0);

 %if &cHandle=NONE %then %do;
  %put ERROR: jdbcLoadTable: handle MUST be specified.;
  %goto EndMacro;
 %end;

 %local sHandle dsid nvars;

 %let dsid = %sysfunc (open (&data));
 %if &dsid %then %do;
   %let nvars = %sysfunc (attrn (&dsid, NVARS));
   %let dsid = %sysfunc (close(&dsid));
 %end;
 %else %do;
   %put ERROR: jdbcLoadTable: could not open &data;
   %goto EndMacro;
 %end;

 %getStatementHandle (cHandle=&cHandle, sHandle_mv=sHandle);

 data _null_;
  length sql $2000 msg $1000;

  array v_name [ &nvars ] $32 _temporary_;
  array v_type [ &nvars ] $1  _temporary_;
  array v_len  [ &nvars ] 8   _temporary_;

  ds = open ("&data");
  if ds = 0 then stop;

  nvars = attrn(ds, "NVARS");

  * instantiate our special interface wrapper;
  declare javaobj ji;
  ji = _new_ javaobj( "DataStepGatewayAdapter" );

  length memname $32 comma $1;

  * remote table name will have same name as in SAS;
  memname = attrc (ds, "MEM");

  * drop existing table;
  * a better scheme might stop here or rename it;
  sql = "DROP TABLE " || trim(memname) ;
  ji.callVoidMethod("executeUpdate",&sHandle,trim(sql));
  %checkException (&sHandle, msg)

  * remote table structure will be same as in SAS;
  sql = "CREATE TABLE " || trim(memname) || "(";

  comma = " ";
  do i = 1 to nvars;
   v_name[i] = varname (ds, i);
   v_type[i] = vartype (ds, i);
   v_len [i] = varlen  (ds, i);

   if v_type[i] = "C"
 then coltype=" VARCHAR("||%tlp(v_len[i],6.)||")";
 else coltype=" FLOAT8";

   sql = trim(sql) || comma
                   || trim(v_name[i]) || coltype;
   comma = ",";
  end;

  * last column will be a necessary primary key
  * needed for moveToInsertRow();
  sql = trim (sql)
  || ",sas_rowid float8 not null primary key)";
  ji.callVoidMethod("executeUpdate",&sHandle,trim(sql));
  %checkException (&sHandle, msg, action=LINK STOP)

  * ready a resultset;
  sql = "SELECT * FROM " || memname;
  ji.callIntMethod("executeQuery",&sHandle,trim(sql),rHandle);
  %checkException (&sHandle, msg, action=LINK STOP)

  * for each row in sas table
  * insert a row into the remote table;
  rownum = 0;
  do while (0 = fetch (ds)
            %if &obs>0 %then and rownum < &obs;
           );
   rownum + 1;

   * most newer JDBC drivers support this;
   ji.callVoidMethod("moveToInsertRow",rHandle);
   %checkException (rHandle, msg, action=LINK STOP)

   * for each var in a SAS row, pump the SAS values
   * into new remote row;
   do i = 1 to nvars;
    if v_type[i] = 'C' then
    ji.callVoidMethod("setText",rHandle,i,getvarc(ds,i));
    else
    ji.callVoidMethod("setValue",rHandle,i,getvarn(ds,i));

    %checkException (rHandle, msg, action=LINK STOP)
   end;

   * set the necessary primary key value;
   ji.callVoidMethod("setValue",rHandle,i,rownum);
   %checkException (rHandle, msg, action=LINK STOP)

   * attempt to insert new row;
   ji.callVoidMethod("insertRow",rHandle);
   %checkException (rHandle, msg, action=LINK STOP)
  end;

STOP:
  ds=close(ds);
  ji.delete();
  STOP;
 run;

 %closeStatement (sHandle=&sHandle);
%EndMacro:
%mend jdbcLoadTable;
%*------------------------------------------------;
%macro jdbcQuery (cHandle=NONE, sql=, obs=0, out=);

 %if &cHandle=NONE %then %do;
  %put ERROR: jdbcQuery: connection handle MUST be specified.;
  %goto EndMacro;
 %end;

  %local sHandle rHandle;

  %getStatementHandle (cHandle=&cHandle, sHandle_mv=sHandle);

 * perform query;
 data _null_;
  length msg $1000;
  declare javaobj ji;
  ji = _new_ javaobj( "DataStepGatewayAdapter" );
  ji.callIntMethod("executeQuery", &sHandle, "&sql",rHandle);
  %checkException (&sHandle, msg, action=LINK STOP)
  call symput ("rHandle", trim(left(put(rHandle,best12.))));
STOP:
  ji.delete();
  STOP;
 run;

 %if &rHandle=%str() %then %do;
  %goto EndMacro;
 %end;

 * process metadata;
 %local length get;

 data jdbc_columns (keep=varnum varname vartype varlen);
  length msg $1000;

  declare javaobj ji;
  ji = _new_ javaobj( "DataStepGatewayAdapter" );

  length varnum 4 varname $64 vartype $1 varlen 4;

  ji.callIntMethod("getColumnCount",&rHandle,nvars);
  %checkException (&rHandle, msg, action=LINK STOP)
  do varnum = 1 to nvars;
    ji.callStringMethod
    ("getColumnName",&rHandle,varnum,varname);
    ji.callStringMethod
    ("getSasColumnType",&rHandle,varnum,vartype);
    ji.callIntMethod
    ("getColumnDisplaySize",&rHandle,varnum,varlen);
    output;
  end;
STOP:
  ji.delete();
  STOP;
 run;

 * process resultset;

 proc sql noprint;
   select
    trim(varname)
    || case vartype
        when 'C' then ' $' || %tlp(varlen,5.)
        else ' 8'
       end
   , case vartype
      when 'C' then
           'ji.callStringMethod("getText",'
           || "&rHandle.,"
           || %tlp(varnum,4.) || ','
           || trim(varname) || ')'
      else 'ji.callDoubleMethod("getValue",'
           || "&rHandle.,"
           || %tlp(varnum,4.) || ','
           || trim(varname) || ')'
     end
   , case varname
      when 'sas_rowid' then '' else varname
     end
   into
    :length separated by ' '
   ,:get separated by ';'
   ,:var separated by ' '
   from jdbc_columns
   order by varnum
   ;
 quit;

 data &out(keep=&var) ;
  length &length msg $1000;
  declare javaobj ji;
  ji = _new_ javaobj( "DataStepGatewayAdapter" );
  ji.callBooleanMethod("nextRow", &rHandle, _N_);
  do while (_N_);
   &get;
   OUTPUT;
   ji.callBooleanMethod("nextRow", &rHandle, _N_);
  end;
  ji.delete();
  STOP;
 run;

 %closeStatement (sHandle=&sHandle);
%EndMacro:
%mend jdbcQuery;

ToggleGateway Sample

This is it. You've made it this far, so you get to see how 'simple' things are up top when you have a complicated understory.

gateway-sample.sas 
/* NOTE:
 * The jars or directories containing the java classes
 * org.postgresql.Driver and DataStepJdbcWrapper
 * _must_ be listed in the environment variable
 * CLASSPATH _prior_ to the SAS session being started.
 */

%include "sas/gateway-macros.sas";

%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 =;
%let sHandle =;

options mprint;

%startServer (
  policy = &jdbc_server_policy
)

%getConnectionHandle (
  driver = &jdbc_driver
, url = &db_url
, user = &username
, pass = &password
, cHandle_mv = cHandle
);

%put cHandle = &cHandle ;

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

options nocenter;

proc print data=jdbc_columns;
proc print data=zipcode;
run;

%closeConnection ( cHandle = &cHandle );

ToggleGateway Sample

Here is the whole Gateway system in a single zip file. Unzip it maintaining folders and run the do-gateway.bat

gateway.zip

You should adjust the batch file to meet the conditions of your SAS installation.

gateway-test.bat.txt 
rem windows batch file can be adapted to other OSs

rem path to SAS

set SAS9="c:\opt\sas\v9\sas.exe"


mkdir class
javac -d class/ java/*.java

cd class
rmic -classpath . GatewayManager
jar cf ..\gateway.jar *.class
cd ..

%SAS9% -sasInitialFolder . -sysin sas/gateway-sample.sas -noovp -pagesize 10000 -set CLASSPATH ./gateway.jar;./pg73jdbc3.jar

notepad gateway-sample.log

The Bottom

Congratulations! You made it to the bottom of the page.