Execute SQL Statement

This action sends SQL commands to your SQL server and collects results. Use commands SELECT, INSERT, UPDATE, and DELETE.

Use Execute SQL Statement action to achieve these two goals:

  • Obtain additional data from a database: You want to print labels with data from your database, but not all of the required values. For example, only values for Product ID and Description, but not for Price. Create an SQL statement to look up the values for Price in the SQL database.

    SELECT Price FROM Products
WHERE ID = :(Product ID)

    The ID is field in the database, Product ID is a variable defined in the trigger.

  • Update or delete records in a database: After your labels are printed, update the database records and inform the system that the particular records have already been processed. SQL code example: Set the table field AlreadyPrinted value to True for the currently processed record.

    UPDATE Products
SET AlreadyPrinted = True
WHERE ID = :(Product ID)

    Or delete the current record from a database, because it's not needed anymore.

    DELETE FROM Products
WHERE ID = :(Product ID)

    The ID is field in the database, Product ID is a variable defined in the trigger.

Note

To use values of variables inside SQL statements, insert colon (:) in front of variable names. This signals that a variable name follows.

Important

When you create your solution with a database connection, use prompt variables on your labels instead of database fields.

Use the same names for prompt variables as are defined for database fields, for example:

Database field: food_products_1c.ProdCode

Prompt variable on your label: ProdCode

Loftware then automatically maps corresponding variables with database fields.

About group identifies the selected action.

  • Name: allows you to define a custom action name. This makes actions easily recognizable on the solution's list of actions. By default, the action name is taken from its type.
  • Description: custom information about the action. Enter a description to explain the purpose and role of action in a solution.
  • Action type: read-only information about the selected action type.

Database Connection group defines the database connection that is used for the statement.

Tip

Before you can send an SQL sentence to a database, set up the database connection. Click the Define button and follow the on-screen instructions. You can connect to a data source that can be controlled using SQL commands, so you cannot use text (CSV) or Excel files.

SQL Statement group defines an SQL statement or query to be executed.

Tip

Statements from Data Manipulation Language (DML) are allowed to execute queries upon existing database tables.

Use standard SQL statements, such as SELECT, INSERT, DELETE and UPDATE, including joins, function, and keywords. The statements in DDL language that are used to create databases and tables (CREATE DATABASE, CREATE TABLE), or to delete them (DROP TABLE) are not permitted.

  • Test: opens Data Preview section. Simulate execution (selected by default) tests the execution of SQL statements. Click Execute to run the simulation.

    Tip

    Data Preview section allows you to test the execution of your SQL statement upon a live set of data. To protect the data from accidental updates, make sure the option Simulate execution is enabled. The statements INSERT, DELETE and UPDATE will execute. This enables you to gain feedback on how many records will be affected, then all of the transactions will be reversed.

    If you use variables in the SQL statement, you will be able to enter their values for the test execution.

  • Insert data source: inserts predefined or newly created variables into an SQL statement.

  • Export/Import: enables exporting and importing SQL statements to/from an external file.

  • Execution mode: specifies the explicit mode of SQL statement execution.

    Tip

    In cases of complex SQL queries, it becomes increasingly difficult to automatically determine what is the supposed action. If the built-in logic has troubles identifying your intent, manually select the main action.

    • Automatic: determines the action automatically.

    • Returns set of records (SELECT): receives the data set with records.

    • Does not return set of records (INSERT, DELETE, UPDATE): use this option if executing a query that does not return the records. Either insert new records, delete or update the existing records. The result is a status response reporting the number of rows that were affected by your query.

  • Execution timeout: allows you to define the time delay for sending your commands to the SQL server. Use the execution timeout if you are sending multiple consecutive SQL commands that require a longer processing time.

    Type the requested timeout duration in seconds. By default, the execution timeout duration is 60 s. If you want your database provider to define the timeout, type in 0 s.

Result group allows you to set how the SQL statement result should be stored, and to define action iteration.

  • Save Data to Variable: selects or creates a variable to store the SQL statement result. This option depends on the selected Execution mode.

    • Result of SELECT statement. After you execute a SELECT statement, it results in a data set of records. You receive a CSV-formatted text content. The first line contains field names returned in a result. The next lines contain records.

      To extract the values from the returned data set and to use them in other actions, define and execute the action Use Data Filter upon the contents of this variable (this action is available in Automation Builder).

    • Result of INSERT, DELETE and UPDATE statements. If you use INSERT, DELETE and UPDATE statements, the result is a number indicating the number of records affected in the table.

  • Iterate for Every Record. If enabled, Loftware automatically adds a new action For Every Record. See more about this action in a dedicated topic.

    All nested actions are repeated for each record that has been returned using the SQL statement.

    Note

    Automatic database field mapping is enabled. Prompt variables on your label automatically connect to your database fields with the same names. For example:

    Database field: food_products_1c.ProdCode

    Prompt variable on your label: ProdCode

Retry on failure group allows you to configure the action to continually retry establishing the connection to a database server in case the first attempt is unsuccessful. If the action fails to connect within the defined number of attempts, an error is raised.

  • Retry attempts: specifies the number of tries to connect to the database server.

  • Retry interval: specifies the duration of time between individual retry attempts.

Warning

Do not put the Database Table object on your printing form if you use Execute SQL Statement action. Results might be wrong.

Example

You want to print labels with data from your food_products_1c database but only records with predefined field value UseBy.

You define UseBy value with variable ProdUser. In this case, ProdUser value is "3".

After each label is printed, Loftware writes database values in a text file on your disc. Use the following actions:

UUID-507bcc38-919d-635b-5209-8706fb3d61e2.png

UUID-cb65b196-d5b2-4725-89d9-14f266f5cb64.png

Only labels where UseBy value equals "3" are printed and values are written in a text file:

UUID-637fe74b-7603-a307-be15-a9e8afba04f5.png

Action Execution and Error Handling

Each action can be set as a conditional action. Conditional actions only run when the defined conditions allow them to be run. To define these conditions, click Show execution and error handling options.

Execution options are:

  • Enabled: specifies if the action is enabled or disabled. Only enabled actions will execute. This functionality may be used while testing a form.
  • Condition: defines one-line programming expression that must provide a Boolean value (true or false). When the result of the expression is true, the action will execute. Condition offers a way to avoid executing actions every time.

Error handling options are:

  • Ignore failure: specifies whether an error should be ignored. If enabled, the execution of actions continues even if the current action fails.

    Note

    Nested actions that depend on the current action do not execute in case of a failure. The execution of actions continues with the next action on the same level as the current action. The error is logged but does not break the execution of the action.

    • Example

    At the end of printing, you might want to send the status update to an external application using the HTTP Request action. If the printing action fails, action processing stops. In order to execute the reporting even after the failed print action, the Print Label action must have the option Ignore failure enabled.

  • Save error to variable: allows you to select or create a variable to save the error to. The same cause of the error is also saved to internal variables ActionLastErrorId and ActionLastErrorDesc.