This action is similar to the Dataset Loop. The difference is that the action works within already open session.
You need to use as a query the expressions which return a set of records or strings. For instance, queries SELECT. DB Loop sorts out the whole set of records in a loop sequentially.
You must be familiar with SQL syntax to use this action.
Enter the query string in SQL syntax.
Assign Field Names to Variable
Stores the field names separated by commas in a specified variable.
Assign Field Values to Variable
Stores the field values separated by commas in a specified variable.
With Memo Fields
Specifies that the query should include memo fields.
Store Memos as "Comma Text"
Indicates that the memo fields should be stored as comma delimited text.
Sometimes using of parameters of a query is more convenient instead of generation of the query by means of variables. For example, when the variable value contains quotes or another special symbols. If you use variables (or macro substitution) when forming SQL inquiry, availability such symbols can "break" the syntax of the query and you will get a runtime error.
Parameters SQL are designated in the inquiry by means of colon (:) before the parameter name.
Select * from employee where hiredate > :dt and salary >= :amount
:dt and :amount are parameters of the query.
Parameters can be several types:
|•||Integer – an integer number|
|•||Float – a real number or the number with a floating dot.|
|•||Boolean – a logical value True or False. |
|•||DateTime – a date and time. The value of such parameter must be defined in current system format of date and time (see the system regional settings). Notice that using of date-time can have its own characteristics for different databases. For example, it is better specify such parameters for the MySQL as a string parameter in the format YYYY-MM-DD or YYYY-MM-DD hh:nn:ss.|
Fill from SQL
Fill the list of parameters from the current query. If a parameter is already in the list, this parameter will be without change. The function fills only the missing parameters in the list.
Add a parameter manually. You will be proposed to type or select from the list the parameter name, select the parameter type and input an expression as the parameter value.
Using of variables in a parameter name and in its value is allowed. The RoboTask will substitute variable values instead of variables before performing the SQL.
Edit the properties of the selected parameter.
Delete the selected parameter from the list.
If you specify the parameter which is absent in the query, the action ignores this parameter when performing and writes the proper warning in the log of the task.
You can tune up the saving of the field value to file on this tab. As a rule, the necessity of export to file exists for large fields (BLOB, CLOB, Memo). These fields do not have a fixed size and can contain unexpectedly big information content. The BLOB field can contain a binary massive which cannot be saved to RoboTask variable.
The action also allows you to save to a text file a value of any type, not only large fields.
Save Large Fields into Files
Switch on this checkbox to save field values to a file.
After that you need to fill in the list of fields to save. Each field must correspond to the file name to save.
You can use variables for the field name and file name.
But pay attention that the field name is calculated when the loop is initialized and stay permanent during the whole cycle. The file name is calculated at each iteration before saving information to the file. Therefore, for a file name, you can use any variables which change their value at each iteration.
Add, Edit, Delete
Buttons for editing a field list.
There you can change the time of waiting of a query process. On default, timeout is 60 seconds. But in case of need, it is possible to change this parameter.
DB Exec SQL
DB Begin Transaction
Note: This feature is available for Business license only