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 -~----------~----~----~----~------~----~------~--~---
