drjdpowell

Is that a “bugâ€?   I used the LabVIEW time definition, as there is no SQLite Timestamp definition.  Not sure why LabVIEW doesn’t match the UNIX definition.

Are you sure you’re not asking for 972x768 pixels from a 768x972 image?

You definitely do not want to have you PID timing based on time delayed send or AF messages in general. Remember, a message is guaranteed to get there, but it's not guaranteed to be handled in any time. So if you have a "Check PID" message, but the "Log data" message gets in there before it, the log data will happen first, then the "Check PID" will happen as soon as it can after the logging is done. This could introduce a lot of jitter.

Another line of reasoning would be to ask why you have PID and Logging in the same actor.  If a “PID actor†is only handling messages to do with PID control, then there may be advantages to keeping all actions serialized in one loop.  

BTW, Lewis, the AF group at NI.com is the best place to ask AF questions.  More people who use the AF extensively will be watching.

This raises a few additional questions :-

I don’t think there is a major difference between using one or multiple connections.   I’ve used either one or a few.  And don’t worry about opening and closing connections, just keep them open.  The issue to (slightly) worry about is Write Transactions, as these lock the db file.   Other operations will block waiting for the db to stop being busy.   These will throw a “busy†error after a timeout which is by default 5 seconds.  So don’t BEGIN a transaction then wait for something, as you are preventing other processes accessing the db.  

The worst that can happen on power failure is that the current transaction is reverted (important point: do not delete any extra files the SQLite may leave in the same directory on power failure, as these are the “journal†or “WAL†files that SQLite needs to rollback). 

Finalize and Close VIs have standard error handling for “cleanupâ€-type VIs (i.e. they cleanup even on error).  You do not need to use “clear errorsâ€.

Note, btw, that you can Prepare statements once and reuse them.   This saves some overhead.  Just Finalize them at the end of the app before Closing the connection (I’ve been thinking of making the Finalization automatic on Close, but haven’t yet).

A related LAVA conversation, including multiple examples of different techniques one can use.  I did a “cluster of cluster†example.

2) top level SQLite performance evaluation code written

1) SQLite isn’t a compression format, and as a flat table won’t necessarily be any smaller on disk than a spreadsheet file.  Larger actually, due to the various lookup indexes.  However, you have the opportunity to use structured data, avoiding a lot of duplication, which can end up with smaller file sizes (or, at least, the freedom to add much more info at not much larger size).   For example, you have “site†and “sending app†strings that repeat often.  If you instead saved keys into separate “Site†and “Application†tables, you could store lots of info in them that can be “joined†in with a “VIEWâ€.   Similarly you could have an “Errors†table that allowed extended error descriptions instead of just a simple error code (or error severity, etc.).   The joining VIEW would look something like:

CREATE VIEW Event_VIEW AS

    SELECT * FROM Application_Events

         JOIN Errors USING (ErrCode)

         JOIN Site USING (SiteID)

         JOIN Application USING (AppID)

Your UI would then query this View, and filter on any info in all these table.   Find all events whose error description contains the word “testâ€, for example. 

2) Look up “LIMIT†and “OFFSETâ€, and study how they are used in the “Cyth SQLite Log Viewerâ€.   In that viewer, updating the filtering of a selected UI table takes ms, not seconds.  This is because only the visible rows of the UI table are actually selected.  When the User moves the scrollbar, the SELECT is repeated multiple times per second, meaning that it looks to the User like a table with thousands of rows.  And one is free to use a lot of slow property nodes to do things like text colour, since one is never doing more than a few dozen rows.

3) I wouldn’t bother with VACUUM in a logging application, as the space from any deletion will just get used for later inserts.   Use VACUUM if you delete a large amount without intending to reuse the space.

4) You cannot unlock the file if you’ve dropped the pointer to the connection without closing it, I’m afraid.  You have to restart LabVIEW to unload the SQLite dll.   Your code should always call the Finalize and Close methods, even on error.  

