Skip to main content
Question

SQL Server BIT datatype not supported when writing to MySQL

  • March 20, 2017
  • 5 replies
  • 327 views
P
Forum|alt.badge.img

I'd like to report what I suspect might be a bug with the MySQL data writer. Applies to FME 2015 Workbench.

I have a simple workspace consisting of a SQL Server data reader and a MySQL data writer. It reads a MS SQL table with some fields being a BIT data type. This then gets pushed out to a MySQL database (using a drop command first). I observed some problems with this, specifically to do with the BIT datatype.

 

(1) - The MySQL data writer dialog lacks support for BIT type. The nearest type FME can use is TINYINT. See screenshot below. Even if I choose Manual attribute definition, there is no "BIT" datatype in the dropdown list.

(2) - You would assume that there shouldn't be any difficulty writing a Bit (1 or 0) to a Tinyint. However FME reports errors during the write process. Even trying various different types: int, smallint, varchar produced same results.

Insert failed on table 'lynx_animals'. Error was 'Incorrect integer value: 'Yes' for column 'Sterilised' at row 1'

 

(3) - FME falsely reports that the write process was successful, even though no records were written (all records failed).

Am I right in assuming this to be a bug? What would be a work-around?

This post is closed to further activity.
It may be an old question, an answered question, an implemented idea, or a notification-only post.
Please check post dates before relying on any information in a question or answer.
For follow-up or related questions, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

5 replies

nic_ran
Contributor
Forum|alt.badge.img+16
  • nic_ranContributor
  • Contributor
  • March 20, 2017

Check the value that is coming out of the FME SQL Server reader. I've run into this before... FME might be interpreting the 0/1 values as no/yes, which can't be written to MySQL tinyint. You might need to convert that values in the workspace...

P
Forum|alt.badge.img
  • peterz
  • Author
  • March 21, 2017

Thanks Nic! If I inspect the data from the reader, the 1's and 0's from SQL Server are indeed being implicitly converted to a text string "Yes" and "No" by FME internally.

Here's the results from the inspector:

And here's the raw data in SQL Server:

Should this be even happening? FME is doing its own casting of a BIT value, which in my particular case I don't want as it undermines the output/writing stage which still requires 1's and 0's. I can try converting them back, but it will create unnecessary work if there's dozens of columns in many other tables.

redgeographics
Celebrity
Forum|alt.badge.img+62

Looks like FME is interpreting the SQL Server "bit" datatype as a boolean and a quick glance at the MySQL datatypes doesn't seem to show anything that's compatible with that, so a tinyint would be an acceptable substitution I suppose (perhaps a char or an enum).

That does mean you'll have to convert the value yourself, an AttributeManager with a conditional value would do the trick.

FME rocks! \m/
P
Forum|alt.badge.img
  • peterz
  • Author
  • March 21, 2017

Looks like FME is interpreting the SQL Server "bit" datatype as a boolean and a quick glance at the MySQL datatypes doesn't seem to show anything that's compatible with that, so a tinyint would be an acceptable substitution I suppose (perhaps a char or an enum).

That does mean you'll have to convert the value yourself, an AttributeManager with a conditional value would do the trick.

Thanks redgeographics. I still think that a Boolean should be handled as a 1 or 0 internally by FME, not be converted to a text string as that is purely an abstraction for the benefit of the user interface.

 

 

Unfortunately there's no AttributeManager in FME 2015, so I ended up using two StringReplacers for the Yes and No conversion. The advantage here is you can specify multiple columns to be converted in one step.

 

 

P
Forum|alt.badge.img
  • peterz
  • Author
  • March 21, 2017

Looks like FME is interpreting the SQL Server "bit" datatype as a boolean and a quick glance at the MySQL datatypes doesn't seem to show anything that's compatible with that, so a tinyint would be an acceptable substitution I suppose (perhaps a char or an enum).

That does mean you'll have to convert the value yourself, an AttributeManager with a conditional value would do the trick.

I forgot to mention, the MySQL database does have a BIT data type, it's just that the FME MySQL Writer doesn't have it, so it picks TINYINT as the nearest thing.

 

 

Community Stats

32,207
Posts
122,303
Replies
39,891
Members

Latest FME

Terms & ConditionsCookie settingsAccessibility statement
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.