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

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.
> >
> > -Jeremy
>
> 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. Received on Wed Oct 29 2003 - 20:29:55 CST