saving labview data to a database

[falkpl]

Thanks, I will try to alter the tables on the fly if needed using the sql commands provided.  I think this should solve the problem at hand.

Paul

[mikeporter]

This sort of highlights the issue I was speaking to earlier, talking to a database is easy. The challenging (and interesting) part is working with them. The first thing is to be sure that you have a good database design to work with. In a properly designed, well-normalized database design you should have to rarely modify the database structure.

The important thing to remember is that the database design should model the structure of your data--NOT the way you acquire or want to display the data. For example, if you are needing to change your database structure because you were collecting three pieces of data and now you are going to be collecting four--that's a bad database design. We like to look at data in tables like spreadsheets. Unfortunately, that's exactly the wrong structure for a database.

There are a bunch of good books on this topic, one of my favorite is The Practical SQL Handbook by Bowman, Emerson and Darnovsky.

Mike...

[mikeporter]

By the way: Item 2 may appear convenient, but in practice it is a really bad way of designing LV code that accesses a database. It's bad because it intimately ties you code into the structure of a particular database table. As a result, if the datatable changes your code has to as well.

Mike...

[falkpl]

What is the proper way to "decouple" the database structure from the labview structure.  I have currently made a cluster with the same structure as the table entry as I wish to add to the DB.  Now if the database changes in the future I understand this will break the code.  It would be easy to solve if labview allowed for more dynamic creation of clusters.  The insert to DB would ideally take a cluster and add an entry to the DB updating only the fields which match the cluster structure (named elements), ignoring any elements which don't match the DB fields and any missing fields would get a default value.  I am still a DB beginner so I have much to learn.  I don't want to pick up bad programming habits this early in the game however.  Are clusters a bad way to encapsulate data for DB communication?  is there a dynamic method for DB insertion?  Ideally there would be little coupling between the data in the DB and the data in the LV code, this way I can change either structure without any penalty in the other.

Paul

[Crystal]

Hi, everyone

I've been following this thread very closely and decided it was my turn to jump in and add my comments.  You can think of the Database Toolkit as a set of different tools for different levels of users much like how the file I/O functions are organized.  The top-level database VIs that use clusters to transfer the data are similar to the top-level file I/O functions in that they do very simple things quickly and hide the details from you.  However, if you've used file I/O enough, you quickly realize that if you want custom formats or more power over how your files are written or read, you need to get rid of the high-level subVIs and start using the things in the file I/O:Advanced palette. 

The database toolkit VIs are similar in that if you want to start doing more complicated or robust apps rather than quick reads and writes, you need to start understanding the concepts of database design and using the main tools of databases such as SQL.  The original SQL Toolkit did all of its operations using clusters that mimicked the structure of the database fields.  When the Database Toolkit was created, some people were very vocal that they wanted to still use clusters eventhough with ADO and the current way you can define and use SQL, clusters didn't make a lot of sense for the exact reasons Paul mentions.  I usually recommend that people skip those top-row database VIs and go directly to doing either parameterized database operations or just use SQL.  To see some examples, start with the Logging Example (parameterized) and the Playback (fetching).  If you'd like more examples using just raw SQL, let me know, I have several.  Another good example to look at is the Read All Data because it uses a more streamlined approach to read the data.

Let me know if you have any further questions,

Crystal

[mikeporter]

Paul;

I started writing a reply earlier this evening, but then I realized that given the importance of this issue, I wanted to take some time and write a reply that has some deeper thought involved in it than what you would get from a quick reply. I'll post an answer tomorrow night that will (hopefully) be worth the wait.

Mike...

[Arnoud]

Hi,

In my opinion the best way to keep your database structure apart from your LabVIEW code is with Stored Procedures (Microsoft SQL Server) or Functions (Oracle). The Stored Procedures (SP's) allow you to define input parameters and SQL code to specify what to do with the input parameters.

Now you need to mind 3 things.

1. Keep your cluster in LabVIEW consistant with the Stored Procedure inputs. (Or use a tool like T&M Datab...... that checks and corrects the consistancy)
2. Use the SQL Wizard to automaticaly create Stored Procedures for your tables (select, insert, update, delete)
3. Freely add columns to your table, only if you change column specification you have to check your SP's

I hope this can help you,

-See you (possibly) at NIWEEK!

With kind regards,

Arnoud de Kuijper
T&M solutions BV

[cs42]

The easiest way IMO is to use the DSC Tag Engine. Set up the tags you want to save and connect the controls to the tag you want to save to (you can do this programatically). And you're done.

Okay, there're some drawbacks:

1. you have to use NI citadel (which is propertiary)
2. you can only save to one database at a time
3. the run time version costs about 900 €
4. there's no tool to do backups included (but some VIs which you can use to write your own backup solution)

But I think the advantages are tremendous:

1. very good integration with LabVIEW
2. easy programming
3. nice additional features, like scaling
   
4. you don't have to know anything about the database
5. it just works
   

[mikeporter]

Paul;

Here's the writeup I promised you last week took a bit longer than I envisioned, but it's also longer than I thought it was going to be. In any case hope this helps...

Mike...

[mikeporter]

Last night when I was doing the final edits on this document I had a brain lapse. The reference in the bibliograpy to books by Randy Johnson, should of course be books by GARY Johnson.

Many apologies Gary...

Mike...