Skip to main content
  • EN

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?

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...

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.

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.

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.

 

 

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.

 

 

Reply


Community Stats

30,095
Posts
114,254
Replies
37,725
Members
Terms & ConditionsCookie settings
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.