I need to run a sql statement using the MSI Generic action but cannot get it to run successfully when I use the AND in a sql statement.
Here is the exact command that I have tried but neither work:
UPDATE Condition SET Level='4' WHERE Level='3' AND WHERE Feature='.Runtime'
UPDATE Condition SET Level='4' WHERE Level='3'' AND Feature='Runtime'
If I run the sql statement without the additional AND in it there are no errors:
UPDATE Condition SET Level='4' WHERE Level='3''
Unfortunately I have two rows in the Condition table with the level and two rows with the same feature name.
The help for Final Builder is sketchy at best and I am finding little resources on the internet for the syntax that Final Builder is looking for. Any help is appreciated!
Hi Anita,
There are a few issues that's preventing this from working, the first one is that you cannot perform an UPDATE on a primary key field in an MSI database (see http://msdn.microsoft.com/en-us/library/aa372021.aspx) and the 'Level' column is a primary key. Another issue is that the 'Feature' column needs to be refered to as 'Feature_', it took me a while to figure this one out as even in Orca (http://msdn.microsoft.com/en-us/library/aa370557.aspx) the column name is presented as 'Feature' until you go to add a new row, where it's displayed as 'Feature_'.
I've attached a sample project which works around these issues by first deleting the existing row and then adding a new row with the updated data, you'll need to modify the values that have been set to the project variables so that they match your environment. Also, you'll notice that I've had to call out to an external VBS script using wscript.exe, this is due to a known limitation with FB that prevents this solution working with the MSI Generic action.
Hopefully this will get things working, let me know if you have any problems.
Regards,
Paul
UpdateMSI.zip (4.086 KB)
Thank you for the final builder project. I see now that the table is Feature_ and that the Level column is a key path.
Just one additional question, does a sql statement that contains an 'AND' not work with the MSI Generic action?
Thank you,
Anita
Hi Anita,
The MSI Generic action should work fine with where clauses with an ‘AND’.
Regards,
Paul.