[wix-users] Override Sql DB in WiX

[Edwin Castro]

Based on my recent reading of the SQL custom action code in WiX 3.9R2, I
*think* the answer is no.

*IFF*  the SQL text in SqlString could be provided as inner text or in
CDATA *AND* that text was Formatted, then you could theoretically use
[SQLDB] in your script and have that text replaced by the value of the
SQLDB property *BUT* that would require that people not use [ and ] in
their SQL text which is allowed by SQL Server. If they wanted or needed to
use [ and ], then they would have to escape them like so [\[] and [\]]
which is a usability nightmare in the general case. The WiX SQL custom
actions do *NOT* provide this functionality and I would guess this might be
the reason why.

--
Edwin G. Castro

On Wed, Mar 7, 2018 at 9:01 AM, Ven H via wix-users <
wix-users at lists.wixtoolset.org> wrote:

> I have an SQL script CREATE_TABLE.sql which has the following statement
>
> use MsiTestDB
> go
>
> if not exists( select 1 from information_schema.tables where table_name =
> 'EMPLOYEES' )
> begin
> create table dbo.EMPLOYEES
> (
> EMP_ID INT NOT NULL,
> EMP_NAME NVARCHAR(100) NOT NULL
> )
> end
> go
>
> I have the following markup in my wxs file.
>
>  <Wix xmlns="http://schemas.microsoft.com/wix/2006/wi" xmlns:util="
> http://schemas.microsoft.com/wix/UtilExtension" xmlns:sql="
> http://schemas.microsoft.com/wix/SqlExtension">
> <Fragment>
>
>     <util:User Id="SQLUser" Name="[SQLUSER]" Password="[SQLPASSWORD]" />
>     <Binary Id="binCreateTbl" SourceFile="CREATE_TABLE.sql" />
>
>     <Component Id="dbComp" Guid="329600D4-F7F4-4038-9963-4A4C25D5AE7F"
> Directory="INSTALLFOLDER" KeyPath="yes">
>
>       <sql:SqlDatabase Id="msiTestDB"
> Database="[SQLDB]"
> Server="[SQLSERVER]" Instance="[SQLINSTANCE]"
> CreateOnInstall="yes"
> User="SQLUser" />
>
>         <sql:SqlScript BinaryKey="binCreateTbl" Id="script_CreateTbl"
> ExecuteOnInstall="yes" ContinueOnError="no" SqlDb="msiTestDB"  />
>
>     </Component>
> </Fragment>
> </Wix>
>
>  The properties (within square brackets) are provided from the UI. The SQL
> Script has a "Use MsiTestDB" statement. So, even if I pass a new and
> different value for the SQLDB property from the UI, it always executes and
> creates the table in the MsiTestDB. Is it possible in WiX to workaround or
> override this behavior?
>
>
> Regards,
> Venkatesh
>
> ____________________________________________________________________
> WiX Toolset Users Mailing List provided by FireGiant
> http://www.firegiant.com/
>