History

DBAL Cookbook for Extension Developer

Database Structure

MySQL supports 'text' columns as part of a WHERE clause using equality, Oracle does not, meaning if you need to do something like that:

SELECT * FROM tx_ext WHERE column = 'something'

Make sure not to use text as column type but instead use varchar(4000) which is the limit for Oracle. The other solution is to use a LIKE operator:

SELECT * FROM tx_ext WHERE column LIKE 'something'

WHERE Clauses

SQL parser is not as powerful as it could be. Typical problems occur with calculated conditions such as

... WHERE column1 + number1 >= number2

Rule 1: The calculated part (column1 + number1) must start with a column name
Rule 2: The calculated part may have a column name or a number as second operand
Rule 3: The part after the operator (number2 in query above) must be a number
Rule 4: The calculated part can only occur on the left hand of the comparison operator
Rule 5: More than two operands on the left hand are not supported

JOIN of multiple tables

  • JOIN, INNER JOIN, LEFT JOIN, LEFT OUTER JOIN, RIGHT JOIN and RIGHT OUTER JOIN are supported
  • Do not use extra parentheses. E.g., instead of
SELECT * FROM (table1 INNER JOIN table2 ON ...) LEFT JOIN table3 ON ...

Write this:
SELECT * FROM table1 INNER JOIN table2 ON ... LEFT JOIN table3 ON ...
  • The same applies for the join condition (ON). Instead of
SELECT * FROM table1 INNER JOIN table2 ON (table1.field1 = table2.field2)

Write this:
SELECT * FROM table1 INNER JOIN table2 ON table1.field1 = table2.field2

Testing queries without using DBAL

If you have a doubt with your query, you may simply install DBAL (that is loading it in Extension Manager) and then use the backend module "DBAL Debug" and its function "Check SQL" that lets you check whether the query you write may be parsed by DBAL. If the query is parsed, then you're very likely having an extension that is DBAL-compatible. Congratulation!

Sorry, you didn't have proper permissions to perform this change.

This error may occur when you try to insert content elements.

In MSSQL, this is caused by columns with attribute "NOT NULL" without a default (empty) value. Then make sure that all of your *_uid tables (the identity sequences) have a value equal to MAX(uid) of the corresponding table.