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:[EMAIL PROTECTED]
Gesendet: Montag, 6. August 2007 14:44
An: [email protected]
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
