What data conversion can happen between client software and target DB when using ODBC?

[x457812]

I have a problem with using Crystal Reports to retrieve data from a MySQL DB.

In this case Crystal connects to the MySQL DB using ODBC, and the ODBC middleware was built using Progress DataDirect.

The target DB has a table in which a field has integer values like "2014" and "2015". However, when I check the values in this field in Crystal (using the "Browse Data" function) it shows the values as "14.00" and "15.00".

This field is used in the SELECT statement stored in Crystal and that statement is restricting the records to those where the value is "2015". As a result the report shows no records because Crystal is not seeing the correct values.

Changing the SELECT statement to retrieve all records where the year is "15" (or "15.00") is not helping either. I am presuming this is because the DB does not contain any records with that value.

So in summary the situation is:

1. Use "15" or "15.00" in the SELECT statement: fails in DB because of no records with that value
2. Use "2015" in the SELECT statement: fails inside Crystal which sees the value as "15.00"

Is there some functionality on the ODBC layer that can cause this situation?

Continue reading...