ExecuteSQL
Last updated
Last updated
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.
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 function.
The connection string that specifies how to connect to the database.
Select the transaction object from a BeginTransaction function.
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.
The timeout value for the query in seconds.
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:
Click on the Results Editor icon (...).
Select the Result Type for each column.
Enter a Result Name.
You can also create a Complex Type and assign an entire query output to that type.
Steps:
In the Results Editor, select your Complex Type from the Custom Type drop-down.
For each column, select from the Result Name drop-downs the names as defined in your Complex Type.
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.
Displayed when theis set to one of the available database types, and not to 'Use transaction'.
Displayed when is set to 'Use transaction'.
Create a .
Click the Create from SQL button to return the original column details from the database, as per your . You can click this button to refresh / reset the column details.