Mailing-List: contact user-help@cayenne.apache.org; run by ezmlm
Precedence: bulk
Reply-To: user@cayenne.apache.org
Received-SPF: pass (athena.apache.org: domain of robert.zeigler@gmail.com
 designates 74.125.46.153 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=gamma;
        h=message-id:from:to:in-reply-to:content-type
         :content-transfer-encoding:mime-version:subject:date:references
         :x-mailer;
        b=EPDumUACLivSQBcWlmLMlVGShDWwkrnCJkVA1aCAafnI+82bj3rTp1RfxUT107ObL9
         k9Ef8ikZHLvXbR5aA/z72g7Pfi8nKnH8gySTQkEkomh2UIN8nAn93gjroV57iV4wgu09
         fTQhKYoQR4pXQ3R0T0J/cWhUuPWNGs4fQRdOs=
Message-Id: <5D355F0C-F315-4B0E-9905-25F821764DC8@puregumption.com>
From: Robert Zeigler <robert.zeigler@gmail.com>
To: user@cayenne.apache.org
In-Reply-To: <4993A960.4070101@mark100.net>
Content-Type: text/plain; charset=US-ASCII; format=flowed; delsp=yes
Content-Transfer-Encoding: 7bit
Mime-Version: 1.0 (Apple Message framework v930.3)
Subject: Re: Apostrophe escaping in select
Date: Wed, 11 Feb 2009 23:08:42 -0600
References: <4993A960.4070101@mark100.net>

Two comments.
First, for the query you're running, you probably want an object  
select query, rather than SQLTemplate:

SelectQuery q = new SelectQuery(Person.class);

Expression qual =  
ExpressionFactory.matchExp(Person.COUNTRY_PROPERTY,country);
qual =  
qual.andExp(ExpressionFactory.matchExp(Person.NAME_PROPERTY,name);

q.setQualifier(qual);
dataContext().performQuery(qual);

I typically use SQLTemplate for performance reasons, or to express  
some query that doesn't necessarily correlate to a particular object  
in the db (aggregrate-function queries, like sums, counts, etc.).   
Otherwise, I stick with object-select queries.  You could even create  
the above object select query in the modeler and just reference it by  
name in the code:

dataContext.performQuery("PersonSelectQuery",params);//2.0.4 only; for  
3.0, you would used a NamedQuery object; params is a map with the  
appropriate parameters, as below.

But if you really want to use SQLTemplate, then you should do two  
things:
   1) use #result to describe the results that cayenne should expect  
from your query (instead of select * from, use select #result(...)[,  
#result(...)] from).
   2) use #bind or #bindEqual directives for parameter binding.  This  
ultimately results in Cayenne using prepared statements and plugging  
in parameters supplied from a map at runtime, so that the parameter is  
properly escaped by the database driver when you execute the query.

For example:

SQLTemplate template = new SQLTemplate(Person.class,
   "SELECT #result(...) FROM people where country_id  
#bindEqual($countryId) and name #bindEqual($name)");

Map<String,Object> params = new HashMap<String,Object>();
params.put("countryId",country.getId());
params.put("name",name);
template.setParameters(params);
dataContext().performQuery(template);

See: http://cayenne.apache.org/doc20/scripting-sqltemplate.html for  
more information on #result, #bind, and #bindEqual
See: http://cayenne.apache.org/doc20/selectquery.html for more  
information on select queries.

Robert

On Feb 11, 2009, at 2/1110:45 PM , Mark Fraser wrote:

> hello,
>
> I am using Cayenne 2.0.4 in a standalone application with Derby  
> embedded.
>
> The following code breaks for obvious reasons when there is an  
> apostrophe ("'") in the person's name.
>
> ---------
>
> SQLTemplate template = new SQLTemplate(Person.class, "SELECT * FROM  
> people where country_id=" + String.valueOf(country.getId()) + " and  
> name='" + name + "'");
>
> List res = dataContext().performQuery(template);
> ---------
>
> What is the best (Cayenne specific or otherwise) approach to dealing  
> with this problem?
>
> Thanks
>