Jump to content

[CR] SQLite Library


Recommended Posts

  • 4 months later...
  • 1 month later...

index.php?app=downloads&module=display&section=screenshot&id=212

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?

Link to comment
  • 2 weeks later...
  • 1 month later...

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

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

Link to comment

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

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

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!

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.  

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

Link to comment
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 by Matze
Link to comment

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 by Matze
Link to comment
  • 3 months later...
  • 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.

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

  • 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?

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

  • 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?   :D  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?

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

 

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

[citation needed]:P

 

Hey now, "(,,,) FROM” comes up as the very first entry in my Quickdrop so why do you need to type it?   :D  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.

Link to comment

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?

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

Link to comment

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

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

Link to comment

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 by raumneun
Link to comment

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.

×
×
  • Create New...

Important Information

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