Introduction
Cycles (loops) allow performing the same actions (cycle
body) over different objects or under different
conditions.
For example, you can calculate the
total of numbers 1 through 100, perform operations over several records stored
in the recordset variable at once or print individual
lines from a text.
A cycle always begins with the For...
string. To identify the end of a cycle body, use the Next Item action:
Lines between the For (2)
and Next (4) lines make the cycle
body.
In this example, the cycle body contains only one line (3).
This program uses cycles of three types that differ by
the cycle subject:
For Interval (1,2,3...)
This cycle type changes the value of a specified
variable in a specified range with a specified step
and performs enclosed commands. Example:
In this example, the [$i] variable
changes its value from 2 to 20
with step 2.
For each of these values, the program will perform the
commands contained in line (3), and the user will see 10
messages with the following text:
- Variable Value: 2
- Variable Value: 4
- Variable Value: 6
- Variable Value: 8
- ...
- Variable Value: 20
For each line from text
This cycle type splits text to lines, and performs the
enclosed actions over each line.
Example:
In this example:
- Line 1: Write the list of three
cities in the [$cities] variable.
- Lines 2, 5: Define the cycle,
in which the [$line] variable will
consecutively contain a value from the list. In our
case, that's the list of cities contained in the [$cities]
variable.
- Lines 3, 4: Cycle body.
Line 3 creates a new record,
and line 4 writes the city name to
the Title field of the record.
Thus, upon completion of
this script, we will have 3
records with titles respectively London, Paris, and
Moscow.
Any data source can be used with this cycle -
data from file (see
Read File),
field values from DB records, etc.
For Each record from recordset
This cycle type allows
performing operations over a set of records in a
database.
The records are defined in the recordset
variable that can be created with a query, relational field, etc.
Learn more about recordsets...
In this example:
- Line 1: Places the set of
records created by the All Records query to the [$rset]
variable.
- Lines 2, 4:
Define the cycle, in which all operations will apply to
records stored in the [$rset] variable instead
of the current record.
- Line 3:
Cycle body. Increments the value of [Field A]
by one.
For each match by regular expression
This cycle splits a string by regular expression and executes the cycle's body for each match.
Each submatch is stored into a variable.
Parameters:
- Variables to copy matches to - variable name that will store matches. If regular expression returns submatches,
they will be stored in [$var_name0], [$var_name1], [$var_name2]... variables;
- Test String - a string to split;
- Regular Expression - a regular expresson in VBScripts format.
The following script will show different parts of all e-mails in the text stored in the [E-Mails] field:
| 1 | For [$m] = Each Match in '[E-Mails]' by Regular Expression '(\w+)@(\w+)\.(\w+)'
|
| 2 | Show Message (Name: [$m0]<BR> Sub-domain: [$m1]<BR> Domain: [$m2], RegExp Demo, Ok)
|
| 3 | Next Item
|
E.g. if the [E-Mails] field contains:
tom@yahoo.ca some text
bill@gmail.com mary@msn.com
We will see 3 messages:
You can also use the "RegExp Match" action to get the first match or to check a string.
For each record from SQL query (External DB)
This cycle executes the "SELECT" query in an opened external database
and executes the cycle's body for each record from the query result.
Before using this cycle an external
database (e.g. MySQL, Access, MS SQL, etc) must be opened using the "Connect to External DB" action.
Parameters:
- Variable prefix - a prefix for variable names, which will store field values from the external database.
E.g. if you execute the "
SELECT id, name FROM table_name WHERE 1" query and have selected "a_" as a prefix,
you will get the following variables: [$a_id] and [$a_name].
- SQL Query - SELECT query to execute. To execute other queries (e.g. UPDATE or DELETE)
you should use the "External DB SQL Query" operation.
Lets review an example that update currency rates from an MySQL database:
| 1 | Connect to External DB (odbc;DRIVER=Unknown block: MYSQL ODBC 3;SERVER=127.0.0.1;DATABASE=mydb1;UID=c;PWD=;OPTION=3;, etcurs)
|
| 2 | For [$a_...] = Records From "SELECT name, rate FROM cur_rates WHERE 1"
|
| 3 | Select Record By Rule (Rates, Rate And ([Currency Title] = "[$a_name]"))
|
| 4 | Rate: [Currency Rate] = "[$a_rate]"
|
| 5 | Next Item
|
| 6 | Close External DB ()
|
This example:
- Connects to an external MySQL database named "mydb1" under user "C";
- Executes a query and run a cycle body (lines 3-4) for each record from the query result. The query returns names and rates from the "cur_rates" table in the MySQL database;
- Finds and selects a record by the [Currency Title] = "[$a_name]" rule.
- Update the [Currency Rate] field value with [$a_rate] variable.
Goess to the next query result;
- Closes the connection to MySQL database.
| |
|
|
All topics in the "About Scripts" section:
|
|
