generated SQL criteria using date comparison does not work with MaxDB: {ts XXX} instead of {d XXX}

[Alexander Zimmer <al...@unycom.com>]

Hello list,

 

I'm using Torque 3.3 with SAP's MaxDB 7.5.

 

This is what I want to do:

-----------------------------------

  Criteria criteria = new Criteria();

  criteria.add(OrdersPeer.DELIVERY_DATE, cal.getTime(),
Criteria.LESS_EQUAL);

 

This should bring up all rows with a "DELIVERY DATE" of max. the date in
the calender object "cal". The column DELIVERY DATE ist DATE column (not
timestamp). Thus, it should be rendered as:

  SELECT DISTINCT ORDERS.ORDER_ID FROM ORDERS WHERE
ORDERS.DELIVERY_DATE<={d '2007-10-29'}

 

which would work fine.

 

The problem:

----------------

However, Torque generates this constraint (only difference is the use of
{ts XXX} instead of {d XXX}):

 

  SELECT DISTINCT ORDERS.ORDER_ID FROM ORDERS WHERE
ORDERS.TARGET_DELIVERY_DATE<={ts '2007-10-29 09:35:09.556'}

 

Unfortunately, this leaves me with an internal MaxDB error: move error.
Don't ask me why the error message is apparently unrelated to the
problem, I tried do execute this query in a MaxDB client and found that
if I change the constraint to a pure date-wise comparison (as shown
above) it would work.

 

 

 

Is this a known issue? I am not 100% sure if this in fact is a MaxDB
error? IMHO it should work with timestamps as well. Anyway, if Torque
would use date comparison ({d XXX} instead of {ts XXX}), it would work
too. How can I get this to work?

 

TIA,

Alex

RE: generated SQL criteria using date comparison does not work with MaxDB: {ts XXX} instead of {d XXX}

[Greg Monroe <Gr...@DukeCE.com>]

One quick thing to check is that you have defined the field 
as DATE in the Torque XML and not TIMESTAMP. Sometimes a 
missmatch in DB vs XML schema can cause this sort of thing.

If that is already set, then it may be an issue with the DBSapDB 
adaptor class not having a DB specific getDateString(Date) method
defined.  The SVN history on this adaptor does not show much a lot
of activity, which generally indicates no one has access to it
for testing during version releases. (Or it could just be a 
stable adaptor).

In general, most modern DBs (JDBC drivers?) will now handle the 
conversion of dates if the query is simply something like 
'yyyy mm dd hh:MM:ss' and doesn't have the DB specific ts or
d delimiter. Alternatively, if the SapDB can use the D'xxx'
format for both date and timestamp fields, the getDateString
method could return this format.

AFAIK, there is no free developer edition of the MaxDB product,
so this DB Adaptor depends on someone with access to a MaxDB
server to test it. If you have the time, it would be great
if you could check out the code from SVN and running the test 
project against your MaxDB server (see developer information 
on the web site).  This will test the adaptor against a large
set of known adaptor problems (and other things).

Let us know if there are problems (or better yet, no problems).
We'll be happy to work with you to update the DB adaptor 
if needed.


> From: Alexander Zimmer
> 
> I'm using Torque 3.3 with SAP's MaxDB 7.5.
> 
> This is what I want to do:
> 
> -----------------------------------
> 
>   Criteria criteria = new Criteria();
> 
>   criteria.add(OrdersPeer.DELIVERY_DATE, cal.getTime(), 
> Criteria.LESS_EQUAL);
> 
> 
> This should bring up all rows with a "DELIVERY DATE" of max. 
> the date in the calender object "cal". The column DELIVERY 
> DATE ist DATE column (not timestamp). Thus, it should be rendered as:
> 
>   SELECT DISTINCT ORDERS.ORDER_ID FROM ORDERS WHERE 
> ORDERS.DELIVERY_DATE<={d '2007-10-29'}
> 
> which would work fine.
> 
> The problem:
> 
> ----------------
> 
> However, Torque generates this constraint (only difference is 
> the use of {ts XXX} instead of {d XXX}):
> 
>   SELECT DISTINCT ORDERS.ORDER_ID FROM ORDERS WHERE 
> ORDERS.TARGET_DELIVERY_DATE<={ts '2007-10-29 09:35:09.556'}
> 
> Unfortunately, this leaves me with an internal MaxDB error: 
> move error.
> Don't ask me why the error message is apparently unrelated to 
> the problem, I tried do execute this query in a MaxDB client 
> and found that if I change the constraint to a pure date-wise 
> comparison (as shown
> above) it would work.
> 
> Is this a known issue? I am not 100% sure if this in fact is 
> a MaxDB error? IMHO it should work with timestamps as well. 
> Anyway, if Torque would use date comparison ({d XXX} instead 
> of {ts XXX}), it would work too. How can I get this to work?
> 
> TIA,
> 
> Alex
> 
> 
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.

---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org