PreparedStatement & parameter Passing. 1

[Draug]

Greetings,

I am attempting to build valid SQL using prepared statements and SQL parameter passing. PreparedStatement has a method called setString which assigns values to parameters of a prepared statement.

According to the Jbuilder Java docs, there should be two implementations of this method:
1. setString(int ordinal, String value)
2. setString(String columnName, String value)

However, I only seem to have the first (integer) implementation available. I need to use the second (string)implementation since assigning parameters by column names would be much more usefull to me for what I am trying to do.

Why is the setString(String columnName, String value) method not in my PreparedStatement class? How do I add it?

Thanks for the help,
Draug


Below is a snippet of my code that works fine with the integer implementation of setString.
Code Snippet:
===========================================================
String qry = null;
PreparedStatement ps = null;
ResultSet rset = null;

qry = "SELECT * FROM WELLS WHERE Well_Id=? AND Site_Id=?";
ps = conn.prepareStatement(queryString);
ps.setString(1, "1" //parameter 1 is Well_Id
ps.setString(2, "27038" //paremeter 2 is Site_Id
rset = ps.executeQuery();
===========================================================

 

[wushutwist]

Are you getting a compilation error or a runtime error?

 

[Draug]

The code in my first post produces no error. However, when I try to use the setString(string columnName, string Value) I get a compile error because that method of setString is not available in PreparedStatement. However, the documentation says that it should be. How can I add it?

 

[idarke]

I think the method you're looking for is in the CallableStatement interface, not the PreparedStatement interface. Use conn.prepareCall( String ) to create it.

 

[wushutwist]

I'm sorry I didn't actually read the post, duh. No there is not a setString(String, String) method in PreparedStatement. This would not make any sense. Based on column name how would the compiler know which parameter you want to set? Go back and look at the documentation again.

As another note, you should fully qualify the column list instead of using *. There are three reasons for this: 1)Column order in the database will not matter. 2) Small Changes to the database (like adding a field) have less of an affect upon code 3) It is faster without the *.

 

[Draug]

Thanks guys. You are right. There is no setString(String, String) in the PreparedStatement interface. I read the docs, and read the docs, but had it in my mind it should be there. I was quite wrong. I will look into prepareCall(String)

Wushutwist, thanks for the query tips. I just used the * in the example so it would all fit in one line.

Again guys, thanks

 

[Draug]

Is there any other way to access the parameters in a prepared statement other than using the parameter number?

For example, in other languages, I have seen something like the following with prepared SQL:
"Select Name From Person_Table Where Job=?JobType" where JobType is the name for that particular parameter. In the above case, a value could be assigned to that parameter by using setString(1, "Janitor&quot. I am looking for a way that you could set the parameter to "Janitor" by using the name of JobType.

Does such a thing exist in Java?

Thanks.

 

[daniel135]

wushutwist is right on about not using '*'

I have had some rather insane queries to code of late (7-table joins with sub-queries..). It was getting rather unmanageable, so I came up with rules to make my code clearer for the next person to play with my code, or just myself. Here's what that would entail for your code:

String qry = " SELECT Well_Depth, Well-Owner"
           + "  FROM WELLS"
           + " WHERE Well_Id=:wellId"
           + "   AND Site_Id=:siteId";

PreparedStatement ps = conn.prepareStatement(queryString);

ps.setString(1, "1");     //parameter 1 is Well_Id
ps.setString(2, "27038"); //paremeter 2 is Site_Id

ResultSet rset = ps.executeQuery();

All my query methods have the same basic layout, and I try to make the code not only readable but scannable. A different line for each clause of the SQL statement, aligned lines... etc, anything to make debuggging easier. It may not be important for small queries, but it's a good habit to get into and essential when you have over a dozen parameters.

Oh, and just like you mention in your last post, I prefer using ':vars' to '?'. The query can then be copied and pasted into TOAD, and it's also immediately obvious what it is.

 

[Draug]

Daniel135,

Good tips on the query layout.

In your queryString, you have 'Well_id=:wellId'. Does this work? In my Java code, how do I assign a value to that parameter in the query?

Thanks.

 

[crufty]

Query layout is important for working with complex queries but the most important step you can take is to write the query as a separate entity and try it out first via SQL*Plus or whatever SQL front end you use.

Debugging a complex query embedded in a string is not an efficient process. Get the query correct first (i.e. no errors, the returned data is correct, etc.) then copy and paste it into the string and format accordingly.

 

[daniel135]

Thanks.

The code above should work just fine, ':wellId' works like '?' does, the paramaterIndex in the set methods is still the order it appeared in the SQL query.

Hmm... I might as well mention... When there are more than 5-6 paramaters to pass (usually an insert), the paramater names take multiple lines and at the end of the line, I comment the parameter number:

String sql = "Insert..."
           + ":wellId, :siteId"; //1, 2

when I do a set, I can more easily find the appropriate variable I'm setting on. Ok, I guess it looks silly with only two values, but I swear, it gets useful!

 

[Draug]

So, just so I am clear on this, there is no way to access the Parameters from inside the Java code by using names instead of paramter numbers?

Thanks,
Draug.

 

[wushutwist]

None that I am aware of. This is not to say that you couldn't write a quick and dirty wrapper for PreparedStatement to do this if you really need the feature.

 

[Draug]

I may write a quick wrapper. But, as you can tell, I am new to Java, and need to get things working with simple parameters first.

I am getting runtime errors when I use ':wellid', but not when I use '?'. Any ideas why? I am using code exactly like the code in the above post, except the SQl sting is assigned to queryString instead of qry.

 

[wushutwist]

I wouldn't suggest writing a wrapper to do this. It would involve String parsing to determine the correct position and this is going to eat away clock cycles. Better to stick with the built in features. I was just pointing out that just because something is not inherit in the core Java API doesn't mean you can't do it.