Handling of date/time in Peers

["Diethelm Guallar, Gonzalo" <Go...@sonda.com>]

I know this has surfaced before; I would like to recap
what is necessary to fix this problem.

The current peer system does not handle very well issues
of formatting for datatypes where the formatting depends
somehow on the client and server side. For example, if
you have a table such as

  create table a (d date);

it is not easy to do this portably:

  Date d = new Date();
  Criteria c = new Criteria();
  c.add(APeer.D, d);
  Vector data = APeer.doSelect(c);

The problem is the SQL code the doSelect() generates; how
does it format the passed in date? It could generate this:

  SELECT * FROM A WHERE D = '12-25-1999'

but if the DB is configured to expect dates as DD-MM-YYYY,
the query will fail.

It has been mentioned that moving Peers to use PreparedStatements
could solve these kinds of problems. What about using JDBC escapes?
For example, the SQL generated for the example above could be:

  SELECT * FROM A WHERE D = {d '1999-12-25'}

and that would be totally portable, according to the JDBC docs.
What do you people think? Same thing could be used to handle
time with {t}, timestamps with {ts} and selects based on the
DBs current time, using the {fn} escape and a (DB dependent)
function that returns the current time.


-- 
Gonzalo A. Diethelm
gonzalo.diethelm@sonda.com

---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org

Re: Handling of date/time in Peers

[Daniel Rall <dl...@finemaltcoding.com>]

"Diethelm Guallar, Gonzalo" <Go...@sonda.com> writes:

> I know this has surfaced before; I would like to recap
> what is necessary to fix this problem.
> 
> The current peer system does not handle very well issues
> of formatting for datatypes where the formatting depends
> somehow on the client and server side. For example, if
> you have a table such as
> 
>   create table a (d date);
> 
> it is not easy to do this portably:
> 
>   Date d = new Date();
>   Criteria c = new Criteria();
>   c.add(APeer.D, d);
>   Vector data = APeer.doSelect(c);
> 
> The problem is the SQL code the doSelect() generates; how
> does it format the passed in date? It could generate this:
> 
>   SELECT * FROM A WHERE D = '12-25-1999'
> 
> but if the DB is configured to expect dates as DD-MM-YYYY,
> the query will fail.

Another solution would be to have the DB adapters provide methods
formatting Java's Date object as expected for each RDBMS.

Daniel

---------------------------------------------------------------------
To unsubscribe, e-mail: turbine-dev-unsubscribe@jakarta.apache.org
For additional commands, e-mail: turbine-dev-help@jakarta.apache.org