File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Wildcard value and PreparedStatement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Wildcard value and PreparedStatement" Watch "Wildcard value and PreparedStatement" New topic

Wildcard value and PreparedStatement

Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
Hi. I am using a PreparedStatement for an order search class I am creating. I want to query a datetime field in one of my tables. If I key the statement "SELECT * FROM ORDERMASTER WHERE ActualShipDate = '%'" it works great. However, since I am using a PreparedStatement, I have to set this parameter using setDate(). Correct? How can I set this value to a wildcard character (%) if it is looking for a value of the date type?
Any help would be GREATLY appreciated!!
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
You should be able to use the setString() method instead. If the query you listed above executes successfully from a sql editor, then that should work.
I'm sure it depends on the db vendor, but for ours (Informix), we can always use setString() regardless of the datatype and the db will interpret it appropriately.

Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Jennifer Sohl
Ranch Hand

Joined: Feb 28, 2001
Posts: 455
Thanks for the help. That worked.
Now I have another question. Here is my PreparedStatement:

I have everything working the way I want it for this search I am creating except for setting the parameter for the Actual Ship date.
The user can select the option to search for shipped and unshipped orders (which is where I used the wildcard character) and that works. They can also choose to just search only shipped orders, or only unshipped orders. This is where I am running into problems.
What do I set the parameter to, to weed out the records with valid ship dates? I am using a default date of 1900-01-01 in unshipped orders so I can't use null. I can't use the wildcard character because that will still bring back all of them.
Also, can I not use the LIKE keyword with any other value than the wildcard on a DateTime field? I do not get any results if I key in "1900%".
Any suggestions?
[ October 07, 2002: Message edited by: Jennifer Sohl ]
I agree. Here's the link:
subject: Wildcard value and PreparedStatement
jQuery in Action, 3rd edition