Improvements to the Oracle adapter

[kmeirlaen]

To be added, in Zcodo framework, the Oracle Adapter. The Oracle adapter has been tested from July 2007 and it is used in some production applications. It is available as external file (from the Qcodo site) but it should be integrated with the tar ball of Zcodo.

The Oracle adapter comes with a script called “Oracle Tools for Qcodo/Zcodo?”. This script can generate the auto-incremental field needed by Zcodo for its CRUD functions. The auto-incremental creates a sequence and a trigger called TABLE_NAME_SEQ and a trigger TABLE_NAME_TRG.

—-
It’s worth noting that the Oractle adapter is not compatible with large column types, such as Text due to the way QCodo’s DB adapters are designed, and the design of the OCI8 PHP module.
—
The OCI8 plugin (and possibly oracle itself) have a string length limit in SQL statements.

I’m afraid it’s been a while since I ran into this, so I’m fuzzy on the details, but I believe it ran something like this:
SQL statements are limited to VarChar?2 length (4000 char, I think)
CLOB and other larger datatypes are not supporeted using the standard SQL, and instead have special OCI8 functions for handling them.
Since QQ and custom queries are all database independant, they aren’t designed to use those special functions.

The only way to “fix” this is to prevent users from writing raw SQL, update all the database adapters with functions and a query structure to support these special types of functions, then update QQ to use those functions rather than using raw SQL.
—
Another example is that Oracle is case sensitive using the LIKE SQL operator. I had to use custom SQL. As you said any adapter should be modified. My big problem is just that I do not know how Zcodo data types works and a clear correspondance between db data types and Zcodo data type.

[agsel]

Few points we have changed in given tool:

• when connecting, 4th parameter for oci_(p)connect is 'UTF8' (instead of 'utf8'). Probably we had problems with lowercase utf8. (around row 110)
• in method "Parse", there was always created a new connection. So, we added check for blnConnectedFlag (row ~165):

      // Connect if Applicable
      if (!$this->blnConnectedFlag) $this->Connect();
  

• to get nice exceptions:
  • in method "Execute":
    • we have added @ in front of oci_execute functions (to suppress warnings). Rows ~180, ~183.
    • when getting oci_error, you must pass result object as the parameter (row ~187):

      if ($objOracle_error = oci_error($objResult))
      

  • the same should be done in methods "Query" and "Non-Query"
• Changed method name "TransactionRollBack?" (was "TransactionRollback?"). Row ~371. This way it uses the same name as in parent class.
• in QOracleDatabaseField class constructor, Unique/Identity/PrimeryKey? flags are not set correctly. I'm not sure, whether given code has got into latest version of adapter, but just in case, here is our current code:

  $objResult=$objDb->Query(sprintf("select position, constraint_type as type from
			user_cons_columns ucc,user_constraints uc where ucc.column_name='%s'
			AND ucc.TABLE_NAME='%s' and ucc.TABLE_NAME=uc.TABLE_NAME and
			uc.CONSTRAINT_NAME=ucc.CONSTRAINT_NAME and constraint_type IN ('P', 'U') order by position",$this->strOriginalName,$this->strTable));
    // TODO: unique and primary?
    // TODO: auto number (sequence)
    // TODO: not null?
    while ($mixRow = $objResult->FetchArray()) {
      if (isset($mixRow['TYPE'])) {
        if ($mixRow['TYPE'] == 'P') {
          $this->blnPrimaryKey = true;
          // auto-number
          $this->blnIdentity = true;
          break;
        }
        if ($mixRow['TYPE'] == 'U') {
          $this->blnUnique = true;
          $this->blnIdentity = false;
          $this->blnPrimaryKey = false;
        }
      }
    }

as you can see, there are some TODOs. But at least it can identify unique keys.

• in method "Query", there is used Oracle hint "/*+ FIRST_ROWS(n) */". I believe there should be number instead of n. Also, we have dropped this hint, as it has no (positive) effects and Oracle itself is said, that hints are not recommended. At least with given (n) some complex queries run like 10 times slower than without this hint. In smaller and simple queries, there is probably no difference. (row ~232)

That's all I remember currently. I have to clean the code, to be able to commit it to the community. May-be someone else, who is dealing with this adapter, can add those fixes/modifications?

And some of those points can be discussed further.

[enzo]

Hello Agsel

Could you attach here your patch or original file in order to apply in a testing environment before to put in production.

Best regards

enzo

[alex94040]

Dupe of #29

[alex94040]

Experimental Oracle adapter has now been added to the core for 2.0; can one of you try to incorporate the suggestions from this ticket into a patch so that we can roll it out to everyone?

[alex94040]

(see ticket #29 for more info)