Geek Answers Handbook

Wix - SQL brackets in binary files

UPDATE:

While the answers below work as alternative solutions, I would like to mention that my original method really works. After looking at the answers below, I found out that my call to Session.Log () actually deleted [...] when it was sent to the log file. The square brackets remained in my SQL as I fed it into the SQLCommand object. My problem was that SQL (of which I only posted the first few lines) had "GO in it" which are not SQL commands. As soon as I solved this problem, it worked :)

* (Reminder, as much information as possible, is always useful: D)

On 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>

Then I use a custom action to pull sql from the binary table, and string to replace the database name (provided by the text box 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 as follows:

 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 row that I exit from the “Binary” table seems to pull out all the [...] content, such as WiX Properties, so I stayed 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 that I can set to make WiX not think that SQL syntax is WiX properties?

+8
sql-server wix
source share
2 answers

The brackets in the binary must 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 can take advantage of this by setting a property called DB_NAME , which WiX will then change to your actual database name.

Another option would be to consider all this as CDATA, for example

 <![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 sure if this will work or not ...

Of course, you could simply simply not use the binary table and instead pack the files in some other way. Insert them directly into the custom action DLL as a resource and pull them out of there, for example, in. Can I embed other files in the DLL? .

+5
source share

I am not familiar with WiX, but the property substitution that you see seems to be explained here .

In SQL Server, quotation marks can be used instead of square brackets for identifiers (see here ). For example:

 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 that your scripts look as if they were generated, but are wondering if it is possible to process them after they are created to replace all square brackets for identifiers with quotes before starting Wix?

+2
source share

More articles:


All Articles