Zeoslib Portal

I got error messages wrong field type boolean/smallint for MySQL/Delphi XE6 . I found no sources for getting the field type stBoolean.
I attached a patch for this issue. Can somebody test and submit it to the 7.2 branch?

Greeetings, Bernhard

Hello Bernhard,

I had a look into the patch. I fully agree with the modifications to ConvertMySQLHandleToSQLType in ZDbcMySqlUtils.pas. But from my POV the modifications to TZAbstractResultSetMetadata.ReadColumnByName in ZDbcResultSetMetadata.pas are not good because they only work to hide that TZMySQLDatabaseMetadata.UncachedGetColumns in ZDbcMySqlMetadata.pas doesn't give correct results. - At least if I did a good research in the code
So TZMySQLDatabaseMetadata.UncachedGetColumns should be modified to give correct results because that could also improve MetaData results if somebody uses the TZSQLMetadata component.

With best regrads,

Jan

Hello Bernhard,

please find attached my propsed patch. What do you think?
With best regards,

Jan

Hello Jan,

Your patch looks fine.
But I found an other issue. The value for a boolean field can not stored as 'Y' or 'N' because the data type is TINY_INT.
Please look at my new patch.

Greetings, Bernhard

Hi Jan & Bernhard,

Assume using the TINYINT(1) as Boolean type simply is wrong! So i disagree continuinng any affort managing to map the tinyint to stBoolean.
Reasons: TINYINT(1) is still a 1Byte value and can hold up 0..255(unsigned) or -128..127. the (1) just indicates the display-width of the field.

Zeos by default just allows a ENUM('Y','N') or BIT(1) as Boolean field. All other MySQL-Types aren't made for this approach. If we would apply one of your patches we'll get the next ticket for users who simply use this type for ordinals f.e..

supported examples: prefered is ENUM('Y','N')

Hello Michael,

if you create a table like this in MySQL:
create table test (
testint integer not null,
testchar varchar(50),
testbool boolean,
constraint pk_test primary key (id)
)
The boolean lart gets translated to tiny(1). I didn't test this and I know that MySQL is special in many ways but I wouldn't expext it to convert boolean to tiny(1) if it allows to store more than 1 and 0? I will have to test that when I am back at the office...
Best regards,

Jan

Hello Michael, hello Bernhard

Michael is right - MySQL converts boolean to tinyint(1) and allows to store arbitrary numbers (0, 1, 2, ...). In my opinion this is a bug, but who cares.
So the right way to store boolean values in MySQL would be bit(1) columns and this patch becomes useless.

With best regards,

Jan

Hello,

I have an old MySQL database. The missing boolean field type ist implemented as TinyInt(1).
For newer MySQL Versions is the field type bit available.

So I agree to
Assume using the TINYINT(1) as Boolean type simply is wrong!

Greetings, Bernhard

Hello,

The ENUM solution works, but BIT(1) don't. See my new patch for BIT(1).

Greetings, Bernhard