Skip to main content
Version: 8.0

Configuration elements of the SQL script type

Level: beginner

Configuration element of the SQL script type is an entity that lets you implement database queries written in SQL. The purpose of an SQL script is to create databas objects, for example, views, procedures, functions, or execute other Creatio database queries.

The item of the Add drop-down list in the toolbar of the Configuration section workspace represents the configuration element of the SQL script type you can add in Creatio IDE.

Learn more about configuration element types in a separate article: Operations in Creatio IDE.

The SQL script item in the Type drop-down list in the toolbar of the Configuration section workspace represents the configuration element of the SQL script type.

View the SQL script type in the figure below.

Learn more about configuration element types in a separate article: Operations in Creatio IDE.

Implement an SQL script

  1. Go to the Configuration section and select a custom package to add the configuration element.

  2. Click AddSQL script on the section list toolbar.

  3. Fill out the configuration element properties in the Script Designer.

    Fill out the main properties of the configuration element:

    • Code is the schema name. Required. Name the SQL script using the template below.

      [Prefix][Operation][Object][DBMS];

      [Prefix] is the prefix of the configuration element name (by default, Usr) specified in the Prefix for object name (SchemaNamePrefix code) system setting. Can contain alphanumeric characters. Creatio checks whether the prefix exists and matches the system setting when you go to a different configuration element property. If the prefix does not exist or does not match, Creatio sends a corresponding user notification.

      [Operation] is the operation the SQL script executes Available values: Insert, Update, Delete. Optional for SQL scripts that create objects (the Create value).

      [Object] is the object with which the SQL script interacts. Required.

      [DBMS] is the database type for which you are developing the SQL script. Required. Must match the DBMS type property value of the SQL script.

    • DBMS type is the database type for which you are developing the SQL script. Required. Available values: "MSSql," "Oracle," "PostgreSql."

    • Installation type is the script execution order when installing the package. Required.

      Available values:

      • Select "BeforePackage" to execute the SQL script before the package installation.
      • Select "AfterPackage" to execute the SQL script after the package installation.
      • Select "AfterSchemaData" to execute the SQL script after the package data (configuration elements of the Data type) installation.
      • Select "UninstallApp" to execute the SQL script when deleting the package to which the script is bound.
    • Package is the custom package where you create the configuration element. The property is populated automatically and non-editable.

    • Backward compatible specifies whether the script is backward compatible. Backward compatible SQL scripts are available in Creatio version 8.0.3 and later. Learn more in a separate article: Backward compatible SQL scripts.

    Click Apply to apply the properties.

    The properties area of the Script Designer lets you:

    • edit the main configuration element properties ( button)
    • specify the additional configuration element properties ( button)

    The additional properties of the configuration element are as follows:

    • Depends on SQL Scripts. Lets you select the SQL scripts to execute before executing the current script.
    • Dependent SQL Scripts. Contains the SQL scripts to execute after executing the current script. The property is populated automatically and non-editable.

    To execute SQL scripts in a set order:

    1. Select the corresponding installation type (the value of the Installation type property).
    2. Set the needed dependencies between scripts (Depends on SQL Scripts and Dependent SQL Scripts properties).
  4. Click Validation on the Script Designer’s toolbar to validate the syntax of the SQL script.

  5. Click Save on the Script Designer’s toolbar to save the changes to configuration element properties.

Use hot keys

To display the index of available hot keys:

  1. Open the Script Designer.
  2. Click on the toolbar.

Hot key

Action

Ctrl+A

Select all

Ctrl+Z

Undo

Shift+Ctrl+Z

Redo

Ctrl+F

Find

F3

Find next

Shift+F3

Find previous

Shift+Ctrl+F

Replace

Shift+Ctrl+R

Replace all

Ctrl+Y

Delete line

Alt+Left

Go to line start

Alt+Right

Go to line end

Alt+G

Jump to line

F11

Fullscreen code editor

Esc

Exit fullscreen mode

Ctrl+Space

Call autocomplete

Ctrl+S

Save the changes. Available for Creatio 8.0.10 and later.