[CR] SQLite Library

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

 

[Mark Balla]

Certified 11-22-2012

Placed in Database & File IO.

[hhtnwpu]

  On 6/24/2012 at 4:56 AM, drjdpowell said:

Name: SQLite LabVIEW

Submitter: drjdpowell

Submitted: 19 Jun 2012

File Updated: 22 Nov 2012

Category: Database & File IO

LabVIEW Version: 2011

License Type: BSD (Most common)

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 or variants 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.

Click here to download this file

thanks ,but can you post a version before labview2011?

[drjdpowell]

  On 12/23/2012 at 1:29 PM, hhtnwpu said:

thanks ,but can you post a version before labview2011?[/center]

As ShaunR’s SQLite package in 2009 was not satisfactory for you then I assume you need before 2009. In which case, “no”, as I use features of 2009 or more recent. You should be able to back save Shaun’s stuff, though.

[hhtnwpu]

  On 12/24/2012 at 1:42 PM, drjdpowell said:

As ShaunR’s SQLite package in 2009 was not satisfactory for you then I assume you need before 2009. In which case, “no”, as I use features of 2009 or more recent. You should be able to back save Shaun’s stuff, though.

what about 2010?

[drjdpowell]

Why not just use ShaunR’s package?

[Matze]

Hello,

your library seems to work well. Thank you!

But I've got one problem:

When inserting data into the database through producer consumer loops, the producer loop gets paused during the calling of the sqlite execution VI.

If I replace your library with the standard file i/o VIs from LabVIEW all works fine without any pause.

Is there a solution to get your library work with producer consumer loops or is this not possible because of a dll call?

Regards

Matze

[drjdpowell]

  On 2/20/2013 at 8:11 PM, Matze said:

When inserting data into the database through producer consumer loops, the producer loop gets paused during the calling of the sqlite execution VI.

Could you post some code illustrating the problem?  Or images of your producer and consumer code?  I can only guess that your holding a lock on the database open somehow.

[Ryan Podsim]

I've been looking through your library and I've come across some questions:

 

I am curious why you used the In Place Element Structure for accessing the sqlite3.dll?

 

In the Execute Prepared SQL (string results).vi you didn't wire the EI/EO terminals on the Get Column Count.vi. I assume that was an oversight?

 

I noticed in the Pointer-to-C-String to String.vi you added 1 to the Length before the MoveBlock call, but elsewhere you used the MoveBlock directly the converted to a string. Additionally in the Pointer-to-C-String to String.vi you preallocate the U8 array, but not elsewhere. This is an inconstancy that's not explained. Could you elaborate on this?

 

Thanks, I like the Class structure you used and have learned alot!

 

Ryan

[Matze]

Hm I'm very sorry. I made a demonstration project and there all works fine.

Strange, I'll analyse my VIs.

 

Thank you for your reply. I'm sure it's not a problem of your library.

[drjdpowell]

  On 2/21/2013 at 3:53 AM, Ryan Podsim said:

I am curious why you used the In Place Element Structure for accessing the sqlite3.dll?

 

One of the topics in the thread following this post by MattW is a possible reduction in performance due to LabVIEW needing to check if the dll path input has changed between calls.  In this later post by me I explained why I switched to using an in-place structure:

 

"I found through testing that some of my subVIs ran considerably slower than others, and eventually identified that it was do to details of how the class wire (from which the library path is unbundled) is treated. Basically, a subVI in parallel to the CLN node (i.e., not forced by dataflow to occur after it) would cause the slowdown. I suspect some magic in the compiler allows it to identify that the path has not changed as it was passed through several class methods and back through a shift register, and this magic was disturbed by the parallel call.

This being a subtle effect, which future modifiers may not be aware off, I’ve rewritten the package to use In-Place-Elements to access the library, thus discouraging parallel use."

  On 2/21/2013 at 3:53 AM, Ryan Podsim said:

In the Execute Prepared SQL (string results).vi you didn't wire the EI/EO terminals on the Get Column Count.vi. I assume that was an oversight?

Opps!

  On 2/21/2013 at 3:53 AM, Ryan Podsim said:

I noticed in the Pointer-to-C-String to String.vi you added 1 to the Length before the MoveBlock call, but elsewhere you used the MoveBlock directly the converted to a string. Additionally in the Pointer-to-C-String to String.vi you preallocate the U8 array, but not elsewhere. This is an inconstancy that's not explained. Could you elaborate on this?

I really should document more.   This was only my second “wrap a C dll” job, and the first time I’ve used “MoveBlock”.  The issue is the fact that C strings have an extra 00 byte at the end and thus are one byte longer than their LabVIEW string length.  I’m not sure I’m doing it correctly, but in “Pointer-to-C-string to String” I’m walking along the string to find the 00 byte, while in the other MoveBlock uses I’m getting the exact string length from sqlite.  

  On 2/21/2013 at 9:03 AM, Matze said:

Hm I'm very sorry. I made a demonstration project and there all works fine.

