LabVIEW
I wonder if you can create SQL statements dynamically?
Solved!
Hello,
I'm using SQLlite from the SQLite Library produced by JD Powell and I'm wondering if its possible to somehow dynamically form the binding of parameters when you do an INSERT to save data.
Here's a screenshot of what I have to do now. For each column in SQL command, you have to bind the source variable to the correct order and type with the SQL Statement Bind Parameters block. This works well but each time I add or remove a field as I develop I have to change the bind parameters block and as you can see, once you've got a lot of them it gets fussy. For what I'm doing, I expect to have a similarly long SQL statement each time.
Going forward, instead of very many local variables, the data will come from a large cluster and I'll have to line up the SQL statement with the binding of parameters.
I'm wondering.. ha ha... is it possible to somehow automate this so I just feed the cluster into a block and it lines up all the column names with the same names in the cluster. I know this is asking a lot and don't expect it to be possible... but I'm still kinda new at this so I thought I'd ask. Thanks.
I haven't used this specific toolkit, but this should actually be relatively easy, as long as your data types are simple.
LV has the tools to programmatically get the information from a cluster (in the cluster>>variant>>data type parsing pallete), and you can use that to get the labels in order to build the insert string and then call the binds in a loop, using the enum to decide which bind to call. The important thing is to pass the statement object between iterations using a shift register, so that you keep working on the same object.
Again, keep in mind that this will be more complicated if your data types are more complicated or nested.
I'd probably end up with something like this (class and text is faked):
The constant wired to Variant To Data might not be needed. It might convert automatically to the type selected in the property node. This means you can simply duplicate the case, select a type, and that's it. That saves a few clicks and potential mistakes.
You can pull the same trick to build the query string:
All kinds of variations are possible of course.
You can also use the cluster name to automatically replace the table name. This would give you a VI that accepts a variant, and if the variant is a cluster it will insert all values in the table that has the cluster's name.
Make sure the cluster is a type def, of lives in a class (which can act as a type def).
Search LabVIEW like a graph! Note that going the other way around, from an array of variants to a cluster, is much harder.
I don't think that can be done without some kind of flattening (to string, xml or JSON).
I still hope I'm wrong, but I've tried many, many times...
Check out the "Parameters" input on the "Execute SQL" vi before you spend any time on this, as I think it does at least some of what you are asking:
On another note, have a look into using JSON with SQLite. I personally would never have a table with that many columns, and never columns named "Operator" or other "metadata". I would have a few important columns, plus an "infoJSON" column that holds all the minor details.
@JohnCookInfineon wrote:
Hey wow, that's interesting!
I kinda suspected that LV would have a way to get field name and type info from a cluster but didn't know how. Thanks!!
I'm gonna try that next chance I get. It would save me so much time rather than manually syncing up the table create, table insert and parameter bind statements.
Thanks!!
-John C.
You can't do this in LV NXG. I think that was a big reason NXG got killed. Not this, specifically, but the reason why you can't do this is what killed it off.
Thank you all so very much!! I have now got a VI that automatically generates CREATE TABLE and INSERT SQL commands, entirely based on just a type definition. It has to be a file based control.
This has saved me the tedious and error prone task of maintaining two chunks of SQL and a large parameter binding block at different parts of my program. Now I can automate it all and focus on the testing and analysis of data. I've attached my example so others may benefit.
