Having Trouble Inserting into a Bit Data Type MySQL Database Column

[evaneer]

Hello,

I am trying to insert data into a simple 3 column MySQL database table.

No matter what data type I send into the PASS_FAIL column I get a "DATA TOO LONG" error. I have tried Booleans, converting the Boolean to 0,1 8 bit integer, and using "0" or "1" strings. What do I need to convert the data to?

 Specific Error Code: -2147467259

Specific Error Message: NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Insert Data.vi->Insert step_result.vi<ERR>ADO Error: 0x80004005
Exception occured in Microsoft OLE DB Provider for ODBC Drivers: [MySQL][ODBC 5.3(a) Driver][mysqld-5.1.47-community]Data too long for column 'PASS_FAIL' at row 1 in NI_Database_API.lvlib:Rec Create - Command.vi->NI_Database_API.lvlib:Cmd Execute.vi->NI_Database_API.lvlib:DB Tools Insert Data.vi->Insert step_result.vi

Thanks for the help!

[aputman]

Did you check the MySQL website to learn about bit values?

I think what you want to use is a TinyInt(1) or boolean datatype, which are basically the same thing.

[evaneer]

Thanks for the feedback. I think what you are suggesting is correct (that LabVIEW is not converting to the correct data type) because as seen in the pictures I have already tried sending s Boolean and “TinyInt” data type to the Insert Data cluster without success. Unfortunately this still doesn’t answer the question because I want to know how to use LabVIEW’s Insert Data.vi to write to a Bit field. Or is this just impossible and I would have to use the Execute Query.vi and type the insert syntax myself?

[aputman]

I am not suggesting that LabVIEW is converting to the wrong datatype.  I'm saying that I would suggest for you to change your database to use a tinyInt(1) or boolean datatype. If you want to continue with the bit field, I'm not sure how you would convert a value of 1 to a bit that MySQL would interpret as a bit.  It may be as simple as using the b'Value' notation as is shown in the link I posted above.   

[aputman]

It turns out to be a pretty simple solution if you want to use a bit datatype.  The boolean to (0,1) function outputs a 16bit integer.  Add a conversion to take it down to 8bits and the data will be inserted.  At least that is the case for MSSQL.  

[evaneer]

Nice you got me thinking down the right track! When I converted to an 8 bit integer MySQL still refused it. This is because I dug deep into the Insert Data.vi in LabVIEW's database toolkit to where it does its typing conversions. Unfortunately, LabVIEW converts all I8 - I32 and U8 - U32 all to I32 datatypes before writing them to databases. This is what was causing my issue. I found a special case where it converts certain datatypes to Binary data. One example was an extended precision float. After seeing this, I swapped the U8 VI for an EXT VI and it worked!

Data Type Conversion Deep Inside Insert Data VI in LabVIEW Database Toolkit (EXT case that turns to Biinary data)Using EXT so that the Data Type Conversion Binary Case is CalledProof that it wrote to the database

[evaneer · Accepted solution]

Wow turns out even that didn't work for TRUE data. Converting to Extended only worked for FALSE data. Had to create a specially configured "To Fixed Point" that is set to length 1 to convert both TRUE and FALSE data. Now finally can write both 0 and 1 to the database.

[aputman · Accepted solution]

---

@evaneer wrote:

Wow turns out even that didn't work for TRUE data. Converting to Extended only worked for FALSE data. Had to create a specially configured "To Fixed Point" that is set to length 1 to convert both TRUE and FALSE data. Now finally can write both 0 and 1 to the database.

---

And remind me why this makes more sense than changing the dB datatype to tinyint(1) or boolean?  

[evaneer]

It is, unfortunately, a database that has a LOT of dependent code (excess of 15+ stations write to it and that is just in one country). We normally write to it using TestStand so the database inserts are very straightforward. I was not about to risk impacting all of our existing stations just because of the conveniency. Otherwise trust me, I would have changed data types  😛

[aputman]

This should do it for you.  

ALTER TABLE step_passfail MODIFY COLUMN pass_fail tinyInt(1)

Live dangerously and do the right thing, not necessarily for you but for those programmers that will have to later interpret what you were trying to do here.