package uk.ac.ucl.cege.cegeg077.uceskkw;

import java.sql.*;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.io.*;
import oracle.jdbc.pool.OracleDataSource;

public class oracleSQLTEST {
private Connection conn;

// nb the initialisation method does not return a value, so not public void
// oracleSQL
public oracleSQLTEST(String theareaname) {
    // the initialisation method
    conn = null;
    System.out.println("-------- Oracle JDBC Connection Initializing... ------");
    try {
        conn = createCon();
    } catch (ClassNotFoundException e) {
        System.out.println("Oracle JDBC Driver Missing.");
        e.printStackTrace();
        return;
    } catch (SQLException e) {
        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
        return;
    }
    if (conn != null) {
        System.out.println("Database Connection Successful");
    } else {
        System.out.println("Failed to make connection.");
    }
}

private Statement createStatement() {
    // Create a statement
    Statement stmt = null;
    try {
        stmt = conn.createStatement();
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
    }
    return stmt;
}

public Connection createCon() throws ClassNotFoundException, SQLException {
    String user = "user";
    String password = "password";
    String database = "database";

    // Open an OracleDataSource and get a connection
    OracleDataSource ods = new OracleDataSource();
    ods.setURL("jdbc:oracle:thin:@localhost:1521:" + database);
    ods.setUser(user);
    ods.setPassword(password);
    Connection conn = ods.getConnection();
    return conn;

}

// an example routine
public void createKMLFile(String kmltablename) {
    try {
        conn = createCon();
        String query = "select geometry from " + kmltablename;
        System.out.println(query);
        Statement stmt1 = createStatement();
        ResultSet rs = stmt1.executeQuery(query);
        System.out.println("Result Set = " + rs);

        while (rs.next()) {
            System.out.println(rs.getString(1));
            String building_id = rs.getString(1);
            if (building_id == null) {// if you fetched null value then
                                        // initialize output with blank
                                        // string
                building_id = "1";
            }

            query = "select * from table(select b.geometry.sdo_ordinates from buildings b ";
            query += " where id = '" + building_id + "')";
            Statement stmt2 = createStatement();

            ResultSet theCoords = stmt2.executeQuery(query);
            System.out.println(query);

            // find the first z value
            int coordCount = 0;
            while (theCoords.next()) {
//                  System.out.println("l00000000000000000000000000000"
//                          + building_id);
                coordCount = coordCount + 1;
                double xvalue = theCoords.getDouble(1);
                theCoords.next();
                double yvalue = theCoords.getDouble(1);
                theCoords.next();
                double zvalue = theCoords.getDouble(1);

                System.out.println(xvalue);
                System.out.println(yvalue);
                System.out.println(zvalue);

            }
            building_id = building_id + 1; // questionable bit of code here...
            theCoords.close();
            stmt2.close();
            System.out.println(building_id);
        }

        rs.close();
        stmt1.close();

        conn.close();

    } catch (ClassNotFoundException e) {
        System.out.println("Class Not Found Error");
        e.printStackTrace();
    } catch (SQLException e) {
        System.out.println("SQL Exception Error");
        e.printStackTrace();
    } catch (Exception e) {// Catch exception if any
        System.err.println("Error: " + e.getMessage());
    }

}