Dont suppose anyone knows why this INSERT routine is taking so long (38 ms)? My storage loop cant get anywhere near the speed of my DAQ loop and eventually I get a buffer overflow. I believe I am using the BEGIN and COMMIT correctly, the speed of DAQ is 1 kHz so this routine is executed once per cycle.  (see sattached screenshot) Thanks!

Hi Rob,

You need to wrap multiple INSERTs into a single transaction with “BEGIN†and “COMMITâ€.   Each transaction requires verified writing to the disk twice, and a hard disk only spins on the the order of once every 10 ms.   You need to buffer your data and do a bulk insert about once a second (place a FOR LOOP between the “Prepare†and “Finalize†subVIs in you code image, and feed in an array of your data clusters).

This touches on jollybandit’s question (5): durability against power failure by confirmed writing to disk is time consuming, so you need some kind of tradeoff between immediate saving and delayed buffering.   About one save per second is what I do.

OpenG’s Trim Whitespace fails on a string of all whitespace, if set to trim the front only.   It returns the last whitespace character.

Good day to all Lava users

I am New to the envoroment of SQLite and have been experimenting with the tool kit .

Attach a zip file of your benchmark code (including a sample dataset) and I can have a look at it.   In the meantime, have you studied the documentation on SQLite.org?

Also, have you seen the “Cyth SQLite Loggerâ€?  Would this work as a preexisting solution for your app?

 

Hello,

I would like to use the SQLite Library but I have an error 12 when trying to open the data base with the "SQLite open" function (screen view attached).

Would you have an idea of what could be the problem ?

I'm using the Labview 2012 SP1 Dev version.

Thanks a lot !

 

Pierre

 

You’ve wired your database file to the wrong input.

Right now for me is June 19 at 13:19.  If someone asked me what day it is, they wouldn’t expect me to round up to "June 20thâ€.  

A few comments, you aren't closing lots of various references, and I figure you probably just threw this together.  

Yes, I just threw this together as an example.  It would need some work to be a robust reusable component, though it does properly shut itself down and close all references as used in the example.  The actual code that this example is modeled on is a component called “Metronome" built into Messenger Library**.

The point, though, is that one can build such a reusable, easy-to-use component, and use it simplify use of the JKI template.

** See the Messenger Library example "Example of Recurring Event Methodsâ€.  It shows both this “trigger†method and an alternative “delayed message to self†method.

This is the template I now use, BTW, which will be in the next version of Messenger Library:

It uses JKI-like strings, but as a “follow-on action†stack, where there is no ability to call “idleâ€.

Along the same lines....do you guys ever use the DAQ Events functionality within the JKI state machine in the same manner?  eg using the DAQmx events along with a daqmx read in the event structure similar to LabVIEW example:

 

I’ve used DAQmx Events multiple times.  Also IMAQdx “Frame Done†Events.

Or are the downsides to reading daq at high speed in an event loop outweigh the potential benefits?

 

I wouldn’t worry about the Event Loop overhead.   

Beta modifications:  Uses Open_v2 to allow Read Only, and search for libsqlite3.so on RT.  Please test.

drjdpowell_lib_sqlite_labview-1.5.1.42.vip

Is there a way to open read-only?

I see that sqlite3_open_v2() supports a SQLITE_OPEN_READONLY flag but you seem to be using sqlite3_open() only.

I’ll look into using sqlite3_open_v2().

"/usr/lib" should be in the standard search path for libraries, and if the library was properly installed with ldconfig (which I suppose should be done automatically by the OPKG script) then LabVIEW should be able to find it. Note that on Linux platforms LabVIEW will automatically try to prepend "lib" to the library name if it can't find the library with its wildcard name. So even "sqlite3.*" should work. However I'm not entirely sure about if that also happens when you explicitedly wire the library path to the Call Library Node. But I can guarantee you that it is done for library names directly entered in the Call Library Node configuration.

I had "sqlite3.*†as one of the search paths, so that must have failed for some reason.

The library is /usr/lib/libsqlite3.so . Provide that path to the API when opening/creating a file, and everything works great.

Can you try just using the string “libsqlite3.so†or “libsqlite3.*â€?   /usr/lib may be the default library path,  and then I can cover the case where the library default path is another location.

