/* Richard A. DeVenezia
 * SUGI 29 - "Greetings from the Edge"
 * www.devenezia.com/papers
 */

%*------------------------------------------------;
%macro getConnectionHandle (driver=,url=,user=,pass=,cHandle_mv=);
  data _null_;
    declare javaobj ji ("GatewayAdapter");
    ji.ExceptionDescribe(1);
    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=, updatable=0);
  data _null_;
    declare javaobj ji ("GatewayAdapter");
    ji.callIntMethod (
      "getStatementHandle"
    , &cHandle
    , &updatable
    , 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 ("GatewayAdapter");
    ji.callVoidMethod (
      "closeConnectionHandle"
    , &cHandle
    );
    ji.delete();
  run;
%mend;
%*------------------------------------------------;
%macro closeStatement (sHandle=);
  data _null_;
    declare javaobj ji ("GatewayAdapter");
    ji.callVoidMethod (
      "closeStatementHandle"
    , &sHandle
    );
    ji.delete();
  run;
%mend;
%*------------------------------------------------;
%macro closeResultSet (rHandle=);
  data _null_;
    declare javaobj ji ("GatewayAdapter");
    ji.callVoidMethod (
      "closeResultSetHandle"
    , &rHandle
    );
    ji.delete()
  run;
%mend;
%*------------------------------------------------;
%macro tlp(varname,format);
  trim(left(put(&varname,&format)))
%mend;
%*------------------------------------------------;
%macro checkException(j=ji, rc=err, action=);
  &j..exceptionCheck(&rc);
  if &rc then do;
    &j..exceptionClear();
    put 'Exception occurred';
    %if (%bquote(&action) ne ) %then %str(&action;);
  end;
%mend;
%*------------------------------------------------;
%macro jdbcPushData (cHandle=NONE, data=, obs=0, out=, pk=);

 %if &cHandle=NONE %then %do;
  %put ERROR: jdbcPushData: 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: jdbcPushData: could not open &data;
   %goto EndMacro;
 %end;

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

 data _null_;
  length sql $2000 ;

  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");

  declare javaobj ji ( "GatewayAdapter" );
  ji.exceptionDescribe(1);  %* log exceptions on StdOut;

  length memname $32 comma $2;

  * remote table name will have same name as in SAS;
  %if %length (&out)
  %then memname = "&out";
  %else 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 ()

  * 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";
  sql = trim (sql) || ')';
  ji.callVoidMethod("executeUpdate",&sHandle,trim(sql));
  %checkException (action=LINK STOP)

  * ready a resultset;
  sql = "SELECT * FROM " || memname;
  ji.callIntMethod("executeQuery",&sHandle,trim(sql),rHandle);
  %checkException (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 (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 (action=LINK STOP)
   end;

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

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

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

 %closeStatement (sHandle=&sHandle);
%EndMacro:
%mend jdbcPushData;
%*------------------------------------------------;
%macro jdbcPullData (cHandle=NONE, query=, 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_;
  declare javaobj ji ( "GatewayAdapter" );
  ji.exceptionDescribe(1);
  ji.callIntMethod("executeQuery", &sHandle, "&query",rHandle);
  %checkException (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);
  declare javaobj ji ( "GatewayAdapter" );

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

  ji.callIntMethod("getColumnCount",&rHandle,nvars);
  %checkException (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;
 * proc print data=jdbc_columns;

 * 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 upcase(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;
  declare javaobj ji ( "GatewayAdapter" );
  ji.exceptionDescribe(1);
  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 jdbcPullData;