Re: Setting a JDBC preparedStatement parameter within a to_date() function

From: the spammers have won :-( <"the>
Date: Wed, 05 Nov 2003 06:52:42 +0200
Message-ID: <3fa88287$1@news.012.net.il>

Jeremy wrote:

> Richard Kuhler <noone_at_nowhere.com> wrote in message news:<lEXnb.4327$CZ5.2196_at_twister.socal.rr.com>...
>

>>Jeremy wrote:
>>
>>>I am attempting to set a java preparedStatment parameter within a
>>>to_date function.  However, if I do so, a blank query is always
>>>returned (no exception is thrown). Any help would be appreciated.
>>>
>>>The prepared statment contains something like this
>>>
>>>...
>>>where x.mydate = to_date(?, 'DD-MON-YYYY')
>>>...
>>>
>>>later on, I use setObject to set the parameter's value:
>>>prepStmt.setObject(paramNumber, someString)
>>>
>>>someString always contains a date format that is readily converted
>>>using to_date within SQL Plus.  I have tried running the same query
>>>within SQLPlus, and have not had a problem.  I have also tried using
>>>prepStmt.setString()(although this is not general enough for my code)
>>>and a blank query is still returned.
>>>
>>>I am somewhat new at programming with JDBC, so I hope this post makes
>>>sense.  Thanks in advance to anyone who responds.
>>
>>Why are you using setObject instead of setString here?

>
> I am using setObject because I am setting parameters using a Vector
> containing objects of various types.
>
> Here's the long version:
> The program takes a SQL file and replaces specified parameters in the
> SQL file with a ? character to create a preparedStatement. Then, the
> program gets the values of each parameter (and their types) from a web
> form, converts Strings from the web form parameters into Integers etc.
> if neccessary, and puts them into a Vector. The program then uses
> setObject to set the parameters in the preparedStatement using the
> Vector, executes the query, and returns an Excel spreadsheet. This
> works in all cases in which no Date parameters are involved. I leave
> dates as Strings, hoping that the to_date function within the SQL will
> handle the conversion from string to date (if there's no to_date
> function in the SQL, the default Oracle date format would be expected
> in the web parameter input).
>
> The first version of this program did not use preparedStatments.
> Instead it used basic concatenation to create a normal statement, then
> executed it. However, my boss instructed me to use a
> preparedStatement instead to increase efficiency for repeated use of
> the same query.

Your boss (as often happens with bosses ;-) may be wrong. I suggest (if you haven't already done so) that you peruse the JDBC articles at the "onJava" Web site:

http://onjava.com/topics/java/jdbc_sqlj

Also, Harald's response emphasizes a subtle, but often overlooked point about Oracle's DATE datatype -- that it contains a date _and_ a time, so if two DATE values differ only by several milliseconds, Oracle will _not_ consider them as being equal. Another approach you may like to try (assuming you haven't already) is to use the JDBC Escape Syntax -- which is supported by Oracle's JDBC (thin) driver. The J2SE documentation explains what Escape Syntax is and how to use it:

http://java.sun.com/j2se/1.4.2/docs/guide/jdbc/getstart/statement.html#999472

Good Luck,
Avi. Received on Tue Nov 04 2003 - 22:52:42 CST