Business Objects Fails to Retrieve Rows from MySQL Table
I just encountered a very odd situation in Business Objects where a Web Intelligence (WebI) report running against a MySQL v5 database suddenly stopped returning any rows, even though the data had not been deleted. It turned out that the underlying application had been upgraded and the database schema had undergone a few minor changes but essentially the table structure appeared not to have changed at all.
After investigating for a few hours I managed to rule out the change in storage engine (from MyISAM to InnoDB) and had managed to narrow the issue down to a single varchar column. I didn’t have access to the pre-upgrade table scripts so I couldn’t see what had changed but I did notice that in the new table definition the character set of the column was being specified as ASCII like so…
`column` VARCHAR(10) CHARACTER SET ASCII DEFAULT NULL
To fix this I went back to the Universe level and amended my object definition to CAST the column values…
CAST(`column` AS CHAR CHARACTER SET UTF8)
That solved the problem, if you have the same issue – I hope this helps you on your way.
Categories: Business Objects, MySQL Tags: business objects, CAST, Character Set, fail, MySQL
