Jump to content

[CR] SQLite Library


Recommended Posts

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.

Link to comment

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.

Link to comment

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.

  • Like 1
Link to comment

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

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.

 

HLNStLR.png

Edited by Stobber
Link to comment
  • 2 weeks later...

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.

Link to comment

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.

Link to comment
  • 2 weeks later...

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 :worshippy:

 

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

 

 

Link to comment

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.

Link to comment
  • 3 months later...
  • 3 weeks later...

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.

  • Like 1
Link to comment

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.

Link to comment

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?

Link to comment

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)

Link to comment
  • 1 month later...

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.  

Link to comment

@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":

attachicon.gifSQLite 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)

  • Like 1
Link to comment
  • 4 weeks later...
  • 2 months later...

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.