Suggested in-process database for use with LabVIEW?

[minnellac]

I've been researching databases for a few days looking for something lightweight that I can use when developing applications. I saw the post by Anthony Lukindo on Expressionflow (http://expressionflow.com/2007/07/05/labview-based-utility-to-package-deploy-ms-sql-server-2005-expr...), but I'm not really looking to use an enterprise level db and it sure seems complicated. I looked at both MS SQL Server Compact 3.5 and SQLite, and they both look to be about what I'm looking for, but I haven't found any LV interfaces to these things. There appeared to be an aborted effort to get a SQLite interface made as an OpenG toolkit (SQLite database toolkit (Windows)), but it seems to have inexplicably vanished. I like the idea of SQLite better than the MS product because it seems to only include the things I'm looking for, and it's cross platform (and public domain) and I am thinking about starting to write an interface myself. I'd appreciate any comments on the topic.

I do have the LV DB toolkit, but I would rather not have all the overhead associated with speaking to an out-of-process db through external components (plus the headeaches associated with deploying the thing). I am pretty much a nubie in this area though, so maybe I can be convinced otherwise.

Thanks,

Chris

[smercurio_fc]

Well, given the fact that you're throwing words like "enterprise level db", and "out-of-process", it doesn't sound like you're that much of a newbie.

You said "I like the idea of SQLite better than the MS product because it seems to only include the things I'm looking for, and it's cross platform". What are the things you're looking for?

[Tomi_Maila]

SQLite is propably the best performing free open source disk based database for a "single-threaded" applications or for reasonably sized multi-threaded applications that do not require row-level or table-level locking. So do not consider SQLite only as a development time alternative... it is a good alternative for many purposes.

You can connect to SQLite with a  ODBC driver, I think, and do not need a native LabVIEW support for SQLite. Just google SQLite ODBC.I have not tested this, but if so, you can use any of the ODBC database toolkits for LabVIEW, even the National Instruments one 🙂

A different thing is that if you need a database for development purposes, should you actually set up one? I'm a friend of a software-as-a-service and currently use hosted mysql databases at Mosso. I use them for web development project but I see no reason to use them with LabVIEW as well, as long as your development computer  is online. The good thing about hosted services is that they really are zero-configuration and zero-maintenance. When ever I've a problem or don't know something, I simply chat with the support person online 🙂 Another good thing is that my hosted databases come with online administrative interface, phpMyAdmin, which simplyfies the database management, especially during the development phase when everything is not working as expected. Furthermore I can use MySQ Workbench to visualize the structure of my database for documentation purposes.

p.s. Mosso pricing starts from $100/month which may be over your budget, but the support is simply superb. For the price you get practically unlimited number of databases of you choice such as MySQL and MS SQL 2005, and practically unlimited number of hosted web sites running in a load balanced cloud with either Linux or Windows or mixed. The downside is you cannot install new programs such as LabVIEW and you have to do with the services they provide. When your computational needs exceed certain limit of processor cycles a month, you need to pay more. This limit is something like a single processor server.If you decide to go for mosso, use the following referral code "REF-EXPRESSIONFLOW" to get the second month free. The promotion is valid until the end of September and after that you get $50 off from the first month price.

[jcarmody]

---

Tomi M wrote:

You can connect to SQLite with a  ODBC driver, I think, and do not need a native LabVIEW support for SQLite. Just google SQLite ODBC.I have not tested this, but if so, you can use any of the ODBC database toolkits for LabVIEW, even the National Instruments one 🙂

---

 

I just tested this and it worked well with the LV DB toolkit.  Here's where I found an ODBC driver.

I used to work for an NI Alliance member and they use SQLite wherever they need a local database.  I've become fond of it over the years, too.  It sounds ideal for your application.

jm

Message Edited by jcarmody on 10-02-2008 05:46 AM

[Wiebe@CARYA]

There is a cross platform ODBC API (called ODBC API, I think). I have
written some C programs that uses this API, and it is supposed to work under
Linux as well. Perhaps also Mac.

If you're going to make your own interface, you might use this api to build
a wrapper around. I'm pretty sure ODBC is the only way to make a generic DB
interface, and this ODBC API is probably the only cross platform interface
to it.

But if you're aiming at only MySQL or SQLite, you could also use the command
line (but it will be slower, because you're using strings).

The MySQL API only has a few functions (connect, command, destroy I think).
I'm pretty sure LAVA has a wrapper for it, and it should be cross platform
as well.

Regards,

Wiebe.

[minnellac]

Thanks for all the comments guys. I have some things to think about. I don't have a specific application in mind, but I'm thinking about using the db as a file format, or entirely in memory. I've been using a custom version of TDMs for one project, and I would like to get out of that mess (custom in that I've added my own fields and stripped out the ones that I am not interested in using). I've been disappointed with the time required to access data, and there aren't any good free tools that I've found to work across multiple files simultaneously. For example, if I was using SQLite dbs, I think I could load multiple dbs and do a join to simultaneously query across them.

I could really just be wrong about this, but I thought that if I was using ODBC that it meant that I was talking to an out-of-process db. I like the idea of making direct api calls through a dll to access the data. It seems like there would be significant performance benefits. I'll take a look at the ODBC drivers that jm and Wiebe mentioned. Maybe (probably) my understanding of ODBC is lacking.

Chris

[Wiebe@CARYA]

> I could really just be wrong about this, but I thought that if I was using
ODBC that it meant that I was talking to an out-of-process db.

You are right about this. But it is probably highly optimized. When using
ODBC from LabVIEW, you'll probably be using ASCII commands, which is very
slow. But you don't have to. The ADO component has interfaces to communicate
binary data. The ODBC API has them too, it's probably what ADO is based on
(it sure has a lot in common).

> I like the idea of making direct api calls through a dll to access the
data. It seems like there would be significant performance benefits.

It shouldn't really matter if you use the ODBC API or ADO to talk to ODBC.
The SQLite dll interface will probably be faster, and if you can use it to
send binary data, you'll get some real speed.

Regards,

Wiebe.