my dog learned polymorphism*
The moose likes JDBC and the fly likes PreparedStatement not working Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "PreparedStatement not working" Watch "PreparedStatement not working" New topic
Author

PreparedStatement not working

Jyotishman Pathak
Greenhorn

Joined: Jan 18, 2005
Posts: 8
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

Joined: Jan 18, 2005
Posts: 8
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
Maximilian Xavier Stocker
Ranch Hand

Joined: Sep 20, 2005
Posts: 381
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?
Sravan Kumar
Ranch Hand

Joined: Sep 11, 2005
Posts: 121
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.


keep smilin :: sravan<br /><a href="http://sravanpens.blogspot.com" target="_blank" rel="nofollow">I scribble here</a>
Saravanan Alagu
Greenhorn

Joined: Oct 08, 2005
Posts: 5
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.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: PreparedStatement not working