database toolkit: casting to int fails

[aartjan]

The Labview database toolkit returns mySQL enum columns as strings. I have posted about this on the idea exchange before. The cast to cluster with a Labview enum will fail. Now I had the idea to cast the enum to an int in SQL. However, all my casting efforts seem to result in string.

This SQL still yields a string datatype:

The database OLE variant type returned is "VT_DECIMAL", which the database toolkit doesn't seem to support.

Error: 0x000001F4 - Invalid type. This data type is not directly supported by the database toolset and will be considered a String.

I have tried casts to all kinds of integer, but I never get the desired OLE variant "VT_I4" (int) or "VT_I2" (smallint). That is the type that is converted to a Labview I32

This SQL is a sneaky workarond:

The database variant type returned is "Variant Type -> VT_R8", which converts to a Labview double datatype.

[aartjan]

I did some additional research. Apparently MySQL returns integer data as SQL_NUMERIC, and the ODBC driver makes it into VT_Decimal, whcih supposedly is not recognised by the database connectivity toolkit.

I have tried two different ODBC drivers:

MYSQL ODBC 9.1 ANSI driver

MariaDB ODBC 3.2 Driver

Both  yield the same results.

It even returns VT_DECIMAL on this:

I also figured out that any cast (SIGNED, UNSIGNED INT) in MySQL makes a BIGINT column. BIGINT is 8 bytes, whereas labview I32 is 4 bytes.

I guess it is time that the database connectivity toolkit get support for I64, rather than I32

[Bob_Schor]

LabVIEW Enums are wonderful (when used in LabVIEW) -- they enable you to assign "User-Friendly Names" to distinct "conditions" that could otherwise be "compactly" rendered as sequential unsigned integers, such as [red, orange, yellow, green, blue, indigo, violet] being "internally represented" as [0, 1, 2, 3, 4, 5, 6] and have LabVIEW treat them as though they were integers (for example, the successor of "red" is "orange", and the predecessor is "violet" (since Enum "arithmetic" does the "sensible thing" of "joining the ends" together to make a circle).

But mySQL doesn't know about this "feature", so you need to humor it.  When you write your Enum to mySQL, first convert it to a String using "Format Into String".  When you read the String representation of the Enum back into LabVIEW, do the opposite conversion using Scan from String.  Here's an example (my Enum has three elements defining how to initialize an analysis routine) -- I'm pasting it as a Snippet so you should be able to run it, but if not, copy the code and put your own Enum in on the left and put an Indicator based on your Enum on the right.

Oops -- the Snippet didn't work (probably because the Enum TypeDef wasn't included).  I'm going to just attach a regular .png, as the routine is so simple you should have no trouble duplicating its functionality.  You only need an Enum saved as a TypeDef, which is what the input (labeled "Fix Initial") is, and what the output will be cast as.  Just a second ...

See if that works ...

Bob Schor

[aartjan]

Hi Bob,

Thanks for taking the time to reply. I fear I did a poor job getting my point across. What I am trying to do is casting directly from the OLE variant to array of cluster, using the "database variant to data" function.

This function does a terrific (and fast!) job at turning database queries directly into regular labview clusters. Like this:

Until you use an enum in MySQL (yes, they do exist, albeit 1-based rather than 0-based) and have an enum in your cluster to represent it.

Although enums in MySQL are also numerics, the database connectivity toolkit will only return them as strings. Hence my idea to cast them to an integer in the SQL query first. This is where I stumbled on the subject of this thread. MySQL gladly casts the enum into an integer. But the database connectivity toolkit appears so aged, that it cannot recognise a bigint and casts it to string instead of I64 or even I32. 

Having said all that: The "database variant to data" function WILL correctly cast the numeric strings to LV enum. But it will only work with "DB tools execute query". It will not work with the "DB tools SELECT" function (since you can't cast the column like in regular SQL).

Also I have gotten into the habit of converting data to variant of array-of-cluster based on the returned datatypes. As soon as the enum VT_DECIMAL is casted to a LV string, the "variant to data" function will return an error when trying to convert it to an array of cluster.