DBAL Cookbook for Extension Developer¶
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'
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¶
LEFT OUTER JOIN,
RIGHT OUTER JOINare supported
- Do not use extra parentheses. E.g., instead of
SELECT * FROM (table1 INNER JOIN table2 ON ...) LEFT JOIN table3 ON ...
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)
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.