ZEOS with BIT(1) field in MySQL and Delphi

[beckert2014]

The ZEOS works very well with MYSQL database. I have not had a problem until I started doing tests with boolean fields.
I have two tables in a Boolean fields are defined as TINYINT (1). Another as BIT (1).
Using HEIDE or MYSQL front, I can see the columns and edits them normally without problems.
When I use to make my Delphi application, the fields defined as TINYINT (1) typically appear in a DBGRID much as I can through a DBcheckbox map the 0 and 1 for true or false without problems.
But the BIT (1) all fields appear with the zeroed columns.
I ask: Does anyone managed to use BIT (1) field in Delphi and using ZEOS for boolean fields?

I did a search to see if the topic already existed, if I was wrong, I apologize

[EgonHugeist]

Hi,

A little testcase would be nice.

Note Zeos assumes TBooleanFields for enum('Y','N');

[beckert2014]

Hi,

A little testcase would be nice.

Note Zeos assumes TBooleanFields for enum('Y','N');

==========================================
Sorry .... I must have expressed myself badly.
I have two tables tests.
In a table defining the field as tinyint (1).
In another table defining the field as bit (1).
In both tables filled random values 0 and 1.
A Delphi application component using ZEOS connection and two DBGrid for example.
Each DBGrid pointed to one of the tables.
In the associated DBGrid table with tinyint field values appear as they are posted.
In the associated DBGrid table with bit field values appear all zeroed.
The same happens if you place DBCheckbox and map check and uncheck to 1 and 0 respectively.
At no time have created boolean field.
I want to keep them 0 and 1 for portability export.
My problem is editing the bit field by delphi.
===== in portuguese ===============
Desculpe.... devo ter me expressado mal.
tenho duas tabelas de testes.
Em uma tabela defini o campo como tinyint(1).
Em outra tabela defini o campo como bit(1).
Em ambas tabelas preenchi valores aleatórios de 0 e 1.
Numa aplicação Delphi, usando componente ZEOS de conexão e dois DBGrid por exemplo.
Cada DBGrid apontava para uma das tabelas.
No DBgrid associado a tabela com campo tinyint, os valores aparecem como foram colocados.
No DBgrid associado a tabela com campo bit, os valores aparecem todos zerados.
O mesmo acontece se colocar DBCheckbox e mapear os check e uncheck para 1 e 0 respectivamente.
Em nenhum momento criei campo booleano.
Quero mante-los 0 e 1 para portabilidade de exportação.
Meu problema é a edição do campo bit através do delphi.

[EgonHugeist]

Ok now i understand.

You need the 'b' prefix for the values see: http://dev.mysql.com/doc/refman/5.1/en/bit-type.html

Lorbs, i don't think Zeos can handle this type as you expect it. Actually i don't know a way to notify the statments or parameters for the b'10101' syntax.
I'm affraid MySQL seems to miss a conversion in this case! So you'll have to to the job manually, i think.

Second approach would be: Add 'prefereprepared=true' to the TZDataSet.Properties. This activates th binary mysql statment.. Might work, but i don't know that for sure.

Note: Zeos is a common access component. We are not able to handle ALL specials. Feel free too attach your approach, if you get this running..

[beckert2014]

Solved.
Thank you Mr. Egon.
put in "prefereprepared = true" properties TZConnection solved the problem.
The column set to BIT (1) field appeared with the data normally and could be edited without problems.

[EgonHugeist]

To be honest...

Indeed it was a problem on our side. So the 'preferprepared=true' option did work for smallint ranges only. LongWord or UInt64 (Byte-range > 2) should be broken on this RS too.

The BIT(X) fields simply did not retrieve the Zeos expected data. So i added a huge test to our regression tests and fixed the problem on 7.2.

Now both kind of statements should work as expected!
Patch done R3485 \testing-7.2 (SVN)

[chrisv5]

Glad to find this post, I just wanted to report the same bug. This means, I either have to add the connection property or replace 7.1.4 stable with 7.2 beta?

[EgonHugeist]

@chrisv5

both 7.1.4 and the packaged 7.2-beta do have same issue. You should use the SVN version in \testing-7.2 branch of:
http://sourceforge.net/p/zeoslib/code-0/HEAD/tree/

Or in both packages use my suggested Parameter. Note i didn't test it by my selves. It's just reported by beckert2014

[chrisv5]

Hi Michale,

thank you for the quick reply. I'll test myself as soon as I get around and will post the result here. By the way, i found another thread which has been locked a long time ago. It is related to this bug, interestingly:

http://zeoslib.sourceforge.net/viewtopi ... 9&start=45

Funny enough, 7.1.4. can write BIT columns but fails to read them correctly (always return 0). What happened now, is this:

- actual value = 1
- Zeos reads 0
- program tries to update to 1
- exception thrown because nothing got updated
- programmer furious!

As you can see, throwing an exception in this case is a bad thing. I give you a another, real life example:

- program reads row without transaction (not really unusual in MySQL as you should be aware, as the popular storage engine does not even support transactions)
- column has value X
- program decides that value Y would be better
- another process, user, whatever, gets the same idea, but is faster: updates to X -> Y
- now program intends to update the column and... exception!

