ExecuteSQL

ExecuteSQL executes a SQL query and fetches data returned by the query. By default, the function will provide a loop to iterate through the data row by row.

Use the "Connection Editor" tool (the icon in the ConnectionString field) to easily create and test a connection string.
Add a query to the SQL property. If the query returns data, place logic for the processing of each row inside the "ForEachRow" loop.

Connection Editor

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, and ODBC. Alternatively, instead of creating a new connection, choose Use transaction to use a Transaction object from a BeginTransaction function.

Connection string

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

Transaction

Visible if Connection type is set to Use transaction. Allows to 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.

Return options

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.

Return type

The columns the query returns as well as their data types.

If the Linx Application 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 the Linx Application 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.

Return Type

You can also create a Type and assign an entire query output to that type. If you create a Person type, for example, you can select data for a person from the database and assign them to the object.