JDBC question

From: Nguyen, Long <Long.Nguyen_at_its.csiro.au>
Date: Wed, 9 Aug 2000 16:02:31 +1000
Message-Id: <10583.114148@fatcity.com>

Hi,

A developer here has encountered a problem with using JDBC accessing Oracle so he came to me (a DBA) for help. Unfortunately I do not know much about Java stuff, hope that someone in this list could help me. The following Java code uses Oracle JDBC thin driver to do a select using prepared statement.

1. The code works fine with Oracle 8.0.4 (using the JDBC drivers that come with 8.0.4)
2. The code does not work with 8.1.5 or 8.1.6 (using the JDBC drives that come with Oracle 8.1.5/8.1.6). Under 8.1.5/8.1.6 it gave "ORA-01008: not all variables bound". However if I issued the same SELECT statment under sqlplus, it works fine.

divid is a NOT NULL CHAR(2) field.

import java.sql.*;

public class TestOraclePrepStmt {

        public static void main(String argv[]) throws Exception {
                Class.forName("oracle.jdbc.driver.OracleDriver") ;

                Connection conn = DriverManager.getConnection(
                 "jdbc:oracle:thin:@rocky:1521:PSS",
                "pss", "psspwd");


                String divId = "93" ;

                String query = "select min(finyear) " +


                        "from sector_target_balances " +

                        "where divid = ?";

                PreparedStatement mainStmt = conn.prepareStatement(query) ;
                mainStmt.setString(1, divId);

                ResultSet mainResult = mainStmt.executeQuery(query) ;

                String minFinYear="", maxFinYear="";
                while (mainResult.next()) { // for each row
                        minFinYear = mainResult.getString(1);
                        maxFinYear = mainResult.getString(2);

                }                               // next row

                System.out.println("minFinYear "+ minFinYear + "maxFinYear "
+ maxFinYear);
                conn.close();
        }

}

Output from the run:

select min(finyear), max(finyear) from sector_target_balances where divid = '?'
 divId 93
minFinYear null maxFinYear null
select min(finyear), max(finyear) from sector_target_balances where divid = ?
 divId 93
Exception in thread "main" java.sql.SQLException: ORA-01008: not all variables bound

        at java.lang.Throwable.fillInStackTrace(Native Method)
        at java.lang.Throwable.fillInStackTrace(Compiled Code)
        at java.lang.Throwable.<init>(Compiled Code)
        at java.lang.Exception.<init>(Exception.java:42)
        at java.sql.SQLException.<init>(SQLException.java:43)
        at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:114)
        at oracle.jdbc.ttc7.TTIoer.processError(Compiled Code)
        at oracle.jdbc.ttc7.Oall7.receive(Compiled Code)
        at oracle.jdbc.ttc7.TTC7Protocol.doOall7(Compiled Code)
        at
oracle.jdbc.ttc7.TTC7Protocol.parseExecuteDescribe(TTC7Protocol.java:595)
        at
oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:1600)
        at
oracle.jdbc.driver.OracleStatement.doExecute(OracleStatement.java:1758)
        at

oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java :1805)

        at
oracle.jdbc.driver.OracleStatement.executeQuery(OracleStatement.java:410)

        at TestOraclePrepStmt.main(Compiled Code) Received on Wed Aug 09 2000 - 01:02:31 CDT