CODEWITHSUNDEEP
c#mysqlsql-serverwindows-installer
lost Coder
lost Coder

Reputation: 577

Is there any restriction to WindowsInstaller msi database queries in sql

I am trying to run some sql queries on my msi database in c#.But it seems like some specific sql queries are not working.

WindowsInstaller.Installer ins = (WindowsInstaller.Installer)new Installer();
string strFileMsi = @"abc.msi";
Database db3 = ins.OpenDatabase(strFileMsi, WindowsInstaller.MsiOpenDatabaseMode.msiOpenDatabaseModeDirect);

string q = "SELECT File FROM File WHERE FileName LIKE '%s%'";
WindowsInstaller.View vw = db3.OpenView(q);
vw.Execute(null);
string q2="SELECT * FROM InstalExecuteSequece ORDER BY Sequence DESC"

WindowsInstaller.View vw2 = db.OpenView(q2);
vw.Execute(null);

If i run the same query without DESC keyword and all,it works fine.Similarly is the case with LIKE KEYWORD also.All of these gives sql exception.

Upvotes: 2

Views: 1313

Answers (2)

BdN3504
BdN3504

Reputation: 1733

To overcome the limited capabilities of installer supported SQL, you can make use of the Microsoft.Deployment.WindowsInstaller.Linq assembly which is shipped with wix. To extract the last sequence number of the InstalExecuteSequece table for example, you'd simply write the following query:

var db = session.Database.AsQueryable( );
var lastSequence = db.ExecuteIntegerQuery( "SELECT `Sequence` FROM `InstalExecuteSequece` ORDER BY `Sequence`" ).Last( );

Upvotes: 0

Michael Urman
Michael Urman

Reputation: 15905

Windows Installer implements a subset of SQL described in SQL Syntax. Within that subset there are several limitations, including three that I'll highlight here:

  • There is no support for DESC or LIKE, and ORDER BY may not handle strings as you expect
  • Update queries cannot modify a value in a primary key column
  • There is no way to escape the apostrophe character ' in a SQL query. If you need to match a string value like 'It's', you have to use the a question mark ? placeholder in the query and pass a record containing the value to view.Execute(record)

Upvotes: 5

Related Questions