Problem with Prepared Statements

[Swati Gupta]

I am using a prepared statement in oracle to query some DB objects which are actually synonyms, created using a DB link to a DB2 database from my oracle database. When I run the query through TOAD, there is one row returnef and when I use prepared statment, it doesnot return any record. Even if I use a simple statement, it does return a record but not with prepared statement. Is there a known bug?


Class.forName("oracle.jdbc.driver.OracleDriver");
Connection conn = DriverManager.getConnection("jdbcracle:thin:@myserver:1521:PMTT101","yyy","xxx");
String query = "SELECT DISTINCT C.CUST_ID FROM V-LOC C";
PreparedStatement stmt = conn.prepareStatement(query);
String search = "SP483952";
stmt.setString(1, search);
stmt.setString(2, search);

System.out.println("Before execute" + stmt.toString());
ResultSet rs = stmt.executeQuery();
System.out.println("After exceute");
while(rs.next()){
System.out.println("Got one");
}
System.out.println("Done");
conn.commit();

[Swati Gupta]

Sorry, the query id like this

SELECT DISTINCT C.CUST_ID
FROM V_LOC C
WHERE c.CIRC_ID = ?
AND c.LOC_ID = ?

All the more, you can ignore the query string. Problem is that I don't get a result with prep stat but do get one with simple stat.

[Jan Cumps]

Can you show us the query you use in the simple Statement?
This will allow us to compare.

Regards, Jan

[Jeanne Boyarsky]

Originally posted by Swati Gupta:
String search = "SP483952";
stmt.setString(1, search);
stmt.setString(2, search);

Do you mean to set both parameters to the same value? It seems unlikely that two fields would contain this identical value.

[Jan Cumps]

All the more, you can ignore the query string. Problem is that I don't get a result with prep stat but do get one with simple stat.

I think the query string is the reason why you don't get a result.

If a simple Statement with Query: [ SELECT DISTINCT C.CUST_ID FROM V_LOC C WHERE c.CIRC_ID = 'SP483952' AND c.LOC_ID = 'SP483952' ] returns a row, then your PreparedStatement will return a row too.

Regards, Jan

[Niranjan Sarkar]

There is no known "bug" using PreparedStatements! The one I know of is to do with WSAD 5.1 versions where the number of parameters exceed 31.

I will recommend you to enclose the code in try-catch block and do a printStackTrace() of the exception, if any.

Also, you can do away with the commit, if you are only doing a SELECT operation.

[D Rog]

Can you try:

stmt.setString(1, search);
stmt.setString(2, new String(searc));