Parameters with SQLTemplate

From: Williams, Beth (beth.william..FIB.ORG)
Date: Tue Nov 23 2004 - 14:10:54 EST

  • Next message: Gentry, Michael: "RE: Parameters with SQLTemplate"

    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);

    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);
    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.


    This archive was generated by hypermail 2.0.0 : Tue Nov 23 2004 - 14:10:56 EST