Automation Interface: Stuck on SQL Statement with WHERE clause

[DLee65]

Ok, I am trying to recover a specific record from a MSI DB using the Windows Installer automation interface.
My sql statement without the where clause works correctly; however, when I add in a WHERE clause no view is created nor is a record created (obviously).

my $sql = "SELECT * FROM File WHERE File.File=\'$foo\'";

The text for this might look like:

SELECT * FROM File WHERE `File`.`File` = '_de600a2e0cd96c43982bae64916fa08f_FAB68E99F0FCAAEA698E58F3A1EE3ACF'

I have tried the following syntax:
"SELECT * FROM `File` WHERE `File`.`File` = \'$foo\`".
This will create an object for me, however, when I used $view->Execute() the object is not created correctly and therefore I cannot fetch any records. It acts like there is no match.

I have confirmed that the DIM file UUID shown above does exist in the DB, but my query still fails.

Any ideas? I would hate to have to grab all records in the file table and then loop through the records each time to find a match.

Thanks.

[RobertDickau]

Does the query work in the InstallShield MSI Query Tool (to determine if it's an issue with the query or with the program executing it)?

[Christopher_Pai]

Your second select statement looked correct to me.

I know this doesn't answer your question, but you might want to consider using C#/DTF for this type of work. In my years of writing MSI automation, I've found nothing else that's as good. The class model, intellisense, managed code, error handling and so on and so on just rocks.

In my current build automation I'm using COM interop to call the IS Automation interface and I've found some property accessors that missing in the interface. I save the project in binary format and then open it up using DTF and it's super easy. Much easier and reliable then anything I ever did in C++, InstallScript or VBScript/VB6.

The above code also implements the MSBuild Task class with property accessors that make it simple to plug into MSBuild. MSBuild/NAnt is also years ahead of any shell scripting procedural build automation I've done over the years. An added benefit ( I work in a .NET shop ) is that my developers can look at the msbuild scripts and C# classes and almost immeadiatly `get it`.

[DLee65]

RobertDickau wrote:
Does the query work in the InstallShield MSI Query Tool (to determine if it's an issue with the query or with the program executing it)?

Hmm, I forgot about that nifty new tool. I will give it a go and see what happens.

Christopher, the tool of choice around here has been Perl. I have not taken the time to learn C#. Perhaps next year :).

[DLee65]

Well, the Query tool worked well and the syntax of the query is correct. There is something weird in opening the file then. I will double check and make sure I am opening the correct file.

[Christopher_Pai]

Be sure to checkout:

http://community.acresso.com/showpost.php?p=337010&postcount=10

I understand Perl is still big in build automation but I just don't roll that way anymore. 🙂

The 90's are over. I'm not doing client/server where I want to be able to use the same languages for client builds as I do server builds. These days the companies I work for are using Windows for the server side and more often then not it's all being done in managed code technologies.

I've drank from the kool-aid cup and I'm 100% sold.

[DLee65]

Yeah, I was just suffering from an I.D. 10 T (known as idiot in human speak) error!

I had created a sub function and instead of passing in the pointer to the msiDB, I was passing in the pointer to the InstallShield ISM DB! No wonder it could not find the record.

[Christopher_Pai]

That's why I like C#/DTF. It's 2008. Why the hell should I care about pointers? For that matter, why should I even care about SQL?

Consider the below code. Notice how the managed types, rich meta data and intellisense allow me to abstract the problem into what I'm really after... fetching data.

[DLee65]

I must admit, that is very nice looking and readable by the average human.

I had a good laugh over "why do I have to worry about pointers - it is 2008!". I guess part of the issue is taking time to learn the syntax for C#. However, it cannot be half as bad as learning syntax for Perl 😄

[Christopher_Pai]

Ya, I'm being sarcastic when I say that, but still, it's true. I did learn ANSI C on Solaris back in the 90's and I do know all about pointers and malloc and what not.... but my question is, why? How is it relevant today in this space? Even the purists at MSFT had to finally concede and start supporting managed code custom actions in MSI.

I was lucky. I had a manager send me to a one week c#/.NET bootcamp a couple years ago. This was despite me telling her that managed code had no business in setup and that it was a waste to send me.

[DLee65]

Christopher Painter wrote:
That's why I like C#/DTF. It's 2008. Why the hell should I care about pointers? For that matter, why should I even care about SQL?

Consider the below code. Notice how the managed types, rich meta data and intellisense allow me to abstract the problem into what I'm really after... fetching data.

What version of Visual Studio are you using? VS2005 does not seem to recognize the commands such as DatabaseOpenMode. I added the following:

using System;
using System.Linq;
using Microsoft.Deployment.WindowsInstaller;
using Microsoft.Deployment.WindowsInstaller.Linq;

I did notice that .Linq does not have the same syntax highlighting so I imagine you are using VS2008, or you have your file properties set different. I just created a new C# file and started adding in the same stuff you had just to see what it was like.

[Christopher_Pai]

Yes, Linq is a .NET 3.5/VS2008 thing. For a more traditional select statement ( done in the context of a custom action ) you can see:

http://blog.deploymentengineering.com/2008/05/data-driven-cas-made-easy-with-dtf.html