Exercise
This week has been an exercise in working around holes in other people's tools.
I am using InstallShield 10.5 Pro to create an msi to deploy an intranet ASP .NET application and the associated database. I want the installation to be smart enough to remove an existing installation of the database if it is already present, and rebuild it from scratch. Alternately, I'd like to upgrade an existing database.
Here are some of the issues I've run in to.
1) InstallShield fails to execute sp_droplogin correctly. Everything else runs correctly but this one thing. I end up having to manually delete the login via Enterprise Manager just to test my installation again. argh! I tested the script via osql and isql and Query Analyzer and it runs flawlessly. The command is simply not being executed from the installer. Opening a customer support issue with them on this issue has been pointless: the advice I got was to create a custom action and run my script using osql from the command line. Then why did we pay $ABUNCH for this tool that supposedly will allow us to build and execute our scripts in an easy manner. On top of everything, I was unabled to find a single tutorial or sample project that illustrates a database installation.
2) Here's a neat one from SQL Server. At the beginning of my upgrade installation, I create a function "dbo.fnVersion()" that returns the current database version. In this way, I can wrap my SQL commands in an IF block that checks the version before executing. A previous developer had created a bit column called bDeleted on just about every table in the system. Instead of deleting rows from the tables on a DELETE command, he was just setting a flag. I can imagine situations in which this might be a good idea, but it isn't necessary in our system. So, as a first step toward removing the column, I've written SQL like this:
IF dbo.fnVersion() = '1.0.0'
DELETE
FROM MyTable
WHERE bDeleted = 1
GO
IF dbo.fnVersion() = '1.0.0'
ALTER TABLE MyTable
DROP COLUMN bDeleted
GO
This logic is fine the first time you run it, but Query Analyzer screams at you if you run it again because bDeleted no longer exists a a column on that table. Nevermind that it's encapsulated in an IF Block. To get this to run correctly I've had to modify the script as follows:
IF dbo.fnVersion() = '1.0.0' BEGIN
DECLARE @SQL varchar(500)
SET @SQL = '
DELETE
FROM MyTable
WHERE bDeleted = 1'
EXEC (@SQL)
END
IF dbo.fnVersion() = '1.0.0'
ALTER TABLE MyTable
DROP COLUMN bDeleted
GO
There are two HeadScratching items in this: the first is the fact that the ALTER TABLE statement doesn't have a problem with the fact that the column name doesn't exist. The second is that if I'm dropping a table that doesn't exist, Query Analzyer doesn't have any problem with that. (e.g., IF dbo.fnVersion() = '1.0.0' DROP TABLE MyUnNecessaryTable GO.
Argh! Which is it? Am I supposed to have valid references to everything in my scripts or not?
3) Now I'm back to Installshield 10.5 Pro again. This time it has to do with Zipcodes. I found a table of Zipcodes and wanted to include them as part of my installation. I used Excel to generate a SQL Script of INSERT statements for the 42,000+ ZipCodes in the file. I included that script in my Installshield project and ran it. At first everything looked fine. I checked the table and sure enough it had Zipcodes in it. YAY! Further investigation however revealed that only the first 455 zipcodes had been installed. ???? I checked my script thinking that perhaps there was a syntax error at that line, but no--everything was okay. Not even sure of the reasoning that was pushing me in this direction, I added a "GO" statement to line 455 of the script and ran the installer again. This time I had 910 rows. hmmm. 910 / 2 = 455. ARGH!!! Obviously they're buffering 455 lines of SQL Commands to execute in one shot. That's probably a good idea, but it'd be nice if their application was smart enough to resume where it left off.
I had to go back to Excel and generate logic to insert a GO statement every 400 rows or so in order to get around this.
Note to self: make sure features I've added work as advertised and follow a consistent use model.