We use the wix extensions from http://msiext.codeplex.com, including for UI that allows you to configure the target database connection. The current beta of MsiExt supports SQL parsing, so you get execution with GO statements (or other separator if you're trying to do Oracle for example). It supports other extended syntax like :a to include files, and we leverage that.
We consider the first version of the database blank. Then we write update1.sql, update2.sql, etc., for every new release. We have a SQL script that figures out that the database is version 5, and that we have the newest update10.sql. So it will generate a SQL script roughly like this: :a update6.sql :a update7.sql ... You get the idea. We run the result with a single ODBCExecute wix extension entry. And voila. The whole installer is 20 lines of wix. dB. @ dblock.org Moscow|Geneva|Seattle|New York -----Original Message----- From: Matt Johnson [mailto:ma...@timeamerica.com] Sent: Thursday, May 06, 2010 5:03 PM To: General discussion for Windows Installer XML toolset. Subject: Re: [WiX-users] db install thoughts I used to use Neil's approach - executing sqlcmd from CAQuietExec. However, I quickly found that there's one major drawback - you can't control the installer's progress bar inside a sqlcmd script. Many of my db scripts are quite long, so we need some interaction with the user or they'll think it's hung and will cancel the install. I ended up writing a custom action. I have much more flexibility there, which lets me run multiple scripts depending on different scenarios. I did this with a c# managed custom action, which uses the .net SqlClient to execute the scripts. I break up the script into separate chunks with "GO" statements. At each GO, I can update the progress bar. I also do some basic string replacement to emulate sqlcmd's variable syntax. Matt Johnson MCPD, MCTS, MCSD, MCDBA Director of Application Development Time America, Inc. (480) 374-7764 - Office | (480) 203-5693 - Cell ma...@timeamerica.com | www.timeamerica.com -----Original Message----- From: Neil Sleightholm [mailto:n...@x2systems.com] Sent: Thursday, May 06, 2010 1:51 PM To: General discussion for Windows Installer XML toolset. Subject: Re: [WiX-users] db install thoughts This is what I do: http://neilsleightholm.blogspot.com/2008/08/executing-sqlcmd-from-wix.ht ml Neil -----Original Message----- From: lewisv [mailto:le...@nitorco.com] Sent: 06 May 2010 21:25 To: wix-users@lists.sourceforge.net Subject: [WiX-users] db install thoughts I am working on a install app that needs to install/update a db. The system will need to install/upgrade the database. The first time i will need to create db (with one or more sql files), Each time after that I will need to issue one or more scripts based on the current version of the db. I see how to use sqldatabase and sql script to create and upgrade the dbs. But it just looks so complicated. I will need two sqldatabase commands, one with integrated security one without. For each of the sqldatabase command i will have one of more scripts per version. Each file needs some check that will check the db version and only run if needed. ( This also includes some special work so that x.x.x as a version will be understood in the db ) I was thinking, Could i do all of this easier with a custom action. Yes I understand that it would be a little more work to begin with, but it seems to me it may be easier to maintain over the long term. I was thinking of a simple custom dll that will use "create database" to create the db on the server backup my db before the install get the db version and execute embedded sql scripts to bring it up to date I may be shooting myself in the foot. But I am interested in knowing the benefits/drawbacks to this approach. The biggest advantage I see is that the code will be easier to read ( not multiple xml components ) It will be easier to follow, since it seems this is more suited to a program centric approach rather than a msi/database approach. Easy to debug because i could run it without runnning the installer. The biggest disadvantages i see, is I will be wrapping my own sql connection stuff which bypasses the tested sqldatabase/util msi code and i will have to change my custom action every rebuild. Please give me some wisdom here. -- View this message in context: http://windows-installer-xml-wix-toolset.687559.n2.nabble.com/db-install -thoughts-tp5016287p5016287.html Sent from the wix-users mailing list archive at Nabble.com. ------------------------------------------------------------------------ ------ _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users ------------------------------------------------------------------------------ _______________________________________________ WiX-users mailing list WiX-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/wix-users
