RE: Parameters with SQLTemplate

From: Andrus Adamchik (andru..bjectstyle.org)
Date: Tue Nov 23 2004 - 15:49:52 EST

  • Next message: Twan Kogels: "selectRelationshipObjects and addPrefetch results in classcastexception"

    This or more general approach supported by SQLTemplate - "#bind" directive
    (User Guide, chapter 7.7.3 -
    http://objectstyle.org/cayenne/userguide/fetch/sqltemplate-scripting.html).

    Using #bind($date) or #bind($date 'DATE') will delegate date formatting
    task to the underlying Oracle JDBC driver. And you can contol how the date
    is treated (i.e. date, timestamp, etc.)

    Andrus

    > Try putting quotes around your date:
    >
    > String rawSql = "SELECT * FROM tecc WHERE start_dt <= '$date' " +
    > ...
    >
    > That should work with Sybase and similar DBs. If using Oracle, you'll
    > have to use those nifty to_date() type functions, probably ...
    >
    > /dev/mrg
    >
    > -----Original Message-----
    > From: Williams, Beth [mailto:beth.william..FIB.ORG]
    > Sent: Tuesday, November 23, 2004 3:31 PM
    > To: cayenne-use..bjectstyle.org
    > Subject: RE: Parameters with SQLTemplate
    >
    >
    > That does fix that issue although I am still having some problems
    > related to using a Date object as a parameter. I have successfully used
    > the Date object with Expression and ExpressionFactory methods, however,
    > this is the first time I've tried to use with the SQLTemplate and taw
    > sql.
    >
    > I am now getting a "SQL command not properly ended" SQL Exception error.
    > Here is the query being built.
    >
    > SELECT * FROM tecc WHERE start_dt <= Sat Oct 16 00:00:00 CDT 2004 AND
    > (stop_dt IS NULL OR stop_dt >= Sat Oct 16 00:00:00 CDT 2004) AND EXISTS
    > (SELECT terr_id FROM invt_mgt_summary WHERE aggr_lvl = 'TERR' AND we_dt
    > = Sat Oct 16 00:00:00 CDT 2004 AND terr_id = tecc.terr_id)
    >
    > I am using the same code as specified early except with the suggested
    > query = query.queryWithParameters(parameters);. Am I going to have to
    > include the TO_DATE function as part of the raw sql? I've tried to do
    > that already, but am having problems setting the date format parameter
    > for the function. I can't figure out what to identify the time zone in
    > the format. Does my question make sense? I apologize for the
    > basic/syntax type question. Any assistance is greatly appreciated.
    >
    > Beth
    >
    > -----Original Message-----
    > From: Andrus Adamchik [mailto:andru..bjectstyle.org]
    > Sent: Tuesday, November 23, 2004 1:38 PM
    > To: cayenne-use..bjectstyle.org
    > Subject: Re: Parameters with SQLTemplate
    >
    >
    > Beth,
    >
    > Method "queryWithParameters" clones the original query and returns a new
    > instance without changing the original object. So
    > "query.queryWithParameters(parameters)" should be replaced with
    >
    > query = query.queryWithParameters(parameters);
    >
    > Alternativey you can change the original using "void setParameters" like
    > this:
    >
    > query.setParameters(parameters);
    >
    > Andrus
    >
    >> I am trying to use a SQLTemplate with some raw sql that incorporates
    >> parameters. Below is the code that I'm using, however, it appears
    > that
    >> the parameters are not being passed to the query for some reason.
    >>
    >> **** Code ******
    >> DataContext cayenneContext = DataContext.createDataContext();
    >> GregorianCalendar gCalendarDate = new GregorianCalendar(2004, 9, 16);
    >> Date asOfWeekEndingDate = gCalendarDate.getTime();
    >> Map parameters = Collections.singletonMap("date", asOfWeekEndingDate);
    >> System.out.println(parameters);
    >>
    >> String rawSql = "SELECT * FROM tecc WHERE start_dt <= $date " +
    >> "AND (stop_dt IS NULL OR stop_dt >= $date " +
    >> "AND EXISTS (SELECT terr_id FROM invt_mgt_summary " +
    >> "WHERE aggr_lvl = 'TERR' AND we_dt = $date " +
    >> "AND terr_id = tecc.terr_id)";
    >> SQLTemplate query = new SQLTemplate(TerritoryOwnership.class, rawSql,
    >> true); query.queryWithParameters(parameters);
    >> List results = cayenneContext.performQuery(query);
    >> *****************
    >>
    >> I get an invalid character SQLException. Here is the query that it is
    >> trying to run. As you can see, it has not replace the $date with the
    >> appropriate value.
    >>
    >> ***** SQL ******
    >> SELECT * FROM tecc WHERE start_dt <= $date AND (stop_dt IS NULL OR
    >> stop_dt >= $date AND EXISTS (SELECT terr_id FROM invt_mgt_summary
    > WHERE
    >> aggr_lvl = 'TERR' AND we_dt = $date AND terr_id = tecc.terr_id)
    >> ***********
    >>
    >> When I do a "query.getParameters();" after the queryWithParameters()
    > is
    >> call it shows no parameters. What am I doing wrong? Any help would
    > be
    >> much appreciated. Thanks.
    >>
    >> Beth



    This archive was generated by hypermail 2.0.0 : Tue Nov 23 2004 - 15:49:54 EST