[opencms-dev] SQL Errors cause Problems with DB2

[Simon Wilks]

Hi Andreas and Team,

In my journeys with DB2 and OpenCms I have noticed two problems with SQL
statements which are causing trouble with DB2. I am overriding them but I
think they should be changed in the genericSql package to help futher
"ports" to other DB's.

1) In CmsQueries I have overridden the C_RESOURCES_UNLOCK constant.

Reason: An SQL statement of the form ...LOCKED_BY = -1WHERE PROJECT_ID... is
treated as an error by DB2.

Change: Added a space before WHERE. Instead of ...."WHERE PROJECT_ID... now
" WHERE PROJECT_ID...

C_RESOURCES_UNLOCK = "UPDATE " + C_DATABASE_PREFIX + "RESOURCES SET " +
"LOCKED_BY = " + I_CmsConstants.C_UNKNOWN_ID + " WHERE PROJECT_ID = ?";


2) In CmsDbAccess I have overridden getTaskTypeCondition.

Reason: In the Cms_Table, the field percentage is defined as varchar(50) but
an SQL statement treats it as an integer. DB2 reports a type mismatch.

Change: Added apostrophies (') around the 0 as follows:

		case C_TASKS_NEW: {
				result = result + m_cq.C_TASK_PERCENTAGE + "='0' AND .........
				break;
			}

Note: In readTasks it is also treated as an Integer:
recset.getInt(m_cq.C_TASK_PERCENTAGE) so I don't know if the DB datatype is
wrong or the app's.

Hope you can make use of this.

Regards,

Simon Wilks
software engineer
simon.wilks at silion.ch
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
silion (switzerland) ag    software engineering
spinnereistrasse 8              9008 st. gallen
tel 0878 80 90 60             fax 0878 80 90 30
info at silion.ch                    www.silion.ch

a member of the Penta Group   www.pentagroup.ch