ExecuteSQL
ExecuteSQL executes custom SQL queries against a SQLite database.
By default, the function provides a loop to iterate through the data row by row.
Quick Steps
Use the Connection Editor (click the ... icon in the Connection string property) to create and test your connection string.
Use the SQL Editor to add a query to the SQL property. If the query returns data, place your row-processing logic inside the "ForEachRow" loop.
Properties
Use Transaction
Use a defined Transaction from a BeginTransaction function for the query.
Connection string
Displayed when Use Transaction is not selected.
The connection string that specifies how to connect to the database.
Transaction
Displayed when Use Transaction is selected.
Select the transaction object from a BeginTransaction function.
SQL
The query or command to execute. It can include any SQL supported by the database driver.
You can write SQL statements using the SQL Editor.
Timeout
The timeout value for the query in seconds.
Result type
Defines the columns and data types returned by your SQL query.
If Linx Designer has database access and no changes are needed, this property is automatically populated.
If there is no database connection or the query cannot run at design time, the columns must be manually defined.
Steps:
Click the Results Editor icon (...).
Select the Result Type for each column.
Enter a Result Name.
Note: If the Result Type differs from the actual column type and the value cannot be converted (e.g. 'ABC' to an integer), an error will be reported.
You can create a Complex Type and assign the entire query output to it.
Steps:
Create a Complex Type.
In the Results Editor, select your Complex Type from the Custom Type drop-down.
For each column, select the matching name from the Result Name drop-down.
Click Create from SQL to refresh the column definitions from the database. Use this to reset any manual changes back to what the query returns.
Return options
Select how the data is to be returned:
First row Returns the first row. Reports an error if the query returns no rows.
First row, else null Returns the first row, or null if the query returns no rows.
List of rows Returns all rows as a list, available for use later in the process without re-executing the query.
Row by row Returns one row at a time via a "ForEachRow" loop. Recommended for large result sets where loading everything into memory at once is not needed.
Links
Last updated