RE: JDBC question

From: Thomas L. Harleman <tharleman_at_iquest.net>
Date: Wed, 9 Aug 2000 13:50:03 -0500
Message-Id: <10584.114200@fatcity.com>

I don't know java, either.
But in java, is this legal SQL syntax? "where divid = ?";

Tom Harleman
11080 Willowmere Dr.
Indianapolis, IN 46280
317-844-2884 Home
317-843-9122 Home Office

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Nguyen, Long
Sent: Wednesday, August 09, 2000 2:14 AM To: Multiple recipients of list ORACLE-L Subject: JDBC question

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)

Any idea how to fix the problem?
Thanks
Long

--
Author: Nguyen, Long
  INET: Long.Nguyen_at_its.csiro.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may

Received on Wed Aug 09 2000 - 13:50:03 CDT