CODEWITHSUNDEEP
javamysqloracle-databasepostgresqljdbc
Alessandro D'Armiento
Alessandro D'Armiento

Reputation: 95

cannot exexute SQL query wirh OJDBC and Oracle DB (but full working with posgtres JDBC and Postgres DB)

I wrote a java program which retrieve data from a PG gb, process them, and write them in an Oracle DB.

While the PG part is fully working, the Oracle one has issues.

I can connect to the DB, but every query ends with a rollback (ResultSet with Oracle is always null)

Of course i have both PG and Oracle JDBC driver.

Here are my DBs object and testing queries

private final static PostgresDB postgres = new PostgresDB("jdbc:postgresql://192.168.2.23:5432/T18CLEAN", "myPGUser", "myPGPasswd", true);
private final static OracleDB oracle = new OracleDB("jdbc:oracle:thin:@192.168.2.20:1521/EFFEVI.T18FV.IT", "myOracleUser", "myOraclePasswd");
private final static String testPostgres = "SELECT product_pricelist_item.x_product_name FROM public.product_pricelist_item;";
private final static String testOracle = "SELECT EFFEVI.PRESA_ORDINI.PO_CLIENTE FROM EFFEVI.PRESA_ORDINI;";

Then I setup the 2 connections:

PG:

    public Connection getConnect() throws ClassNotFoundException {
    System.out.println("-------- Posgres JDBC Connection Testing ------");
    String url = c_url;
    Connection conn = null;
    Properties props = new Properties();
    props.setProperty("user", user);
    props.setProperty("password", passwd);
    props.setProperty("ssl", boolToString(sslEnabled));
    try{
        Class.forName("org.postgresql.Driver");
        System.out.println("Postgres JDBC Driver Registered!");
    } catch(ClassNotFoundException e) {
        System.out.println("Where is your Oracle JDBC Driver?");
        e.printStackTrace();
        return null;
    }

    try {
        conn = DriverManager.getConnection(url, props);
        System.out.println("You made it, take control your Postgres database now!");
    } catch (SQLException e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        System.out.println("Failed to make connection to Postgres DB!");
    }
    return conn;
}

Oracle:

    public Connection getConnect(){
    Connection connection = null;
    System.out.println("-------- Oracle JDBC Connection Testing ------");

    try {

        Class.forName("oracle.jdbc.driver.OracleDriver");

    } catch (ClassNotFoundException e) {

        System.out.println("Where is your Oracle JDBC Driver?");
        e.printStackTrace();
        return connection;

    }

    System.out.println("Oracle JDBC Driver Registered!");



    try {

        connection = DriverManager.getConnection(c_url, user, passwd);

    } catch (SQLException e) {

        System.out.println("Connection Failed! Check output console");
        e.printStackTrace();
        return connection;

    }

    if (connection != null) {
        System.out.println("You made it, take control your Oracle database now!");
        return connection;
    } else {
        System.out.println("Failed to make connection to Oracle DB!");
    }

    return connection;
}

After all these pass i perform queries

    public ResultSet executeCommand(Connection c, String command) {
    Statement st = null;
    ResultSet rs = null;
    try {
        st = c.createStatement();
        rs = st.executeQuery(command);
    } catch (SQLException e) {

    }

    if(rs==null){
        System.out.println("Failed to Execute command " + command);
    } else {
        System.out.println("Command Executed: " + command);
    }

    return rs;
}

Assuming that there are no parameters error... What could it be? Any help?

Thank you very much

Upvotes: 0

Views: 173

Answers (1)

krokodilko
krokodilko

Reputation: 36107

Remove a semicolon at the end of the query.

Use this:

private final static String testOracle = 
"SELECT EFFEVI.PRESA_ORDINI.PO_CLIENTE FROM EFFEVI.PRESA_ORDINI";

instead of this one:

private final static String testOracle = 
"SELECT EFFEVI.PRESA_ORDINI.PO_CLIENTE FROM EFFEVI.PRESA_ORDINI;";



Also don't silently "swallow" an exception in your code:

} catch (SQLException e) {

}

Rethrow the exception, or at least print the error to the log:

} catch (SQLException e) {
  log.error("Error while executing query " + command, e);
  throw new RuntimeException("Error while executing query " + command, e);
}

Upvotes: 1

Related Questions