RE: JDBC question

From: <Val_Gamerman/Victoria_Financial.VICTORIA_FINANCIAL_at_lnn.com>
Date: Wed, 9 Aug 2000 15:46:43 -0400
Message-Id: <10584.114211@fatcity.com>

It is a valid syntax for JDBC. The prepare call will take care of passing this sql statement to Oracle in the right format.

As far as the issue goes:
1) It's a bug in 8.1.5 and 8.1.6 version of JDBC drivers that he got (get the latest patch from technet)
2) He is using JDK1.1 instead of JDK1.2 which 8.1.5 and 8.1.6 drivers are meant for.

In other words, for him to be able to use 8.1.5/8.1.6 drivers he needs to compile and run his app with JDK1.2.

Val Gamerman
Oracle DBA

To:       Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
cc:        (bcc: Val Gamerman/Victoria Financial)
From:     "Thomas L. Harleman" <tharleman_at_iquest.net> @ NOTES NET
Date:     08/09/2000 07:55:11 PM GMT

Subject: RE: JDBC question

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-----
Long
Sent: Wednesday, August 09, 2000 2:14 AM To: Multiple recipients of list ORACLE-L

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.jav a
: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
also send the HELP command for other information (like subscribing).
--
Author: Thomas L. Harleman
  INET: tharleman_at_iquest.net
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 - 14:46:43 CDT