Dave, Welcome to being me. I wrote that damned thing, use it every day, and of course I'm still bound to the same rules you are. I'm glad you took the time to document the hoops you have to jump through to make it work. But it you follow those simple steps, 90% of what you need to do just works; except of course for the major limitations of the driver as outlined in a couple open enhancement tickets.
Regards, jeff On Fri, May 23, 2008 at 3:04 AM, Bensoft <[EMAIL PROTECTED]> wrote: > > Spent some time getting this work so I thought I'd document it for > anyone else having trouble with Oracle and the AppModel query() > method. > > Basically, PHP's OCI interface is very limited when it comes to > MetaData. There is no staight forward way to get table names of > columns in a result set. As a work-around the dbo_oracle.php driver > contains a method called _scrapeSQL() that attempts to extract table > and column names from the given query. I began re-writing dbo_oracle > to use OCI_ASSOC to extract column names and convert to OCI_NUM format > but then realized I still couldn't get the table names (In case you > are thinking about doing this, don't waste your time) > > It's easier to follow a compatible format when composing your > dbi_oracle queries. > follow these few simple rules when using query() to get your results > back correctly: > > 1) Do not use * designators in your field list. > > BAD: SELECT * FROM users User > GOOD: SELECT User.id, User.name, User.etc FROM users User > > 2) Make sure columns in your field list are separated with a comma > FOLLOWED by at least one space. > > BAD: SELECT User.id,User.name,User.etc FROM users User > GOOD: SELECT User.id, User.name, User.etc FROM users User > > 3) Make sure your SQL command syntax is in upper case (NOT including > the columns and table specs) > > BAD: select User.id, User.name from users User > GOOD: SELECT User.id, User.name FROM users User > > 4) Make sure the case name of your column definition table > declarations match the name of your table Alias. > > BAD: SELECT user.id, user.name from users User > GOOD: SELECT User.id, User.name FROM users User > > 5) When doing custom record counts, make sure your column designation > is aliased lowercase and the function > is uppercased > > BAD: SELECT count(*) from users User WHERE User.etc like '%misc%' > GOOD: SELECT COUNT(*) AS count from users User WHERE User.etc like > '%misc%' > > That's all for now, hopefully, the OCI interface will be enhanced to > provide good MetaData in the future so Oracle access will improve. > For more information see function _scrapeSQL in dbo_oracle.php > (currently at line 209) > > --Dave > > > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "CakePHP" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/cake-php?hl=en -~----------~----~----~----~------~----~------~--~---
