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

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

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'

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

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.

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

We have an existing exe out in the field with the appropriate dll folder beside - I guess the version is about two years old. There are several installations.

If now a new package built with a newer wrapper version in LabVIEW will be distributed, but a user replaces only the exe and misses the dll, is there a problem with the existing functionality (nothing new on the database part has been added)? Is it backward compatible or should the SQLite version - which is available and goes hand in hand with the wrapper - be checked?

thanks

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

Quote

Any objections to moving SQLite LabVIEW up to LabVIEW 2013 (from the current 2011)?

late answer....

LabVIEW 2014 will be important to support a long time as it is the version of LabVIEW Home...

Thank you for the feedback and help James! I am going to examine SQLite Example 1 -- Create Table.vi

Quote

Did you wrap your INSERTs in a transaction?  

It was made that way, like from the template

A great toolkit, thanks a lot!

When I used it the last time a year ago I had the problem that inserting a csv file took a long time due to the whole insert loops in labview compared to the import a table from a csv command in the DB Browser for SQLite.

Wouldn't it be possible to have a vi that imports a table from a csv directly with the dll what would be comparable in speed to the DB Browser tool?

thanks in advance