MySQL 5.1

DataTable is a powerful control that is connecting to DB directly. Business cases like search, add, edit and delete from a DB table can be implemented without single line of Java Code.

Jspx is being tested against MySQL DB. DataTable worked fine with normal quires like



However, with quires like this,

SELECT (g.NAME) AS g_name, (m.NAME) AS m_name,

       (CASE c.gender

           WHEN 1

              THEN 'male'

           WHEN 2

              THEN 'female'

           ELSE c.gender

        END

       ) AS c_gender,

       c.*

  FROM dcs.customer c, dcs.grade g, dcs.martial_status m

 WHERE c.grade = g.ID

   AND c.martial_status = m.ID

   AND ID = custid

   AND passport_num LIKE '%pass%'

   AND NAME LIKE '%custName%'

   AND phone LIKE '%custPhone%'

   AND gender = custgender

   AND TRUNC (creation_date) >= str_to_date ('from_date', '%d/%m/%Y')

   AND TRUNC (creation_date) <= str_to_date ('to_date', '%d/%m/%Y')

   AND job LIKE '%custJob%'

   AND grade = cutgrade

   AND martial_status = custmart

   AND deleted = custdel

we noticed that it could not retrieval the full columns returned. To know the reason, let's dive under the Hood, while JSPX  is executing the search SQL, it constructs a hashmap of the returned records

public static List<Hashtable<String, DataField>> search(String datasource, String sql, int start, int size) throws Exception

where the DataField is storing the column name , type and value. The hash map representing one record of the result set is indexed by the column name.

Inside the above method, the column name is retrieved as

if (resultSet.absolute(start + 1))

{

int colCount = resultSet.getMetaData().getColumnCount();

      do

      {

            cols = new Hashtable<String, DataField>();

            for (int i = 1; i <= colCount; i++)

            {

                  fieldName = resultSet.getMetaData().getColumnName(i).trim().toLowerCase();

                  cols.put(fieldName, new DataField(fieldName, resultSet.getObject(i)));

                  }

            rows.add(cols);

      }

      while (resultSet.next() && (++counter < size));

}

The method getColumnName(i) in MySQL JDBC driver returns the original name of the column instead of the alias name in the SQL statement, so assume you have table Customer with column "NAME" and you have table JOB with Filed "NAME".
Creating aliasing for the filed "NAME" in JOB Table as "JOB_NAME" will return the column "NAME" from JOB table under the label "JOB_NAME". But JSPX will get "NAME" as the name of the column.

Instead of  this method we used the getColumnLabel(i) method to get the correct name of the column.