Wrong field type boolean/smallint for MySQL

[hemmingway]

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

[marsupilami]

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

[marsupilami]

Hello Bernhard,

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

Jan

[hemmingway]

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

[EgonHugeist]

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:

Code: Select all

CREATE TABLE TEST_MYSQL_BOOLEANS
(
  id INT NOT NULL,
  fld1 ENUM('Y','N'),
  fld2 enum('n','y'),
  fld3 BIT(1),
  fld4 ENUM('Y','N') default 'Y',
  fld5 enum('n','y') default 'n',
  fld6 BIT(1) default 0,
  fld7 ENUM('Y','N') default 'N',
  fld8 enum('n','y') default 'Y',
  fld9 BIT(1) default b'1',
  PRIMARY KEY (id)
);

prefered is ENUM('Y','N')

[marsupilami]

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

[marsupilami]

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

[hemmingway]

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

[hemmingway]

Hello,

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

Greetings, Bernhard