Database Independence: Problem with Conversion of SQL Statements with Quoted Strings

Quoted Strings in SQL statemens might get converted unintentionally

Resolved

Any, except for Oracle

erbox_253b-*

Convert

convertStatement(...), replaceStrings(...), recoverStrings(...)

There is an unresolved flaw in Compiere's database independence approach, at least until release 2.5.3b: The conversion from an Oracle style SQL statement into an equivalent statement for the selected target database is done partly by a few "simple" search and replace operations with regular expressions, which are bundled in class ConvertMap.

The problem is that quoted strings in SQL statements are not excluded from these search and replace operations, which leads to unintended side effects and errors.

E.g. for Sybase the regex \\bSTART WITH\\b gets replaced by the string START. Any user who ever enters the string sequence START WITH in one of text fields (e.g. Product Description) on an update operation will be astonished to see, that his ERP system chooses to know better and stores START in the database instead.

The high level descriptin of the solution can be explained in three steps:

1. Extract all quoted strings from the SQL statement before the actual conversion starts and replace them with other strings which will not be affected by the search and replace operations mentioned above. Remember the substitutions performed in this step.
2. Proceed with the ususal conversion procedure.
3. After conversion is complete recover the quoted strings in the converted statements.

The actually implemented algorithm in Convert.replaceStrings(...) and Convert.recoverStrings(...) is more complex because finding suitable replacement strings is not trivial. It is achieved by generating a random string of growing length until the random string cannot be found in the statement to be converted. This is repeated until all quoted strings are stripped from the original statement.