SQLite, Firebird or SQL compact

[joshuatree]

Hi All,

   I'm trying to replace an existing applications use of text and excel files for data storage with an embedded database model.  I've narrowed it down to 3:  Sqlite, Firebird, and Microsofts SQL Compact.  

For simplicity i like sqlite, but i'd like some thread safety, and concurrency.

  My biggest hang up in trying to finalize my decision, is that in the future i'll want to query another database (i.e query a customer's remote database to retrieve employee passwords) or allow another database to insert or query my tables to retrieve operational data.  My google-fu is strong, but I have yet to see examples or read documentation that specifically mentions whether this is possible.  What's the correct terminology?  I've seen discussions about linked databases (pros and cons), as well as cross database queries, but never a definitive "Yes, i did this... and it worked".  It could be so commonly done that noone mentions it, and i'm just retarded. 😕  

If i adopt Firebird, will a customer with SQL Server just be able to use an odbc connection to interact with my database?  It's got to be a simple thing, i'm just missing it.

Please help me put this monkey to rest.

[jack47]

I have queried MySQL, Oracle and Access databases, with the same LabVIEW code and different connection strings (and table names, column names, user names and passwords).  So you should be able to pull data from different schema.  You just have to manage getting all the correct drivers, connection information and schema details.

Can your customer insert data into your tables?  They should be able to if they are using java, cold fusion, C++.  And know what they are doing and willing to make code changes...

[joshuatree]

Thanks for the reply Jack47.  

 My last customer gave us a .udl file and a SQL query to perform.  That worked for a one off solution but we're trying to design for the long term.  Using your example, and some comments from here and there, one idea is to have a "connection" table thats used to store their connection info and the unique query for that particular customer.

 What's the hassle factor of allowing the customer to insert?  Essentially, Is it easier to query the data we need, or rely on them to have it in place when we need it? 

thanks again 

[Aashish_M]

Hey Joshuatree,

What programming environment is your customer looking to use to access the database?

There are quite a few examples on using LabVIEW to access SQL databases. Here are a few links:

Insert Data into DataBase from an Array using LabVIEW Database Connectivity Toolkit LV 8.6

Read DataBase Structure and Data in LabVIEW using DataBase Connectivity Toolkit LV 8.6

DataBase Query using SQL Statements and Database Connectivity Toolkit with LV 8.6/8.5

Thanks,

[rolfk]

NI has a design pattern solution for SQLLite that even works on their CompactRIO RT controller hardware to use SQLLite from within LabVIEW. And http://www.ch-werner.de/sqliteodbc/ shows an ODBC driver for SQLLite. There are of course problems since the ODC driver contains the actual SQLLite engine and accesses the database file. So remote access through ODBC seems not trivial, though you could of course share the database file over the network and access it through an ODBC driver installed on a different computer. This probably still leaves the difficulty how to deal with concurrent access to the database file if your client tries to query the database file at the same time as your application has it open to write or query data from it.

Firebird also has an ODBC driver: http://www.firebirdsql.org/index.php?op=files&id=odbc. Firebird seems to either have a filebased access model that does allow concurrent access to the same database as well as a so called super server mode, where a service process controls access to the database file(s) and access is done through network communication to this service process. Not sure if the ODBC driver supports the super server mode transparently but if it does this would be a very easy solution to have your application use the database to store date and allow your clients to query the database from other computers directly.

Microsoft SQL Compact I'm not sure. If this is what was before the SQL Server Desktop Edition, then it is basically a full featured SQL Server installation with the database size limitation to 2GB. This would certainly work also with remote access through ODBC and/or ADO .Net. But if you have ODBC drivers for your database you can basically access it through ADO ActiveX or ADO .Net as well and it doesn't even have to be slower if the driver and interface are done well.

Message Edited by rolfk on 03-23-2010 09:15 AM

[kc64]

@rolfk:

"NI has a design pattern solution for SQLLite that even works on their CompactRIO RT controller hardware to use SQLLite from within LabVIEW."

I would really like to see a link to this.  Do you have one?  My searching has been fruitless.

[joshuatree]

I think rolfk was talking about this:

http://zone.ni.com/devzone/cda/epd/p/id/6287

[Aplonis]

I have a project for which I plan to use SQLite as the RDBMS. There won't be any NI hardware involved. And while I could just do like I have before and built the GUI for talking to SQLite with Perl/Tk or something...it would be way prettier and easier to modify ex-post-facto if I could make the GUI with LabVIEW.

Anybody have ideas on this? on using LabVIEW (exporeted as an *.EXE application and running with LabVIEW RTE) as the GUI to SQLite without any NI hardware involved? Is it even possible?

[Dennis_Knutson]

Of course it's possible. Why would you think you need some sort of NI hardware to run it? The only 'hardware' you need is a pc.