DataTable
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.