db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Brad Berens <theg...@gmail.com>
Subject Re: Derby/Oracle - single SQL query?
Date Wed, 04 Feb 2009 18:50:59 GMT
I was going to suggest the PreparedStatement solution as well, but if you
MUST do it the hard way.. the oracle cast for a date uses a specific
format.  For example, it'd be cast('01-Jan-2005' as date)... and while I
don't have derby handy... :-P but I'm not sure that format would work in
Derby.  In the end, you're doing yourself a HUGE favor by switching to
PreparedStatement.  The big fuss against the PS is that it's not easy to log
the params for debugging.  I suggest you look at this for help there.
http://www.javaworld.com/javaworld/jw-01-2002/jw-0125-overpower.html?page=1

You could change their factory to only use the loggable PS when your logger
is in debug mode or whatever.

Hope this helps!
Brad

On Wed, Feb 4, 2009 at 1:34 PM, Stephan van Loendersloot (LIST) <
stephan@republika.nl> wrote:

>
> Kathey Marsden wrote:
>
>> Kent Spaulding wrote:
>>
>>>
>>> Is there some query format that will for both?
>>>
>> I don't have access to Oracle, but would it work to use an explicit cast
>> and cast(d.insertion_date as date) <= CAST('2009-02-28'  AS DATE);
>>
>>
>>  I don't have access to Oracle either, so my preffered solution would be
> (again) to use a PreparedStatement
>
> Example code (not very efficient, the DateFormat should be reused instead
> of recreating it each time. Preferrably by using it from a ThreadLocal since
> it's not thread-safe):
>
> public static void getSqlDate(String date) {
>   java.sql.Date result = null;
>   DateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd");
>   try {
>      result = new java.sql.Date(dateFormat.parse(date).getTime());
>   } catch (ParseException e) {
>      // Logging goes here
>   }
>   return result;
> }
>
>
> String strDate = "2009-02-28";
>
> PreparedStatement pstmt = conn.prepareStatement("SELECT d.insertion_date
> FROM d WHERE CAST(d.insertion_date as date) >= ?");
> pstmt.setDate(1, getSqlDate(strDate));
>
>
> Regards,
>
>   Stephan.
>
>
>
>

Mime
View raw message