Re: Fetch spec with raw SQL
Re: Fetch spec with raw SQL
- Subject: Re: Fetch spec with raw SQL
- From: Chuck Hill <email@hidden>
- Date: Mon, 10 Apr 2006 17:45:57 -0700
On Apr 10, 2006, at 4:49 PM, Randy Wigginton wrote:
Perhaps there is a better way with EOModeler. For one example, I
would like to put lowercase() around some of the columns being
queried in order to be case-insensitive. Can EOModeler do that?
That is wrong in so many ways. :-) First, the efficient way to do
this is to modify the database schema so that these columns use a
collation order that ignores case. Second, using the qualifier
comparison QualifierOperatorCaseInsensitiveLike will do this for you
(though, IIRC, it uses UPPER instead of LOWER). You have to get into
some pretty funky schema to need custom SQL.
I guess I am not impressed with EOModeler's sql.
EOModeler does not generate SQL, EOF does.
Another case... I have a table that looks like this:
id
name
parent_id
This table represents a tree. I want to query based on the name of
a node, and the parent's name. I create a relationship from the
object to its parent (linking parent_id to id), and I tried to
create a query based on name and parent.name. The sql it wants to
use is:
SELECT name, id, location_id, parent_id FROM table WHERE (name = ?
AND name = ?) AND parent_id = id
Something is wrong in your model or in your qualifier. The qualifier
should look like name = %@ and parent = %@ with bindings for name
and the parent object.
Which clearly won't work. The sql I would elect to use is:
select * from table where name=? and parent_id=(select id from
table where name=?)
You might be able do this this with a qualifier like name = %@ and
parent.name = %@
That should produce something like
select * from table T0, table T1 where to.id = t1.parent_id and
to.name=? and T1.name=?
Failing that, Pierre Bernard has some qualifier additions posted at
http://homepage.mac.com/i_love_my/code.html
I have started using these and they are excellent for overly complex
schemas.
Chuck
On Apr 10, 2006, at 4:24 PM, Chuck Hill wrote:
Randy,
On Apr 10, 2006, at 3:12 PM, Randy Wigginton wrote:
If someone can point me to an example, I’d be most grateful.
I want to create a fetch spec with two bind variables, yet using
custom sql. Is there a way to do it?
I don't know, but I will ask why? Are you sure you need custom
SQL? I've done a bit of WebObjects, and I don't recall ever
having to use custom SQL in a fetch spec. EOF is remarkably
flexible when it comes to generating SQL. Perhaps there is
another, better, more portable solution to your problem.
Chuck
I’ve created a fetch spec with two keys, and I’ve executed it.
As soon as I click the button for “raw sql”, I get an oracle
error (not all variables bound).
Is there some kind of trick? This seems incredibly simple, yet I
can’t get it to work.
--
Coming in late 2006 - an introduction to web applications using
WebObjects and Xcode http://www.global-village.net/wointro
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve
specific problems. http://www.global-village.net/products/
practical_webobjects
--
Coming in late 2006 - an introduction to web applications using
WebObjects and Xcode http://www.global-village.net/wointro
Practical WebObjects - for developers who want to increase their
overall knowledge of WebObjects or who are trying to solve specific
problems. http://www.global-village.net/products/practical_webobjects
_______________________________________________
Do not post admin requests to the list. They will be ignored.
Webobjects-dev mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
This email sent to email@hidden