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:46:20 EST