[CR] SQLite Library

[Stobber]

Rolf beat me to it. One addition: SQLite does have a defined epoch and constraints. Actually, it has several epochs depending on the datatype of each time value (see https://www.sqlite.org/datatype3.html#datetime ).Their implementation of helper functions that  manipulate time using an old C library is described at the bottom of this page: http://www.sqlite.org/lang_datefunc.html

 

Note that trying to use any of the library functions on that page will result in storing time as a string instead of the column affinity, unless you cast the returned value.

 

Since your API provides access to SQLite from LV, as Rolf said, you have to change LV's understanding of time to match SQLite's. This allows any other APIs/apps to retrieve time from the db under the common (SQLite) definition so they can convert it to their own definitions.

[drjdpowell]

Since your API provides access to SQLite from LV, as Rolf said, you have to change LV's understanding of time to match SQLite's. This allows any other APIs/apps to retrieve time from the db under the common (SQLite) definition so they can convert it to their own definitions.

Would that help you?   They use Integers to store UNIX time, which has no partial seconds.   I can certainly add a Bind Timestamp (Integer) and make Get Column as Timestamp accept integers.  

 

But how common is Julian days since 4714 BC?   Not sure if that should override LabVIEW’s definition of a DBL timestamp, especially as this would be a breaking change in the library.

[Rolf Kalbermatter]

Would that help you?   They use Integers to store UNIX time, which has no partial seconds.   I can certainly add a Bind Timestamp (Integer) and make Get Column as Timestamp accept integers.  

 

But how common is Julian days since 4714 BC?   Not sure if that should override LabVIEW’s definition of a DBL timestamp, especially as this would be a breaking change in the library.

 

While it's a breaking change to modify this now, the original of writing seconds since january 1. 1904 as a timestamp to SQLLite is truely broken too. So I would investigate if you can demote the current method as depreciated and remove it from the palettes and add a new one that does the right thing. Existing applications using that function will still work as they used too, and still use a broken timestamp while new developments would use the right method.

 

Also document that difference somewhere for anyone wanting to read databases written with the old method to use the depreciated method for reading, but a string reminder to not use it for new development.

[Stobber]

I was mistaken when I said DBL should convert to Unix time in my bug report. It will still help me (and everyone who writes to a db using LV and reads from it using other tools/languages) to convert LV time to the correct format for whichever datatype is used when storing the value.

• Bind Timestamp (Real) should convert to Gregorian time
• Bind Timestamp (Text) should convert to ISO8601
• Bind Timestamp should stay LV time, since it's a BLOB
• I'd like it if you added Bind TImestamp (Int) and wrote Unix time with that

Edit: My use case is to write to the db using a LV app on cRIO, then read from it using modern data analysis and visualization tools. Using LV to display and analyze datalogs is like cooking dinner with a hammer and a campfire. So this fix will very much help me. Right now I'm using Bind Integer as a workaround.

Edited July 22, 2015 by Stobber

[Stobber]

Edit: The screenshot I had attached didn't convert from seconds to days. Removing it.

 

Edit2: Second screenshot had another bug. Fixed it, tested thoroughly, adding "final" version.

 

Referencing Wikipedia: (https://en.wikipedia.org/wiki/Julian_day

 

Here's an attempt at converting LV time to SQLite time for type REAL.

 

Edited July 30, 2015 by Stobber

[Yair]

Excel has a default time definition of number of days since January 1, 1900, although due to a mishap when defining that epoch and forgetting about that 1900 wasn't a leap year, the real epoch starts on midnight December 31, 1899.

 

This was actually there back in Lotus123 and is apparently by design, as it allowed the program to calculate leap years by using a much simpler algorithm at the cost of being wrong for 1900 and 2100. Probably a minor price to pay for a program designed in the early to mid 80's.

[Rolf Kalbermatter]

This was actually there back in Lotus123 and is apparently by design, as it allowed the program to calculate leap years by using a much simpler algorithm at the cost of being wrong for 1900 and 2100. Probably a minor price to pay for a program designed in the early to mid 80's.

 

Well I guess two modulo-remainder operations where a big deal back in the early eighties  . Apple smartly sidestepped that issue by choosing 1904 as their epoch for MacOS, and yes I'm sure that was not only to be different than Lotus. As to if that was a deliberate decision back then or more negligence by the gals and guys at Lotus will probably never be found out for sure. It may also just have been something they "inheritet" from VisiCalc.

[Chris]

Firstly I'd like to say what a great package - I don't think I'll ever want to go back to using text files again

 

I just have some questions on the update and inclusion (in an exe) of the sqlite.dll.

 

On my computer it is located in the following directory....

 

C:\Program Files\National Instruments\LabVIEW 2013\vi.lib\drjdpowell\SQLite Library\SQL Connection\sqlite3.dll (version 3.8.7.4).

 

Now if I wanted to use the latest sqlite3.dll (which can be downloaded from http://www.sqlite.org/download.html) is it OK to just overwrite the version in your vi.lib folder?  I tried version 3.8.11.1 and it all seemed to work OK, but I just want to make sure I'm not doing anything wrong.

 

Also when building an installer using your SQLite Library where does the sqlite3.dll get installed - in the root directory of the executable?

 

Thanks Chris

 

 

[drjdpowell]

I tried version 3.8.11.1 and it all seemed to work OK, but I just want to make sure I'm not doing anything wrong.

That’s fine.  I include the latest dll at time of package release for convenience, but you should be able to use any version.

 

Also when building an installer using your SQLite Library where does the sqlite3.dll get installed - in the root directory of the executable?

When building the EXE it goes in the “data†directory of the executable, and the “Open†vi looks for it there.   This should happen automatically, but if not you can mark it as “Always Include†in the EXE builder.

[drjdpowell]

A beta version of 1.6 is posted here.  If you ignore the newest features, you could use this in production code; it has the latest SQLite 3.9.2, including the interesting JSON1 extension.

[3esmit]

Can you add by default the function last insert rowid? 
I always add this function in your API.

 

https://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid

[drjdpowell]

Can you add by default the function last insert rowid? 

I always add this function in your API.

 

https://www.sqlite.org/capi3ref.html#sqlite3_last_insert_rowid

If you look in the latest beta version you’ll find it (though it’s not in the palettes, it is in the Connection class).  

 

PS> Some notes:

 

— one can also use the last_row_ID() SQL function

— Be careful that you aren’t executing multiple INSERT statements in parallel on the same Connection, as you might mixup the rowIDs.

— note the existence of WITHOUT RowID tables, which avoids the need to determine the auto assigned rowID.

[Stobber]

When throwing an error from the API, could you add the SQLite error code to the error description? My client got error 402864 last night, and it took a lot of digging to figure out what that meant. The code doesn't resolve in the Explain Error dialog, and even if it did, I'm more interested in the code returned by the sqlite3 DLL because I use that code to search their documentation when troubleshooting.

[drjdpowell]

When throwing an error from the API, could you add the SQLite error code to the error description? My client got error 402864 last night, and it took a lot of digging to figure out what that meant. The code doesn't resolve in the Explain Error dialog, and even if it did, I'm more interested in the code returned by the sqlite3 DLL because I use that code to search their documentation when troubleshooting.

 

I add 402859 to the SQLite error code (apologies for that number, but those are the range of codes assigned to me by NI).   I’m currently calling sqlite2_errmsg() to get an error description from the SQLite dll itself.   And the message should have also contained either the database file, or SQL statement on which the error occurred.  Was this information not in the error?

[Stobber]

The error description from the log file is:

 

 

Error 402864 occurred at redacted.lvlib:redacted.vi on "INSERT OR IGNORE INTO redacted (  redacted, redacted, redacted)  VALUES (?, ?, ?);" Possible reason(s): database is locked

[drjdpowell]

Ah, I see.  We need something like

 

Error 402864, SQLITE_BUSY(5), occurred at redacted.lvlib:redacted.vi on "INSERT OR IGNORE INTO redacted (  redacted, redacted, redacted)  VALUES (?, ?, ?);" Possible reason(s): database is locked.  See https://www.sqlite.org/rescode.html#busy.

 

 

Edit> can’t do the above, but how about:

 

Error 402864 occurred at redacted.lvlib:redacted.vi on "INSERT OR IGNORE INTO redacted (  redacted, redacted, redacted)  VALUES (?, ?, ?);"

Possible reason(s):
SQLITE_BUSY(5) database is locked (see https://www.sqlite.org/rescode.html)

[Stobber]

Yeah, that'd be great!

[Mrmas]

Hi, very nice library here. I am very impressed with it and sqlite. 

 

I was wondering would this library work on labview linux RT? 

[drjdpowell]

Hi, very nice library here. I am very impressed with it and sqlite. 

 

I was wondering would this library work on labview linux RT? 

 

I don’t have a system to test, but the library attempts to find the copy of libsqlite.so if it is installer on the Linux RT system (see the series of posts starting here).  Stobber can give you better information, as he was trying to do this.  

[Stobber]

Mrmas, the link drjdpowell gave above will help you tell the API where your SQLite library is. You'll have to install SQLite using NI's opkg server. if you want to use the very latest version of SQLite instead of the one NI's currently distributing, you can follow these instructions.

[LogMAN]

@drjdpowell: I installed the latest version of your library (1.6.2). There is a new VI called "SQLite Database Path" which is missing the output terminal for "Last INSERT RowID":

[drjdpowell]

@drjdpowell: I installed the latest version of your library (1.6.2). There is a new VI called "SQLite Database Path" which is missing the output terminal for "Last INSERT RowID":

SQLite Database Path missing terminal.png

 

Oh dear, that was amateurish. This is an unfinished VI that I obviously never tested.   Sorry about that.  I’ve only been using WITHOUT ROWID tables recently, and in the past I’ve only generated the rowID in LabVIEW and thus never used this dll call.  

 

Added later> fixed 1.6.3 version now in the LAVA-CR (note, LAVA will often have a later version than on the LabVIEW Tools Network)

[Mrmas]

Thank you Dr and Stobber, I will try it out.

[drjdpowell]

Introductory video now available.   I’ll try to make some videos on more advanced techniques when I get the chance.

[Stobber]

How hard might it be to add support for this feature? https://sqlite.org/c3ref/update_hook.html

I'm really, really interested in using it to avoid polling for changes to a db that gets written by other processes.