SQLite Library Beta: Parameters in "Execute SQL"

[drjdpowell]

For comment, here is a beta version of the next SQLite Library release (1.11).   It has a significant new feature of a "Parameter(s)" input to the "Execute SQL" functions.  This can be a single parameter or a cluster of multiple parameters.  Uses Variant functions and will be not as performance as a more explicit preparing and binding of a Statement object, but should be easier to code.

 

drjdpowell_lib_sqlite_labview-1.11.0.86.vip

[drjdpowell]

There is an example, found this way:

 

[drjdpowell]

Hmmm, one download, eh?   Nobody use SQLite here?

[smithd]

Stopped using it from labview, mostly do my database interactions in python currently. Actually, I use python to generate a data structure which I then json over to labview using your jsontext

 

[mramsdale]

1) Can parameters be used for writing multiple rows to a table?  (rather than the step method)

Guessing the answer is to use a loop for multiple parameter values.  

To expand, somehow the variant data is substituted into the SQL statement.   So I'm writing two fields to a table, and I provide an array with multiple rows of two columns.  The data from each row is put into the table.  I tried something like this, "insert into directory (directoryname,directorypath) values (?,?)  ", and the ? are replaced with rows in the parameters variant input.  (doesn't work of course) 🙂

Semi answered.  I can parameter input one row, using two strings, and it works, relatively intuitive.  But I was hoping this could be used to enter multiple rows too, without loops to pull from an array and then separate into strings or insert into clusters.  (Feeling greedy am I)

In the image, the highlighted structures don't work, but the cluster does.

 

 

 

2)I'm guessing the one row array doesn't work as the datatype is not an array, and there are two fields but only one array (datatype).  Does this sound more or less correct?

 

Noticed the example is broke in LV2020.

Also, noticed the example is broken in LV2020.  

 

 

 

 

 

[LogMAN]

2 hours ago, mramsdale said:

1) Can parameters be used for writing multiple rows to a table?  (rather than the step method)

Guessing the answer is to use a loop for multiple parameter values.  

I think you already answered your own question. Each statement only affects a single row, which means you have to wrap arrays in loops. Don't forget that Execute SQL can handle multiple statements at once. This is shown in one of the examples: 

 

Note that there are multiple statements that utilize the same input cluster.

2 hours ago, mramsdale said:

I can parameter input one row, using two strings, and it works, relatively intuitive.  But I was hoping this could be used to enter multiple rows too, without loops to pull from an array and then separate into strings or insert into clusters.  (Feeling greedy am I)

You could use the Array To Cluster function to simplify the task:

3 hours ago, mramsdale said:

2)I'm guessing the one row array doesn't work as the datatype is not an array, and there are two fields but only one array (datatype).  Does this sound more or less correct?

You are on the right path. In this case Execute SQL will report an error because only arrays of clusters are allowed:

Quote

SQLite.lvlib:SQL Statement.lvclass:Parse Parameters (Core).vi:6060003<ERR>
Unable to Bind Array of Type: "String"

Please take the following section with a grain of salt, I make a few assumptions based on my own findings.

@drjdpowell It would be great to have an example of how Arrays of Clusters are supposed to work.

I opened the source code and it looks like it is supposed to take an array of cluster and iterate over each element in the order of appearance (as if it was a single cluster). So these will appear equivalent (The "Text" element of the second cluster is used for the 'Reason' field in this example):

However, in the current version (1.12.2.91) this doesn't work at all. The table returns empty. I had to slightly change Parse Parameters (Core) to get this to work:

Before:

Note that it parses the ArrayElement output of the GetArrayInfo VI, which only contains the type info but no data.

After:

This will concatenate each element in the array as if it was one large cluster.

Perhaps I'm mistaken on how this is supposed to work?

[drjdpowell]

4 hours ago, mramsdale said:

1) Can parameters be used for writing multiple rows to a table?  (rather than the step method)

Hi Guys,

Support for writing multiple rows in this manner is on the Roadmap, but not yet existing, partly because I am debating multiple options:

1. A new function, "Execute SQL Multiple Times", that takes a parameter input of arrays of clusters or a cluster of arrays and does multiple executions of one SQL statement. 
2. Integrating it into the existing "Execute SQL" node.  
3. Upping the LabVIEW version to 2017 and introducing a new VIM version of either (1) or (2).

The problems with (2), which you guys are thinking should work are:

• "Execute SQL" is a polymorphic VI with many members, and major changes to support functionality like this is very time expensive for me.
• That function is already quite complicated, and increasing what it can do is going to make it more confusing.

Usually when one is executing the same SQL many times, one is INSERTing rather than getting any results, so a new function (1) would only be one VI, and it could have documentation (and a different icon) that clearly describes using it on multiple INSERTs.   An advantage of the VIM (3) is that I can make the input accept only data in the right form (array of cluster, cluster of arrays).  

 

This is not high priority for me, as I see it as about convenience rather than new capability.  You can already do things more flexibly and faster-executing using "Prepare Statement" followed by a loop, or you can do it easy (but slow) with "Execute SQL" in a loop.

[drjdpowell]

5 hours ago, mramsdale said:

Noticed the example is broke in LV2020.

That example isn't broken for me in LabVIEW 2020.  What happens if you delete the broken wire and reconnect?

[LogMAN]

2 hours ago, drjdpowell said:

Support for writing multiple rows in this manner is on the Roadmap, but not yet existing, partly because I am debating multiple options:

1. A new function, "Execute SQL Multiple Times", that takes a parameter input of arrays of clusters or a cluster of arrays and does multiple executions of one SQL statement. 
2. Integrating it into the existing "Execute SQL" node.  
3. Upping the LabVIEW version to 2017 and introducing a new VIM version of either (1) or (2).

1+3 makes the most sense in my opinion. And it should be limited to a single SQL statement (or multiple statements if they use absolute parameter bindings like "?NNN").

Perhaps it also makes sense to apply that limitation to Execute SQL, because it can get very confusing when executing multiple statements in a single step with parameters that include arrays. For example, what is the expected outcome of this?

I could be mislead into thinking that the user name only applies to the first SQL statement and the array to the second, or perhaps that it uses the same user name multiple times while it iterates over each element of the array, but that is not how it works.

Right now it produces empty rows 😟

With my fix above, it will produce exactly one row 🤨

By executing the statement over-and-over again until all parameters are used up (processed sequentially), it will insert data into wrong places 😱

In my opinion it should only accept

a) an array of any type, each element of which must satisfy all parameter bindings.
b) a cluster of any type, which must satisfy all parameter bindings.
c) a single element of any type.

In case of a or b, subarrays are not allowed (because a subarray would imply 3D data, which is tricky to insert into 2D tables).

That way I am forced to write code like this, which is easier to comprehend in my opinion:

This is the expected output:

 

Here is what I did to Execute SQL (in addition to the fix mentioned before):

Edited October 22, 2020 by LogMAN

[drjdpowell]

5 hours ago, LogMAN said:

For example, what is the expected outcome of this?

That's sloppy on my part.   Arrays should not be supported at all and that should throw an "Unable to Bind Arrays" error.  The code for that is in the Disabled case of "Parse Parameters (Code).vi".  I was experimenting with the idea of allowing U8 Arrays as binding as BLOBs, and I seem to have not undone my buggy experimental modification.

[LogMAN]

I was wondering why it wasn't documented, now I know 😄

 

[drjdpowell]

Working on Idea (1)  https://bitbucket.org/drjdpowell/sqlite-labview/issues/12/add-an-execute-sql-statement-multiple