Jump to content

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

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

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

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

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

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

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

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

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

The error description from the log file is:

 

 

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

Link to post
Share on other sites

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

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

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