package javatemplate1;

import com.sun.rowset.CachedRowSetImpl;
import com.sun.rowset.JdbcRowSetImpl;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
import java.util.*;
import java.util.function.Function;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.sql.RowSetEvent;  
import javax.sql.RowSetListener;  
import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.JdbcRowSet;
import javax.sql.rowset.RowSetProvider;

public class JavaTemplate1 {
    // example of 
       public static void main(String[] args) {
        // TODO code application logic here
        System.out.println("main block executing ");
       Connection c = null; //manage copnnection
       Statement st = null;// query statement support
       ResultSet rs = null;// active connection database result
       CachedRowSet crset = null;
     String dbURL = "jdbc:postgresql://localhost:5432/pgsdemo1";
     String user = "postgres"; String pwd = "postgre_manas9";
     // String dbURL = "jdbc:mysql://localhost/test?"; 
     //String user = "Manas9"; String pwd = "Manas9237";
      // String sql1 = "Select *  from pgsvisitor1b ";
      // String sql1 = "Select *  from pgsvisitor1b ";
      
        // connecting to db
      try {
          // connection>>statement >> Resultset 
       c = DriverManager.getConnection(dbURL,user,pwd);
       Statement stmt = c.createStatement();
stmt.execute("CREATE OR REPLACE FUNCTION refcursorfunc() RETURNS refcursor AS '"
+ " DECLARE "
+ " mycurs refcursor; "
+ " BEGIN "
+ " OPEN mycurs FOR SELECT col FROM table_pgsql1 UNION SELECT col  FROM table_pgsql2; "
+ " RETURN mycurs; "
+ " END;' language plpgsql");
stmt.close();
         // crset. 
c.setAutoCommit(false);
// Procedure call.
CallableStatement proc = c.prepareCall("{ ? = call refcursorfunc() }");
proc.registerOutParameter(1, Types.OTHER);
proc.execute();
ResultSet results = (ResultSet) proc.getObject(1);
int rownum = 0 ; 
while (results.next())
{
   // do something with the results.
     System.out.println("---" + results.getString("col") );
     rownum++;
}
 System.out.println("rows affected :" +rownum);
//Process.loadrset( results, 4);
results.close();
proc.close();
      } catch (SQLException ex) { ex.getErrorCode();
            String message = ex.getMessage();  
            System.out.println(message); 
      } finally {
          System.out.println("going through final block"); 
            try {
                 if (st== null || st.isClosed()) {
                } else {
                  st.close();
              }
                if (c != null && !c.isClosed()) {
                    c.close(); }
            } catch (SQLException ex) { ex.getErrorCode();
                  ex.getMessage();              
            }
        }
      
      System.out.println("dis-Connected to  database successfully"); 

    } 
      
}