Strange, I'll analyse my VIs.

Check that you are “reseting” any statements when finished (or you’ll be holding a Read lock on the database file), and that you aren’t holding an SQL transaction open (a Write lock).  

 

— James

[Matze]

  On 2/21/2013 at 10:48 AM, drjdpowell said:

Check that you are “reseting” any statements when finished (or you’ll be holding a Read lock on the database file), and that you aren’t holding an SQL transaction open (a Write lock).

 

I tried "Execute SQL.vi" and "Execute Single SQL Statement". In these VIs you reset the statements, so I don't need any additional steps, I think. I don't use the SQL transaction statements and I only call the statements one after the other (not in parallel).

First I though the indexing of the database will be the problem (database size about 50 MB), but a new, empty database causes the same problem (the project is too big to send it to you).

 

I also tried once the "Prepare.vi" outside the loop and in the loop I'll bind the parameters and called "First Step.vi" and "Finalize.vi". But I can't see any performance advantages.

When I disable the "First Step.vi" I did not get any pauses. But in this VI you only call directly the DLL function.

I tried to set the DLL path directly in the DLL Node, but no changes.

 

The strange thing is, that sometimes there'll be a "xxx.db-journal" file generated, like there is a locked database. But I hope, the DLL call finishes when the execution is really done and not only queued within the database. But with a delay after every SQL execution the pause still will be present. Only the journal files won't be generated anymore.

 

Also interesting is that the windows cursor will change to a waiting cursor for about 1 second when calling the DLL. If I call standard file i/o VIs the cursor will stay normal.

 

I have no ideas anymore, but I'll do some tests. When I find something I'll tell you.

Edited February 21, 2013 by Matze

[Matze]

Oh I have news:

 

I had my database folder in my LabVIEW project explorer with activated auto population. So LabVIEW always had access to the database and blocked it sometimes.

 

Now I disabled auto population for this folder and it works.

 

But unexplainable is the fact, that my LabVIEW executable didn't work on other comupters. There I had the pauses and only the LabVIEW runtime is installed there.

Now, after disabling the auto population option and rebuilding the executable, it runs on any other PC perfectly.

 

Why has the auto population option any influence on my executable? I searched for days and this simple option caused such behaviour on my computer.

 

Sorry, this topic here is now not the right place for this anymore, but I didn't know that a LabVIEW option could cause such behaviour.

Edited February 21, 2013 by Matze

[drjdpowell]

I don’t know why that caused you problems, but always try and use the Default Data Directory for saved files, since you can rely on having write access even in an executable.

[Stobber]

James -

 

I just used v1.0.3.16 in a small benchtop product test application, and I think it's fantastic. The API makes sense and is flexible enough without getting cumbersome for simple tasks. Great job!

 

I did collect a few points of feedback that I'd like to share, though:

• Could you expose a read accessor for "database path"? It'd save me some effort when wrapping the API in higher-level classes.
• Could you remove ".lvclass" from the class Localized Name (in the Properties dialog)? It'll make class property nodes smaller on the block diagram.
• I would make the tunnel mode "indexing" on "Get Column Template Code.vi", since that's how it's used 99% of the time.
• Please put your package in <addons> or <user.lib>, not directly into <vi.lib>!
• This would be backward-incompatible, but I'd like to see the VI names changed so they're more "Quickdrop friendly". At present, there are names like:
  • "(,,,) FROM.vi" which is hard to type
  • "Get Column Template code.vi" which I think is confusing to read and remember
  • "Open.vi", "Close.vi", Step.vi", etc. which are hard to discern from other libraries (and primitives!!!) whose palette items share the same simple names.
  • Many (all?) of the VIs don't appear in QD unless the API is added to my project. This might be due to the API not living in <addons> or <user.lib>.
    

  
  

[drjdpowell]

  On 6/21/2013 at 1:52 AM, Stobber said:

• Could you expose a read accessor for "database path"? It'd save me some effort when wrapping the API in higher-level classes.
  

I can do that.

  On 6/21/2013 at 1:52 AM, Stobber said:

• Could you remove ".lvclass" from the class Localized Name (in the Properties dialog)? It'll make class property nodes smaller on the block diagram.
  

Wait, what?!?  How can I have missed that little option for so long!  Thanks.

  On 6/21/2013 at 1:52 AM, Stobber said:

• I would make the tunnel mode "indexing" on "Get Column Template Code.vi", since that's how it's used 99% of the time.
  

Can one set a while loop to index by default for new tunnels?

  On 6/21/2013 at 1:52 AM, Stobber said:

• Please put your package in <addons> or <user.lib>, not directly into <vi.lib>!
  

As I understand it, vi.lib is now recommended for installed packages.

  On 6/21/2013 at 1:52 AM, Stobber said:

