It's not a secret anymore!*
The moose likes JDBC and the fly likes Problem with Prepared Statements Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Problem with Prepared Statements" Watch "Problem with Prepared Statements" New topic
Author

Problem with Prepared Statements

Swati Gupta
Greenhorn

Joined: Nov 16, 2005
Posts: 6
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
Greenhorn

Joined: Nov 16, 2005
Posts: 6
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
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

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

Regards, Jan


OCUP UML fundamental and ITIL foundation
youtube channel
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30085
    
149

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.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Jan Cumps
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

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
Greenhorn

Joined: Jan 17, 2006
Posts: 5
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
Ranch Hand

Joined: Feb 07, 2004
Posts: 472

Can you try:


Retire your iPod and start with HD Android music player Kamerton | Minimal J2EE container is here | Light weight full J2EE stack | and build tool | Co-author of "Windows programming in Turbo Pascal"
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Problem with Prepared Statements
 
Similar Threads
JDBC Driver, Oracle sequence number problems
Problem with PreparedStatment and updatequery postgresql
Prepared Statement and Date class - Problem still exists
Oracle CHAR bind parameter
Auto Increment