SQLite for OpenG

[mischl]

I tested the difference between the former insert template and the current one with BEGIN and COMMIT. It is quite obvious and about factor 300 to 400 faster. Thank you for the fantastic job implementing this!

BTW: saw that the project is hosted under bitbucket - excellent! in the readme there are two small faults: LabVIEW Versions: -> 2013 or later and Use VI Package Manager (2017 or later) according to the support page on lava

[Mrmas]

Hi,

Thanks for a wonderfull sqlite api drjdpowell.

Is there away to read out how much memory is used by sqlite with this library or a general status of sqlite?

 

Thanks

[drjdpowell]

They have this Sqlite function https://www.sqlite.org/c3ref/memory_highwater.html.  But I haven't implemented it (yet).

[mischl]

I want to optimize the upper part on the screenshot with the lower (to avoid too many transactions).

It seems that I am not aware of the correct handling at the moment. Thank you in advance for some instructions:

1. place the BEGIN and COMMIT outside. ok

2. the command in 2 and 3 is something like SELECT * FROM stueli WHERE d = 'xy'. Do I have to do that by bind or just as prepare statement on the picture?

3. is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

4. here the 'insert many row template' is used without the BEGIN and COMMIT. So it is the same question as in 3: how to handle the connection.

there is also a vi snippet attachet if this would be easier to explain something as one can draw in it directly with LV

thanks a lot

 

 

[LogMAN]

3 hours ago, mischl said:

I want to optimize the upper part on the screenshot with the lower (to avoid too many transactions).

My general advice for refactoring code is to do it step-by-step. Don't try to fix everything at once, as it will likely break your code (as it did in your case).

Note:

• I wasn't able to load your VI snippet. I'm not sure why though (either I'm to stupid to do it or Lava sanitizes images)
• My suggestions below are based on your original VI

Step 1 - Improve readability

Arrays and for loops

The outer for loop iterates over multiple arrays at the same time. This means that the loop will end after processing each element of the smallest array. I suggest you use one array for iteration and use the Array Index function to access elements from the other arrays. Here is an example:

You should play around with it to see the different outcomes. Note that indexing an element from an array of smaller size returns a default value (i.e. zero for numbers, an empty string for Strings and false for Boolean).

Shift registers

The outer for loop uses a shift register for the SQLite instance as well as the error cluster. The inner for loop, however, doesn't. This can be problematic for two reasons:

1) The SQLite instance in the inner for loop gets reset after each iteration.

2) An error in the inner for loop is reset after each iteration and only the last error is returned.

The solution depends on what you want to do. If you want to abort as soon as possible on any error, I suggest not using a shift register but making the for loop conditional instead (exit on error). Otherwise use shift registers on both loops.

Step 2 - Optimize your code

Use transaction outside of for loops

Transactions are very useful for improving performance if used correctly. In your second example, you already added the BEGIN and COMMIT Transaction using "MySavepoint". This is generally a good idea.

3 hours ago, mischl said:

1. place the BEGIN and COMMIT outside. ok 

Disable transactions on Execute SQL

The Execute SQL function has an optional input on the top which you can set to false in order to disable transactions. This will already improve performance considerably.

Answering questions

3 hours ago, mischl said:

2. the command in 2 and 3 is something like SELECT * FROM stueli WHERE d = 'xy'. Do I have to do that by bind or just as prepare statement on the picture?

