SQL Script Text Replacement Sequencing problem?

[mumbles]

Hello All,

I'm experiencing a rather strange issue i'm assuming is related to sequencing.

Firstly, does anyone in the know - know whether SQL Text replacement is the same action/sequence item as Text File Changes?

If not, then i think this may be my problem. I have a SQL script which i don't want to use SQL Text Changes and instead use Text File Changes. I finally have my .sql files the way i want them in terms of changes.

Then when i go to run them, via the log file, i saw the variables not being set by the time the script runs. The variables are still in their original form.

I also noticed that in the script the text file changes happened seemingly before the SQL files were run.

So i'm at a loss. All i can think is that the replacement is happening in some other sequence which in reality is after the SQL scripts are run.

Any advice is greatly appreciated.

[mumbles]

I tried moving some sequencing around, but then i get errors. Anyone able to lend a hand?

[skolte]

Please look at this thread. Just yesterday I posted a working sample there that shows how to use SQL text replacement with MSI properties. Let me know if you have any questions.

[mumbles]

Thanks Skolte,

I am using some properties to make changes.... but when the script runs, it runs without the variables being changed. Again, i'm not using SQL text replacement. I'm using SearchReplace, Text replacement changes...

Anyone else know about the sequencing here?

TIA

[skolte]

You don't necessarily need custom actions. That was just what the other user wanted.

If I am understanding you correctly, what you are looking for is a way to replace some text in your predefined SQL scripts before they are executed and you don't want to use SQL replacement. Instead you want to use Text Replacement feature, fix the SQL script and finally execute it, and it's not working properly. Correct me if I am wrong.

If that is what you are looking for, similar to my thread earlier, in place of MSI Properties, you can use global variables, set them as per your needs in your InstallScript. Now, write your own separate InstallScript method just to change your predefined sql scripts the way you want. You can call this method from different events.

SQL Scripts can either be included in your installer project (as files under some components or in SQL Connection or simply as Support Files). Then call the new InstallScript method from your SQL events such as OnSQLLogin() or even OnFirstUIBefore().

[mumbles]

skolte wrote:
You don't necessarily need custom actions. That was just what the other user wanted.

If I am understanding you correctly, what you are looking for is a way to replace some text in your predefined SQL scripts before they are executed and you don't want to use SQL replacement. Instead you want to use Text Replacement feature, fix the SQL script and finally execute it, and it's not working properly. Correct me if I am wrong.

This is exactly right.

skolte wrote:

If that is what you are looking for, similar to my thread earlier, in place of MSI Properties, you can use global variables, set them as per your needs in your InstallScript. Now, write your own separate InstallScript method just to change your predefined sql scripts the way you want. You can call this method from different events.

Ok, there is a problem here. It's not the method in which i set the variables that i collect and write to the SQL files. It's when i'm writing them. Apparently, even though the log shows the variables being changed before the SQL scripts run, whent the script runs (according to the log), i'm seeing the variables in the raw state. Not the changed state.

However, when i check the SQL files in the file system, once installed, they are fully changed correctly. Which means my method for changing them is fine. It's just WHEN they are getting changed...

Now i believe it's either a sequencing thing. Or SQLCosting(Which is running before my searchreplace) is throwing the SQL file in memory and making changes there.

Any knowledge around that one?

skolte wrote:

SQL Scripts can either be included in your installer project (as files under some components or in SQL Connection

I have them as components and being called in the SQL Connection.

[hidenori]

mumbles,

If you are using the SQL Scripts view to execute SQL statements, it is not possible to perform text replacemenets on your SQL script files using the Text File Changes view. Basically InstallShield extracts your SQL script files from your .msi to a temporary location, decrypts, executes, and deletes them within one custom action. If you really want to accomplish it, you would need to write your own code to execute your SQL script files.

Hope that helps.

[mumbles]

Hidenori,

Thank you for the response. I can't sequence this SQL portion later in the installation?

If not, i guess i can just pass the variables collected in the SQL Login dialog to a custom action which later runs SQLCMD... was trying to use most of Installshields built-in functionality though. 😞