Jump to content

SQLite Library Beta: Parameters in "Execute SQL"


Recommended Posts

  • 2 weeks later...
  • 5 months later...

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.

 

image.png.3c0459bb923f7321e1974f09a79d28f4.png

 

 

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.

image.png.9439ab93f810afc77ccf1d5266b8cada.pngAlso, noticed the example is broken in LV2020.  

 

 

 

 

 

image.png

Link to comment
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: 

image.png.4d6d7d589a1f8e64a58b0fb74c0ec554.png 

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:

443903803_InsertMultipleRowsWithParameters.png.5a81ca1b98478da7ce22a6d390afd2c9.png

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):

2141373278_SQLiteExecuteSQLwithClusterandArrayofCluster1_12.2_91.png.d50d44ff4715c7b5cd8b4d80baf307ca.png

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:

image.png.ef7d8936355bf1defb3dcab6cf3884dd.png

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

After:

image.png.699855681c1509df87b1f74fa35d3913.png

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?

Link to comment
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.

Link to comment
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?

1614269050_ExecuteSQLwithmultiplestatements.png.f81d09b3d04a4bc187228ee5e7fde74e.png

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 😟

image.png.40907393fe9092bfea518fd10fcd5b37.pngimage.png.b64a8dc6fa024c64904d130b6bcea354.png

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

image.png.d4b6e551200c921961d10f2a8e387ac0.pngimage.png.d2e7178c5f03700a48398093132a300d.png

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

image.png.cf08223d43847827767086729e7c01b3.pngimage.png.7f69660bd91bb69ba220e5668a560f3b.png

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:

2062549825_ExecuteSQLwithmultipleinsertions.png.f75383969d05555217587a532cf3e0e8.png

This is the expected output:

image.png.c0c1566c28dd4bd0fcc3fc5f09488b00.png image.png.52abd453332c2a269451e1e9559611ea.png

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

image.png.7eb1e52e61bc4f7883e0083576553890.png

Edited by LogMAN
Link to comment
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.

Link to comment
  • 2 years later...
  • 9 months later...
  • 2 weeks later...

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.