Skip to content

SQL Command

Element Name: SQL Command

Category: Programming

Description: Represents a Transact-SQL statement or a stored procedure to execute in a SQL Server database.

Functionality: This extension module enables executing SQL transactions in a database. For instance, running a stored procedure to obtain an address associated with a specific RUT.

The following parameters must be considered for its configuration:

SQL Command Configuration (Required)

Name: Unique descriptor for configuration box identification within the flow.

[Synchronize action execution with client message display.] To ensure synchronization of transaction execution before displaying any messages, please select 'Yes' in the Lynn application.

[Expression to define the SQL database connection string without the password.] String Expression field where you should enter the connection string corresponding to database access.

Note: It's crucial to emphasize that the above field should contain the connection string excluding the database access password.

[Database connection password.] Field allowing entry of the password associated with the database connection.

[Expression to define the query to execute.] String Expression field where you can input the query commands you wish to execute in the selected transaction.

[Type of SQL command.]

  • StoredProcedure: Allows querying associated with a stored procedure.
  • Text: Specifies the transaction format in plain text.

[Type of SQL command execution.]

  • ExecuteNonQuery: Does not return data; only the number of rows affected by an insertion, update, or deletion.
  • ExecuteReader: Returns an object that can iterate over the entire result set, keeping only one record in memory at a time.
  • ExecuteScalar: Only returns the value of the first column of the first row of your query.

[A list of entities to define the parameters.] Field allowing entry of a list with input entities to use within the query to be performed.

[A list of entities to define output parameters.] Field allowing entry of a list with output entities to use within the query to be performed.

[Entity where the query result will be stored in JSON format.] Dropdown list with entities created in the Lynn application where the query result will be stored in JSON format.

[Error execution intention. Action flow will be interrupted, and the error description will be passed to the selected intention named 'Sql_Command_Error'.] Dropdown box identifying the action to be activated in case of an error.

Example:

-- Preconditions:

It's essential to have an active connection to the database and ensure that the credentials are valid and current.

-- Input:

Name: PruebaSqlC

Synchronize action execution with client message display: No

Expression to define the SQL database connection string without the password: data source = SQLServer; initial catalog = Database; user id = User;

Database connection password: XXXXXXXXX

Expression to define the query to execute: EXEC sp_consultarut

Type of SQL command: StoredProcedure

Type of SQL command execution: ExecuteNonQuery

A list of entities to define the parameters: @rut

A list of entities to define output parameters: @rut

Entity where the query result will be stored in JSON format: resultadoprueba

Error execution intention. Action flow will be interrupted, and the error description will be passed to the selected intention named 'Sql_Command_Error': Error_Handler

-- Result:

When the user enters the requested RUT in the stored procedure, for example, the associated address can be obtained.