[CR] SQLite Library

[Olivier Jourdan]

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

[Olivier Jourdan]

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 September 3, 2020 by Olivier Jourdan
add info

[drjdpowell]

Unfortunately, the SQLite DLL uses bare pointers, rather than safe refnum references, and is not protected against using a pointer after the thing pointed to has been released.  If I were doing the library again, I would use a DVR to prevent calling functions on a dead pointer.

[kosist90]

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?

[G-CODE]

@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

[LogMAN]

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"

[drjdpowell]

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.

[drjdpowell]

20 hours ago, kosist90 said:

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

Have you used this?  Any fixes needed?

[kosist90]

Just now, drjdpowell said:

Have you used this?  Any fixes needed?

Yes, I was using it and it works fine; I guess no fixes are needed. It is possible to supply there cluster type, and read it back - so if you could publish it, that would be great. Thank you very much!

[drjdpowell]

On 10/8/2020 at 6:37 AM, LogMAN said:

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

This fix is on the Tools Network now.  JDP Science Common Utilities 1.2.1.12

[drjdpowell]

On 10/8/2020 at 8:37 AM, kosist90 said:

so if you could publish it, that would be great.

I've submitted a new version to the Tools Network.

[kosist90]

Thanks a lot for the new release!

[Landert_SCHJ]

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

[drjdpowell]

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.

[Landert_SCHJ]

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.

[MzazM]

 

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

[MikaelH]

I just have to add this fun vim using your (@drjdpowell) SQLite library.
It's LINQ for LabVIEW (I only spent 20 minutes on it so it's a bit rough)

 

LcArray_LINQ.vim

[MikaelH]

Here is an updated version and more complete version of this LINQ VI

 

LcArray_LINQ.vim

[Виктор Зиновьев]

Hello,

Thank you for plugin!

I need to use Shared-Cache Mode. How to set it on? As I suppose i need to set it when I opening the db file.

Thanks in advance!

[drjdpowell]

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

[Виктор Зиновьев]

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. 

 

[drjdpowell]

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

[PyLabVIEW73]

Good afternoon fellow LabVIEWers....

I am having an issue with the SQLite toolkit on a Linux RT setup. 

The chassis is an NI-cRIO-9057 and I used opkg to update packages and then install sqlite3.

The dialog said that it installed SQLite3 to root and I confirmed and have a screenshot of the files in the proper location.

When I try to run an open and read my SQLite Database I keep getting an error 7 that it can't find the shared library it needs. Strange because the files are in the exact directory where the error code says it is looking for them.

 

Not sure what I am doing wrong. Any help would be greatly appreciated! I know there was some discussion regarding the library locations on a Linux RT quite a few years back.

 

Thanks!

 

Steven

 

[Rolf Kalbermatter]

On 11/1/2022 at 6:06 PM, PyLabVIEW73 said:

When I try to run an open and read my SQLite Database I keep getting an error 7 that it can't find the shared library it needs. Strange because the files are in the exact directory where the error code says it is looking for them.

 

Not sure what I am doing wrong. Any help would be greatly appreciated! I know there was some discussion regarding the library locations on a Linux RT quite a few years back.

That's usually a dependency error. Shared libraries are often not self contained but reference other shared libraries from other packages and to make matters worse sometimes also minimum versions or even specific versions of them. Usually a package should contain such dependencies and unless you use special command line options to tell the package manager to oppress dependency handling, should attempt to install them automatically. 

But errors do happen even for package creators and they might have forgotten to include a dependency. Another option might be that you used the root account when installing it, making the shared library effectively only accessible for root. On Linux it is not enough to verify that a file is there, you also need to check its access rights. A LabVIEW executable runs under the local lvuser account on the cRIO. If your file access rights aren't set to include both the read and executable flags for at least the local user group, your LabVIEW application can't load and execute the shared library, no matter that it is there.

 

 

[drjdpowell]

SQLite3.so has no dependencies, so I am suspecting access rights.

Edited November 14, 2022 by drjdpowell