PreparedStatement

[Anonymous]

PreparedStatement pstmt =
con.prepareStatement("select at_test.nextval into ? from dual");
How come i can use above, query ? I did setInt as below, where chartID is defined as 0.
// Setting an integer.
pstmt.setInt(1,chartID);
// Submit a query, creating a ResultSet object
ResultSet rs = pstmt.executeQuery();

chartID = rs.getInt(1);
At this immediate above line i got, NullPointerException.
So how come i can solve this problem ?

[Thomas Paul]

That's not how you use a PreparedStatement. You can do something like this:
PreparedStatement pstmt = con.prepareStatement("UPDATE EMPLOYEES SET SALARY = ? WHERE ID = ?");
pstmt.setBigDecimal(1, 153833.00)
pstmt.setInt(2, 110592)
You can't do a "select into" the way you are using it. In fact, I can't quite figure what exactly you are trying to do.

[Peter Tran]

Thomas,
That's a valid use of a PreparedStatement. Vikram is just indicating that he's going to pass in different values for the "where clause" each time he executes it.
There's a couple of things wrong with Vikram's example.
1. I'm not sure his SQL is valid a valid SQL statement, unless he's trying to implement a SELECT..INTO.
2. Assuming that there is an error in the SQL statement and he just wants to do a SELECT and pass in different values for the where clause, then he's missing one step after executing the query.

PreparedStatement pstmt = 
  con.prepareStatement("select at_test.nextval into ? from dual");
// Setting an integer.
pstmt.setInt(1,chartID);
// Submit a query, creating a ResultSet object
ResultSet rs = pstmt.executeQuery();
// Need to loop through the ResultSet.
// The first ResultSet.next() moves the pointer to the first record; subsequent execution moves to the next record one at a time.
while (rs.next()) {
  chartID = rs.getInt(1);
}

-Peter

[This message has been edited by Peter Tran (edited January 11, 2001).]

[Thomas Paul]

Vikram is just indicating that he's going to pass in different values for the "where clause" each time he executes it.

Look again. Vikram's statement doesn't have a where clause. You are correct that he needs an rs.next() but that still won't help. Look at the statement. If chartID (an int) is 5 then he would generate a statement that looks like this: "select at_test.nextval into 5 from dual". That is not a valid SQL statement. But you can't do that anyway. The ? can only be the right hand assignment of a where clause.

[Peter Tran]

Thomas,
I am in total agreement with you. His SQL is invalid, but I was going on the assumption that if it was valid, that he can use a PreparedStatement to execute a "SELECT" SQL statement. Notice, my first point: "I'm not sure his SQL is valid a valid SQL statement, unless he's trying to implement a SELECT..INTO." Even if he was trying to do a select..into, his SQL is still wrong.
When I read your first post, you said "That's not how you use a PreparedStatement. You can do something like this:" I thought you meant you could only use a PreparedStatement with update/insert SQL only.
-Peter
Ps. We're both in agreement that Vikram SQL is incorrect, and he's missing the rs.next().

[Thomas Paul]

The example I used happened to be from the Sun API. The rule is that the question mark can only be used as the right hand of a comparison in a where clause.