Iteration: The SQL Ooperation

How it works

The Iteration operation 'sql' evaluates the contents of the body element for each result in an SQL query. This operation is extremely used exclusively for retrieving data from SQL databases.

For each row of results provied by a given SQL query in the control element, the body is executed. Columns from the SQL are available as field input types.

Whilst in the body of an 'sql' operation the following meta field values are available:

#row - The current row number being iterated over (starting from 1)
#rowcount - The total number of results returned by the query
#fieldcount - The total number of fields returned by the query (number of columns)

'SQL' Examples

The following examples use this simple SQL table:

+----+-----------+
| id | name      |
+----+-----------+
|  1 | Salvatore |
|  2 | Jazz      |
|  3 | Claire    |
|  4 | Hailey    |
|  5 | Burt      |
+----+-----------+

Example 1: Execution per result

This example shows a basic query executing. For each result the field called 'name' is ouput. In this case the query returns 5 results and so the code in the body is evaluated 5 times.

<iteration operation="sql">
	<control value="select id, name from members" />
	<body space="field" value="name" />
</iteration>

SalvatoreJazzClaireHaileyBurt

Example 2: SQL Fields and Meta Fields

This example outputs the names from the table ordered by id. For each ouput the row number is displayed using the meta fields:

<iteration operation="sql">
	<control value="select id, name from members order by id" />
	<body>
		<instruction operation="append">
			<input space="field" value="#row" />
			<input value="/" />
			<input space="field" value="#rowcount" />
			<input value=":'" />
			<input space="field" value="name" />
			<input value="'. " />
		</instruction>
	</body>
</iteration>

1/5:'Salvatore'. 2/5:'Jazz'. 3/5:'Claire'. 4/5:'Hailey'. 5/5:'Burt'.

Last Modified: Thu, 15 Jan 2015

Obyx