• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Problem with Prepared Statements

 
Greenhorn
Posts: 6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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("jdbc:oracle: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
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Bartender
Posts: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 2661
19
Netbeans IDE C++ Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 472
Objective C Ubuntu Linux
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Can you try:
reply
    Bookmark Topic Watch Topic
  • New Topic