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

bgorsline
Member
Registered: 2017-03-15

SQL TinyInt field seen as Bit

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

Offline

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

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL TinyInt field seen as Bit

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

Offline

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

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL TinyInt field seen as Bit

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

Offline

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

bgorsline
Member
Registered: 2017-03-15

Re: SQL TinyInt field seen as Bit

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.

Offline

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

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL TinyInt field seen as Bit

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

Offline

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

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL TinyInt field seen as Bit

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

Offline

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

bgorsline
Member
Registered: 2017-03-15

Re: SQL TinyInt field seen as Bit

Good stuff - thanks for the thorough followup Eduardo.

Offline

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

eduardo
Administrator
Registered: 2016-11-01
Website

Re: SQL TinyInt field seen as Bit

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

Offline

Board footer

Powered by FluxBB