File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Problem with Prepared Statements

 
Swati Gupta
Greenhorn
Posts: 6
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 6
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you show us the query you use in the simple Statement?
This will allow us to compare.

Regards, Jan
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 33697
316
Eclipse IDE Java VI Editor
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Bartender
Posts: 2577
11
C++ Linux Netbeans IDE
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 5
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 472
Linux Objective C Ubuntu
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can you try:
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic