Display of MySQL Bit-Field

Hi all, I need help with the following problem. I want to display data fields retrieved by a query with the MergeBlock function. There are BIT fields (true/false) among. The DB interface is ODBC and with SQL Server the method for display is simple and looks like
<code>
[Data.Notify;if [val]=1;then '+';else '-']
</code>
SQL Server uses 0/1 for BIT fields. But this does not work e.g. with MySQL. They use an 'angle' as true and nothing as false. Simple output using
<code>
[Data.Notify]
</code>
prints nothing. When using the onformat option $CurrVal echoes this ‘angle’, strlen($CurrVal) is 1 but intval($CurrVal) is always 0 as ($Curval ? 'true' : 'false') is always false.
Who knows a way to get a 0/1 from MySQL or a  working onformat function for both databases? Any help appreciated.

Regards,
Georg.

Hi,

MySQL doesn't have the BIT type. It has TINYINT which is a INT with 4 bytes. So there is no problems with boolean data.

I dont know what is the data you retrieve in PHP for BIT data items coming from SQL Server. But you can discover it by doing a gettype() and a ord() on the value you get.

I'm sorry, but I have to disagree - partially.

MySQL does support ODBC Type SQL_BIT (-7) as 'bit' which is stored as a char(1). When using UTF-8 encoding the result seems to be 2 Bytes e.g. [01 00] for true.

When displaying with TBS using [Data.Bitfield] nothing is printed. Using gettype in an onformat event always reports a 'string'. The ord function seems to be the only way to get an usable value; the result is in fact 0 or 1.

SQLServer gives back '0' or '1' as characters, so ord results in 48 or 49. Probably I need to implement an onformat event which does something like this


Hopefully the manipulated $CurrVal will then be correctly passed to an if-clause so that the following will work.


Thanks for your help so far,
regards, Georg

Hi Triggy,

Your'e right. I've discovered that the type BIT has been added to MySQL 5.0.3.
  http://dev.mysql.com/doc/refman/5.0/en/numeric-type-overview.html

Your way is the good one. TBS converts any value to string just before to merge it (no other way to do), but you can handle this value before its conversion using a "onformat" or "ondata" function.

Hi Skrol29,

the following code works. Not as nice and comfortable, but does the right thing with both databases and hopefully others, too.

Thanks again.

Hi,

I've just been having a similar issues, thanks for the help. I was trying to use IS_NULL to evaluate my bit field which obviously doesn't work. I didn't use the function you gave (Triggy) up above. Simply the following (Guess it amounts to the same thing though and works fine:

if(ord($query_Results['sex']) == 0) { DO SOME STUFF

You would think using the BIT field for boolean logic would be much simplier.

Anyway, thanks