Problem with SQLTemplate and Date-binding

From: Jens Mayer (jens.mayer..mx.de)
Date: Mon Aug 06 2007 - 08:43:55 EDT

  • Next message: Peter Schröder: "AW: 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 - 08:44:41 EDT