Jump to content

Recommended Posts

  • 4 months later...
  • Replies 211
  • Created
  • Last Reply

Top Posters In This Topic

Top Posters In This Topic

Popular Posts

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

Powerpoint slides from a presentation I gave at the European CLA Summit: CLA Summit 2014 SQLite.pptx

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

Posted Images

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

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.

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

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

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

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

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

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



×
×
  • Create New...

Important Information

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