Re: how to call a custom query

[Frank]

I'm not sure why do you want to execute just a SQL. After doing query
like you are proposing no data could be edited and saved back to db
easily.
Cayenne is a ORM, so why not getting a list of schedules ordered by
procedure name:
Expression exp = ExpressionFactory.matchExpr(Schedule.DATE_PROPERTY,
new Date());
<<
I do not know how to join tables in the Modeler, that is why I opted for the 
rawSelect.
If I just use the Schedule.class, there is no procedure_name field, just 
procedure_id

Frank
----- Original Message ----- 
From: "Marcin Skladaniec" <[EMAIL PROTECTED]>
To: <user@cayenne.apache.org>
Sent: Thursday, February 01, 2007 10:09 PM
Subject: Re: how to call a custom query


Hi
schedule_date = CURDATE():
what is the schedule_date data type ? Check if you are not comparing 
TIMESTAMP with DATE.


I would use the functionality of NamedQuery. All you do you define a 
Query in Modeller, example :

SELECT #result('count(*)' 'int' 'C') FROM $entityName $whereClause

and in the code you do something like :

HashMap map = new HashMap();
map.put("entityName", entity);
map.put("whereClause", where);

NamedQuery query = new NamedQuery("SpecialCount", map);
Map row = (Map) getContext().performQuery(query).get(0);
return ((Number) row.get("C")).intValue();


I'm not sure why do you want to execute just a SQL. After doing query 
like you are proposing no data could be edited and saved back to db 
easily.
Cayenne is a ORM, so why not getting a list of schedules ordered by 
procedure name:
Expression exp = ExpressionFactory.matchExpr(Schedule.DATE_PROPERTY,  new 
Date());
Ordering o = new Ordering(Schedule.PROCEDURE_PROPERTY 
+"."+Procedure.NAME_PROPERTY);
SelectQuery sq = new SelectQuery(Schedule.class, exp);
sq.addOrdering(o);

List schedules = context.performQuery(sq);
so you have the schedules
now just access the values you are want to display.

Marcin


On 02/02/2007, at 12:41 PM, Frank wrote:

Here is my code.
My table show no data. I have three records for this query.

I am trying to display procedure_name, provider_name, office_phone, 
alt_phone
The _Schedules class does not have these fields.
What do I need to do?

Thanks
Frank
public List getRecords() {

String query = "SELECT ALL 
schedules.schedule_id,schedules.schedule_date,";

query += " procedures.procedure_name,providers.provider_name,";

query += " providers.office_phone,providers.alt_phone";

query += " FROM schedules";

query += " INNER JOIN procedures ON schedules.procedure_id = 
procedures.procedure_id";

query += " INNER JOIN providers ON schedules.provider_id = 
providers.provider_id";

query += " WHERE schedule_date = CURDATE()";

query += " ORDER BY procedure_name";

DataContext context = DataContext.getThreadDataContext();

SQLTemplate rawSelect = new SQLTemplate(Schedules.class, query);

List records = context.performQuery(rawSelect);

return records;

}

----- Original Message ----- From: "Christian Mittendorf" 
<[EMAIL PROTECTED]>
To: <user@cayenne.apache.org>
Sent: Thursday, February 01, 2007 7:16 PM
Subject: Re: how to call a custom query


Please check out the documentation at the great new Cayenne homepage:

http://cayenne.apache.org/doc20/queries.html

...especially the part on SQLTemplates

Christian

P.S. The link to the API  (http://cayenne.apache.org/1_2/api/ cayenne/ 
org/objectstyle/cayenne/query/package-summary.html) from  the 1.2 
documentation returns a 404 error....


Am 02.02.2007 um 01:02 schrieb Frank:

Can anyone help me with the a short example on how to do this?

Thanks

Frank
----- Original Message ----- From: "Frank" <[EMAIL PROTECTED]>
To: <user@cayenne.apache.org>
Sent: Thursday, February 01, 2007 11:33 AM
Subject: how to call a custom query


Hello,

I have the following raw query defined.
SELECT BSYDTAA.BSYPEMP.EMHSP#, BSYDTAA.BSYPEMP.EMYLNM, 
BSYDTAA.BSYPEMP.EMYFNM, BSYDTAC.BPRPPCP.PCDEP4, 
BSYDTAC.BPRPPCP.PCLDDS, BSYDTAA.BSYPEMP.EMEMP# AS EMEMP, 
BSYDTAA.BSYPEMP.EMWPH# AS WPHONE FROM BSYDTAA.BSYPEMP, 
BSYDTAC.BPRPPCP WHERE BSYDTAA.BSYPEMP.EMHSP# =  BSYDTAC.BPRPPCP.PCHSP# 
AND BSYDTAA.BSYPEMP.EMDEP4 =  BSYDTAC.BPRPPCP.PCDEP4 AND 
(BSYDTAA.BSYPEMP.EMHSP# IN (1,5)) AND  (BSYDTAA.BSYPEMP.EMDOT=0)
AND EMEMP# = ?

How do I call this from code using the SelectQuery and passing  in 
the value?

Thanks

Frank





Marcin