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