I understand the intention of this check. If one is not using a primary key and a very selective query (only a few columns) with wmWhereAll, it might be possible to update hundreds of columns instead of one. That would indeed be a real bummer. The intention of this warning is implied by the wording of the message "only one...". However, it does not say "at least one and only one...".

If I remember the source code correctly, there is a check if "columns updated" <> "columns intended to update". Wouldn't it better to code it if "columns updated" > "columns intended to update"? Or accept the value 0 explicitly?

By the way, I m not aware of other DBMS to report "zero rows updated" in that case. At least not so in MS-SQL and DB/2. IMHO this is an idiosyncrasy of MySQL. The RDMS should report how many rows *qualified* for an update, not if anything has really changed.

Let me repeat, this check fails, if:

- there is another bug in ZEOS which reports a wrong value for a column (this thread)
- the stored value in the Dataset is out-of-date due to lack of transaction control.

Sorry for hijacking this thread, but as I said in the beginning, this bug bit me at the same time as the original bug.

Oh, by the way. I also had a bad time installing ZeosLib 7.1.4 on Delphi 7, namely ZComponents70.bpl being created in the wrong folder (in the ZeosLib build folder instead of the Delphi folder, like all other packages). Has this bug been fixed?

Sorry for ranting, but had a real bad day with ZeosLib yesterday: first, have to find out that BIT does not work at all in 6.6.x. After that the install issues, but at least now I could write the BIT column. Finally, the read bug and the follow-up exception!

Unfortunately, the database is not designed by me, I only have to read/update the rows from my app. A five minute job turned into an endless hair raising nightmare!

Was just going to report the various bugs when I found this thread! Heck, I was even going to fix it myself :-)

Cheers,
Christian

[chrisv5]

Unfortunately, this still does not work. Still reports BIT(1) = 0 to me, even if it is actually 1

MySQL server 5.0.85

[chrisv5]

Well, i have to revert my statement. It *does* work. however, only the testing branch, not the trunk where you had me pointed at!
Works like a charm, by the way!

[EgonHugeist]

Well, i have to revert my statement. It *does* work. however, only the testing branch, not the trunk where you had me pointed at!
Works like a charm, by the way!

Where did i point you to trunk? All i'd say the fix is available on \testing-7.2.

However thanks for confirmation. Next Question: The package issue is resolved too?

- there is another bug in ZEOS which reports a wrong value for a column (this thread)
- the stored value in the Dataset is out-of-date due to lack of transaction control.

Sorry for hijacking this thread, but as I said in the beginning, this bug bit me at the same time as the original bug.

Nope that isn't true. It's the way you use it.
Note:
You can use the TZSQLUpdate-Component, write your own Statements INCLUDING a RefreshRow-Stmt. Transaction control is your turn. You can use AutoCommit mode or simply forgett it and start,commit and rollback the transaction by your selves.

[chrisv5]

Where did i point you to trunk? All i'd say the fix is available on \testing-7.2.

Well, just scroll back to your own statement and check the link you have provided...

However thanks for confirmation. Next Question: The package issue is resolved too?

Indeed, it is. Both in trunk and testing!

Nope that isn't true. It's the way you use it.

Please be fair, do not blame me for bugs in ZeosLib! It is not "how I use it", but the fact that ZeosLib reports (reported in testing branch) wrong values which lead to the other "bug"!

You can use the TZSQLUpdate-Component, write your own Statements INCLUDING a RefreshRow-Stmt.

Way too much hassle! I prefer to work on the application instead of working around bad design decisions! Actually, I can change that single line myself. I did that before I tried the testing trunk. Works like a charm! Instead of <> 1 I am using > 1. Voila!

Transaction control is your turn. You can use AutoCommit mode or simply forgett it and start,commit and rollback the transaction by your selves.

Please read my post before making such statements. The MyISAM storage engine does not even support transactions! It is not my DB I have to work with, but that of a third party application.

Anyway, thanks for the bug fix with regard to BIT(1). If I am not mistaken, this part of the code did the trick:

Code: Select all

-    Result := RawToIntDef(Buffer, 0);
+    if FMySQLTypes[ColumnIndex {$IFNDEF GENERIC_INDEX}-1{$ENDIF}] = FIELD_TYPE_BIT then
+      case FLengthArray[ColumnIndex{$IFNDEF GENERIC_INDEX}-1{$ENDIF}] of
+        1: Result := PByte(Buffer)^;

Disclaimer: I have only a single bit column, thus this is all it needed.

[EgonHugeist]

@chrisv5

Note englih isn't my native language. I'm a germin who just want to help on developing Zeos. So i'm sorry if i missunderstand your posts or did express me unclear...

@chrisv5

both 7.1.4 and the packaged 7.2-beta do have same issue. You should use the SVN version in \testing-7.2 branch of:
http://sourceforge.net/p/zeoslib/code-0/HEAD/tree/

Note: this is the head URL. As i've been trying to say use the \testing-7.2 branch.

Finally: Are there more issues or are you happy now?

[chrisv5]

You are right, my fault. I see that you have meanwhile moved the code from testing to trunk, great!

I am happy with the solution for BIT columns.

Still unhappy about the update issue. Let my try it this way:

The check is intended to catch problems with the WHERE clause. Instead, it caters to a peculiarity of MySQL (claiming no suitable records exist for updating just because no value changed).

Just my two cents. I see that I am stuck with modifying this line myself with every release (or trunk, in case I need that again)

Christian