• This would be backward-incompatible, but I'd like to see the VI names changed so they're more "Quickdrop friendly". At present, there are names like:
  • "(,,,) FROM.vi" which is hard to type
    
  • "Get Column Template code.vi" which I think is confusing to read and remember
    
  • "Open.vi", "Close.vi", Step.vi", etc. which are hard to discern from other libraries (and primitives!!!) whose palette items share the same simple names.
    
  • Many (all?) of the VIs don't appear in QD unless the API is added to my project. This might be due to the API not living in <addons> or <user.lib>.
    

I’m not a Quickdrop user so I don’t notice these things.  It looks to me like Quickdrop uses the VI title rather than VI name, so I may be able to improve things without breaking backwards compatibility (make the title of Open.vi “SQLite Open” etc.).

 

Hey now, "(,,,) FROM” comes up as the very first entry in my Quickdrop so why do you need to type it?     Actually, I’ve considered removing those two SQL formatting utility VIs as I haven’t found the need to use them.  Have you found them useful?

[Stobber]

  On 6/21/2013 at 1:56 PM, drjdpowell said:

Can one set a while loop to index by default for new tunnels?

No...and I have no idea what I was asking for with this comment. Ignore it. (I had collected comments in a text file and pasted them here without review.)

 

  On 6/21/2013 at 1:56 PM, drjdpowell said:

As I understand it, vi.lib is now recommended for installed packages.

^{[citation needed]}

 

  On 6/21/2013 at 1:56 PM, drjdpowell said:

Hey now, "(,,,) FROM” comes up as the very first entry in my Quickdrop so why do you need to type it?     Actually, I’ve considered removing those two SQL formatting utility VIs as I haven’t found the need to use them.  Have you found them useful?

Nah, all my statements are developed and tested in a real SQL editor and pasted into string constants on the diagram. I'd probably never develop statements directly in LV code.

[drjdpowell]

  On 6/21/2013 at 2:14 PM, Stobber said:

^{[citation needed]}

A discussion here.

[Stobber]

I'm having a little trouble with timestamps. If I bind a timestamp to an INSERT statement as text, it gets converted internally (by your API or by the db) to UTC. When I try to SELECT a record using that value, I get no results because the timestamp I'm using is local time. If I INSERT the timestamp as a blob, my SELECT works, but that makes the field illegible when viewing the database in an external program. Do you know how to get it working with timestamps bound as text?

[Stobber]

  On 6/21/2013 at 5:04 PM, Stobber said:

If I bind a timestamp to an INSERT statement as text, it gets converted internally (by your API or by the db) to UTC.

I see where your API formats the timestamp to a UTC string in the "Bind" VI, and I see where it parses a UTC string back into a timestamp in the "Column" VI. That should work. I'm going to test my code again and make sure it's not a logic error on my part. If I don't post back, disregard this bug report.

[raumneun]

Hey guys,

 

I hope I don't hijack the thread with this question, but this seems to be the right place.

 

I've been using the SQLite wrapper (thanks a lot James!) for quite a while now and I'm really happy with the functionality. I'm writing a lot of data from a testrig to a database on a network drive. This actually performs pretty well.

 

What I wanted to do now is to code a simple standalone viewer for the database so I can see what my testrig is doing while I'm in my office/at home. The problem is: when I try to run the compiled code, it simply does not work...

It compiles and runs nicely, I have no access to the database though. Nor any error message. And I didn't even try it on another machine, it's still my lab-pc running Win7 and LV2011.

 

I know, this is not how you're supposed to ask questions in a forum, I just don't know how to debug it. So I kinda hoped one of you guys experienced a similar behaviour and was able to solve it

 

/ Max

[drjdpowell]

  On 6/25/2013 at 7:36 AM, raumneun said:

What I wanted to do now is to code a simple standalone viewer for the database so I can see what my testrig is doing while I'm in my office/at home. The problem is: when I try to run the compiled code, it simply does not work...

It compiles and runs nicely, I have no access to the database though. Nor any error message. And I didn't even try it on another machine, it's still my lab-pc running Win7 and LV2011.

Do you mean you are building an EXE executable?  I would suspect the problem is not getting sqlite.dll included.  Check the “Preview” page of the build and see if sqlite.dll is installed under “data”.  Sqlite.dll is part of the “Connection” class library and thus should be included, but I noticed just recently that the library, when installed with VIPM, loses it’s connection to this file.  If this is the problem, you can fix it either by opening the “Connection" class and fixing it to include sqlite.dll, or adding sqlite.dll to the “Always Included” section of the build spec.

[raumneun]

Well this would be embarrassing if that was the problem. I guess this should have come to my mind before asking the question

I'll state a "problem solved" as soon as I had the chance to try it out, maybe this'll help another person having the same problem. I didn't install it using VIPM though.

Edit: Yes, problem solved.... thanks a lot!

Edited June 26, 2013 by raumneun

[raumneun]

Yes, that solved the problem....thanks a lot

[drjdpowell]

  On 6/26/2013 at 7:32 AM, raumneun said:

I didn't install it using VIPM though.

How did you install it?