DB2

pdo_db2 face difficult with LOWER() - SQL0418N

Submitted by hswong3i on Sun, 2008/02/10 - 20:15

Finally, I give up Drupal 7.x + ibm_db2 development. It is because ibm_db2's BLOB INSERT/UPDATE/SELECT handling are too complicated... So let's skip it and dig to pdo_ibm directly, as like as case of SQLite ;-)

Most likely, pdo_ibm is just a simple clone of pdo_pgsql implementation, with different connection string handling (surly, we need different schema.*.inc and common.*.inc to serve database dependent differences). Up to this point, I am able to implement install.pdo_ibm.inc without any difficulties.

BTW, we still can't escape from some basic DB2 limitation: SQL0418N with SELECT + LOWER(), e.g. we use LOWER() for username compare. To solving this, we may use CAST().

IMHO, I would like to revamp our programming logic, rather than get rid this limitation with some hybrid implementation. This problem may left for other developers: it seems to be a problem of cross database query implementation, but not a duty of PDO driver development ;-(

Here are some reference message for SQL0418N:

DB2 VARCHAR with 4000 characters logical limitation

Submitted by hswong3i on Wed, 2008/01/02 - 10:57

Even though DB2 don't explicitly indicate the limitation of VARCHAR as like as that for Oracle (Oracle will only allow for VARCHAR2 with max 4000 characters; above this limitation, please consider to use CLOB with TB-scale storage), it is still logically limited as 4000 characters.

A minor footnote from IBM DB2:

The functions in the SYSFUN schema taking a VARCHAR as an argument will not accept VARCHARs greater than 4 000 bytes long as an argument. However, many of these functions also have an alternative signature accepting a CLOB(1M). For these functions, the user may explicitly cast the greater than 4 000 VARCHAR strings into CLOBs and then recast the result back into VARCHARs of desired length.

This should be a good example why CLOB is something necessary for most database implementation. We are not always as lucky as case of MySQL and PostgreSQL, which we are able to work with TEXT for (nearly) unlimited size. On the other hand, we have CLOB, which usually required for special handling, e.g. database dependent INSERT/UPDATE/DELETE abstraction, decode CLOB as like as BLOB when fetching data, etc.

Since we are now having drupal_write_record() for high level programming abstraction, and (may) have INSERT/UPDATE/DELETE API for driver level abstraction, CLOB should be something much simpler than case of before. Moreover, this logic is now proved as function with Siren: Siren is able to work with Oracle CLOB, on the other hand don't introduce complicated abstraction to MySQL and PostgreSQL.

Script for testing PHP ibm_db2 functioning

Submitted by hswong3i on Mon, 2007/12/31 - 22:34

NOTE! Don't forget to setup ibm_db2.instance_name=db2inst1 correctly, as guideline in PHP :)

<?php
  $db_conn = db2_connect("DATABASE=SAMPLE;HOSTNAME=127.0.0.1;PORT=50000;PROTOCOL=TCPIP;UID=db2inst1;PWD=CHANGE", '', '');
  $cmdstr = 'SELECT "LASTNAME", "SALARY" FROM "EMPLOYEE"';
  $stmt = db2_prepare($db_conn, 'SELECT COUNT(*) AS "NROWS" FROM ('. $cmdstr .')');
  db2_execute($stmt);
  $nrows = db2_fetch_object($stmt)->NROWS;
  $stmt = db2_prepare($db_conn, $cmdstr);
  db2_execute($stmt);
  echo "<html><head><title>DB2 PHP Test</title></head><body>";
  echo "<center><h2>DB2 PHP Test</h2><br>";
  echo "<table border=1 cellspacing='0' width='50%'>\n<tr>\n";
  echo "<td><b>Name</b></td>\n<td><b>Salary</b></td>\n</tr>\n";
  while ($array = db2_fetch_assoc($stmt)) {
    echo "<tr>\n";
    echo "<td>" . $array["LASTNAME"] . "</td>";
    echo "<td>$ " . number_format($array["SALARY"], 2). "</td>";
    echo "</tr>\n";
  }
  echo "<tr><td colspan='2'> Number of Rows: $nrows</td></tr></table><br>";
  echo "<em>If you see data, then it works!</em><br>";
  echo "</center></body></html>\n";
?>

IBM DB2 v9.5: a lot of improvement!

Submitted by hswong3i on Fri, 2007/12/07 - 17:43

At least IBM DB2 v9.5 give me more hope for Drupal/Siren driver development ;-)

Large identifier support lets you more easily port applications from other DBMS vendors. You will also find it easier to migrate data definition language (DDL) because you no longer need to shorten identifiers.

https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2....

In Version 9.5, the IBM® DB2 Data Server Client (formerly, the DB2 Client) comes with Hypertext Preprocessor (PHP) extensions; you no longer need to download them. Also, Version 9.5 builds on the Version 9.1 PHP support by providing a new extension called PDO_IBM.

https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2....

Version 9.5 provides new Unicode conversion tables that allow Big5-HKSCS clients to connect to and store HKSCS (Hong Kong Supplementary Character Set) data in Unicode databases.

https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2....

The scalar functions UPPER (UCASE) and LOWER (LCASE) can now change the case of text using a locale-sensitive conversion. By default, UPPER and LOWER convert characters in the string without considering locale. For some characters, there is a different mapping between uppercase and lowercase characters when using a locale-based conversion.

https://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2....

P.S. too bad that LIKE is still not working as like as other database... see here.

Syndicate content