/**************************************************************************************************
* A SQL Access program class
* Makes a connection to a Mysql or Oracle database (identified by client)
* Executes incoming SQL command, and returns SQL result
* - Line  1   of result are column headers (tab delimited)
* - Lines 2-n of result are rows (newline delimited) and column values (tab delimited) 
***************************************************************************************************/
import java.sql.*;                              

public class SQLaccess 
{
    String user;                    
    String pswd;
    String db;
    String dbtype;
    String host  = "localhost";
    String port  = null;
    
    SQLaccess(String user, String pswd, String db, String dbtype)         //constructor
    {
        this.user   = user;             
        this.pswd   = pswd;             
        this.db     = db;               
        this.dbtype = dbtype;           
    }

    String execSQL(String sql)
    {
        String     host_db = "";
        String     output  = "";

        Connection connect = null;
        Statement  stmt    = null;
        ResultSet  rs      = null;

        try 
        {
            if (dbtype.equalsIgnoreCase("mysql"))
            {
                Class.forName("com.mysql.jdbc.Driver");                     //dynamically load the JDBC MySql Driver class
                if(port == null) port="3306";
                host_db = "jdbc:mysql://" + host +":"+ port +"/"+ db;
            }

            if (dbtype.equalsIgnoreCase("oracle"))
            {
                Class.forName("oracle.jdbc.driver.OracleDriver");           //dynamically load the JDBC Oracle Driver class
                if(port == null) port="1521";
                host_db = "jdbc:oracle:thin:@"+ host +":"+ port +":"+ db;             
            }
            
            connect = DriverManager.getConnection(host_db, user, pswd);

            stmt    = connect.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE,   //with the ability to go up & down 
                                              ResultSet.CONCUR_READ_ONLY);         //through the cursor
            rs = stmt.executeQuery(sql);

            ResultSetMetaData metadata = rs.getMetaData();

            int num_of_cols   = metadata.getColumnCount();

            rs.last();                                                  //move to last record
            int num_of_rows   = rs.getRow(); 
            rs.beforeFirst();                                           //reset to start
            
            for (int col=0; col < num_of_cols; col++)                   //get column names 
            {   
                String colName = metadata.getColumnName(col+1);         //index starts at 1 not 0 
                output += "<" + colName + ">\t" ;
            }
            output += "\n";

            for (int row=0; row < num_of_rows; row++)                   //loop thru each row 
            {   
                rs.next(); 
                for (int col=0; col < num_of_cols; col++)               //loop thru each column within each row 
                {       
                    String colValue = rs.getString(col+1); 
                    output += colValue + "\t" ;
                }
                output += "\n";             
            }
            output += "\n";
        }

        catch (Exception e) 
        {
            output = "Could not execute SQL - \n" + e + "\n\n";
        }
        finally 
        {
            try
            {
                if (rs != null)      rs.close();        
                if (stmt != null)    stmt.close();      
                if (connect != null) connect.close();
            }   
            catch (SQLException e) 
            {                            
                output = "Could not close database - \n" + e + "\n\n";
            } 

            return(output);
        }        
    }        
}
/**************************************************************************************************/