I suspect the problem in the original code was using 'DATE' instead
of 'TIMESTAMP'
#bind($creationdate 'DATE')
should be
#bind($creationdate 'TIMESTAMP')
But the exact date comparison may indeed be asking for trouble with
precision. Specifying the interval using BETWEEN or something may be
a more reliable way.
Andrus
On Aug 6, 2007, at 4:11 PM, Peter Schröder wrote:
> hi jens,
>
> you can just let cayenne do the date-parsing for you.
>
> this is an example of mine:
>
>
> Map<Object, Object> parameters = new HashMap<Object, Object>();
> Calendar tmp = new GregorianCalendar(...);
> Calendar cal = new GregorianCalendar(...);
> parameters.put("start", cal.getTime());
> parameters.put("date", tmp.getTime());
> queryString = "rec_change > #bind($start 'TIMESTAMP') and
> rec_change < #bind($date 'TIMESTAMP')";
> ...
>
>
>
>
> -----Ursprüngliche Nachricht-----
> Von: Jens Mayer [mailto:jens.mayer..mx.de]
> Gesendet: Montag, 6. August 2007 14:44
> An: use..ayenne.apache.org
> Betreff: Problem with SQLTemplate and Date-binding
>
> Hi,
>
> I am having a problem with date-parameter-binding in a SQLTemplate.
> This is my coding:
>
> public static void validate(DataContext dataCtx)
> throws ParseException {
>
> final Map<String,Object> para = new HashMap<String,Object>();
> final SimpleDateFormat sdf =
> new SimpleDateFormat("dd.MM.yyyy HH:mm:ss");
>
> para.put("creationdate",sdf.parse("28.04.2007 19:05:10"));
>
> SQLTemplate query = new SQLTemplate(Datei.class,
> "select * from sen_dateien where datum_erzeugt =
> #bind($creationdate 'DATE')");
>
> query.setParameters(para);
> try {
> List queryResult = dataCtx.performQuery(query);
> if (queryResult.size()>0) {
> Datei datei = (Datei)queryResult.get(0);
> System.out.println(datei);
> } else {
> throw new CayenneRuntimeException("empty resultset!");
> }
> } catch (CayenneRuntimeException cre) {
> AppLogger.log(Level.SEVERE, cre);
> }
>
> return;
> }
>
> In table 'sen_dateien' are several rows matching the where-clause
> of the
> query, so I expect from the result to be not empty. But this is the
> logging output:
>
> INFO QueryLogger: --- transaction started.
> INFO QueryLogger: select * from sen_dateien where datum_erzeugt = ?
> [bind: '2007-04-28 19:05:10.0']
> INFO QueryLogger: === returned 0 rows. - took 16 ms.
> INFO QueryLogger: +++ transaction committed.
>
> I suspect the fractional second (.0) in the binding of being the cause
> for the empty resultset I get, but how to get rid of it ?
>
> By the way: the query shown in the code above is only an example. The
> origin query is much more complex, that's why I decided to use a
> SQLTemplate with raw SQL.
> For the moment, I helped myself by casting the field
> 'datum_erzeugt' in
> the query to varchar and using a #bind($creationdate 'VARCHAR'), but
> then the db doesn't use any index, what is leading to VERY bad
> response
> times....
>
> Can anybody help me to make the binding work ?
>
> the database is oracle 10.2.0.3, I'm using cayenne 2.0.2
>
> thanks in advance,
>
> Jens
>
This archive was generated by hypermail 2.0.0 : Mon Aug 06 2007 - 09:46:12 EDT