You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by T E Schmitz <ma...@numerixtechnology.de> on 2004/04/13 00:57:59 UTC
PostgreSQL TIMESTAMP comparison fails
Hello,
I created a table with a required TIMESTAMP attribute.
I selected a perviously created record via torque, then attempted DELETE
with doDelete (obj).
doDelete does not delete the record as the following WHERE clause
returns no results:
whereClause=BRAND_NAME='Olympus' AND TIMESTAMP={ts '2004-04-11
17:36:51.291'}
The timestamp comparison obviously fails. (Before I had a TIMESTAMP
column, doDelete (obj) worked fine).
Do you have any ideas why and, more importantly, is there a work-around?
PostgreSQLs TIMESTAMP type is WITH TIME ZONE. I also tried TIMESTAMP
WITHOUT TIMEZONE but that causes a crash.
I noticed Torque uses java.util.Date rather than java.sql.Date.
--
Regards,
Tarlika Elisabeth Schmitz
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re[3]: generated queries
Posted by "Algirdas M." <sn...@splius.lt>.
Hello,
Sunday, April 18, 2004, 9:20:34 PM, you wrote:
BV> Yes,
BV> INSERT SQL statements are not logged,
BV> but you always can (at least in development phases)
BV> enable your database server to log all SQL statements in a file.
Thanks.
BV> bogdan
BV> ----- Original Message -----
BV> From: <sk...@engr.uky.edu>
BV> To: "Apache Torque Users List" <to...@db.apache.org>
BV> Sent: Sunday, April 18, 2004 10:10 PM
BV> Subject: Re: Re: generated queries
>> Hi,
>>
>> You cannot see some of the SQL commands though. Insert
>> SQL statements is one of them. The reason for this
>> AFAIK is that village generated some of the SQL
>> statements and it doesn't log them. If I'm wrong
>> someone please correct me. I will be glad if I'm wrong
>> coz I too need to see tbe Insert SQL statements but am
>> not able to.
>>
>> -Sarav
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
>> For additional commands, e-mail: torque-user-help@db.apache.org
>>
BV> ---------------------------------------------------------------------
BV> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
BV> For additional commands, e-mail: torque-user-help@db.apache.org
--
Best regards,
Algirdas mailto:snk@splius.lt
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Re: generated queries
Posted by Bogdan Vatkov <bv...@globaltech-bg.com>.
Yes,
INSERT SQL statements are not logged,
but you always can (at least in development phases)
enable your database server to log all SQL statements in a file.
bogdan
----- Original Message -----
From: <sk...@engr.uky.edu>
To: "Apache Torque Users List" <to...@db.apache.org>
Sent: Sunday, April 18, 2004 10:10 PM
Subject: Re: Re: generated queries
> Hi,
>
> You cannot see some of the SQL commands though. Insert
> SQL statements is one of them. The reason for this
> AFAIK is that village generated some of the SQL
> statements and it doesn't log them. If I'm wrong
> someone please correct me. I will be glad if I'm wrong
> coz I too need to see tbe Insert SQL statements but am
> not able to.
>
> -Sarav
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Re: generated queries
Posted by sk...@engr.uky.edu.
Hi,
You cannot see some of the SQL commands though. Insert
SQL statements is one of them. The reason for this
AFAIK is that village generated some of the SQL
statements and it doesn't log them. If I'm wrong
someone please correct me. I will be glad if I'm wrong
coz I too need to see tbe Insert SQL statements but am
not able to.
-Sarav
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: PostgreSQL TIMESTAMP comparison fails
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Hello,
Just to prevent other people from falling into the same trap, here's
what caused the TIMESTAMP comparison to fail:
When updating the record I had provided the setter method setTimestamp
in BaseXxx.java with a java.util.Date. This was with hindsight maybe
rather stupid but I did that because I saw the BaseXxx class declare it
as java.util.Date.
When fetched from the database, somewhere in the Torque runtime the
timestamp is declared as java.sql.Timestamp which is a subclass of
java.util.Date and as such adds nanosecond precision. While setting the
TIMESTAMP column with a java.util.Date is syntactically correct, it
causes later comparisons to fail.
I am still not quite sure where I could / should have seen that a
java.sql.Timestamp was required to handle TIMESTAMP columns.
Question to the Torque developers: would it not be wiser to declare
timestamps as java.sql.Timestamp in the generated code?
Best Regards,
Tarlika Elisabeth Schmitz
>> doDelete does not delete the record as the following WHERE clause
>> returns no results:
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: PostgreSQL TIMESTAMP comparison fails
Posted by T E Schmitz <ma...@numerixtechnology.de>.
Hello Daniel, Thank you for your suggestion, this will do the trick for me.
Further to yesterday's posting, I would like to add some more details to
highlight the problem:
If the record was previously inserted via Torque, doDelete's TIMESTAMP
comparison will work and therefore delete the record. If, however, the
record had been added 'by hand' using the now() function to set the
TIMESTAMP, the comparison will fail.
It would appear that this is a precision / type incompatibility problem.
I presume PostgreSQL now() returns a TIMESTAMP of a greater precision
then java.util.Date. I wonder whether a Torque implementation using
java.sql.Date might remedy this incongruence.
As an aside, I need a further suggestion: Rather than storing the client
machine's Date() I want to store the server's current time in the
TIMESTAMP attribute. The only way I can think of would be using straight
JDBC and the now() function.
Best Regards,
Tarlika Elisabeth Schmitz
Daniel Huang wrote:
> A possible workaround for your doDelete().
>
> Assuming that obj holds the object you want to delete and no further checks
> need to be done before deleting obj, you can call
> Peer.doDelete(obj.getPrimaryKey())
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: PostgreSQL TIMESTAMP comparison fails
Posted by Daniel Huang <da...@cisco.com>.
A possible workaround for your doDelete().
Assuming that obj holds the object you want to delete and no further checks
need to be done before deleting obj, you can call
Peer.doDelete(obj.getPrimaryKey())
This will generate a delete with where clause containing only the primary
key columns.
Regards,
Daniel
-----Original Message-----
From: T E Schmitz [mailto:mail@numerixtechnology.de]
Sent: Monday, April 12, 2004 3:58 PM
To: Apache Torque Users List
Subject: PostgreSQL TIMESTAMP comparison fails
Hello,
I created a table with a required TIMESTAMP attribute.
I selected a perviously created record via torque, then attempted DELETE
with doDelete (obj).
doDelete does not delete the record as the following WHERE clause
returns no results:
whereClause=BRAND_NAME='Olympus' AND TIMESTAMP={ts '2004-04-11
17:36:51.291'}
The timestamp comparison obviously fails. (Before I had a TIMESTAMP
column, doDelete (obj) worked fine).
Do you have any ideas why and, more importantly, is there a work-around?
PostgreSQLs TIMESTAMP type is WITH TIME ZONE. I also tried TIMESTAMP
WITHOUT TIMEZONE but that causes a crash.
I noticed Torque uses java.util.Date rather than java.sql.Date.
--
Regards,
Tarlika Elisabeth Schmitz
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org