Monday, December 13, 2010

Case Senstive DBMS


Is a powerful Web Control that is connecting directly to the DB making full management of data without no single line of codes. DataTable is using plain sql statement that it has a place holders for parameters and variables.

Now let's consider the following SQL statement to run on MySql DB.

SELECT (g.NAME) AS g_name, (m.NAME) AS m_name, gen.NAME AS c_gender,
       bdb.NAME bdb_name, c.*
  FROM dcs.customer c,
       dcs.grade g,
       dcs.martial_status m,
       dcs.gender gen,
       dcs.boolean_db bdb
 WHERE c.grade = g.ID
   AND c.martial_status = m.ID
   AND c.gender = gen.ID
   AND bdb.ID = c.deleted
   AND c.ID = custid
   AND passport_num LIKE '%pass%'
   AND c.NAME LIKE '%custName%'
   AND phone LIKE '%custPhone%'
   AND gender = custgender
   AND (creation_date) >=str_to_date ('from_date 00:00:00', '%e/%m/%Y %H:%i:%s')
   AND (creation_date) <=str_to_date ('to_date 23:59:59', '%e/%m/%Y %H:%i:%s')
   AND job LIKE '%custJob%'
   AND grade = custgrade
   AND martial_status = custmart
   AND deleted = custdel
   AND (birth_date) >= str_to_date ('from_Bdate', '%e/%m/%Y')
   AND (birth_date) <= str_to_date ('to_Bdate', '%e/%m/%Y')

The highlighted conditions are used to limit the search to certain date window. The problem that the date formatter in Oracle is different than MySQL. in MySQL the formatter is case senstive. i.e. %Y is different than %y. Full description of the date formatting can found here

Jspx DataParam is used to inject user values into the place holders in the SQL statement. To do that, DataParam converts the Expression [ (birth_date) >= str_to_date ('from_Bdate', '%e/%m/%Y') ] to lower case, then replaces the variable [ from_Bdate] with the value . Changing the case will cause the format to work differently on MySQL. That when we decided to make the replacement case sensitive which will work for both MySql and Oracle. This simply make it difficult to developer as he has to write the DataParam attributes (Expression, name) case identical.

In order to solve this, a new Boolean attribute caseSensitive is added to the DataTable to decide if the Expression will be case sensitive or not. As usual, this parameter has a default value to false, which means that it will be backward compatible with what you have done before.

If this attribute is set to true, then your expression should contain the place holder variable as the same case as the name attribute.

<dataparam name="from_Bdate" control="bdFrom" id="dp1" expression="(BIRTH_DATE) >= STR_TO_DATE('from_Bdate','%e/%m/%Y')"/>

in case there a casing diffrence, you will get the exception

DataParam [dp1] has the Expression [(BIRTH_DATE) >= STR_TO_DATE('from_Bdate','%e/%m/%Y')] that does not contain the name [from_bdate] Please make sure of the name exists and match case

Despite that fact that this attribute caseSensitive is related to the DataParam behavior, it is a property in DataTable. The reason for that, is that this property is consistent over the DB. So why it is not a property a cross the application instead of repeating it on every DataTable? The answer is, this will allow to have your application connecting to different DBMS (Oracle, MySQL) based on your DataTable.