Click here to Skip to main content
14,641,849 members
Rate this:
Please Sign up or sign in to vote.
See more:
Hello Guys,

Am trying to access the MSI database, I can insert, delete etc. in the database.
My issue is how to check for existing entry in the database and Insert if not exists.

I get error in my code below.

installer = CreateObject("WindowsInstaller.Installer")
database = installer.OpenDatabase(Path, 1) 'database will be opened for editing.

SQLquery = "IF NOT EXISTS(SELECT * FROM database WHERE `Feature` = 'Complete') INSERT INTO Feature (`Feature`, `Feature_Parent`, `Title`, `Description`, `Display`, `Level`, `Directory_`, `Attributes`) VALUES ('Complete', '', 'Complete', '', '2', '1', 'INSTALLDIR', '0' )"

View = database.OpenView(SQLquery) ---------> Error: COMException was Unhandled, OpenView,Sql



Please assist me in solving the error in the code.
Posted

1 solution

Rate this:
Please Sign up or sign in to vote.

Solution 1

Looking at the documentation[^], Windows Installer does not support the IF NOT EXISTS(...) clause. You'll need to issue separate SELECT and INSERT commands.
   
Comments
Mohan Subramani 10-Sep-14 8:42am
   
Thanks for your reply,
I have gone through the documentation. I will use SELECT and INSERT commands seperatly.
I have a question here,
Can i get value of the executing SELECT statement in a binary 1 or Zero value.
For e.g
SQLquery = "SELECT * FROM database WHERE `Feature` = 'Complete'"
View = database.OpenView(SQLquery) ---> This statement to give values 1 if YES, 0 is No

So that i can execute my next INSERT command accordingly ?
Richard Deeming 10-Sep-14 8:47am
   
It doesn't look like it; the SELECT statement can only select existing columns, not computed columns.

I suspect you'll need to Execute the SELECT view, then Fetch the first record. If it returns a record, then the feature exists; if it returns Nothing, then the feature doesn't exist.
Mohan Subramani 11-Sep-14 1:37am
   
I got the tips from msdn link, to do SELECT in a MSI database.
(Am using below code to SELECT a Feature named Complete)

View = database.OpenView("SELECT `Feature`.`Feature` FROM `Feature` WHERE `Feature`.`Feature`= 'Complete'")
View.Execute()
database.commit()

fetchDB = View.fetch()
MessageBox.Show(fetchDB)

If (fetchDB = "") Then
MessageBox.Show("This Database doesnt contain Feature 'COMPLETE'")
End If



i get error in the line "MessageBox.Show(fetchDB)" -- Am not able to convert _COMobject ot String
How can i convert the fetch data from dtabase to string to Check whether it is Null or not

Error:

Invalid CaseException was handled.
Conversion from type '_ComObject' to type 'String' is not valid.


Please assist
Richard Deeming 11-Sep-14 7:44am
   
You don't need to convert an object to a string to check whether it contains a value.

Try:

fetchDB = View.fetch()
If fetchDB Is Nothing Then
MessageBox.Show("This Database doesnt contain Feature 'COMPLETE'")
End If

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month



CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100