147 replies to this topic

[ShaunR]

Name: SQLite API
Submitter: ShaunR
Submitted: 05 Oct 2010
File Updated: 05 Jan 2011
Category: Database & File IO
LabVIEW Version: 2009
License Type: Other (included with download)

SQLIte API


This LAVA Code Repository Download is no longer distributed or supported by LAVA.

Please follow this link for further support.
Description:
This is a Labview API wrapper for the SQLite dll.

From the authors of SQLite:
"SQLite is a in-process library that implements a self-contained,
serverless, zero-configuration, transactional SQL database engine.
The code for SQLite is in the public domain and is thus free for use
for any purpose, commercial or private".
More information can be found on the SQLite website http://www.sqlite.org.

The LabView API contains all the basic SQLite features but it is not a full implementation.
However, it is comprehensive enough to satisfy most application requirements for
a databse enabled application and the featurelist will grow with each new release.

Currently supported features include:
Creating, deleting, listing and renaming tables and columns.
Selecting, inserting, updating and deleting records.
Retrieving SQLite system informaion.
"Onlne" database backup.
"On Insert", "On Delete" and "On Update" Triggers.
File Import.
Query Saving.

Installation:
Unzip to a directory of your choice.
Install the pallet menu file (.mnu) provided.

Required Packages:
Labview (Win x32, x64,) 9.0 or greater.
SQLite DLL (Included).

Supported Environments.
Windows x32, x64

Known Issues.
Cannot use semicolons ";" in values when using "Query Transaction" polymorphic instance.
Workaround: Replace semicolons with another character (e.g. tilda "~") or use the standard "Query" or "Query By Ref" VIs.

Versioning:
Current version 1.2.1


