Hi,

I recently installed ubercart on oracle database and found that it is not supporting it. I came to conclusion that a lot of queries were written using db_query which are specific to mysql. I think we need to make use of pdo classes (db_select, db_update, db_insert, db_delete) so that it supports other databases too. Soon i will be submitting a patch for this as currently doing code changes to make this compatible.

Regards
Sagar

Files: 
CommentFileSizeAuthor
#10 1684266-oracle-compatibility.patch39.83 KBlongwave
PASSED: [[SimpleTest]]: [MySQL] 2,457 pass(es).
[ View ]

Comments

longwave’s picture

Category:bug» feature
Status:Active» Postponed (maintainer needs more info)

db_query() calls are not MySQL specific. Ubercart works fine on SQLite, and also Postgres (except for product attributes in some setups, for which there is an open bug report).

I am not sure we need to change all db_query() calls, it is not deprecated in D7, and the documentation even says:

Use this function for SELECT queries if it is just a simple query string. If the caller or other modules need to change the query, use db_select() instead.

Many SELECTs in Ubercart are simple query strings.

Can you give more detail about the errors you are getting when using the Oracle driver?

Sagar Ramgade’s picture

Hi,
When i enable uc_attributes module, error mentioned below started appearing :

PDOException: SELECT upa.aid FROM {uc_product_attributes} AS upa LEFT JOIN {uc_attributes} AS ua ON upa.aid = ua.aid WHERE upa.nid = :nid ORDER BY upa.ordering, ua.name (prepared: SELECT upa.aid FROM "EDABBA_"."UC_PRODUCT_ATTRIBUTES" AS upa LEFT JOIN "EDABBA_"."UC_ATTRIBUTES" AS ua ON upa.aid = ua.aid WHERE upa.nid = :nid ORDER BY upa.ordering, ua.name ) e: SQLSTATE[HY000]: General error: 933 OCIStmtExecute: ORA-00933: SQL command not properly ended (/tmp/PDO_OCI-1.0/oci_statement.c:142) args: Array ( [:nid] => 1 ) in uc_product_get_attributes() (line 1154 of ../public_html/sites/all/modules/contrib/ubercart/uc_attribute/uc_attribute.module).

TR’s picture

Title:Support for Other databases like Oracle, Mongo db» PDOException with Oracle
Category:feature» bug

Support for other databases is provided by the core Drupal Database API and contributed database driver modules like http://drupal.org/project/oracle. There is nothing inherently wrong about using db_query(), and in fact it is *recommended* for most select queries. However, there might be some complicated queries in Ubercart where the syntax used in the SQL isn't recognized by Oracle. It's also possible that either core Drupal or the Oracle driver are doing the wrong thing.

Looking through the issues on drupal.org, it seems that Oracle might require a different syntax for the ORDER BY clause. If you can let us know what the syntax should be for Oracle, then we could look through the code and modify and queries that use ORDER BY.

Because db_select() is significantly slower than db_query(), we'd like to avoid changing queries unless they have a specific identifiable problem.

longwave’s picture

Perhaps the same as #1340360: from table AS alias - if you remove the "AS" before the table aliases, does that fix it without having to rewrite the query entirely?

TR’s picture

Version:7.x-3.1» 7.x-3.x-dev

@Sagar Ramgade: Have you made any progress on this?

Sagar Ramgade’s picture

Hi Tim,

Yes i figured it out, as oracle doesn't support "AS" keyword it was throwing errors. So i modified most of the queries in the module to make it work.

longwave’s picture

Could you post your changes as a patch file?

Sagar Ramgade’s picture

Hi,

Yes i will be posting a patch file soon as i am still in process of testing all of the submodules too.

longwave’s picture

Title:PDOException with Oracle» PDOException with Oracle due to AS keyword in SQL
Status:Postponed (maintainer needs more info)» Active

Better title, and back to active as we know what the problem is.

longwave’s picture

Status:Active» Needs review
StatusFileSize
new39.83 KB
PASSED: [[SimpleTest]]: [MySQL] 2,457 pass(es).
[ View ]

@Sagar Ramgade: Can you test the attached patch? This should remove AS for table aliases across all modules in Ubercart.

longwave’s picture

Status:Needs review» Fixed

Manually tested the pages with queries that were changed in the above patch, all seems fine, so committed.

Status:Fixed» Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.