community.egroupware.org: Community wiki

  
Community wiki
Portable SQL

Portable SQL or how to support all the different DB's we already support and others in future


This list is just a start and is based on my experiences with porting eGW to MsSQL? and MaxDB.

1. construct your column-name with a prefix specifying the table/app or a shortcut for it
Some databases (specialy MaxDB) have reserved words which are widely used as column-name, but could not be used with MaxDB. Using a prefix separated by an underscore eliminates the problem (eg. 'my_id'). Keep in mind the max size for identifers (table- / column-names) is 32 chars in MaxDB!
  • id
  • uid
  • year, month
  • count
  • time
  • and I guess all words usualy used in SQL statements like select, from, table, ...
2. Some DB's (MsSQL and MaxDB) are very limited in the use of text or blob columns (MaxDB LONG). They can't compare them with any other operator then LIKE and therefor can't sort or order after them. For MsSQL? you can cast them to varchar to do so (by of cause truncating them!)

3. MaxDB cant update text / LONG columns with a normal update statement
As inserts are ok, you can use in most cases an insert instead with with the modifier "UPDATE DUPLICATES".
If you use the new select, insert, update, delete functions they take care of that and a lot of other things like eg. correct quoting of values and converting timestamps to the DB's internal format

4. MaxDB always need to specify the table a column belongs to in a JOIN's ON clause.
Other DB's only need that in case of equaly named columns in the joined tables. Unique columns retrived via SELECT are Ok.

5. MaxDB and MsSQL? can't use a boolean value / expression in select
You have to use "CASE WHEN $expression THEN 1 ELSE 0 END" for an expression like "column IN ('a','b')" or "id > 5"

6. Only use the view data-types our schema-proc and ADOdb's datadictornary support.
It's not just a random selection, they both support.

7. Please feel free to add your own recomendations and experiences ...



Other useful links:
  1. ADOdb's portable SQL tips
  2. EliminateSqlInjectionHowTo



Back to DeveloperDocs
You are here