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.