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,
) AS c_gender,
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();
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)));
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.