Function: sql(statement, query params.)

The sql function can be used when dynamic statements need to be constructed in a database collector or exporter with query parameters.

The standard way of writing a database collector statement is:

select a,b,c from table where x= ${xValue} 

For simple statements, this is recommended. Phixflow automatically converts this into a parameterized statement that ensures special characters are handled correctly and protects against SQL injection attacks.

In this case, the query sent to the database would be "select a,b,c from table where x=?" and the xValue variable would be sent as a query parameter.

Sometimes it is necessary to construct the statement dynamically. For example, if a dynamic number of insert statements need to be created or the table names vary. This can be achieved by having the expression return either:

a) A single result returned from the SQL statement. 

${sql("select * from " + tableName + " where id=?", inputId)}

b) A list of results (array) containing multiple SQL statements 

${
	forEach($var, [1,2], 
		sql( "INSERT INTO childTable (Id, row) VALUES (?, ?)", id, toString($var) )
	)
}

Syntax

sql(statement, query parameters)

Examples

In this example we select attr1 from MyTable, where the age is greater than the value provided by in.age and the type equals the value set by in.type. Note, the value must be prefixed with a $ and wrapped in curly braces i.e. ${...}

${
	sql( "select attr1 from " + in.MyTable + " where age > ? and type =?", in.age, in.type )
}

You can also return a list of results, such as with the list shown here:

${
	[

		sql("update foo set (column = ?) where age > ?",  in.newAge, in.age),

		sql("update bar set (column = ?) where value > ?", in.newValue, in.value)

	]
}

Or using a forEach: 

${
	forEach($var, [1,2], 
		sql( "INSERT INTO childTable (Id, row) VALUES (?, ?)", id, toString($var) )
	)
}

See Also

• Functions
• Expressions and PhixScripts
• forEach