Is it possible to dynamically type the Database Toolkit column data?

[bluetimeCharlie]

Hi, I am trying to figure out a way to dynamically type the output of Database Toolkit's recordsets. I was surprised to see Labview returns the DB data as variants that need external typing information to be typed correctly (through the Database Variant To Data VI). I'd like to avoid hard-coding the types into the program for easier maintenance.

I figured out I can get an enum with the column type name from the DB Tools List Columns VI, but that doesn't seem to get me anywhere as LV doesn't give me the ability to convert the data value into a type information at runtime. I played around with malleable VIs, but those allow typing to change only at compile time. The deeper I get into the topic, the more it appears there's just no way to achieve this at runtime in LV.

Is that the only way? To hard-code the types based on the DB architecture?

[tst]

The question is what would you do with it? At some point in your code you do need strict types if you want to actually use the data, so what's your use case of having it dynamic at run time?

With a malleable VI you probably could write code which will use the type you input (probably a 1D array of clusters) to assume what the output is. It would still require some code which will parse and handle what's inside the cluster (somewhat similar to how the DB insert VI works) and as you said, would only work at edit time, but why isn't that enough?

If you do want something that's dynamic at run time, then I expect it would need to be something that operates by named references and then it doesn't need to be a compound strict type, but rather operate by columns (e.g. you say give me the int value at column X). That should be doable.

[bluetimeCharlie]

From my limited experience with database drivers outside labview, the data is usually querried and returned already typed in that language's own types, i.e. varchars become strings, integers stay integers, floats become doubles etc. The idea is I'd like to avoid creating a dedicated code for every recordset I need from the database. Instead, I want to have some universal code that querries arbitrary table/view and returns the data in LV's strict types. So basically I'd like for this particular VI to operate without the knowledge of what columns it's gonna be returning and yet return the data typed. That's why edit/compile time is no good for me - the data will be coming in at runtime.

Since the returned data is always variant, I as a developer have to step in and type it before I can work with it, even though the database knows what type it is, the Labview can see what type it is, and the implicit conversion is fairly obvious, if I say so myself.

I'll look into the named references, but I'm starting to feel like this is gonna be too much work to warrant me spending more time on this instead of actually hardcoding the types...

Thanks for now!

[tst · Accepted solution]

LabVIEW is strictly typed. Every wire has a known type at edit time. There is some wiggle room with this (variants, classes, etc.), but the basic idea even with those is the same - if you want to place an unbundle node in your code, it needs to have a known cluster structure at edit time.

While I have no experience with working with DBs in other languages, I would assume you have something like ORM (like the entity framework for .NET) or that you're in a language with dynamic typing. For the first, my understanding is that it does require the classes to match in structure to the DB. For the second, I expect it's similar to what I suggested of working by name. In either case, at some point in your LV code, you need to say "I expect this wire to be an integer" if you want to actually use it and that choice needs to be static.

[Defaphe]

Hello,

I suspect this answer will not be what you expect but as I'm using DB toolkit daily I will give my 2cents.

I always convert the Variant output as a 2D string array that will be either stocked in a shift register or in a class. In that way I know what is the datatype that is going throught the application as it is strictly always the same type.

Then when I need a data (one element,1D array, 2D array) I do the conversion right where it is needed as I know what to do with it.

The datatype is something you are rarely changing once the database architecture is defined, then no big need to update the conversions that are done at the end of the data road. What can change is the query and the output and this is something I do not need to handle the change in every subVI.

[JimB.]

---

@bluetimeCharlie wrote:

From my limited experience with database drivers outside labview, the data is usually querried and returned already typed in that language's own types, i.e. varchars become strings, integers stay integers, floats become doubles etc. The idea is I'd like to avoid creating a dedicated code for every recordset I need from the database. Instead, I want to have some universal code that querries arbitrary table/view and returns the data in LV's strict types. So basically I'd like for this particular VI to operate without the knowledge of what columns it's gonna be returning and yet return the data typed. That's why edit/compile time is no good for me - the data will be coming in at runtime.

Since the returned data is always variant, I as a developer have to step in and type it before I can work with it, even though the database knows what type it is, the Labview can see what type it is, and the implicit conversion is fairly obvious, if I say so myself.

I'll look into the named references, but I'm starting to feel like this is gonna be too much work to warrant me spending more time on this instead of actually hardcoding the types...

Thanks for now!

---

I still don't understand. If this conversion happened at runtime, you still wouldn't be able to do anything with it. LabVIEW is strictly typed, so if you don't know the type at edit time then what wire could you possibly put the data on? What could you do with it?

Knowing the type information (and db structure) at edit/compile time seems pretty essential to me.

Perhaps you can give an example of what you are talking about in other languages? My experience is mostly limited to C#, albeit a couple different APIs, and I haven't seen a case where the type of the data returned by a query isn't required to be known at compile time for things to work properly.

[bluetimeCharlie]

I have interacted with a DB in Python (which I realize is a bad example here) and C#, though only in a very limited way, as I mentioned. LV is really my first serious dive into database management and interaction with from the outside.

I am part of a team building a LV application and I am responsible for the database module, i.e. interacting with the DB and sending/writing data to/from other modules as they ask for it. My gut instinct was to create a centralized VI for get-type operations and then just supply what and from where is needed at runtime, a very much Python-like thinking, I'm afraid. I think I can still do something like that with a malleable VI, albeit at compile time, which now makes more sense to me. The goal was essentially to save on work and to learn more about how LV deals with a DB, at which I succeeded at least 50%, I'd say 😀

I think, as you've all have been suggesting, that I'm approaching the issue from the wrong angle. I have interpreted Python's weak typing as somehow the data from the DB arriving already typed (which was enforced by the fact I can extract column information, including its type, in LV too, if only as an enum). It just felt too unwieldy having to reflect the DB structure on the app side.

I believe @tst's reply should technically be marked as the answer, since it basically explained I was at the wrong. The string approach of @Defaphe seems also solid, as the modules asking for the data would know best what to expect anyway.

Thanks for the replies everyone!

[tst]

It should probably be pointed out that for various reasons it's usually recommended to work with DBs using a strict API (usually stored procedures), rather than operating directly on the tables. This doesn't actually solve your issue (the stored procedures still need specific params and still return recordsets/table), but it's just a general point.

As for converting to strings, it's essentially the same as passing the variants around, just with another conversion step in the middle (variants to strings). My own preference is to convert data to strict types as early as possible, but I understand how you wouldn't want to do that, especially coming from Python. I could see cases where working by name might make sense, but you are fighting the language to a degree. In any case, it opens you up to runtime errors at any point where you do the conversion.

[Yamaeda]

Either convert the variant answer to an array of strings (Variant to data), or make a matching type def of the table and convert directly to such a cluster (my preferred way). 

Technically you can make all of your code with Variants and Dynamic data, but that is setting you up for much more pain down the line.

I don't know enough Python to come up with an analogy, but it's similar to create a loop counter of billions instead of having it dynamically change each time.