SQL Prepare and SQL Finalize should be placed outside the for loop and you need to inject values for your parameters at some point (unless your SQL Statement is static, which it isn't according to your info above).

The "INSERT many rows Template code.vi" provided by the library (see Code Templates palette) visualizes that very well:

I highly suggest not using "SELECT *" but explicitly selecting particular columns instead. That way your SQL Statement becomes predictable.

3 hours ago, mischl said:

4. here the 'insert many row template' is used without the BEGIN and COMMIT. So it is the same question as in 3: how to handle the connection.

Here is an example that may or may not work (I can't test it for obvious reasons):

I suggest making a VI for each statement (without transactions of course). That way you can test things in isolation and be certain that it works.

3 hours ago, mischl said:

3. is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

I'm not sure what you mean by that. Can you explain what you mean in more detail?

 

[mischl]

thank you for the detailed feedback!

Quote

The outer for loop iterates over multiple arrays at the same time. This means that the loop will end after processing each element of the smallest array.

all the arrays has exactly the same size

Quote

Shift registers

thanks for that!

Quote

The Execute SQL function has an optional input on the top which you can set to false in order to disable transactions.

yes, but that is set already to false by default. isn't it?

Quote

SQL Prepare and SQL Finalize should be placed outside the for loop and you need to inject values for your parameters at some point (unless your SQL Statement is static, which it isn't according to your info above).

correct, it isn't static and therefore I will bind them according your hint.

 

3. is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

in the example, the 'SQL statement' ends after the reset at 2. at 3 and 4 further ''SQL statement' are in used and only one can be finalized. but there are three now. how to handle that?

the 'Prepare' at 3 needs a 'SQL Connection', it is the one chained from 2, or?

 

sorry, it seems that I forgot to attach the snippet. here is it. no problem if there are broken wires, but it might be easier to wire an example with the correct usage of 'SQL Connection in' and 'SQL statement in' than to explain them by text.

 

[LogMAN]

18 hours ago, mischl said:

yes, but that is set already to false by default. isn't it?

You are right, my bad.

18 hours ago, mischl said:

is the usage of the 'SQL Connection in' and therefore no 'SQL statement in' correct? Or how to use them?

Yes and no.

The SQL Prepare function creates the SQL Statement. It allows you to run the same SQL query multiple times, but more efficient. I've attached an example in my last post, using your SQL query:

SQL Prepare (second VI from left) receives an SQL Connection and the SQL query ("SELECT * FROM sueli WHERE d = ?"). Note, that it says "d = ?". The "?" will later be replaced in the loop by the actual value.

In the for loop, the first node does exactly that. It replaces "?" by an element from the list. Note that you can have multiple values, using multiple "?" in your query.

SQLite First Step (the next VI) executes the query once and you can read the results using the node that follows (Get Column String).

Before running the same query again, you must reset the SQL Statement. Note, that everything takes place within the loop.

When the loop ends, you need to finalize the SQL Statement or it stays in memory.

18 hours ago, mischl said:

in the example, the 'SQL statement' ends after the reset at 2. at 3 and 4 further ''SQL statement' are in used and only one can be finalized. but there are three now. how to handle that?

You must finalize each SQL Statement separately. In your example, everything is inside the loop, so you gain nothing by using prepared statements.

SQLite Prepare and SQLite Finalize must be placed outside the loop. I suggest you split those SQL Statements into separate VIs or it will be very confusing.

18 hours ago, mischl said:

the 'Prepare' at 3 needs a 'SQL Connection', it is the one chained from 2, or?

Yes, they all share the same connection.

18 hours ago, mischl said:

sorry, it seems that I forgot to attach the snippet. here is it. no problem if there are broken wires, but it might be easier to wire an example with the correct usage of 'SQL Connection in' and 'SQL statement in' than to explain them by text.

Actually, the snippet was attached, but Lava removes the source code of snippets right now (download the image and drag it into LabVIEW).

Can you copy the code into a new VI and post it here?

[LogMAN]

If you are set on doing multiple queries in the same loop, here is another example that might help understand how it should work. The VI is executable, using dummy data.

I've also attached the VI for LV2014, so you can play around with it.

 

Using Multiple Prepared Statements In One Loop.vi

[mischl]

Thank you @LogMAN for all your explanations and your efforts to explain it in depth - very appreciated!

I am going to edit it according the description and I am very confident that this optimizes the code by factors.

Now as the new work year has started it will last some days until there is enough time to do it. I will keep you informed.

 

Vielen Dank nochmals und Grüsse aus der Schweiz

[mischl]

thanks to the help of @LogMAN I was able to update the code into a transaction and it works like a charm and much faster, thank you again!

I was facing only one problem: '?-x' in a statement is processed as text and the question mark will not be exchanged with bind, use concat instead: ? || '-x'

 

[mischl]

just as a hint: the project page https://lavag.org/files/file/212-sqlite-library/ as well as the vi package information in the vipm states in the release notes that SQLite version 3.23.0 is integrated.

on dev as well as in an exe, select sqlite_version(); states 3.25.3

[LogMAN]

On 1/11/2019 at 8:30 AM, mischl said:

I was facing only one problem: '?-x' in a statement is processed as text and the question mark will not be exchanged with bind, use concat instead: ? || '-x'

Parameter bindings require you to follow specific syntax. It is explained here: https://www.sqlite.org/c3ref/bind_blob.html

Quote

In the SQL statement text input to sqlite3_prepare_v2() and its variants, literals may be replaced by a parameter that matches one of following templates:

• ?
• ?NNN
• :VVV
• @VVV
• $VVV

In the templates above, NNN represents an integer literal, and VVV represents an alphanumeric identifier. The values of these parameters (also called "host parameter names" or "SQL parameters") can be set using the sqlite3_bind_*() routines defined here.

'?-x' gets interpreted as parameter, which doesn't work. Two solutions come to mind:

1) Use the concatenation function like you suggested. This makes sense if the suffix is static for all parameters.

2) Do the concatenation before binding the parameter (i.e. in LV). If your suffix can change, make it part of the parameter.

Edited January 12, 2019 by LogMAN

[mischl]

how to set up a REGEXP? actually it leads to an error.

according to https://www.sqlite.org/lang_expr.html

The REGEXP operator is a special syntax for the regexp() user function. No regexp() user function is defined by default and so use of the REGEXP operator will normally result in an error message. If an application-defined SQL function named "regexp" is added at run-time, then the "X REGEXP Y" operator will be implemented as a call to "regexp(Y,X)".

[drjdpowell]

2 hours ago, mischl said:

how to set up a REGEXP?

You would need to compile a loadable extension to add that functionality.  As an example, I compiled extension-functions.c in order to allow advanced math functions like standard deviation, so you could have a look at that.    I note that there is a regexp.c  extension on the SQLite website, so you could try and compile that using the loadable extension instructions.  

Edited August 5, 2019 by drjdpowell

[Víctor M.]

On 9/19/2012 at 6:13 PM, SDietrich said:

Patch to allow the loading of extensions, e.g. SPATIALite

Hi all,

my current project demands a lightweight spatial database and so SQLite and SPATIALite came to my mind. Thanks to the excellent work of drjdpowell SQLite integrates smoothly in LabVIEW and with the attachedl VI loading extensions like SPATIALite becomes possible, too.

Simply execute it with "on/off = true" and then run a query like

SELECT load_extension('lib-spatialite-2.dll')[/CODE]

and you have your extension running.

So James, please include it in the next release. Thanks!

Sebastian

Enable Load Extension.viFetching info...

Hello,

I am working with LabVIEW and SQLite and I am using the SQLite library created by drjdpowell.

Moreover, I need to use SPATIALite as SDietrich used in the previous quoted post. To do so I am following the instructions of this Spatialite page and I am trying to include the file "mod_spatialite.dll" (SDietrich loaded "lib-spatialite-2.dll" but as this post is old I consider that now I should follow the instructions of the Spatialite web page). My question is, how can I load it?

I tried to do that with the VI shown in the next image:

where "BBDD Path" is the link to my SQLite database and "dll file Path" the link to the "mod_spatialite.dll" file. When I execute this VI I get the 402860 error after executing "Execute SQL.vi", and I also get the message "SQLite returned SQLITE_ERROR(1), see www.sqlite.org/rescode.html#error" in the error source string, but it does not give information about the error.

My VI is based on the "Load Extended Math Functions.vi" of the SQLite library created by drjdpowell, and it works when I try to load the file "extension-functions.dll" (which is the file loaded by "Load Extended Math Functions.vi") instead of the "mod_spatialite.dll" file.

In case that it helps, I am using LabVIEW 2019 and the version of SQLite and Spatialite shown in the next image:

 

Can anyone help me to solve this problem?

 

Thank you in advance!

Víctor

[drjdpowell]

First thing I would try is to follow the "Useful Hint" given in the spatialite link and try and load the extension via the sqlite3 command-line tool to see if it works there or gives a more informative error message.

[Víctor M.]

On 1/26/2020 at 10:52 AM, drjdpowell said:

First thing I would try is to follow the "Useful Hint" given in the spatialite link and try and load the extension via the sqlite3 command-line tool to see if it works there or gives a more informative error message.

Thank you for your quick response. I follow your instructions and I did several experiments but I cannot get my problem solved. These are the experiments performed:

• First experiment: loading "mod_spatialite.dll":

I wrote this line in the sqlite3 command-line tool: 

SELECT load_extension('mod_spatialite.dll');

As a result of this sentence I did not get any error message. It is interesting to note that the file "mod_spatialite.dll" was in the same folder than "sqlite3.exe". Nevertheless, I could not do that in LabVIEW, also after copying the DLL file in the same folder than the VI file.

 

• Second experiment: loading "mod_spatialite.dll" with its absolute path:

I wrote this line in the sqlite3 command-line tool: 

SELECT load_extension('D:\Program Files (x86)\National Instruments\LabVIEW 2019\VMM.lib\mod_spatialite-4.3.0a-win-amd64\mod_spatialite.dll');

As a result of this sentence I get this error message: "Error:    No se puede encontrar el módulo especificado" (in English it can be translated as "Cannot find the specified module"). It is insteresting to note that "'D:\Program Files (x86)\National Instruments\LabVIEW 2019\VMM.lib\mod_spatialite-4.3.0a-win-amd64\" is the absolute path of the location of the "mod_spatialite.dll" file.

 

• Third experiment: loading "extension-functions.dll" with its absolute path:

I wrote this line in the sqlite3 command-line tool: 

SELECT load_extension('D:\Program Files (x86)\National Instruments\LabVIEW 2019\vi.lib\drjdpowell\SQLite Library\SQL Connection\extension-functions.dll');

As a result of this sentence I get the same error message: "Error:    No se puede encontrar el módulo especificado". It is insteresting to note that:

1. "'D:\Program Files (x86)\National Instruments\LabVIEW 2019\vi.lib\drjdpowell\SQLite Library\SQL Connection\" is the absolute path of the location of the "extension_functions.dll" file.
2. This sentence worked properly in the LabVIEW code shown in my previous post when selecting this path in the "dll File Path" control.

 

• Fourth experiment: loading "extension-functions.dll" without its absolute path:

The last experiment consisted on trying to do not get an error message by repeating the procedure employed in the first experiment. To do so, I moved the "extension-functions.dll" file to the same folder than the "mod_spatialite.dll" and the "slqite3.exe" files. After that, I wrote this line in the sqlite3 command-line tool:

SELECT load_extension('extension_functions.dll');

As a result of this sentence I get again the same error message: "Error:    No se puede encontrar el módulo especificado".

 

To sum up, the description of the error obtained is "Error:    No se puede encontrar el módulo especificado" (in English it can be translated as "Cannot find the specified module"). Moreover, in the first experiment I was able to load the "mod_spatialite.dll" file without error, but it did not worked in LabVIEW. Finally, in the fourth experiment I get an error message even with a sentence that worked in LabVIEW.

 

Thank you in advance for your help!

Víctor

[drjdpowell]

Are you using matching bitness?  You have to use the same bitness, 32 versus 64, for all DLLs and LabVIEW.

[Víctor M.]

On 2/4/2020 at 11:45 PM, drjdpowell said:

Are you using matching bitness?  You have to use the same bitness, 32 versus 64, for all DLLs and LabVIEW.

First of all thank you again for your quick response. And sorry for the delay, but I was out and I could not check it until today.

I took some time to try to use the same bitness in all DLLs and LabVIEW as you suggested but it did not worked. I had the same error in all the trials I did.

Moreover, I was surprised because I repeated the same experiment than the explained in my previous post with the two extension functions DLLs (extension-functions.dll and extension-functions_64.dll, which I suppose that are the files for 32 and 64 files respectively) and I get no errors when trying to load any of these files. These are the sentences employed:

SELECT load_extension('D:\Program Files (x86)\National Instruments\LabVIEW 2019\vi.lib\drjdpowell\SQLite Library\SQL Connection\extension-functions.dll');

SELECT load_extension('D:\Program Files (x86)\National Instruments\LabVIEW 2019\vi.lib\drjdpowell\SQLite Library\SQL Connection\extension-functions_64.dll');

For these reasons I think there is another problem that is not allowing me to load the SpatiaLite DLL files.

 

Thank you in advance for your help!

Víctor

[anonimo anonimo]

Hi, I have some questions.

In the VI: "SQLite.lvlib:Connection.lvclass:Prepare.vi". What is the function of the input "SQL Statement subclass"?

On the other hand, I am trying to get the total number of rows after a select. Simply comment, that it is not posible to count the number of rows at the output, because the LIMIT function is used to limit the máximum number of rows at the output. 

First of all, I thought the input "SQL Statement subclass", the purpose was to put perform operations on a previous select, but I don't know if I am using this function correctly.

Thank a lot.

[drjdpowell]

That input is to allow one to (optionally) make a LVOOP child class of SQL Statement.  Normally one doesn't use this, but you can use a child class to add functionality.  For example, I have used it to define new Bind and Get Column methods that store data in a more compressed format.  But you probably can ignore it.

[anonimo anonimo]

Thank you very much, for the quick answer. In any case, would you have a simple example, that could illustrate this case?

Thank a lot.

[drjdpowell]

10 hours ago, anonimo anonimo said:

Thank you very much, for the quick answer. In any case, would you have a simple example, that could illustrate this case?

I have a project where Absorption Spectra are saved in an SQLite database.   Data is stored in a custom format so as to take up a minimum of space.  I have a child class of "Connection" that adds a lot of application-spec code, and a matching child of "SQL Statement.  Here is the VI that does a Prepare:

Here, multiple bits of information are passes into the child class, about the Timezone of the data and the offset time (times are stored as ms relative to an offset, to save space).

The "SQL Statement with Scaled Data" child class has several additional Bind and Get Column vis, which have the scaling in then, allowing the calling code to easily work with different quantities (Absorption or Transmission, Local or UTC Timestamps):

Here is the Bind Absorption method (the db actual contains a specially chosen 3-byte format that has to be converted to Absorption):

So basically, this is extending the Connection and SQL Statement classes to be more capably for a specific application.  Note that one does not have to do this, as one can use regular subVIs instead.  

[igorbounov]

drjdpowell,

it appeared so that I've got an installation of Labview 2018 (Full development system) for Linux (my main OS). The distribution, althogh full development sysytem, has no VIPM. Moreover, the most recent VIPM is VIPM 2017 which (for Linux) is shipped with Labview 2015 SP1 RTE and it doesn't see installed Labview 2018.

And - the final blow - I see that your SQLite Add-on for Labview is available only for Windows! For LV 2017 maximum!

Is there any workaround?

[drjdpowell]

Go get VIPM from JKI directly. 

I have not tested the library on Linux (other than Linux RT), but it should be able to work, if one has a copy of the sqlite3.so shared library installed in the right place.  

Does anyone else use this library on Linux?