Parameterized Query in DataMap

[Chandni Jain <ch...@andrew.cmu.edu>]

Hello,

 

I am trying to use Cayenee to connect to mysql database. I wanted to create
a parameterized query and store it in DataMap so I can reuse the query.
Please can you tell me how I should specify the query and how do I map the
parameters.

Currently this is what I am doing: 

In the  datamap 

<query name="ParamDecompQuery"
factory="org.objectstyle.cayenne.map.SQLTemplateBuilder" root="obj-entity"
root-name="DesignModule">

            <sql
adapter-class="org.objectstyle.cayenne.dba.mysql.MySQLAdapter"><![CDATA[SELE
CT m.* FROM

  design_module m

  JOIN design_modulerefinementrelation d ON m.`factid`=d.child AND
m.version=d.version

  JOIN __versions__ v ON m.version=v.id

WHERE version_name=? AND d.parent=?]]></sql>

      </query>

 

In the java code

 

SQLTemplate DecomParamQuery = (SQLTemplate)
context.getEntityResolver().lookupQuery("ParamDecompQuery");

                        Map params = new HashMap();

                        params.put("$version","Student_Example1");

                        params.put("$parent","<Fact-107>");

                        SQLTemplate newDecomParamQuery =
DecomParamQuery.queryWithParameters(params);

 

When I try to run the application I get an error that the parameter 1 is not
set. If I use $ version instead on '?' in data map I get an error on invalid
column name.

 

Chandni 

 

Re: Parameterized Query in DataMap

[Andrus Adamchik <an...@objectstyle.org>]

Hi Chandni,

to accept parameters, the WHERE part of the query has to look like this:

   WHERE version_name=$version AND d.parent=$parent

And the code where you are setting the parameters - like this  
(without a '$', just the name):

Map params = new HashMap();
params.put("version","Student_Example1");
params.put("parent","<Fact-107>");

Cheers,

Andrus

P.S. I noticed that you are not subscribed. If you want to see all  
replies from the list, you can subscribe by following the  
instructions here: http://objectstyle.org/cayenne/lists.html


On May 3, 2006, at 12:52 PM, Chandni Jain wrote:

> Hello,
>
>
>
> I am trying to use Cayenee to connect to mysql database. I wanted  
> to create
> a parameterized query and store it in DataMap so I can reuse the  
> query.
> Please can you tell me how I should specify the query and how do I  
> map the
> parameters.
>
> Currently this is what I am doing:
>
> In the  datamap
>
> <query name="ParamDecompQuery"
> factory="org.objectstyle.cayenne.map.SQLTemplateBuilder" root="obj- 
> entity"
> root-name="DesignModule">
>
>             <sql
> adapter-class="org.objectstyle.cayenne.dba.mysql.MySQLAdapter"><! 
> [CDATA[SELE
> CT m.* FROM
>
>   design_module m
>
>   JOIN design_modulerefinementrelation d ON m.`factid`=d.child AND
> m.version=d.version
>
>   JOIN __versions__ v ON m.version=v.id
>
> WHERE version_name=? AND d.parent=?]]></sql>
>
>       </query>
>
>
>
> In the java code
>
>
>
> SQLTemplate DecomParamQuery = (SQLTemplate)
> context.getEntityResolver().lookupQuery("ParamDecompQuery");
>
>                         Map params = new HashMap();
>
>                         params.put("$version","Student_Example1");
>
>                         params.put("$parent","<Fact-107>");
>
>                         SQLTemplate newDecomParamQuery =
> DecomParamQuery.queryWithParameters(params);
>
>
>
> When I try to run the application I get an error that the parameter  
> 1 is not
> set. If I use $ version instead on '?' in data map I get an error  
> on invalid
> column name.
>
>
>
> Chandni
>
>
>