SQL Script Generation and Execution

When a package contains a Manage List, a database lookup, a queue, or a database activity, while configuring the package template in source environment, a related script is auto generated and populated in the SQL script text area. You do not need to write the script or run the script manually. While installing the package in the target environment, the SQL script is run first and then the package is installed. The SQL script does not execute during updation or uninstallation of package.

In artifacts such as Database Connection, the SQL scripts are not auto-generated. You can add a SQL script, if required.

If a list exists in the target environment, the list items are not installed or tables are not updated. For example, consider that a list is created and packaged in the source environment. While packaging, the relevant SQL script is auto-generated. When this package is installed in the target environment, the SQL script is executed and the relevant tables are created. If new fields are added to the list tables in the source environment, and a new package is generated, the auto-generated SQL script is also updated with the new information. While installing (or updating) the new package in the target environment, the package verifies if the object is available in the target environment. As the list object exists from the previous installation, the newly auto-generated SQL script does not execute, but a success message appears. Though the list gets updated, the relevant tables does not get updated. You must ensure to update the tables in the target environment based on the modifications done.

Two objects with the same name, irrespective of their types, cannot exist in the target environment during package installation. Consider that a table, Department exists in the package template. While installing the package in the target environment, the auto-generated SQL script executes if the target environment does not contain a table with the same name, Department. However, if the target environment contains another object type, such as an index with the same name, Department, the auto-generated SQL script executes, but returns an exception.

If a package template exists in an upgraded environment, then its existing artifacts does not contain any auto generated script. In such case, you must create a new package with the existing artifacts. However, if you want to use the existing package, use the Complete Rebuild option to rebuild the package template. The Complete Rebuild option generates the script for the selected and supported artifact. So the same will be executed during the installation in the target environment . The rebuild option appears only after you modify an artifact in the existing package template.

In an upgraded environment, consider that a package is already installed in the target environment. Now, consider that a database lookup (or any other supported artifact) is added to the package and a package template is created in the source environment. Now, while updating the package in the target environment, though the package template contains the auto-generated SQL script, the script will not be executed. To overcome this behavior, you can do one of the following:

1. Add the new artifact to a new package template in the source environment, and then install it in the target environment.

2. If the artifact is added to the existing package template, then execute the SQL script manually in the target environment.

3. Uninstall the package from the target environment and install it again with the added artifacts (not recommended).

Note:
- The following keywords are restricted. Hence, only these keywords can be validated as part of "Validate SQL Script".
- Delete
-Truncate
- Drop
- Update
- Create User
- Grant
- Revoke
- Execute

- The keyword "Alter" is restricted for DB Connection and Queue.

See Also:

• Queue

• Manage List

• Database Connection

• Lookup

• Database Activity in Workflows