> 1) If I have several parameters specified in the SQL and I don't provide
> values for each in the map I pass in what happens to those values?
Bad idea. Raw SQL is not like Expressions where you can omit parameters. A
"bare" parameter will remain unparsed in the SQL and will be passed to the DB
as "$xyz". Don't remember what happens if such a parameter is wrapped in a
directive. But probably nothing good.
> 2) The code is throwing a parse exception. But it's not clear to me why
> that is.
Could you trace the error to a specific row and column in the SQL? Also:
> #result('count(*)',int)
Should be 'int' I think.
> 3) When I get the results back, they do not map to a specific entity in my
> model. How do I extract them from the query results?
You need to fetch them as "Data Rows". So you get them as a list containing
maps of values, one map for each row.
Andrus
> On Jan 14, 2015, at 8:41 PM, Tony Giaccone <t...@giaccone.org> wrote:
>
> I have a table that represents log data, and I want to group that data
> time, by using SQL rather then pulling back 65000 rows and aggregating in
> the java app.
> The intent is to look at how many transactions occurred over time in a
> bucket of transactions. So you might look at the hours worth of
> transactions in 15, 10, or 5 minute windows.
>
> To do that I have a fairly complex query with a subquery, and I'm trying
> use a named query to pull back this data and so I have a NamedQuery in the
> model..
>
> I know the query works, because I've been able to successfully run it in a
> query window in postgress admin
>
> I have a few questions about how this works, and hopefully I can get this
> cleared up..
>
>
> 1) If I have several parameters specified in the SQL and I don't provide
> values for each in the map I pass in what happens to those values?
>
> 2) The code is throwing a parse exception. But it's not clear to me why
> that is.
>
> 3) When I get the results back, they do not map to a specific entity in my
> model. How do I extract them from the query results?
>
>
> Thanks in Advance.
>
>
> Tony Giaccone
>
> Error parsing template 'select ?#result('year','String'),
> ?#result('month','String'), ?#result('day', 'String'),
> ?#result('hour','String'), ?#result('minute','String'),
> ?#result('count(*)',int) from ( select
> "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME", ?EXTRACT(year from
> "LOG_DATE") AS year, ?EXTRACT(month from "LOG_DATE") AS month,
> ?EXTRACT(day from "LOG_DATE") as day, ?EXTRACT(hour from "LOG_TIME") as
> hour, ?trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
> from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall) ?order by
> "FUNCTION_CALL",year, month, day,hour, minute ) foo group by
> year, month, day, hour, minute order by ? year, month, day,hour,
> minute' : Invalid arg #1 in directive #resultselect
> ?#result('year','String'), ?#result('month','String'), ?#result('day',
> 'String'), ?#result('hour','String'), ?#result('minute','String'),
> ?#result('count(*)',int) from ( select
> "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME", ?EXTRACT(year from
> "LOG_DATE") AS year, ?EXTRACT(month from "LOG_DATE") AS month,
> ?EXTRACT(day from "LOG_DATE") as day, ?EXTRACT(hour from "LOG_TIME") as
> hour, ?trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
> from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall) ?order by
> "FUNCTION_CALL",year, month, day,hour, minute ) foo group by
> year, month, day, hour, minute order by ? year, month, day,hour,
> minute[line 1, column 176]|
>
> at org.apache.cayenne.access.jdbc.SQLTemplateProcessor.buildStatement(
> SQLTemplateProcessor.java:149)
>
>
> The query is named, FetchBatchCountsForTransaction, and it looks like this:
>
>
> select
> #result('year','String'),
> #result('month','String'),
> #result('day', 'String'),
> #result('hour','String'),
> #result('minute','String'),
> #result('count(*)',int)
> from
> (
> select "KEY","FUNCTION_CALL","LOG_DATE","LOG_TIME",
> EXTRACT(year from "LOG_DATE") AS year,
> EXTRACT(month from "LOG_DATE") AS month,
> EXTRACT(day from "LOG_DATE") as day,
> EXTRACT(hour from "LOG_TIME") as hour,
> trunc(EXTRACT(minute from "LOG_TIME")/#bind($divisor)) as minute
> from "LOG_DATA" where "FUNCTION_CALL" = #bind($functionCall)
> order by "FUNCTION_CALL",year, month, day,hour, minute
> ) foo
> group by
> year, month, day, hour, minute
> order by
> year, month, day,hour, minute
>
>
> Here's the java code I use to call it:
>
> Map<String, Object> params = new HashMap<String, Object>();
>
> params.put("divisor", ""+(60 / divisor));
>
> params.put("functionCall","getTransactionHistory");
>
> NamedQuery fetchGraphData = new NamedQuery("FetchBatchCountsForTransaction",
> params);
>
> ObjectContext ctx = BaseContext.getThreadObjectContext();
>
> List dbData = ctx.performQuery(fetchGraphData);
