Wrong field type boolean/smallint for MySQL

The BETA tester's forum for ZeosLib 7.2 Report problems. Ask for help, post proposals for the new version and Zeoslib 7.2 features here. This is a forum that will be edited once the 7.2.x version goes into RC/stable!!

My personal intention for 7.2 is to speed up the internals as optimal a possible for all IDE's. Hope you can help?! Have fun with testing 7.2
hemmingway
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 20.01.2010, 13:51

Wrong field type boolean/smallint for MySQL

Postby hemmingway » 27.03.2016, 16:04

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
You do not have the required permissions to view the files attached to this post.

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Re: Wrong field type boolean/smallint for MySQL

Postby marsupilami » 28.03.2016, 09:54

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
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Re: Wrong field type boolean/smallint for MySQL

Postby marsupilami » 28.03.2016, 10:44

Hello Bernhard,

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

Jan
You do not have the required permissions to view the files attached to this post.

hemmingway
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 20.01.2010, 13:51

Re: Wrong field type boolean/smallint for MySQL

Postby hemmingway » 29.03.2016, 18:49

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
You do not have the required permissions to view the files attached to this post.

User avatar
EgonHugeist
Zeos Dev Team
Zeos Dev Team
Posts: 1701
Joined: 31.03.2011, 22:38

Re: Wrong field type boolean/smallint for MySQL

Postby EgonHugeist » 30.03.2016, 04:13

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')
King regards, Michael

You want to help? viewtopic.php?f=4&t=3671
You found a (possible) bug? Use the new bugtracker dude! http://sourceforge.net/p/zeoslib/tickets/

Image

marsupilami
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Re: Wrong field type boolean/smallint for MySQL

Postby marsupilami » 30.03.2016, 12:09

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
Gold Boarder
Gold Boarder
Posts: 591
Joined: 17.01.2011, 14:17

Re: Wrong field type boolean/smallint for MySQL

Postby marsupilami » 30.03.2016, 14:00

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
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 20.01.2010, 13:51

Re: Wrong field type boolean/smallint for MySQL

Postby hemmingway » 31.03.2016, 09:01

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
Last edited by hemmingway on 02.04.2016, 06:54, edited 1 time in total.

hemmingway
Fresh Boarder
Fresh Boarder
Posts: 19
Joined: 20.01.2010, 13:51

Re: Wrong field type boolean/smallint for MySQL

Postby hemmingway » 02.04.2016, 06:22

Hello,

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

Greetings, Bernhard
You do not have the required permissions to view the files attached to this post.


Return to “ZeosLib 7.2 Betatest Forum”

Who is online

Users browsing this forum: No registered users and 1 guest