java.sql.SQLException: ORA-01006: bind variable does not exist

[Eva Chow]

Hi,

I am learning JDBC. I copied an example from a web site, but when I ran it, I got the error java.sql.SQLException: ORA-01006: bind variable does not exist.
After looking at the code myself, I noticed that the line "ResultSet rs = stmt.executeQuery(sql);" is causing the problem. But I cannot tell what is wrong with this statement.


Here is the code.

public class JavaPreparedStatement {

    //JDBC driver name and database URL
    static final String JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
    static final String DB_URL = "jdbc:oracle:thin:@localhost:1521:XE";
    
    static final String USER = "user";
    static final String PASSWORD = "123";

    public static void main(String[] args) {
        Connection conn = null;
     
        try {
            Class.forName(JDBC_DRIVER);

            System.out.println("Connecting to database...");
            conn = DriverManager.getConnection(DB_URL, USER, PASSWORD);

            System.out.println("Creating statement...");
            String sql = "UPDATE Employee set age=? WHERE id=?";
            stmt = conn.prepareStatement(sql);

            //Bind Values into the parameters.
            stmt.setInt(1, 35);   // This would set age
            stmt.setInt(2, 102);  // This would set ID

            //Let us update age of the record with ID=102;
            int rows = stmt.executeUpdate();
            System.out.println("Rows impacted : " + rows);

            //Let us select all the records and display them.
            sql = "SELECT * FROM Employee";
           
            ResultSet rs = stmt.executeQuery(sql);
             
            while (rs.next()) {
                // Retrieve by column name
                int id = rs.getInt("id");
                int age = rs.getInt("age");
                String first = rs.getString("first");
                String last = rs.getString("last");

                //Display values
                System.out.print("ID: " + id);
                System.out.print(", Age: " + age);
                System.out.print(", First: " + first);
                System.out.print(", Last: " + last);
            }

            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
        } catch (SQLException se) {
            // Handle errors for JDBC
            se.printStackTrace();
        } catch (Exception e) {
            //Handle errors for JDBC
            e.printStackTrace();
        } finally {
            //finally block used to close resources
            try {
                if(stmt!=null)
                    conn.close();
            } catch (SQLException se2) {
            }// nothing we can do

            try{
                if(conn!=null)
                    conn.close();
            }catch(SQLException se){
                se.printStackTrace();
            }//end finally try
        }// end try
        System.out.println("Goodbye!");
    }//end main
}// end JavaPreparedStatement

[Greg Charles]

Hi Eva,

Welcome to JavaRanch!

The problem comes when you reuse your prepared statement as an ordinary statement. You should close stmt, and then create a new statement with Connection.createStatement() to execute your static SQL.

[Eva Chow]

Thanks. I took your suggestion and it's working now.