Database

[demmy]

Hello All,

I am writing a code and i am stuck with this part: I want labview to look into a database containing a list of tables. If a value is changed in any of the tables, do the following:

• go to the table where the value is changed,
• go to the roll where the 'value change' occurred,
• get a value under a column called 'idnumber'(in the same roll),
• get the name of the column where the change occurred
• finally get the new value.

I know i need an event structure but i don't know how to represent the above. Pls help.

[Norbert_B]

Hm, do you have an interface to the database? Do you have the Database Connectivity Toolkit?

Furthermore, i am not aware that databases create "value change" events which are accessable using LabVIEW (if they do at all).

Norbert

[demmy]

I have an access database that i want to connect to and i also have the database connectivity toolkit. I just want to write a code that monitors my database so that if  a value is changed in the database, i want to get the value of the 'id column' of the table where the change occured, the name of the column where the change occurred and the new value. Can you help?

[Norbert_B]

As i already wrote, i am not aware of any information service from the database in order to let your application know that a value has changed. Therefore, the only working mechanism i can think of is:

Poll the whole database, compare the content with the dataset of the "previous" poll and, if a change occurred, display the desired data and save the new dataset as "previous" for the next poll.

This will definetly work, but will waste a good part of your PCs resources and therefore i would not suggest you to do so.

Norbert

[mishkin]

There aren't any built-in tools in Labview that will let you monitor for value change events in database data. This application most likely won't be event-driven at all - the only way I see implementing this is storing the current value of each database table locally and periodically comparing to see if the local and remote versions are the same. If not, find where the change occured and take further action.

[RGreg]

I think this is a case of, "if you only have a hammer in your toolbox, then every problem looks like a nail."  

There are a couple of straightforward ways to do this if you have access to the DB and know something about modifying it (or can get someone else to do it for you).  The first way would be to write a stored procedure that checks the table for you.  You can have LV execute the SP periodically and report on the results.  Of course, this presupposes that you a) know SQL well enough to write the SP, b) you have access to the DB, and c) you can change the permissions on the SP so that the LV user can execute it.

The 2nd way is more direct.  Implement an auto-indexing key on the table, then have LV periodically check the table to see if the index has incremented.  I think a SQL command like

select top 1 [index col name] from [table name] order by [index col name] desc

would give you the latest index value.  The top 1 part returns a single row from the DB (speed up the code), and the order by desc part orders the results in a descending fashion so that you get the highest (i.e. - the latest) index.  But again, this method assumes you can change the DB table (or get someone else to), and that your program user will have the right permissions to run a select command on the table.

[Dennis_Knutson]

And if 'changes' include a modification to an existing row (or 'roll'), then it becomes much, much more complicated.

[demmy]

Thanks all, I will be the one changing the values in the database manually. I just want a vi that can recognise that a change has ocurred and tell me where and tell me the new value. Pls, can someone help me with a sample code?

[Dennis_Knutson]

The simplest answer is don't make manual changes. If you want a record of changes, make the changes with a VI and an event structure.

As mentioned several times, there is nothing in the database that will easily tell you a change has been made.

[RGreg · Accepted solution]

I agree with Dennis that you shouldn't make changes to the DB if you don't have to.  But I've also had to deal with DB architectures that, to be euphemistic, lacked industry standards.  A primary key, auto-indexing column on a large data table is one such standard.

I know for a fact that in Microsoft SQL Server you can readily add a new auto-indexed column to an existing table - I'm not sure about Access.  Assuming you can, then the 2nd option I suggested should work.  Below is a code snippet that will get the latest row of data added to that table.

It also just occurred to me that you might be referring to update commands that are made to specific rows - what I'm suggesting above only covers new rows added to the DB.  If you want to monitor when an update or deletion happen to a particular table, then LV is not the tool to do that.  Do it with a stored procedure in Access, and call the SP from LV if you must.

But as a general DB rule you shouldn't let users have the permissions to update or delete data from the DB, except under controlled conditions.  Instead, add new data to the DB and mark the old data as superseded (but still keep the data). 

Message Edited by RGreg on 03-11-2010 10:38 AM