SQL TinyInt field seen as Bit

bgorsline · 2017-03-15 13:51:23

Our AWS Aurora (MySQL) DB has a column with a datatype of 'tinyint'. This is imported into the ReportServer Enterprise demo (v3.0.2-6) as a field of type 'bit'.

Is there a way to override how the system interprets this field type? Would prefer it treat tinyint as integer. If there's no workaround, we'd try changing the datatype in the source schema to integer.

thanks in advance

eduardo · 2017-03-21 10:32:22

Hi bgorsline,

we will check if this is the case. A column of type "tinyint" should not be imported as a "bit", this seems incorrect. I will update when I have more information.

Cheers,
Eduardo

eduardo · 2017-03-21 13:19:36

Hi bgorsline,

this is not happening in my testcases: a TINYINT is being imported as a TINYINT. Could you please share more information? Your DB version? Some screenshots? Maybe a reachable test db, so I can directly take a look?

Could you test with a simple table and mysql instance?

Cheers,
Eduardo

bgorsline · 2017-03-21 14:41:52

Eduardo - found something interesting. We're using AWS Aurora version 1.8.1. When the tinyint field is length 3, it's translated as tinyint to ReportServer. When the tinyint field length is 1, it's seen as 'bit'. The field in question stores "hour of the day", which of course fits in 1 byte.

Our workaround will be to increase the field length.

eduardo · 2017-03-21 16:47:15

Hi bgorsline,

yes, this seems to be a bug. TINYINT(1) is in fact mapped to a BIT, which is incorrect. I will keep you informed here.

Cheers,
Eduardo

eduardo · 2017-03-22 08:51:18

Hi bgorsline,

please take a look here: https://dev.mysql.com/doc/refman/5.7/en … rview.html
"BOOL, BOOLEAN: These types are synonyms for TINYINT(1)."

So according MySQL a TINYINT(1) *is* a boolean, and a boolean is a BIT in reportserver. So this is the correct behavior.

If you want to change this behavior, you can modify the jdbc URL by setting tinyInt1isBit=false:

jdbc:mysql://localhost:3306/DBNAME?tinyInt1isBit=false

With this setting, TINYINT(1) will be treated as TINYINT in reportserver. However, be careful with this setting: if your database maps booleans as TINYINT(1) (which is a common mapping) you might get problems with these, since they will be shown in reportserver as TINYINTs instead of BITs.

Cheers,
Eduardo

bgorsline · 2017-03-22 14:15:56

Good stuff - thanks for the thorough followup Eduardo.

eduardo · 2017-03-22 16:13:10

bgorsline wrote:

When the tinyint field length is 1, it's seen as 'bit'. The field in question stores "hour of the day", which of course fits in 1 byte.
Our workaround will be to increase the field length.

Note that TINYINT has always the same length in MySQL. The (m) just indicates the column display width: http://stackoverflow.com/questions/1283 … difference

Cheers,
Eduardo

ReportServer Forum

#1 2017-03-15 13:51:23

SQL TinyInt field seen as Bit

#2 2017-03-21 10:32:22

Re: SQL TinyInt field seen as Bit

#3 2017-03-21 13:19:36

Re: SQL TinyInt field seen as Bit

#4 2017-03-21 14:41:52

Re: SQL TinyInt field seen as Bit

#5 2017-03-21 16:47:15

Re: SQL TinyInt field seen as Bit

#6 2017-03-22 08:51:18

Re: SQL TinyInt field seen as Bit

#7 2017-03-22 14:15:56

Re: SQL TinyInt field seen as Bit

#8 2017-03-22 16:13:10

Re: SQL TinyInt field seen as Bit

Board footer