ExecuteSQL

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

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

Quick Steps
  1. Select your Connection type, e.g. SQL Server, ODBC, etc
  2. Use the Connection Editor (click the ... icon in the ConnectionString property) to create and test your connection string.
  3. 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

Connection type

The type of database driver to use to connect to the database. The supported driver types are:

  • SQL Server

  • Oracle

  • OLE DB

  • ODBC

Alternatively, instead of creating a new connection, choose Use transaction to use a Transaction object from a BeginTransaction function.

Connection string

Displayed when the Connection type is set to one of the available database types, and not to 'Use transaction'.

The connection string that specifies how to connect to the database.

Transaction

Displayed when Connection type is set to 'Use transaction'.

Select the transaction object from a BeginTransaction function.

SQL

The query or command to execute. The query can contain any SQL and calls supported by the database driver, including stored procedures.

You can write SQL statements using the SQL Editor.

Timeout

The timeout value for the query in seconds.

Result type

The columns that your SQL query returns, as well as their data types.

If Linx Designer has access to the database and you do not want to change any type or column name, you can ignore this property. The values will be populated automatically from your SQL query for you.

If Linx Designer does not have a connection to the database or does not have a query to run at design time, you need to define the query output columns using this property manually.

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:
If the Result Type is different to the original Type, and a returned value is not convertable (e.g. 'ABC' can't be converted to an integer type), you will receive an error.

You can also create a Complex Type and assign an entire query output to that type.

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 empty row
    The function will return the first row returned by the query. If no data is returned by the query, the function will return a row containing default values.

  • 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.


videos icon How to run a query on your DB



Wikipedia: Connection string

Wikipedia: Database connection

The Connection Strings Reference