Database Variant to Data behavior with boolean input

[ssegal]

I'm writing a simple tool to pull data out of a SQL database for offline analysis. The data all lives in a single table. The columns of the table have varying datatype: varchar, real, bit, etc. I've used the LabVIEW database toolkit to write up a routine to grab the data. I've tried two different methods with Database Variant to Data to get the data into usable form.

Version 1: Create a cluster. The elements of the cluster match the datatypes of the columns of the data, in order. Place the cluster constant in a 1D array constant. Wire that to Database Variant to Data. Result: Success! All data is pulled into LabVIEW as expected.

Version 2: Create a 2D array of strings. Wire that to Database Variant to Data. I expect LabVIEW to convert all of the database data into "equivalent" strings. Result: Partial success. Everything except for SQL bit data converts as expected. SQL bits, however, are converted such that SQL bit 0--> string "0" and SQL bit 1--> string "-1".

That's annoying. Why does LabVIEW put the minus sign in front of the "1" bits? I can write some extra code to reformat my string array but I'd rather not. Anyone know why Database Variant to Data treats SQL bits this way?

Snippet attached.

I'm using LabVIEW 2018 SP1, f4 patch.

[aputman]

Can you use a TINYINT datatype as opposed to a BIT datatype, especially if you are only storing a 1 or a 0? 

[ssegal]

Sure, I could. But that means modifying years of existing data in a database to bypass what seems like a bug in LabVIEW. I don't much like that as a solution.

[aputman]

Are you using OLEDB provider to access the SQL data?  If so, OLEDB supposedly reports a TRUE value as -1.  I haven't tried myself but this doesn't look like a LabVIEW bug to me.  You can try using a different provider and see if you get the desired results.

[ssegal]

Huh, that's interesting. We use ODBC, so I think we shouldn't be affected by that bug, but it's still worth looking into. Do you recall where you saw that? My quick searching didn't turn anything obvious up.

[aputman]

Google "OLEDB true value".

[aputman]

You could change your query to cast the bit value to an ABS(int), which wouldn't require any post-processing in LabVIEW.  

[aputman]

Here is a reference on MSDN that says this....not sure what all it applies to.

https://docs.microsoft.com/en-us/dotnet/api/system.boolean?view=netframework-4.8

[ssegal]

That's the documentation for .NET. I don't think it applies to SQL.

I've already written code to get rid of the minus signs. Tedious and annoying, but my code does what it needs to do now.

I've submitted a service request to NI. I guess they'll investigate and let me know if it's really a LabVIEW bug or caused by something else.