• 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

PreparedStatement not working

 
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi,

I am trying to use PreparedStatement in my code and whenever I try to "set" values using the primitive "setString", "setInt" etc., it does not work. In other words, the values are never set in the query.

As an example, I am trying to query a table "source", and its schema is as follows:

SQL> desc source;
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL VARCHAR2(4)
METHOD NUMBER(1)
RES NUMBER(9,2)



And the following is my code snippet:

public static void main(String[] args)
{
try
{
Class.forName ("oracle.jdbc.driver.OracleDriver");
String dbURL = "jdbc racle:thin:@machineip:1521 pi";
Connection conn = DriverManager.getConnection (dbURL, "scott", "tiger");

String query = "SELECT id from source WHERE id = ?";
PreparedStatement prep = conn.prepareStatement(query);
prep.setInt(1, 0);

System.out.println("Query is : " + query);

ResultSet rset = prep.executeQuery();
while (rset.next())
{
System.out.println(rset.getString(1));
}

rset.close();
prep.close();
conn.close();
}

catch (Exception e)
{
e.printStackTrace();
}
}

As you can see, using PreparedStatement, the query should be of the form:

SELECT id from source WHERE id = 1

However, when I try to print the query, I get the following:

SELECT id from source WHERE id = ?


Thus, the "question mark" (?) is not replaced. Can anyone tell me what is going on ? FYI, I am using JDK 1.4.2, and Oracle 10g.

Thanks,
- Jyoti
 
Jyotishman Pathak
Greenhorn
Posts: 8
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just a minor modification to my previous post - the query should be :-

String query = "SELECT id from source WHERE method = ?";

Rest all remains same...any suggestions, please ?

Regards,
- Jyoti
 
Ranch Hand
Posts: 381
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Jyoti,

You may be having some other problem, it's hard to say yet. But you are quite incorrect in how PreparedStatements work and how you would be able to see what is going on in them.

Calling the setXXX methods on PreparedStatement does not magically change the orginial SQL string that you prepare as you seem to be assuming. Your original query will NOT be changed by PreparedStatement. Ever.

Now like I said if you are getting unexpected results returned well you may indeed have another problem, but not the one you identified. Are you getting unexepcted results?

If you want to "trace" what queries a PreparedStatement is doing I suggest consulting the FAQ http://faq.javaranch.com/view?JdbcUsageQuestions Specifically the question on How do I view the actual SQL that a PreparedStatement is sending to the database?
 
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You formed a String as
. And you print it out. What you will get is what you formed, with the question mark. This has nothing to go with PreparedStatement.

Secondly, the code seems fine. Are you getting any Exception? If yes, please post it.
 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Your code looks fine. There is no problem in prepared statement as this is not precompiled. If you would like to see the actual query you need to customize prepared statement to handle this. You can get from the net search for Loggablestatement.
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic