DB2 & Ibatis:problem creating WHERE clause with CURRENT_TIMESTAMP

classic Classic list List threaded Threaded
5 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

DB2 & Ibatis:problem creating WHERE clause with CURRENT_TIMESTAMP

apinke
Hi All,

I am using DB2 with IBatis and facing a problem in creating a where condition which uses CURRENT_TIMESTAMP
The SQL that works  :
Select sum(TOTAL),code  from ORDER_DATA
where (INSERTTIME >= CURRENT_TIMESTAMP - 30 MINUTES)
group by code

I am trying to parametrize the value in minutes.

I tired this :
params.put("duration",new Integer(duration));
...
where (INSERTTIME >= CURRENT_TIMESTAMP  -  #duration#  MINUTES)

but that did not work .Got a DB2 invalid sql error
I then tried

params.put("duration_string","CURRENT_TIMESTAMP -  30 MINUTES");
...
where (INSERTTIME >= #duration_string# )

But this throws a "error occurred while applying a parameter map. Date/Time must be JDBC format"
which is as expected

Any suggestions on how can I pass then duration to the query ?

thanks
Pat


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DB2 & Ibatis:problem creating WHERE clause with CURRENT_TIMESTAMP

Larry Meadors
Since you scrub the input by doing this:

params.put("duration",new Integer(duration));

You are pretty safe to do this:

where (INSERTTIME >= CURRENT_TIMESTAMP  -  $duration$  MINUTES)

That'll work.

Larry


On Wed, Feb 27, 2008 at 2:18 AM, apinke <[hidden email]> wrote:

>
>  Hi All,
>
>  I am using DB2 with IBatis and facing a problem in creating a where
>  condition which uses CURRENT_TIMESTAMP
>  The SQL that works  :
>  Select sum(TOTAL),code  from ORDER_DATA
>  where (INSERTTIME >= CURRENT_TIMESTAMP - 30 MINUTES)
>  group by code
>
>  I am trying to parametrize the value in minutes.
>
>  I tired this :
>  params.put("duration",new Integer(duration));
>  ...
>  where (INSERTTIME >= CURRENT_TIMESTAMP  -  #duration#  MINUTES)
>
>  but that did not work .Got a DB2 invalid sql error
>  I then tried
>
>  params.put("duration_string","CURRENT_TIMESTAMP -  30 MINUTES");
>  ...
>  where (INSERTTIME >= #duration_string# )
>
>  But this throws a "error occurred while applying a parameter map. Date/Time
>  must be JDBC format"
>  which is as expected
>
>  Any suggestions on how can I pass then duration to the query ?
>
>  thanks
>  Pat
>
>
>
>  --
>  View this message in context: http://www.nabble.com/DB2---Ibatis%3Aproblem-creating-WHERE-clause-with-CURRENT_TIMESTAMP-tp15709225p15709225.html
>  Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DB2 & Ibatis:problem creating WHERE clause with CURRENT_TIMESTAMP

apinke

That worked .

Thanks a lot !!

Pat

Larry Meadors wrote
Since you scrub the input by doing this:

params.put("duration",new Integer(duration));

You are pretty safe to do this:

where (INSERTTIME >= CURRENT_TIMESTAMP  -  $duration$  MINUTES)

That'll work.

Larry


On Wed, Feb 27, 2008 at 2:18 AM, apinke <apinkelefant@gmail.com> wrote:
>
>  Hi All,
>
>  I am using DB2 with IBatis and facing a problem in creating a where
>  condition which uses CURRENT_TIMESTAMP
>  The SQL that works  :
>  Select sum(TOTAL),code  from ORDER_DATA
>  where (INSERTTIME >= CURRENT_TIMESTAMP - 30 MINUTES)
>  group by code
>
>  I am trying to parametrize the value in minutes.
>
>  I tired this :
>  params.put("duration",new Integer(duration));
>  ...
>  where (INSERTTIME >= CURRENT_TIMESTAMP  -  #duration#  MINUTES)
>
>  but that did not work .Got a DB2 invalid sql error
>  I then tried
>
>  params.put("duration_string","CURRENT_TIMESTAMP -  30 MINUTES");
>  ...
>  where (INSERTTIME >= #duration_string# )
>
>  But this throws a "error occurred while applying a parameter map. Date/Time
>  must be JDBC format"
>  which is as expected
>
>  Any suggestions on how can I pass then duration to the query ?
>
>  thanks
>  Pat
>
>
>
>  --
>  View this message in context: http://www.nabble.com/DB2---Ibatis%3Aproblem-creating-WHERE-clause-with-CURRENT_TIMESTAMP-tp15709225p15709225.html
>  Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DB2 & Ibatis:problem creating WHERE clause with CURRENT_TIMESTAMP

Stephen Boyd-3
Your originally query should work.  I ran a similar query that worked in db2 v9.  What was the explicit db2 sql exception?

You could also try the timestampdiff scalar function (4 = minutes)?  For example,

where (sysfun.timestampdiff(4,char(CURRENT_TIMESTAMP - INSERTTIME)) <=  #duration#)



On Thu, Feb 28, 2008 at 12:22 AM, apinke <[hidden email]> wrote:


That worked .

Thanks a lot !!

Pat


Larry Meadors wrote:
>
> Since you scrub the input by doing this:
>
> params.put("duration",new Integer(duration));
>
> You are pretty safe to do this:
>
> where (INSERTTIME >= CURRENT_TIMESTAMP  -  $duration$  MINUTES)
>
> That'll work.
>
> Larry
>
>
> On Wed, Feb 27, 2008 at 2:18 AM, apinke <[hidden email]> wrote:
>>
>>  Hi All,
>>
>>  I am using DB2 with IBatis and facing a problem in creating a where
>>  condition which uses CURRENT_TIMESTAMP
>>  The SQL that works  :
>>  Select sum(TOTAL),code  from ORDER_DATA
>>  where (INSERTTIME >= CURRENT_TIMESTAMP - 30 MINUTES)
>>  group by code
>>
>>  I am trying to parametrize the value in minutes.
>>
>>  I tired this :
>>  params.put("duration",new Integer(duration));
>>  ...
>>  where (INSERTTIME >= CURRENT_TIMESTAMP  -  #duration#  MINUTES)
>>
>>  but that did not work .Got a DB2 invalid sql error
>>  I then tried
>>
>>  params.put("duration_string","CURRENT_TIMESTAMP -  30 MINUTES");
>>  ...
>>  where (INSERTTIME >= #duration_string# )
>>
>>  But this throws a "error occurred while applying a parameter map.
>> Date/Time
>>  must be JDBC format"
>>  which is as expected
>>
>>  Any suggestions on how can I pass then duration to the query ?
>>
>>  thanks
>>  Pat
>>
>>
>>
>>  --
>>  View this message in context:
>> http://www.nabble.com/DB2---Ibatis%3Aproblem-creating-WHERE-clause-with-CURRENT_TIMESTAMP-tp15709225p15709225.html
>>  Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>>
>>
>
>

--
View this message in context: http://www.nabble.com/DB2---Ibatis%3Aproblem-creating-WHERE-clause-with-CURRENT_TIMESTAMP-tp15709225p15729984.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.


Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: DB2 & Ibatis:problem creating WHERE clause with CURRENT_TIMESTAMP

shivani
This post has NOT been accepted by the mailing list yet.
In reply to this post by apinke
I need to add 30 MINUTES to start time where in ibatis mapper. I followed the same way as u did.but im nt getting the result.

SELECT count(*) from table where #{1} > start_time_z+$offsetHrs$ MINUTES.

Please let me know how to solve this
Loading...