Lazarus DBGrid not rendering columns correctly
lazarus / 16-Databases

----------------------------------------------------------------------
famichael · 2013-01-23 21:27

Hi,

I'm having some trouble with a dbgrid component.

I have a few tinyint(1) columns and some datetime columns that I
want to display.

Col3 is tinyint(1) unsigned containing either a zero(0) or a one(1).
I'm using MYSQL. In my sql text, i have the following

select a.col1,b.col2,if(a.col3 = 0,'No','Yes') as Active,b.col1
from table1 a,
        table2 b
where (a.col = b.col1);

The problem is the dbgrid is not rendering a 'No' or 'Yes' when I have
joined tables. The column Active displays the integer value 28494
instead of a 'Yes' or 'No'.  The actual data in the column is either a
zero or a 1

It works when the query is against a single table. When I use datediff
against two dates, the column is empty.

I have executed the exact same queries in my sql editor, MYSQL is
returning the correct results everytime.

Anyone seen this?  I have removed the dbgrid and replaced it,  removed
the TSQLQuery and datasource, etc, checked the column types, etc.
The problem persists ... I'm not sure how to get past this issue.

Please help !!!  

Thanks

----------------------------------------------------------------------
famichael · 2013-01-23 22:16

All -

Never mind ... I found the problem. When you have your TFields defined
in your TSQLQuery (right click on your TSQLQUERY component and select
"Edit Fields ..."  the if(), case(), datediff()  ... functions don't
work. You don't get an error as the query seems to run fine, you just
don't get the expected output. Maybe something to do with the column
name?  dunno,
i'm out of the woods! I should understand real cause but no time
today.

Whew!

----------------------------------------------------------------------
goodname · 2013-01-23 23:06

Instead of using the if() function try the case expression.
http://dev.mysql.com/doc/refman/5.6/en/control-flow-
functions.html#operator_case
Used case expression with postgresql and it works.