|
Note: Please make sure to use the SQL Databases view for database object creation before the wizard for SQL script creation is used. Each SQL script needs to operate on a specific database connection, which is established by the definition of an SQL database object. It is not possible to create SQL database objects directly via the Create SQL Script wizard interface. |
In order to add an SQL script to a packaging project, users go to the SQL Scripts view of the Visual Designer mode.
With a click on the Create new... button, the options menu is displayed. Click on SQL database to invoke the New SQL Database Wizard.
Work your way through the steps of the wizard to define all required properties for the new item.
At any time, using the Next or Back buttons, which are displayed at the bottom of the wizard dialog, allows to navigate within the already processed steps.
To exit the wizard without creating a new object, use the Cancel button, also located at the bottom of the wizard dialog.
Step 1: Database
Database
Please select the database object the new SQL script has to operate on. The database object has to be provided as row within the RPSqlDatabase table, since this table is read to provide the list of databases users can select from within this wizard step.
Once the database object is selected from the list, the matching server address is automatically displayed as additional informative reference.
User
RayPack allows to run SQL scripts either via Windows Authentication, or as a specific user.
The default setting is Windows Authentication, indicated by the active checkbox Use Windows Authentication. To enable the usage of individual user credentials, the checkbox needs to be deselected. Once it is inactive, the controls for user object management become active for user selection.
Individual user credentials have to be given in the form of a RayPack user object. To select or create a user object, click on the browse button [...] at the right-hand side of the user input field. The Select User dialog is displayed. Please refer to the Common dialogs section for further details on how to manage user data objects.
|
Be aware: User objects may very likely be used at several places within SQL database, script, and IIS management structures. When the same user item is referred to from different locations, changing the properties of that user item actually takes effect for all related objects. Please double-check the correctness of user profile changes, and make sure all related objects are still valid and operational with the modified set of properties. |
Step 2: Advanced
Name
A default name for the new script object is automatically generated once this wizard step is displayed. It is combined by the Prefix RPSqlScript and an automatically calculated and incremented index value. The script name has to be defined as unique alphanumerical string of max. 72 characters length.
Even though blanks and special characters (e. g. question mark (?) or period (.)) are technically allowed, it is recommended to stick to alphanumerical script names.
Component
Each SQL script object has to be related to a component item of the packaging project. RayPack automatically creates a new component when an SQL database item is created. It is recommended to add SQL scripts to the same component as the parent SQL database object, which is why the component of the SQL database selected during step 1 is pre-selected for the new SQL script.
To select another existing component as parent object of the SQL script item, users click on the downwards-pointing arrow at the right-hand side of the component selector control. A list of existing components is displayed. The new selection is done with a click on any of the given components.
To create a new component for the SQL script object, users click on the browse button [...] at the right-hand side of the component control. The common dialog type Select Component is displayed, ready for creating and selecting components.
Sequence
The sequence defines in which specific order SQL scripts are executed on the target database. The first SQL script defined for a database is always executed first, whilst additional scripts may be assigned to be run either at the beginning of the SQL sequence or after any of the already existing SQL scripts.
Please be aware that the sequence does not consider scripts that are run on other database objects within the packaging project. Adjust the order of database script groups at the SQL scripts list view to manage the sequence of database manipulations at that higher level.
Attributes
The attributes column of the RPSqlScript table is equipped to provide the following script execution options:
•Execute on install
The script will be executed when the package is installed.
•Execute on uninstall
The script will be executed when the package is uninstalled.
•Continue on error
If the script execution causes an error, the package run-time execution is not aborted, no matter if it is an installation, repair or uninstallation procedure.
•Rollback on install
The rollback mechanism for this SQL script is executed when the package is installed.
•Rollback on uninstall
The rollback mechanism for this SQL script is executed when the package is installed.
Users may activate each execution option by selecting the checkbox at the left-hand side of the option label.
|
Be aware: If neither "Execute on install" nor "Execute on uninstall" are selected, the script may very well never run on the target database during package run-time. |
Step 3: Script
The actual content of the SQL script has to be defined during this wizard step. There are three different options for the source type of the script:
Binary Table
The SQL statements are already present as content of a binary stream object within the current packaging project.
Once this option is chosen from the Source Type selector, another selector "Binary" is displayed, waiting for the user to pick the desired binary file from the list of available binaries.
As soon as a binary has been selected, the content of that binary file is displayed within the Script text area below.
|
Be aware: Changing the content of the SQL script text area once a binary object has been selected will cause an update of the content stored within the binary resource itself. The SQL script shown here is not a copy that will be created within the SQL script object, but the actual binary content. Please make sure to double-check the binary usage at other places before the content is changed within this view. |
Property Table
The SQL statements are already present as content of a property within the current packaging project.
Once this option is chosen from the Source Type selector, another selector "Property" is displayed, waiting for the user to pick the desired property from the list of available Property table rows.
As soon as a property has been selected, the value of that property is displayed within the Script text area below.
|
Be aware: Changing the content of the SQL script text area once a binary object has been selected will cause an update of the content stored within the property itself. The SQL script shown here is not a copy that will be created within the SQL script object, but the actual property content. Please make sure to double-check the property usage at other places before the content is changed within this view. |
Text
This source type option stores the script as text within the RPSqlScript table column Target. Even if the same SQL string is used several times within the packaging project as text source for SQL script objects, there will be a separate source copy for each script object.
|
Be aware: The source type of the SQL script may not be changed via the Visual Designer user interface at a later time. If Property has been selected and the SQL script object has been saved, the source type setting will always be Property. However, it is possible to edit which property the script content is stored within. (Or to change the binary that is read for the Binary source type option). |
Load from file...
No matter which source type has been selected, it is always possible to replace the current content of the SQL script input field with a string retrieved from any file within reach. Users simply click on the Load from file button, navigate to the desired *.sql file, and click Open. The file content immediately replaces the existing SQL script content string. Please keep in mind, that this function reads the file content and writes the string into one of the source types references named above (a binary, a property, the Target column of the SQL script database row). However, the file itself is not saved as physical resource within the packaging project. This means it will not be available for direct access, e. g. via the Files & Folders view.
Switch off '[', ']' and Switch on '[', ']'
Square brackets may either be considered to be triggers for special SQL script notations, such as delimited identifiers, or as simple text signs without special functionality. In order to support packagers in their aim for clean and unambiguous source definitions, the switch for square brackets has been added to the SQL script text area.
Switching square brackets off leads to the explicit escaping of square bracket, which means that each opening and closing square bracket is prefixed with escape triggers. The escaped bracket signs are no longer considered to be special notations, but as a mere opening / closing bracket signs without additional functionality.
Example:
The original text "This is [sample] text" turns into "This is [\[]sample[\]] text" once square brackets are turned off.
Switching square brackets on removes escaping from already existing escaped opening / closing bracket signs. They are considered to be special characters (SQL delimited identifiers) again.
Example:
The original text "This is [sample] text with different [\[]square[\]] bracket usages." turns into "This is [sample] text with different [square] bracket usages." once square brackets are turned on.
Step 4: Replacement
If search & replace activities on SQL scripts are required for execution at package run-time, this is the interface to create them. With a click on the Add button, a new replacement task is generated with default values. To change any of these values, users have to double-click the cell value of the list item they want to modify. The current value is marked and ready for direct inline modification:
Sequence
The sequence value is an automatically incremented integer value, indicating the order of replacement execution. The item with the lowest sequence value is run first.
Search
The search string has to be given as plain text. There is no support for wild card usage or regular expression based search strings.
Please keep in mind, that the standard search algorithm is case insensitive. To change that, the attributes value 2 has to be set for the replacement job.
Replace
The string that will be put instead of the matched keyword from Search.
Please keep in mind, that the standard search algorithm replaces all matches. To stop the replacement after the first match, the attributes value 4 has to be set for the replacement job.
Attributes
Users may either enter any combination of the available attribute bit value representations manually, or use the helping hand of the Attributes Editor for this property. To display the Attributes Editor, users have to double-click the current value of the attributes column, and use the downwards pointing arrow button. Checkboxes become visible, allowing to individually enable or disable each attribute option:
1 = Match whole word only
2 = Match case
4 = Replace once only
The default attribute value for new replacement tasks is 1, which means that the search is resolved word by word.
To remove a replacement task, users have to select it from the list of existing replacements, and use the Remove button below the list. The task is removed immediately, without an intermediate confirm dialog.
Step 5: Summary
Use the summary page to check the correctness of the SQL script properties that were defined during the previous wizard steps.
•If all properties are set as required, click Process to finally create the SQL script item.
•If changes are due, click Back until the wizard step with the incorrect property definition(s) is displayed and make modifications as required.
Please note that changes in an early step may lead to different defaults or options in any later step. Therefore, please verify that all steps contain the desired settings whilst NEXTing to the summary page again.
Step 6: Finished
Once the new SQL script object has been created, the wizard can be closed by using the Finish button at its lower right corner. The SQL Scripts view is updated, and the list of existing items contains the newly created object at the lowest position within the group of scripts saved for the same target database.