drjdpowell, that path ^^^ is the standard NI installation of SQLite3 on a cRIO target. (I'm using NI's opkg server to install the package.) Can you add that path as a default for the OS and targettype inside your API?

What’s the Target_Type conditional disable symbol on the cRIO?

Questions:

— does it work on Windows?

— Do the examples work on the cRIO?

— Can you attach a sample db so I can inspect it?

Putting “?†is the string “?â€, not a parameter, so when you tried to bind a parameter it was an error.

Not sure why your code isn’t working, but try eliminating the SELECT and changing the INSERT to “INSERT OR IGNOREâ€, as that should accomplish the same thing (and be faster too).

If JKI didn't want us to be able to enqueue at the end then they shouldn't have included that feature.  They obviously saw a need, or their users saw the need for it.

With the JKI one has a single stack-like, last-in-first-out, method but TWO queue-like, first-in-first-out, methods: States on back, and an Event queue.  The former has priority over the latter, but one can mess with that by adding “idle†states at various places.  Having two very different methods of doing the same thing is complicating without creating meaningful additional capability.   Reducing things a single event queue, with a subVI-like stack handling of each event is actually much simpler.  

The easiest way to do this is just the replace “States on back†with a User Event message to oneself, but with a little sophistication one can create reusable subVIs that either do a delayed User Event or set up a “trigger source†of periodic events.  Here’s an example of a JKI with two timed triggers:

Periodic Triggers with JKI statemachine.zip

Thanks, I found why my states were never going into the idle state while still doing everything else that I request. I tend to use the "States In Front" too much in my software. So it always does what I request and skip the idle states queuing them at the end.

You use it too little; one should ALWAYS use “States in Frontâ€, never at back.   Enqueue the “idle†at front.  Don’t think of it as a queue, think of it as a call stack.

Adding calls to the "Idle" state is an easy way to allow a long process to run without hanging the UI (and without launching an actor).

But note that there are better and more flexible ways to do this.  Another example is to have a separate loop fire “Do XYZ†User Events at the JKI machine.  One can have multiple such loops with independent periods, and the UI remains instantly responsive, even if the periods are long.  

Is there a list somewhere of the operations that block a thread?   In other words, when do I have to start worrying about threads or execution systems?

Back when I used to “loop†states in a JKI template in this way, I would just call “idle†if I wanted to give an Event a change to be handled.  Often, I would modify the Timeout case such that the timeout could be specified in the “idle†command.   So my “DMM_Read†state would post:

idle >>100

DMM_Read

Does calling “idle†not work for you?

— James

BTW, I would currently recommend creating the ability to fire a “delayed User Event†at the Event Structure.  So “DMM_Read would send a User Event, delayed by 100 ms, containing the command “DMM_Readâ€.  The advantage of this is that any number of “loops†can be occurring in the JKI statemachine at the same time, with independent cycle times, and with all Events being unblocked.

Rules for reentrancy:

Reentrant code may not hold any static (or global) non-constant data.

I think the “static†data they are referring to there is accessible from every call to the function, thus it would not be safe reentrantly call the function.  But LabVIEW reentrant have separate data spaces, and don’t share data between reentrant calls.

I'm ok with giving up type safety in exchange for just passing in a vi server reference...

What would be nice would be if LabVIEW supported a multi-part “Call by Referenceâ€, where one could fill the inputs of a function, pass it to an async process for execution, then read to outputs when it comes back.  That would be type-safe and very simple.  For async HTTP Get, you’d just need a reference to HTTP Get.  Might also simplify command messages as in the AF.

It'll work, but I'm pretty sure you'd have to do some casting to get the control panels to line up. See my posted example for a cleaner (IMO) implementation that's pretty similar to what you're suggesting.

Can’t open the example ‘cause I’m still on 2013, but there is no “casting†required in the method I’m thinking of.

For comparison, I made a quick version of the asynchronous HTTP Get (LabVIEW 2013; Messenger Library):

Async HTTP Get.zip

Since any really long running tasks (like wait on TCP or whatever) can't be effectively cancelled,

You can, if it is alright to kill the TCP reference from a parallel loop.