sql:prepared-query

Prepares a SQL query for execution, returning a function which can be called with parameters to execute the query.

prepared-query($connection as javatype:java.sql.Connection, $query as xs:string) ➔ function(*)

Arguments

 

$connection

javatype:java.sql.Connection

A JDBC database connection established using sql:connect

 

$query

xs:string

A SQL SELECT statement, typically containing question marks as place-holders for parameters

Result

function(*)

Namespace

http://saxon.sf.net/sql

Saxon availability

Implemented since Saxon 9.9. Requires Saxon-PE or Saxon-EE. Available for Java only.

Details

The function prepares a SQL query for execution. The query will typically be a SQL SELECT statement using question marks as place-holders for parameters, for example SELECT * FROM EMP WHERE EMP-ID = ?. A call on sql:prepared-query returns a function which can be called with parameters to execute the query.

The arity of the returned function (the "invocation function") is equal to the number of question-marks appearing in the source query.

The invocation function expects the supplied arguments to be atomic values. The data types of the arguments should correspond to the types of the values expected by the SQL query: for example, xs:string for a VARCHAR column, xs:decimal for a SQL decimal, and so on.

The value returned by the invocation function is a sequence of maps. Each row in the query result is represented by one map in this sequence. The map represents a row in the result using a string-valued key to represent the column name, and an appropriately-typed atomic value to represent the value of the column. A SQL NULL value is represented by an empty sequence.

Note that the result is a sequence rather than an array. It can be converted to an array (for example, for serializing in JSON) using the expression array{$result}.

Example:

<xsl:variable name="query" as="function(*)" select="sql:prepared-query($connection, 'SELECT date, title FROM book WHERE author = ?')"/> <out> <xsl:for-each select="$selected-authors"> <xsl:variable name="result" select="$query(.)" as="map(*)*"/> <author name="{.}"> <xsl:for-each select="$result"> <book date="{?date}" title="{?title}"/> </xsl:for-each> </author> </xsl:for-each> </out>