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

dazeconf_at_yahoo.com (Jeremy) writes:

> 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.

The following works fine for me so I assume your problem is maybe in your passed data. Personally, I think you should invoke the debugger and look for the passed values.

,----

| String val = "30.10.2003";
| String sql = 
|     "select 1 from dual" +
|     " where trunc(sysdate) = " +
|     " trunc(to_date( ?,'dd.mm.yyyy'))";
| PreparedStatement 
|     stmt = Sql.get(con,sql);
| stmt.setObject(1,val);
| ResultSet set = stmt.executeQuery();
| while( set.next()){
|     System.out.println(set.getInt(1));
| }

Harald Received on Thu Oct 30 2003 - 00:52:45 CST