01-23-2019 11:25 AM
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!
Solved! Go to Solution.
01-23-2019 01:57 PM
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.
01-23-2019 02:24 PM
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?
01-23-2019 02:38 PM
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.
01-23-2019 03:21 PM
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.
01-23-2019 07:58 PM
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!
01-23-2019 09:04 PM
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.
01-23-2019 09:14 PM
@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?
01-23-2019 09:18 PM
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 😛
01-23-2019 09:31 PM
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.