%*------------------------------------------------;
%macro startServer (policy=, classpath=);
  %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;