Getting SGL to work with MaxDB
I wrote a DB-Wrapper for the PEAR-Odbc-Database-Driver called maxdb_SGL, it will be available soon.......
Reserved Words for !MaxDB
| Reserved in !MaxDB | Context of usage in !MaxDB | !MySQL counterpart |
| @ | May prefix identifier, like '@table' | Not allowed |
| ADDDATE() | SQL function | ADDDATE(); new in !MySQL version 4.1.1 |
| ADDTIME() | SQL function | ADDTIME(); new in !MySQL version 4.1.1 |
| ALPHA | SQL function | Nothing comparable |
| ARRAY | Data type | Not implemented |
| ASCII() | SQL function | ASCII(), but implemented with a different meaning |
| AUTOCOMMIT | Transactions; ON by default | Transactions; OFF by default |
| BOOLEAN | Column types; BOOLEAN accepts as values only TRUE, FALSE, and NULL | BOOLEAN was added in !MySQL version 4.1.0; it is a synonym for BOOL which is mapped to TINYINT(1). It accepts integer values in the same range as TINYINT as well as NULL. TRUE and FALSE can be used as aliases for 1 and 0. |
| CHECK | CHECK TABLE | CHECK TABLE; similar, but not identical usage |
| COLUMN | Column types | COLUMN; noise word |
| CHAR() | SQL function | CHAR(); identical syntax; similar, not identical usage |
| COMMIT | Implicit commits of transactions happen when data definition queries are being issued | Implicit commits of transactions happen when data definition queries are being issued, but also with a number of other queries |
| COSH() | SQL function | Nothing comparable |
| COT() | SQL function | COT(); identical syntax and implementation |
| CREATE | SQL, data definition language | CREATE |
| DATABASE | SQL function | DATABASE(); DATABASE is used in a different context, for example CREATE DATABASE |
| DATE() | SQL function | CURRENT_DATE |
| DATEDIFF() | SQL function | DATEDIFF(); new in !MySQL version 4.1.1 |
| DAY() | SQL function | Nothing comparable |
| DAYOFWEEK() | SQL function | DAYOFWEEK(); the first day (1) by default is Monday in !MaxDB, and Sunday in !MySQL |
| DISTINCT | SQL functions AVG, MAX, MIN, SUM | DISTINCT; but used in a different context: SELECT DISTINCT |
| DROP | inter alia in DROP INDEX | DROP INDEX; similar, but not identical usage |
| EBCDIC() | SQL function | Nothing comparable |
| EXPAND() | SQL function | Nothing comparable |
| EXPLAIN | Optimization | EXPLAIN; similar, but not identical usage |
| FIXED() | SQL function | Nothing comparable |
| FLOAT() | SQL function | Nothing comparable |
| HEX() | SQL function | HEX(); similar, but not identical usage |
| INDEX() | SQL function | INSTR() or LOCATE(); similar, but not identical syntaxes and meanings |
| INDEX | USE INDEX, IGNORE INDEX and similar hints are being used right after SELECT, like SELECT ... USE INDEX | USE INDEX, IGNORE INDEX and similar hints are being used in the FROM clause of a SELECT query, like in SELECT[[BR]]... FROM ... USE INDEX |
| INITCAP() | SQL function | Nothing comparable |
| LENGTH() | SQL function | LENGTH(); identical syntax, but slightly different implementation |
| LFILL() | SQL function | Nothing comparable |
| LIKE | Comparisons | LIKE; but the extended LIKE !MaxDB provides rather resembles the !MySQL REGEX |
| LIKE wildcards | !MaxDB supports '%', '_', 'ctrl+underline', 'ctrl+up arrow', '*', and '?' as wildcards in a LIKE comparison | !MySQL supports '%', and '_' as wildcards in a LIKE comparison |
| LPAD() | SQL function | LPAD(); slightly different implementation |
| LTRIM() | SQL function | LTRIM(); slightly different implementation |
| MAKEDATE() | SQL function | MAKEDATE(); new in !MySQL version 4.1.1 |
| MAKETIME() | SQL function | MAKETIME(); new in !MySQL version 4.1.1 |
| MAPCHAR() | SQL function | Nothing comparable |
| MICROSECOND() | SQL function | MICROSECOND(); new in !MySQL version 4.1.1 |
| NOROUND() | SQL function | Nothing comparable |
| NULL | Column types; comparisons | NULL; !MaxDB supports special NULL values that are returned by arithmetic operations that lead to an overflow or a division by zero; !MySQL does not support such special values |
| PI | SQL function | PI(); identical syntax and implementation, but parantheses are mandatory |
| REF | Data type | Nothing comparable |
| RFILL() | SQL function | Nothing comparable |
| ROWNO | Predicate in WHERE clause | Similar to LIMIT clause |
| RPAD() | SQL function | RPAD(); slightly different implementation |
| RTRIM() | SQL function | RTRIM(); slightly different implementation |
| SEQUENCE | CREATE SEQUENCE, DROP SEQUENCE | AUTO_INCREMENT; similar concept, but differing implementation |
| SINH() | SQL function | Nothing comparable |
| SOUNDS() | SQL function | SOUNDEX(); slightly different syntax |
| STATISTICS | UPDATE STATISTICS | ANALYZE; similar concept, but differing implementation |
| SUBSTR() | SQL function | SUBSTRING(); slightly different implementation |
| SUBTIME() | SQL function | SUBTIME(); new in !MySQL version 4.1.1 |
| SYNONYM | Data definition language: CREATE [PUBLIC] SYNONYM, RENAME SYNONYM, DROP SYNONYM | Nothing comparable |
| TANH() | SQL function | Nothing comparable |
| TIME() | SQL function | CURRENT_TIME |
| TIMEDIFF() | SQL function | TIMEDIFF(); new in !MySQL version 4.1.1 |
| TIMESTAMP() | SQL function | TIMESTAMP(); new in !MySQL version 4.1.1 |
| TIMESTAMP() as argument to DAYOFMONTH() and DAYOFYEAR() | SQL function | Nothing comparable |
| TIMEZONE() | SQL function | Nothing comparable |
| TRANSACTION() | Returns the ID of the current transaction | Nothing comparable |
| TRANSLATE() | SQL function | REPLACE(); identical syntax and implementation |
| TRIM() | SQL function | TRIM(); slightly different implementation |
| TRUNC() | SQL function | TRUNCATE(); slightly different syntax and implementation |
| USE | !MySQL commandline user interface command | USE |
| USER | SQL function | USER(); identical syntax, but slightly different implementation, and parantheses are mandatory |
| UTC_DIFF() | SQL function | UTC_DATE(); provides a means to calculate the result of UTC_DIFF() |
| VALUE() | SQL function, alias for COALESCE() | COALESCE(); identical syntax and implementation |
| VARIANCE() | SQL function | Nothing comparable |
| WEEKOFYEAR() | SQL function | WEEKOFYEAR(); new in !MySQL version 4.1.1 |
Modify SGL DB lib file (OLD)
You have to modify the SGL_DB::singleton(), if you want to use all PEAR objects. Because the object variables will be returned in uppercase letters and SGL works with lowercase variable names.
Insert the line after the fetchmode setting:
$conn->setFetchMode(DB_FETCHMODE_OBJECT);
$conn->setFetchMode('portability', DB_PORTABILITY_LOWERCASE);
This option is tested for !MySQL, !PostgreSQL and !MaxDB and works fine.
If you need more information use the PEAR docs here
Next Step (OLD)
You have to replace all COALESCE-Functions in the SQL-Statements with the VALUE-Function, because VALUE() is the alias for COALESCE().
