Wix - SQL square brackets in Binary Files

Question

UPDATE:

While the answers below do work as alternative solutions, I'd like to mention that my initial method does in fact work. After reviewing the answers below, I found out that my Session.Log() call was actually stripping the [ ... ] when it posted to the log file. The square brackets remained in my SQL as I fed it into the SQLCommand object. My actually issue was that the SQL (of which I only posted the first few lines) had 'GO's in it, which are not SQL commands. Once I solved that problem everything worked :)

*(A reminder, posting as much information as possible is always helpful :D)

---

In Wix, I have SQL files stored in binary elements

<Binary Id="SQLStep1" SourceFile="SourceDir\Step1_SQL_Build.sql"></Binary>
<Binary Id="SQLStep2a" SourceFile="SourceDir\Step2a_SQL_Build.sql"></Binary>
<Binary Id="SQLStep2b" SourceFile="SourceDir\Step2b_SQL_Build_sp_iv6Login.sql"></Binary>
<Binary Id="SQLStep2c" SourceFile="SourceDir\Step2c_SQL_Grant.sql"></Binary>

I then use a custom action to pull the sql out of the binary table, and string-replace the database name (provided by a textbox in the installer)

private static string ReplaceDBName(Session session, string binaryKeyName)
    {
        View v = session.Database.OpenView("SELECT Data FROM Binary WHERE Name = '{0}'", binaryKeyName);
        v.Execute();
        Record r = v.Fetch();

        using (StreamReader reader = new StreamReader(r.GetStream("Data")))
        {
            string text = reader.ReadToEnd();
            text = text.Replace(@"DB_NAME", session["DATABASE_NAME"]);
            session.Log("Running SQL: " + text);
            return text;
        }
    }

an example SQL statement is like this:

USE [master]
GO
/****** Object:  Database [DB_NAME]    Script Date: 02/23/2010 15:02:47 ******/
CREATE DATABASE [DB_NAME] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

However, the string I get out of the 'Binary' table seems to pull out all the [ ... ] content like they were WiX Properties, so I am left with

USE 
GO
/****** Object:  Database     Script Date: 02/23/2010 15:02:47 ******/
CREATE DATABASE  COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Is there a flag I can set to make WiX not think the SQL syntax is WiX Properties?

Answer 1

Square brackets in the binary have to be escaped. So

USE \[master\]
GO
/****** Object:  Database \[DB_NAME\]    Script Date: 02/23/2010 15:02:47 ******/
CREATE DATABASE \[DB_NAME\] COLLATE SQL_Latin1_General_CP1_CI_AS
GO

Note that you could use this to your advantage by setting a property called DB_NAME, which WiX would then change to your actual database name.

Your other option might be to treat the whole thing as CDATA, e.g.

<![CDATA[USE [master]
GO
/****** Object:  Database [DB_NAME]    Script Date: 02/23/2010 15:02:47 ******/
CREATE DATABASE [DB_NAME] COLLATE SQL_Latin1_General_CP1_CI_AS
GO]]>

But I'm not really sure if that would work or not...

Of course, you could also just simply not use the Binary table and instead package the files in some other way. Embed them directly in the custom action DLL as a resource and pull them out from there, like in Can I embed other files in a DLL?.

Answer 2

I'm not familiar with WiX but the property substitution you are seeing seems to be explained here.

In SQL Server, quotation marks could be used instead of square brackets for identifiers (see here). E.g:

USE "master"
GO
/****** Object:  Database "DB_NAME"    Script Date: 02/23/2010 15:02:47 ******/
CREATE DATABASE "DB_NAME" COLLATE SQL_Latin1_General_CP1_CI_AS
GO

I see your scripts look like they've been generated but wonder if it would be possible to post-process them after they are generated to replace all square brackets for identifiers with quotation marks before running through Wix?