New Feature: Added "Conflict Clause" to SQLite_Insert and SQLite_Insert Table.vi (allow replace, fail ect)
Performance: Changed "SQLite_Prepare.vi" and "SQLite_Bind.vi" to pass data as a U8 array rather than string.
Performance: Changed "Insert" (SQLite_Insert Row.vi) to use "Insert Table". (All inserts now use prepared statements.
Performance: Changed some low level VIs to re-entrant for better concurrency.
Bug Fix: Changed all polymorphic API VIs to show instance icon.
(some context help for VI was not being displayed when set to polymorphic VIs' icon)
Bug Fix: Exposed "Distinct" boolean on "SQLite_Select.vi". (No terminal on compane).
Bug Fix: Replaced some cursor "Unset Busy" with "Set Busy" in SQLite DB Manager.
Bug Fix: Changed "SQLite_Bind.vi" to force sqlite dll to use local copy of data.
(Fixes reported bug in LVx32 insering the same data in multiple columns).
Changes: Changed icon on "Insert Table" to make more visually discerning from "Insert".

Version 1.2.0.0
See changelog.txt.

Special Thanks to
Gepponline
MattW


Contact:
PM ShaunR on lavag.org (http://www.lavag.org)

This LAVA Code Repository Download is no longer distributed or supported by LAVA.


Please follow this link for further support.

[jcarmody]

This is wonderful. I've begun implementing it in a current project and need to ask about the Query Transaction function.



I was excited to read this comment because one of our current systems takes forever to write to our SQL Server DB. (~30 seconds. I imagine it's executing one SQL INSERT for each step it's reporting.) So, I thought I'd test this in SQLite:



As I played with it I came up with this to see the effect of larger SQL strings:



I've tried each of the locking modes (immediate, deferred and exclusive) but it's taking almost three seconds to complete only 5000 SQL INSERT statements. This is not as fast as the documentation suggests.

What am I doing wrong?

[ShaunR]

This is wonderful. I've begun implementing it in a current project and need to ask about the Query Transaction function.

I was excited to read this comment because one of our current systems takes forever to write to our SQL Server DB. (~30 seconds. I imagine it's executing one SQL INSERT for each step it's reporting.) So, I thought I'd test this in SQLite:

As I played with it I came up with this to see the effect of larger SQL strings:

I've tried each of the locking modes (immediate, deferred and exclusive) but it's taking almost three seconds to complete only 5000 SQL INSERT statements. This is not as fast as the documentation suggests.

What am I doing wrong?

Its not the number of statements. Its the number of "TRANSACTIONS".

When you use the "INSERT" VI in a for loop, each iteration is 1 transaction. Additionally, the insert vi opens the file before the transaction and closes it afterwards for a slightly safer implementation. The insert is for convenience and a bit more safety.

If you want blistering speed. You need to use the "transaction query.vi". this will wrap your Insert statements in the "BEGIN...END" and execute all the statements as 1 transaction.

With the test below. I achieved 10,000 inserts in 609 ms.

[jcarmody]

Its not the number of statements. Its the number of "TRANSACTIONS".

I understood that part (my snippet uses the transaction query VI), it turns out that my problem was that I was querying the database for all records at the end of all the INSERTs. Is there any way to speed up a SELECT?

[ShaunR]

I understood that part (my snippet uses the transaction query VI), it turns out that my problem was that I was querying the database for all records at the end of all the INSERTs. Is there any way to speed up a SELECT?

Possibly. This release basically does a "GetRow" and has to iterate for each row. There is another method, but it isn't implemented yet. (Still trying to figure out how to do it....if it is possible in LV).

Do you have a real-time target that uses ETS?

Edited by ShaunR, 06 October 2010 - 12:56 PM.

[jcarmody]

Do you have a real-time target that uses ETS?

No.

[asbo]

I wish I had an excuse to play around with this, it looks sweet.

[Clio75]

[François Normandin]

I wish I had an excuse to play around with this, it looks sweet.

I concur. Next time I ask myself whether or not to choose a database implementation or simple binary files to store data, I'll try to find some time to try these first...

[ShaunR]

I wish I had an excuse to play around with this, it looks sweet.

You need an excuse? I thought it was our nature to play with this sort of stuff

I concur. Next time I ask myself whether or not to choose a database implementation or simple binary files to store data, I'll try to find some time to try these first...

Keep it in your palette and I'm sure you'll think of something.

[asbo]

You need an excuse? I thought it was our nature to play with this sort of stuff

Haha, I've resisted the urge to install LV at home so far. Some might say that's a bad thing, but I still need that mental division

[ShaunR]

Some might say that's a bad thing,

Others might say you have a life

[Gepponline]

Hi!
i'm porting this toolkit in Ubuntu Linux with version 8.5 of Labview.
Some polimorphic vi needs to be modifyied to work correctly such as SQLLite_Delete column.vi and SQLLite_Rename column.vi it said that "you cannot use "SQLLite_Rename column.vi" recursively, so i deleted them
After that you place a conditional disabled structure for Unix but to make it work i had to change that condition from OS==Unix to TARGET_Type==Unix.
I try changing it in OS==Linux as i see in the conditional Disable Symbols, but it didn't work the first time it said that OS was undefined. now i start labview again and it seems to work...what is better? OS or Target_Type?
Instead, TARGET_BITNESS is an unexisting variable.

[ShaunR]

Hi!
i'm porting this toolkit in Ubuntu Linux with version 8.5 of Labview.

Swwet.
I did include aLinux x32 library but didn't advertise the fact because it was untested.

Some polimorphic vi needs to be modifyied to work correctly such as SQLLite_Delete column.vi and SQLLite_Rename column.vi it said that "you cannot use "SQLLite_Rename column.vi" recursively, so i deleted them

I'm surprised by this. What said it? Those two function are actually a trick. They just use SQL to copy the data to another table, delete then recreate the original and copy all the data back. SQLite doesn't have delete or rename columns function, but it is needed. To get this error, one of the other VIs would have to include it. Do you have an example use that shows this error?

After that you place a conditional disabled structure for Unix but to make it work i had to change that condition from OS==Unix to TARGET_Type==Unix.
I try changing it in OS==Linux as i see in the conditional Disable Symbols, but it didn't work the first time it said that OS was undefined. now i start labview again and it seems to work...what is better? OS or Target_Type?
Instead, TARGET_BITNESS is an unexisting variable.

OS and bitness are only available from within a Project. But Target Type is available all the time. Did you open them from within a project? Or just stand-alone VIs? Try adding them to a project.
The problem is that linux, like windows, also has x32 and x64. We need the bitness to choose the appropriately compiled DLL.


The choice of whether to use OS or Target Type was arbitrary. They both support the same things (more or less). I leaned towards OS because that is the one that NI use in their example.

[Mark Balla]

Certified 10-9-2010
Placed in Database & File IO.

[Gepponline]

I'm surprised by this. What said it? Those two function are actually a trick. They just use SQL to copy the data to another table, delete then recreate the original and copy all the data back. SQLite doesn't have delete or rename columns function, but it is needed. To get this error, one of the other VIs would have to include it. Do you have an example use that shows this error?

I Didn't make an example i only created a new blank vi and place a Table.vi inside.
Could it be a converting from LV9 to LV 8.5 problem? To make this i had to convert to 8.6 first and then with LV 8.6 to 8.5...maybe something get lost inthis step.

OS and bitness are only available from within a Project. But Target Type is available all the time. Did you open them from within a project? Or just stand-alone VIs? Try adding them to a project.
The problem is that linux, like windows, also has x32 and x64. We need the bitness to choose the appropriately compiled DLL.
The choice of whether to use OS or Target Type was arbitrary. They both support the same things (more or less). I leaned towards OS because that is the one that NI use in their example.

I created a New project but the OS variable was not "Unix" but "Linux" and the bitness variable did not exists.


Now i'm trying reconverting Vis from LV9 to LV8.5 and using a project, i'll let you know if i find something new

[Gepponline]

Ok, i find that After converting Vi's to 8.6, Labview autodisconnect rename column and Delete column to prevent recursion.
There is a SQLite_Query.VI that is recursive and cause problem with versions 8.6. SQLlite_query3.vi cause the problem.

[ShaunR]

I Didn't make an example i only created a new blank vi and place a Table.vi inside.
Could it be a converting from LV9 to LV 8.5 problem? To make this i had to convert to 8.6 first and then with LV 8.6 to 8.5...maybe something get lost inthis step.



I created a New project but the OS variable was not "Unix" but "Linux" and the bitness variable did not exists.


Now i'm trying reconverting Vis from LV9 to LV8.5 and using a project, i'll let you know if i find something new

I believe TARGET_BITNESS was first introduced in LV 8.6. Do you have any difficulties in LV 9?

[Gepponline]

I believe TARGET_BITNESS was first introduced in LV 8.6. Do you have any difficulties in LV 9?

No I don't

...even if i cannot find any TARGET_BITNESS variable in the project...where is it placed?

Oh, QUery3.vi, rename column and dlete column report the same recursion problem.

Edited by Gepponline, 11 October 2010 - 09:02 AM.

[ShaunR]

No I don't

...even if i cannot find any TARGET_BITNESS variable in the project...where is it placed?

Oh, QUery3.vi, rename column and dlete column report the same recursion problem.

It's an in-built definition the same as "TARGET_TYPE".

I'm a bit stumped as to why LV9.0 on Linux cannot support recursive VI's when Windows x64 and x32 don't seem to have an issue. You could remove them from the polymorphic table VI and add them separately to your palette since it seems it's the polymorphic instance that is causing the problem.

Unfortunately, Linux documentation a is bit lacking. I have several VMWare Linux images, but was unable to find a downloadable trial version of LV9.0 for Linux, otherwise I would have tried it. The NI website only has windows and Mac. If you go to their Linux download section you only get a windows EXE installer although it says its for all OSs .