Jump to content

SQLite Library Beta: Parameters in "Execute SQL"


Recommended Posts

  • 2 weeks later...

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

 

Link to post
Share on other sites
  • 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 post
Share on other sites
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 post
Share on other sites
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 post
Share on other sites
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?

Link to post
Share on other sites
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 post
Share on other sites
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 post
Share on other sites

Join the conversation

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

Guest
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.

  • Similar Content

    • By drjdpowell
      View File SQLite Library
      Introductory video now available on YouTube: Intro to SQLite in LabVIEW
       
      SQLite3 is a very light-weight, server-less, database-in-a-file library. See www.SQLite.org. This package is a wrapper of the SQLite3 C library and follows it closely.

      There are basically two use modes: (1) calling "Execute SQL" on a Connection to run SQL scripts (and optionally return 2D arrays of strings from an SQL statement that returns results); and (2) "Preparing" a single SQL statement and executing it step-by-step explicitly. The advantage of the later is the ability to "Bind" parameters to the statement, and get the column data back in the desired datatype. The "Bind" and "Get Column" VIs are set as properties of the "SQL Statement" object, for convenience in working with large numbers of them.

      See the original conversation on this here.
      Hosted on the NI LabVIEW Tools Network.
      JDP Science Tools group on NI.com.
      ***Requires VIPM 2017 or later for install.***
      Submitter drjdpowell Submitted 06/19/2012 Category Database & File IO LabVIEW Version 2013 License Type BSD (Most common)  
    • By drjdpowell
      Introductory video now available on YouTube: Intro to SQLite in LabVIEW
       
      SQLite3 is a very light-weight, server-less, database-in-a-file library. See www.SQLite.org. This package is a wrapper of the SQLite3 C library and follows it closely.

      There are basically two use modes: (1) calling "Execute SQL" on a Connection to run SQL scripts (and optionally return 2D arrays of strings from an SQL statement that returns results); and (2) "Preparing" a single SQL statement and executing it step-by-step explicitly. The advantage of the later is the ability to "Bind" parameters to the statement, and get the column data back in the desired datatype. The "Bind" and "Get Column" VIs are set as properties of the "SQL Statement" object, for convenience in working with large numbers of them.

      See the original conversation on this here.
      Hosted on the NI LabVIEW Tools Network.
      JDP Science Tools group on NI.com.
      ***Requires VIPM 2017 or later for install.***
    • By Thang Nguyen
      Hi,
      I would like to know if I can write stream data into two different tables with SQLite at the same time or not? Currently my system has one camera. I store the capture images and particle measurement in one table. I have just add one more camera to the system and would like to do the same thing and write to a second table in a parallel process.  I wonder if this is possible or not.
      I use SQLite library.
      Thanks in advance.
       
    • By McQuillan
      Hi Everyone,
      I (re)watched James Powell's talk at GDevCon#2 about Application Design Around SQLite. I really like this idea as I have an application with lots of data (from serial devices and software configuration) that's all needed in several areas of the application (and external applications) and his talk was a 'light-bulb' moment where I thought I could have a centralized SQLite database that all the modules could access to select / update data.
      He said the database could be the 'model' in the model-view-controller design pattern because the database is very fast. So you can collect data in one actor and publish it directly to the DB, and have another actor read the data directly from the DB, with a benefit of having another application being able to view the data.
      Link to James' talk: https://www.youtube.com/watch?v=i4_l-UuWtPY&t=1241s)
       
      I created a basic proof of concept which launches N-processes to generate-data (publish to database) and others to act as a UI (read data from database and update configuration settings in the DB (like set-point)). However after launching a couple of processes I ran into  'Database is locked (error 5) ', and I realized 2 things, SQLite databases aren't magically able to have n-concurrent readers/writers , and I'm not using them right...(I hope).
      I've created a schematic (attached) to show what I did in the PoC (that was getting 'Database is locked (error 5)' errors).
      I'm a solo-developer (and SQLite first-timer*) and would really appreciate it if someone could look over the schematic and give me guidance on how it should be done. There's a lot more to the actual application, but I think once I understand the limitations of the DB I'll be able to work with it.
      *I've done SQL training courses.
      In the actual application, the UI and business logic are on two completely separate branches (I only connected them to a single actor for the PoC) 
      Some general questions / thoughts I had:
      Is the SQLite based application design something worth perusing / is it a sensible design choice? Instead of creating lots of tables (when I launch the actors) should I instead make separate databases? - to reduce the number of requests per DB? (I shouldn't think so... but worth asking) When generating data, I'm using UPDATE to change a single row in a table (current value), I'm then reading that single row in other areas of code. (Then if logging is needed, I create a trigger to copy the data to a separate table) Would it be better if I INSERT data and have the other modules read the max RowId for the current value and periodically delete rows? The more clones I had, the slower the UI seemed to update (should have been 10 times/second, but reduced to updating every 3 seconds). I was under the impression that you can do thousands of transactions per second, so I think I'm querying the DB inefficiently. The two main reasons why I like the database approach are:
      External applications will need to 'tap-into' the data, if they could get to it via an SQL query - that would be ideal. Data-logging is a big part of the application. Any advice you can give would be much appreciated.
      Cheers,
      Tom
      (I'm using quite a few reuse libraries so I can't easily share the code, however, if it would be beneficial, I could re-work the PoC to just use 'Core-LabVIEW' and James Powell's SQLite API)

    • By Munch
      Good Afternoon,
      I have been having an issue trying to resolve an error all afternoon, and hope that you might be able to help.
      I am trying to execute the follow SQL "
      SELECT m.name as tableName,
             p.name as columnName
      FROM sqlite_master m
      left outer join pragma_table_info((m.name)) p
           on m.name <> p.name
           where columnName = 'UploadedFlag'
      order by tableName, columnName
      " That returns a list of the tables that Contain the 'UploadedFlag' Column.   I have testing the SQL on both HeidiSQL and SQLite Expert Personal applications and run and return what I expect, however when I run it through LabVIEW I get a 402860 Error with the description " SQLITE_ERROR(1): near "(": syntax error " 
      If anyone could point me in the direction of the syntax error then I would be very greatful.
      Regards
      Mark
×
×
  • Create New...

Important Information

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