affected rows when using db tools update data.vi

[aartjan]

I have an issue with DB tools update data.vi from the database toolkit. I have created an VI that can update database records, and it will immediately log the update, too. But unfortunately, it will also log the update when the update conditions resulted in zero matches. I tried, but could not find a simple way to retreive the "affected rows" after doing an update.

I welcome any suggestion on how to tackle this. Thanks!

Aart-Jan

[aputman]

Using built-in VI functionality of the dB toolkit, I don't think this is possible.  You could do it with a stored procedure (recommended) but getting these to work does have a learning curve and even requires modifying the toolkit code slightly (at least in older versions of LabVIEW). 

An indirect method would be to select the rows first, and only execute the update if the number of rows returned from the select statement is > 0.  Of course, this requires an additional query and depending on size of your database, how often you are updating records, and how well you have the dB indexed, can be time-consuming. 

[tst]

Depending on your DB, there are some options to get the number of affected rows. See here for some examples: https://database.guide/get-the-number-of-rows-affected-by-previous-sql-statement/

Note that this has various caveats (such as what its scope is. It might be for a connection, it might be global, etc. This means that something else might come in between your update command and your reading of the value and change the value).

In general, I would also recommend having DB interactions using stored procedures and not direct operations on the tables.

[jcarmody]

If the query doesn't take long, you could do a SELECT with the same WHERE clause before doing the UPDATE.