[CR] SQLite API

[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 October 6, 2010 by ShaunR

[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]

:worshippy: :worshippy: :worshippy: :worshippy:

[Francois 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 October 11, 2010 by Gepponline

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

[Gepponline]

maybe there is a a little misunderstanding.

i don't have LV 9.0 for linux, i have LV 8.5 for linux. That's why i have to convert VI's forst to LV 8.6 in windows and then to 8.5 for linux.

[ShaunR]

maybe there is a a little misunderstanding.

i don't have LV 9.0 for linux, i have LV 8.5 for linux. That's why i have to convert VI's forst to LV 8.6 in windows and then to 8.5 for linux.

IC.

Well that explains the recursion issue you are seeing. Prior to LV9.0 recursion was only supported by opening a ref and using a call vi node. But its nice to know with a bit of hacking it can work on older LV versions......even Linux . I presume (since you haven't mentioned any so far) that there are no issues in your windows LV 9?

[Gepponline]

IC.

Well that explains the recursion issue you are seeing. Prior to LV9.0 recursion was only supported by opening a ref and using a call vi node. But its nice to know with a bit of hacking it can work on older LV versions......even Linux . I presume (since you haven't mentioned any so far) that there are no issues in your windows LV 9?

I confirm, no issues with LV9. And now that i have deleted recursive VI, no issues even with LV 8.5. Maybe i'll convert the recursive VIs as you said with call vi nodes to have all the tool working, but for the moment i only need to make some simple query and i don't need nothing more

[jzoller]

Fun stuff, thanks Shaun!

It's worth noting that attempting to write a raw "\00" causes a glitch in the low level prepare (that calls sqlite3_prepare_v2) vi. I'm guessing it's due to:

If the nByte argument is less than zero, then zSql is read up to the first zero terminator. If nByte is non-negative, then it is the maximum number of bytes read from zSql. When nByte is non-negative, the zSql string ends at either the first '\000' or '\u0000' character or the nByte-th byte, whichever comes first. If the caller knows that the supplied string is nul-terminated, then there is a small performance advantage to be gained by passing an nByte parameter that is equal to the number of bytes in the input string including the nul-terminator bytes.

The statement size is wired as a -1 by default. I'm tinkering with different sizes now, but would take any ideas out there.

Thanks,

Joe Z.

Edited October 11, 2010 by jzoller

[ShaunR]

I confirm, no issues with LV9. And now that i have deleted recursive VI, no issues even with LV 8.5. Maybe i'll convert the recursive VIs as you said with call vi nodes to have all the tool working, but for the moment i only need to make some simple query and i don't need nothing more

You probably only need to take them out of the polymorphic VI. Rename column calls delete column and they are both in the table polymorphic vi.

Fun stuff, thanks Shaun!

It's worth noting that attempting to write a raw "\00" causes a glitch in the low level prepare (sqlite3_prepare_v2) vi. I'm guessing it's due to:

The statement size is wired as a -1 by default. I'm tinkering with different sizes now, but would take any ideas out there.

Thanks,

Joe Z.

What sort of glitch?

-1 is much safer since if you supply a normal Labview string, you would have to add 1, however, if you supplied a null terminated string, you mustn't add the 1.The logic involved in detecting null chars outweighs any performance improvements you may get. If its better performance you are after, you'll have to wait for Version 1.1

However. If you are trying to insert a null char into the database. You will have problems since c strings are null terminated, So when you pass the string (even with the length) Labview needs to convert it from a Labview string (which has a length that may have leading nulls) to a C string and this conversion will invariably truncate your string.

Usually the way forward in the latter case is to use a "Blob" field but I haven't implemented that in this release.