SQLite in LabVIEW using Python

[jcarmody]

Hello,

I've read a few posts about using SQLite in LabVIEW and haven't found the library to replace the broken link I mentioned here. My former employer had some useful VIs, but they are proprietary (and, being that they're my former employer, I no longer have a right to use them). So... I've wanted to write some wrapper VIs to use SQLite without ODBC and the NI DB toolkit (it works fine) and have made some initial progress. I first tried to use a Call Library Function node to access the functions inside SQLite3.dll directly, but got lost right away. I had seen a post where some work was started using CVI, but I didn't understand why a C wrapper was needed around the dll to use it in LabVIEW. What I got out of this, and a few hours of Googling, was that it was needed and I was missing something fundamental.

Anyway, in a moment of inspiration I decided to use a Python Script node to access the database and was able to execute a SELECT query and also divine some schema information. I haven't tested it extensively yet (it's very much preliminary work and a bit sloppy), but I've just begun and would like some feedback as to whether I'm way off base or if this would be useful. I'm very interested in doing this, especially if others would be able to use it. I'll appreciate any suggestions you make.

Thank you,

Jim

Download File:post-7534-1225677106.zip

[shoneill]

QUOTE (jcarmody @ Nov 3 2008, 04:03 AM)

Hello,

I've read a few posts about using SQLite in LabVIEW and haven't found the library to replace the broken link I mentioned here. My former employer had some useful VIs, but they are proprietary (and, being that they're my former employer, I no longer have a right to use them). So... I've wanted to write some wrapper VIs to use SQLite without ODBC and the NI DB toolkit (it works fine) and have made some initial progress. I first tried to use a Call Library Function node to access the functions inside SQLite3.dll directly, but got lost right away. I had seen a post where some work was started using CVI, but I didn't understand why a C wrapper was needed around the dll to use it in LabVIEW. What I got out of this, and a few hours of Googling, was that it was needed and I was missing something fundamental.

Anyway, in a moment of inspiration I decided to use a Python Script node to access the database and was able to execute a SELECT query and also divine some schema information. I haven't tested it extensively yet (it's very much preliminary work and a bit sloppy), but I've just begun and would like some feedback as to whether I'm way off base or if this would be useful. I'm very interested in doing this, especially if others would be able to use it. I'll appreciate any suggestions you make.

Thank you,

Jim

Download File:post-7534-1225677106.zip

Assuming you're working on Windows, there's a SQlite.net component available over at http://sqlite.phxsoftware.com/

I've used it and it's quite easy to use. Probably quite a bit easier then direct linking to a DLL. Check out the benchmarking section on the site regarding run-time performance.

I switched to SQLite.net from SQL compact and saw dramatic improvements in search times in my DBs. I think SQLite is pretty well optimised, even though they don't support some functionality found in other DBs (Like proper foreign key support).

Shane.

[jcarmody]

QUOTE (shoneill @ Nov 3 2008, 07:43 AM)

I've used it and it's quite easy to use.

Shane,

Do you have an example you could share? I don't have any experience with .net (except for a horrible application I inherited that has a slick user interface using .net controls).

Thanks,

Jim

[jzoller]

QUOTE (jcarmody @ Nov 3 2008, 07:36 AM)

Shane,

Do you have an example you could share? I don't have any experience with .net (except for a horrible application I inherited that has a slick user interface using .net controls).

Thanks,

Jim

Jim,

I had the attached laying around, hopefully it's helpful.

Another method: it's possible to invoke sqlite from the command line (or the System Exec.vi). The exact syntax escapes me at the moment, but it's something like C:\path\to\sqlite.exe databasename "databasecommand".

Joe Z

[shoneill]

QUOTE (jcarmody @ Nov 3 2008, 03:36 PM)

Shane,

Do you have an example you could share? I don't have any experience with .net (except for a horrible application I inherited that has a slick user interface using .net controls).

Thanks,

Jim

I was working in C# at the time, so I don't have any LabVIEW examples at all....

If you want a C# example (The .NET objects are the same......) I can deliver that.

Shane.

[jcarmody]

QUOTE (shoneill @ Nov 3 2008, 04:38 PM)

If you want a C# example (The .NET objects are the same......) I can deliver that.

No, thanks. I get cut on sharp things.

QUOTE (jzoller @ Nov 3 2008, 10:55 AM)

I had the attached laying around, hopefully it's helpful.

Thanks. There's sure an awful lot going on in there.

I'm going go forward with Python. I'm not sure what would be easier, but I'm more interested in learning Python than how to use .net in LabVIEW. I played around at lunch today and wrote open, query and close VIs so I have enough to get started and a clearer vision of where I want to go.

Thanks to both of you for your suggestions.

Jim

[Götz Becker]

Hi,

thanks for the hint to SQlite.net and your example. This really looks nice and I hope to get some free time to test its itegration as replacement for a current DB component.

:thumbup:

Götz