• Open Menu Close Menu
  • Apple
  • Shopping Bag
  • Apple
  • Mac
  • iPad
  • iPhone
  • Watch
  • TV
  • Music
  • Support
  • Search apple.com
  • Shopping Bag

Lists

Open Menu Close Menu
  • Terms and Conditions
  • Lists hosted on this site
  • Email the Postmaster
  • Tips for posting to public mailing lists
Re: Fetch spec with raw SQL
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

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
References: 
 >Fetch spec with raw SQL (From: "Randy Wigginton" <email@hidden>)
 >Re: Fetch spec with raw SQL (From: Chuck Hill <email@hidden>)
 >Re: Fetch spec with raw SQL (From: Randy Wigginton <email@hidden>)

  • Prev by Date: MEETING: Chicago - CAWUG April 11th
  • Next by Date: WebObjects URL Decoding?
  • Previous by thread: Re: Fetch spec with raw SQL
  • Next by thread: WebServices and attachments
  • Index(es):
    • Date
    • Thread