ExecuteSQL

With ExecuteSQL, you can create and execute custom SQL queries. ExecuteSQL is commonly used for SELECT, INSERT, UPDATE, and DELETE queries.

By default, the function provides a loop to iterate through the data row by row.

Quick Steps

1. Use the Connection Editor (click the … icon in the ConnectionString property) to create and test your connection string.
2. Use the SQL Editor to add a query to the SQL property. If the query returns data, place logic for the processing of each row inside the “ForEachRow” loop.

---

Properties

Use Transaction

Use a defined Transaction object 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 and calls 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.

To configure the output columns of the SQL statement, follow these steps:

Steps:

1. Click on the Results Editor icon (…).
2. Select the Result Type for each column.
3. Enter a Result Name.

Note

Note:
If the Result Type is different from the original Type, and a returned value is not convertible (e.g. ‘ABC’ can’t be converted to an integer type), you will receive an error.

You can create a Complex Type and assign the entire query output to it.

Steps:

1. Create a Complex Type.
2. In the Results Editor, select your Complex Type from the Custom Type drop-down.
3. For each column, select from the Result Name drop-downs the names as defined in your Complex Type.

Click the Create from SQL button to return the original column details from the database, as per your SQL query. You can click this button to refresh/reset the column details.

Return options

Select how the data is to be returned:

• First row
  The function will return the first row returned by the query. If no data is returned by the query, an error will be reported.
• First row, else null
  The function will return the first row returned by the query. If no data is returned by the query, a null value will be returned.
• List of rows
  The function will return all rows in one list. The list can then be used later in the process without having to execute the query again.
• Row by row
  The function will automatically return one row at a time. You will see a “ForEachRow” loop icon as a child of this function. Any function you attach to the results will be inside of the loop. This is recommended whenever you expect to retrieve multiple items, but you don’t need the complete list of items all at once.

Links

• MySQL connection strings
• Wikipedia: Database connection
• The Connection Strings Reference