Jump to content

[CR] SQLite Library


Recommended Posts

4 minutes ago, drjdpowell said:

I've been burned by Semaphores in the past and won't use them, but I'll use some other method.

Semaphore was the most natural solution to implement for me, but I made the fix for my use case, and I can see issues in some cases. Building tools for everyone is not that simple.
Anyway, I take the opportunity of this message to thank you for the great job you've been with this tool 👍.

Link to comment
  • 4 weeks later...

Hi there,

I'm still using attributes feature of the SQLite Lib and come across a random issue. I'm sometimes stuck in the Reset function of the Get Attribute. VI cannot be aborted and the only solution I have is to kill LabVIEW to get out of this.

It guess something goes wrong with SQLite ref, but can't figure out what. I open the connection at the application start up and close the connection at the end. Issue occurs out of these 2 different steps.

Any idea on what's happening?

 

[EDIT] I think, I found the bug in my code where I was closing the connection. Anyway, calling SQLite functions on a closed reference causes the code to be stuck in the SQLite dll function calls... Good to know to avoid a big problem 😆)

Edited by Olivier Jourdan
add info
Link to comment
  • 1 month later...
On 7/10/2020 at 4:56 PM, drjdpowell said:

@kosist90

Here is a beta version, that you can test if you like:

drjdpowell_lib_sqlite_labview-1.12.0.89.vip 4.36 MB · 3 downloads

It's not implemented exactly the same way as your example, but it should work in the same places.  See the example "SQLite Parameters in Execute".

@drjdpowell, let me ask whether do you plan to release this version as the new release?

Link to comment

@drjdpowell, thanks for all the work you put into this really useful library. I noticed you have some timestamp conversion utilities that the SQLite library uses. I am confused by some behavior I am seeing when converting a string timestamp with a local time offset. Can you look at the attached screenshot of that function? Maybe this is expected behavior and I'm not understanding something.

Thanks,

Eric

Text to Timestamp function.png

Link to comment

You need to take time zones into account (UTC+14:00 in your case).

By default the Scan From String function returns time stamps in local time if you use the "%<>T" format specifier. This is mentioned under section Format Specifier Examples, here: https://zone.ni.com/reference/en-XX/help/371361R-01/lvconcepts/format_specifier_syntax/

You'll get the right response if you use the "%^<>T" format specifier: "%[+-]%^<%H:%M>T"

Link to comment

I suffer from the handicap of being located in UTC+00:00, which makes it difficult for me to spot bugs in local to UTC conversion.  I have to remember to switch my computer to the wrong timezone to test things, and I obviously didn't do that here.  Thanks for spotting this. The format specifier should indeed have a ^ in it.

Link to comment
  • 1 month later...
On 7/10/2020 at 4:56 PM, drjdpowell said:

Here is a beta version, that you can test if you like:

drjdpowell_lib_sqlite_labview-1.12.0.89.vip 4.36 MB · 7 downloads

It's not implemented exactly the same way as your example, but it should work in the same places.  See the example "SQLite Parameters in Execute".

In the meantime this ist the official version though. I'm trying to run some of the examples you made "SQLite Parameters in Execute.vi" and "... Timestamps.vi" on my Industrial Controller IC-3120, running Linux RT but they require quite big changes. Before I get into all the SQL programming I want to make sure that I can use this library on my realtime target with large clusters (as your example shows well enough). My question is now if the problems I have could be due to LV2017 or how else I can find out which SQL commands work on the target and which don't? I used the ni packages opkg install sqlite3 which installed libsqlite3.so.0 and ...0.8.6 (both don't work with the unchanged example).1359500030_SQLiteTimestampscomparison.png.f8382a34efe38369633a757d1cc1db7d.png

Link to comment

Try just taking out the generated column.  Those were added recently, in SQLite 3.31.0.  If the NI Linux package for SQLite is an earlier version than that then it will throw an error at the Create Table statement.

What error was given?   The error should have given useful debug info.

Link to comment
38 minutes ago, drjdpowell said:

What error was given?   The error should have given useful debug info.

The error from example "... Parameters in Execute.vi" was due to a whitespace in the CREATE TABLE section:

CREATE TABLE IF NOT EXISTS Log (Time, Value,Note);

didn't work

CREATE TABLE IF NOT EXISTS Log (Time,Value,Note);

works now. Apparently Linux is sensitive to whitespace... And YES, removing the generated column did also solve for the problem in the "...Timestamps.vi" Example.
Thank you!! I would never have expected to get solved this issue today! Now I have to get into SQL, this will take probably more than half a day 😬.

For your information: To run the examples on a RT target, apart from the problems discussed, one has to remove the file dialog VI with a path "/home/lvuser/Parameters Example.db" for example, where you have write access from the application.

Link to comment
  • 1 month later...

 

On 11/27/2020 at 12:54 PM, drjdpowell said:

Try just taking out the generated column.  Those were added recently, in SQLite 3.31.0.  If the NI Linux package for SQLite is an earlier version than that then it will throw an error at the Create Table statement.

What error was given?   The error should have given useful debug info.

here a post on how to update to the last sql libraries (3.34) to run successfully that example on NI RT

  • Thanks 1
Link to comment
  • 8 months later...
  • 2 weeks later...
  • 4 months later...
7 hours ago, drjdpowell said:

I have never tried shard cache mode.  Are you sure you actually need it?  If so, try using a URI path to set it following this:

https://www.sqlite.org/c3ref/open.html#urifilenameexamples

Hello,

I tried to add &cache=shared or with ? to the file's path but it does not work, it only creates a new db file with this name, as I suppose I need to create something in this vi. Could you please write what to add there? One of my computer will ask a server in the internet and save data to db and other computers will read this db by using LAN. Some times I think that it is better to use postgre. 

 

image.png.169e3f8af13317afc7f85bd0e0536f5f.png

Link to comment
18 hours ago, Виктор Зиновьев said:

I tried to add &cache=shared or with ? to the file's path but it does not work

Did you add "file:" to the front to make a URI path.  And what do you mean by "does not work"?  What are you expecting shared-cache mode to do exactly?  Shared Cache has nothing to do with multiple computers accessing a db (and yes, that is something you would be better off using Postgres).

Link to comment

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.