Thank you to everyone who assisted with this. The #bind worked. Not sure how I missed that. I ready that SQLTemplate page a number of times and it just didn't sink in. Thanks again. You guys ROCK!!!!!
Beth
-----Original Message-----
From: Andrus Adamchik [mailto:andru..bjectstyle.org]
Sent: Tuesday, November 23, 2004 2:50 PM
To: cayenne-use..bjectstyle.org
Subject: RE: Parameters with SQLTemplate
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 - 16:10:02 EST