I've found a possible bug related to Ultralite and SELECTs commands with UNION. If I try to execute a select command union all with another select command, and one of these commands have a null column, the Ultralite throws an error, or, in some cases, leaves the ResultSet in a invalid state. It seems like the Ultralite want to convert the varchar column to numeric so that it can merge the result sets.
-- Error SELECT '22222222222222222222222222222' as c2 UNION ALL SELECT null as c2 -- Invalid State SELECT null as c2 UNION ALL SELECT '22222222222222222222222222222' as c2
When an error occurs, the following message is shown:
There was an error reading the results of the SQL statement. The displayed results may be incorrect or incomplete. [UltraLite Database] Cannot convert 22222222222222222222222222222 to a numeric SQLCODE=-157, ODBC 3 State="07006"
Thanks for reporting the problem. It appears that UL doesn't do a good job of inferring the column type when it sees the constant null in the UNION. I did a little experimentation and it seems that if the null comes from a column (or some expression where the type is better determined) UL does the right thing.
Breck's workaround is the right one (I confirmed it works with version 12 at least).
I'll add the issue to our bug list. In the meantime, hopefully Breck's workaround does the trick for you.
answered 02 Jul '12, 14:09