Project

Profile

Help

Revision af97c18a

Added by Debbie Lockett 8 months ago

Docs build: move sql-functions

View differences:

src/userdoc/functions.xml
11333 11333
  <xi:include href="functions/EXPath/functions-archive.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
11334 11334
  <xi:include href="functions/EXPath/functions-binary.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
11335 11335
  <xi:include href="functions/EXPath/functions-file.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
11336
  <xi:include href="sql-extension/sql-functions.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
11336
  <xi:include href="functions/sql-functions.xml" xmlns:xi="http://www.w3.org/2001/XInclude"/>
11337 11337

  
11338 11338
  
11339 11339
</article>
src/userdoc/functions/sql-functions.xml
1
<?xml version="1.0" encoding="UTF-8"?>
2
<section xmlns:doc="http://www.saxonica.com/ns/doc"
3
         id="sql"
4
         title="Namespace sql">
5
   <h1>Namespace sql</h1>
6
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
7
      <name namespace="http://saxon.sf.net/sql">connect</name>
8
      <signatures>
9
         <proto return-type="javatype:java.sql.Connection">
10
            <arg name="options"
11
                 type="map(*)"
12
                 desc="Database connection parameters"/>
13
         </proto>
14
      </signatures>
15
      <description>
16
         <p>Returns an external object representing a connection to a SQL database. This object is used as the first
17
         argument of other functions such as <code>sql:query</code>, <code>sql:insert</code>, etc.</p>
18
      </description>
19
      <status>
20
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
21
            href="/sql-extension/instructions/connect">sql:connect</a>. The <code>saxon-sql-10.#.jar</code> file,
22
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
23
            are used.</p>
24
      </status>
25
      <xpath>3.1</xpath>
26
      <saxon-edition>9.9 PE/EE J</saxon-edition>
27
      <details>
28
         <p>Returns an external object representing a connection to a SQL database. This object is used as the first
29
            argument of other functions such as <code>sql:query</code>, <code>sql:insert</code>, etc.</p>
30
         <p>The argument is a map, which follows the option parameter conventions. The defined
31
            options are:</p>
32
         <table>
33
            <thead>
34
               <tr>
35
                  <th>Keyword</th>
36
                  <th>Type</th>
37
                  <th>Value</th>
38
               </tr>
39
            </thead>
40
            <tbody>
41
               <tr>
42
                  <td>driver</td>
43
                  <td><code>xs:string</code></td>
44
                  <td>The Java class name of the JDBC driver to be used, for example <code>sun.jdbc.odbc.JdbcOdbcDriver</code></td>
45
               </tr>
46
               <tr>
47
                  <td>database</td>
48
                  <td><code>xs:string</code></td>
49
                  <td>The name of the database: naming conventions depend on the driver in use</td>
50
               </tr>
51
               <tr>
52
                  <td>user</td>
53
                  <td><code>xs:string</code></td>
54
                  <td>Username to be used for authentication</td>
55
               </tr>
56
               <tr>
57
                  <td>password</td>
58
                  <td><code>xs:string</code></td>
59
                  <td>Password to be used for authentication</td>
60
               </tr>
61
               <tr>
62
                  <td>autoCommit</td>
63
                  <td><code>xs:boolean</code></td>
64
                  <td>Sets or unsets the auto-commit option on the connection that is established</td>
65
               </tr>
66
            </tbody>
67
         </table>
68
         <p>For example:</p>
69
         
70
         <samp><![CDATA[<xsl:variable name="connection" select="sql:connect(map{
71
        'database':'jdbc:mysql://localhost/saxontest',
72
        'driver':'com.mysql.jdbc.Driver',
73
        'user':'dbadmin',
74
        'password':$password,
75
        'autoCommit':true()})"/>
76
]]></samp>
77
         <p>It will often be appropriate to bind the result of the call to a global variable.</p>
78
         <p>A dynamic error is thrown in the event of any connection failure. Improved diagnostics on the reason
79
            for failure are output if the configuration option <code>Feature.TIMING</code> is set (<code>-t</code>
80
            on the command line).</p>
81
         <p>The connection object acts as a map and it is possible to call methods as dynamic function calls using the entries
82
            in this map. For example <code>$connection?isClosed()</code> will return a boolean indicating whether
83
            the connection has been closed.</p>
84
         <p>For actions that have side-effects, it is recommended to invoke them using the <a class="bodylink code"
85
            href="/extensions/instructions/do">saxon:do</a> extension instruction: for example to close the connection use the
86
            instruction:</p>
87
         
88
         <samp><![CDATA[<saxon:do action="$connection?close()"/>]]></samp>        
89
         
90
         <p>JDBC connections are not thread-safe. It is therefore advisable when using JDBC connections to
91
            suppress Saxon multi-threading by setting the configuration option <code>Feature.ALLOW_MULTI_THREADING</code>
92
            to false. A warning is issued if this is not done.</p>
93
         
94
         <p>Note that the Saxon extension functions make no attempt to validate or verify the SQL statements being
95
         passed through the interface. In particular, there is no attempt to prevent SQL injection attacks: this is
96
         entirely the application's responsibility.</p>
97
      </details>
98
   </function>
99
   
100
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
101
      <name namespace="http://saxon.sf.net/sql">delete</name>
102
      <signatures>
103
         <proto return-type="item()?">
104
            <arg name="connection"
105
               type="javatype:java.sql.Connection"
106
               desc="A JDBC database connection established using sql:connect"/>
107
            <arg name="table" type="xs:string" desc="The database table from which rows are to be deleted"/>
108
            <arg name="predicate" type="xs:string" desc="A SQL predicate identifying the rows to be deleted"/>
109
         </proto>
110
      </signatures>
111
      <description>
112
         <p>Executes a SQL DELETE statement, returning no result.</p>
113
      </description>
114
      <status>
115
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
116
           href="/sql-extension/instructions/delete">sql:delete</a>. The <code>saxon-sql-10.#.jar</code> file,
117
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
118
            are used.</p>
119
      </status>
120
      <xpath>3.1</xpath>
121
      <saxon-edition>9.9 PE/EE J</saxon-edition>
122
      <details>
123
         <p>The function executes a SQL DELETE statement, and returns no result.</p>
124
         
125
         <p>The statement is constructed simply by concatenating the arguments: <code>DELETE {$table} WHERE {$predicate}</code>.</p>
126
         
127
         <p>Although the declared return type is <code>item()?</code>, the function always returns an empty
128
            sequence. The declared return type is designed to prevent the optimizer making unwarranted assumptions.</p>
129
         
130
         <p>Because this function is executed for its side-effects, it is recommended to invoke it using
131
            the <a class="bodylink code" href="/extensions/instructions/do">saxon:do</a> extension instruction.</p>
132
         
133
         <p>Example:</p>
134
         <samp><![CDATA[<saxon:do action="sql:delete($connection, 'book', 'price > 20.00')"/>]]></samp>
135
      </details>
136
   </function>
137
   
138
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
139
      <name namespace="http://saxon.sf.net/sql">execute</name>
140
      <signatures>
141
         <proto return-type="item()?">
142
            <arg name="connection"
143
               type="javatype:java.sql.Connection"
144
               desc="A JDBC database connection established using sql:connect"/>
145
            <arg name="statement" type="xs:string" desc="The SQL statement to be executed"/>
146
         </proto>
147
      </signatures>
148
      <description>
149
         <p>Executes a SQL statement, returning no result.</p>
150
      </description>
151
      <status>
152
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
153
           href="/sql-extension/instructions/execute">sql:execute</a>. The <code>saxon-sql-10.#.jar</code> file,
154
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
155
            are used.</p>
156
      </status>
157
      <xpath>3.1</xpath>
158
      <saxon-edition>9.9 PE/EE J</saxon-edition>
159
      <details>
160
         <p>The function executes a SQL statement, and returns no result.</p>
161
         
162
         <p>If the statement is <code>COMMIT WORK</code> or <code>ROLLBACK WORK</code> (exactly as written)
163
         then it is intercepted and translated into a call on <code>$connection?commit()</code>
164
         or <code>$connection?rollback_0()</code>.</p>
165
         
166
         <p>Although the declared return type is <code>item()?</code>, the function always returns an empty
167
         sequence. The declared return type is designed to prevent the optimizer making unwarranted assumptions.</p>
168
         
169
         <p>Because this function is executed for its side-effects, it is recommended to invoke it using
170
            the <a class="bodylink code" href="/extensions/instructions/do">saxon:do</a> extension instruction.</p>
171
         
172
         <p>Example:</p>
173
         <samp><![CDATA[<saxon:do action="sql:execute($connection, 'DROP TABLE book')"/>]]></samp>
174
      </details>
175
   </function>
176
   
177
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
178
      <name namespace="http://saxon.sf.net/sql">insert</name>
179
      <signatures>
180
         <proto return-type="item()?">
181
            <arg name="connection"
182
               type="javatype:java.sql.Connection"
183
               desc="A JDBC database connection established using sql:connect"/>
184
            <arg name="table" type="xs:string" desc="The database table from which rows are to be updated"/>
185
            <arg name="values" type="map(*)" desc="A map holding values for the new row"/>
186
         </proto>
187
      </signatures>
188
      <description>
189
         <p>Executes a SQL INSERT statement (adding a new row to a table), returning no result.</p>
190
      </description>
191
      <status>
192
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
193
           href="/sql-extension/instructions/insert">sql:insert</a>. The <code>saxon-sql-10.#.jar</code> file,
194
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
195
            are used.</p>
196
      </status>
197
      <xpath>3.1</xpath>
198
      <saxon-edition>9.9 PE/EE J</saxon-edition>
199
      <details>
200
         <p>The function executes a SQL INSERT statement (adding a new row to a table), and returns no result.</p>
201
         
202
         <p>The <code>$table</code> argument names the database table to be updated. 
203
            New values for selected columns are taken from the <code>$values</code> map.</p>
204
         
205
         <p>The entries in the <code>$values</code> map are (name, value) pairs, where the name is a column
206
            name in the table being updated, and the value is the value for the column. The value should be
207
            an appropriate data type for the column (for example, <code>xs:string</code> for a <code>VARCHAR</code>
208
            column, <code>xs:decimal</code> for a <code>DECIMAL</code> column, etc).</p>
209
         
210
         <p>Although the declared return type is <code>item()?</code>, the function always returns an empty
211
            sequence. The declared return type is designed to prevent the optimizer making unwarranted assumptions.</p>
212
         
213
         <p>Because this function is executed for its side-effects, it is recommended to invoke it using
214
            the <a class="bodylink code" href="/extensions/instructions/do">saxon:do</a> extension instruction.</p>
215
         
216
         <p>Example:</p>
217
         <samp><![CDATA[<saxon:do action="sql:insert($connection, 'book', 
218
   map{'title': TITLE,
219
       'author': AUTHOR,
220
       'category': $category,
221
       'price': PRICE,
222
       'date': current-date()})"/>
223
]]></samp>
224
      </details>
225
   </function>
226
   
227
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
228
      <name namespace="http://saxon.sf.net/sql">prepared-statement</name>
229
      <signatures>
230
         <proto return-type="function(*)">
231
            <arg name="connection"
232
               type="javatype:java.sql.Connection"
233
               desc="A JDBC database connection established using sql:connect"/>
234
            <arg name="statement"
235
               type="xs:string"
236
               desc="A SQL statement, typically containing question marks as place-holders for parameters"/>
237
         </proto>
238
      </signatures>
239
      <description>
240
         <p>Prepares a SQL statement for execution, returning a function which can be called with parameters to execute the statement,
241
            returning no result.</p>
242
      </description>
243
      <xpath>3.1</xpath>
244
      <saxon-edition>9.9 PE/EE J</saxon-edition>
245
      <details>
246
         <p>The function prepares a SQL statement for execution. The statement will typically be one that does not return
247
            a result, but which uses question marks as place-holders for parameters, for example <code>INSERT INTO EMP
248
               VALUES (?, ?, ?)</code>. A call on <code>sql:prepared-statement</code> returns a function which can be
249
            called with parameters to execute the statement.</p>
250
         <p>The arity of the returned function (the "invocation function") is equal to the number of 
251
            question-marks appearing in the source statement.</p>
252
         <p>The invocation function expects the supplied arguments to be atomic values. The data types of the arguments
253
            should correspond to the types of the values expected by the SQL statement: for example, <code>xs:string</code>
254
            for a <code>VARCHAR</code> column, <code>xs:decimal</code> for a SQL decimal, and so on.</p>
255
         <p>The invocation function always returns an empty sequence, though to avoid over-aggressive optimization, it is
256
            declared as returning <code>item()?</code>. Because the invocation function is called for its side-effects,
257
            it is recommended to call it in the <code>action</code> expression of a <a class="bodylink code"
258
               href="/extensions/instructions/do">saxon:do</a> instruction.</p>
259
         
260
         <p>Example:</p>
261
         <samp><![CDATA[<xsl:variable name="add-book" as="function(*)" 
262
   select="sql:prepared-statement($connection, 
263
   'INSERT INTO book (isbn, title, author, category) VALUES (?, ?, ?, ?)')"/>        
264
<xsl:for-each select="//book">
265
   <saxon:do action="$add-book(@isbn, @title, @author, @category)"/>
266
</xsl:for-each>
267
]]></samp>
268
      </details>
269
   </function>
270
   
271
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
272
      <name namespace="http://saxon.sf.net/sql">prepared-query</name>
273
      <signatures>
274
         <proto return-type="function(*)">
275
            <arg name="connection"
276
               type="javatype:java.sql.Connection"
277
               desc="A JDBC database connection established using sql:connect"/>
278
            <arg name="query"
279
               type="xs:string"
280
               desc="A SQL SELECT statement, typically containing question marks as place-holders for parameters"/>
281
         </proto>
282
      </signatures>
283
      <description>
284
         <p>Prepares a SQL query for execution, returning a function which can be called with parameters to execute the query.</p>
285
      </description>
286
      <xpath>3.1</xpath>
287
      <saxon-edition>9.9 PE/EE J</saxon-edition>
288
      <details>
289
         <p>The function prepares a SQL query for execution. The query will typically be a SQL SELECT statement using question marks
290
            as place-holders for parameters, for example <code>SELECT * FROM EMP WHERE EMP-ID = ?</code>. A call on
291
            <code>sql:prepared-query</code> returns a function which can be called with parameters to execute the query.</p>
292
         <p>The arity of the returned function (the "invocation function") is equal to the number of 
293
            question-marks appearing in the source query.</p>
294
         <p>The invocation function expects the supplied arguments to be atomic values. The data types of the arguments
295
            should correspond to the types of the values expected by the SQL query: for example, <code>xs:string</code>
296
            for a <code>VARCHAR</code> column, <code>xs:decimal</code> for a SQL decimal, and so on.</p>
297
         <p>The value returned by the invocation function is a sequence of maps. Each row in the query result is
298
            represented by one map in this sequence. The map represents a row in the result using a string-valued key
299
            to represent the column name, and an appropriately-typed atomic value to represent the value of the column.
300
            A SQL <code>NULL</code> value is represented by an empty sequence.</p>
301
         <p>Note that the result is a sequence rather than an array. It can be converted to an array (for example,
302
            for serializing in JSON) using the expression <code>array{$result}</code>.</p>
303
         <p>Example:</p>
304
         <samp><![CDATA[<xsl:variable name="query" as="function(*)" 
305
   select="sql:prepared-query($connection, 'SELECT date, title FROM book WHERE author = ?')"/>
306
        
307
<out>
308
   <xsl:for-each select="$selected-authors">
309
      <xsl:variable name="result" select="$query(.)" as="map(*)*"/>
310
      <author name="{.}">
311
         <xsl:for-each select="$result">
312
            <book date="{?date}" title="{?title}"/>
313
         </xsl:for-each>   
314
      </author>
315
   </xsl:for-each>
316
</out>
317
]]></samp>
318
      </details>
319
   </function>
320
   
321
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
322
      <name namespace="http://saxon.sf.net/sql">query</name>
323
      <signatures>
324
         <proto return-type="map(*)*">
325
            <arg name="connection"
326
               type="javatype:java.sql.Connection"
327
               desc="A JDBC database connection established using sql:connect"/>
328
            <arg name="table" type="xs:string" desc="The name of a table in the database"/>
329
            <arg name="columns" type="xs:string*" desc="A list of required column names, or '*' to select all columns in the table"/>          
330
         </proto>
331
         <proto return-type="map(*)*">
332
            <arg name="connection"
333
               type="javatype:java.sql.Connection"
334
               desc="A JDBC database connection established using sql:connect"/>
335
            <arg name="table" type="xs:string" desc="The name of a table in the database"/>
336
            <arg name="columns" type="xs:string*" desc="A list of required column names, or '*' to select all columns in the table"/>          
337
            <arg name="predicate"
338
               type="xs:string"
339
               desc="A SQL expression suitable for inclusion in the WHERE clause"/>
340
         </proto>
341
      </signatures>
342
      <description>
343
         <p>Executes a SQL query, returning the result as a map.</p>
344
      </description>
345
      <status>
346
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
347
           href="/sql-extension/instructions/query">sql:query</a>. The <code>saxon-sql-10.#.jar</code> file,
348
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
349
            are used.</p>
350
      </status>
351
      <xpath>3.1</xpath>
352
      <saxon-edition>9.9 PE/EE J</saxon-edition>
353
      <details>
354
         <p>The function executes a SQL query. For convenience the query is split across three arguments <code>$table</code>,
355
         <code>$columns</code> and <code>$predicate</code> but the function simply concatenates these together to form
356
         a SQL query which is then executed.</p>
357
         
358
         <p>The 3-argument form of the function retrieves all the rows in a table (the effective predicate is <code>TRUE</code>).</p>
359
  
360
         <p>The value returned by the function is a sequence of maps. Each row in the query result is
361
            represented by one map in this sequence. The map represents a row in the result using a string-valued key
362
            to represent the column name, and an appropriately-typed atomic value to represent the value of the column.
363
            A SQL <code>NULL</code> value is represented by an empty sequence.</p>
364
         <p>Note that the result is a sequence rather than an array. It can be converted to an array (for example,
365
            for serializing in JSON) using the expression <code>array{$result}</code>.</p>
366
         <p>Example:</p>
367
         <samp><![CDATA[<out>
368
   <xsl:for-each select="sql:query($connection, 'book', ('date', 'title'), 
369
      'author = ''J. K. Rowling''')">
370
      <book date="{?date}" title="{?title}"/>
371
   </xsl:for-each>
372
</out>
373
]]></samp>
374
        <p>To avoid problems with nested quotation marks, you could also write:</p>
375
        <samp><![CDATA[<out>
376
   <xsl:variable name="condition" as="xs:string">author = 'J. K. Rowling'</xsl:variable>       
377
   <xsl:for-each select="sql:query($connection, 'book', ('date', 'title'), $condition)">
378
      <book date="{?date}" title="{?title}"/>
379
   </xsl:for-each>
380
</out>
381
]]></samp>
382
      </details>
383
   </function>
384
   
385
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
386
      <name namespace="http://saxon.sf.net/sql">update</name>
387
      <signatures>
388
         <proto return-type="item()?">
389
            <arg name="connection"
390
               type="javatype:java.sql.Connection"
391
               desc="A JDBC database connection established using sql:connect"/>
392
            <arg name="table" type="xs:string" desc="The database table from which rows are to be updated"/>
393
            <arg name="predicate" type="xs:string" desc="A SQL predicate identifying the rows to be updated"/>
394
            <arg name="changes" type="map(*)" desc="A map holding new values for the updated rows"/>
395
         </proto>
396
      </signatures>
397
      <description>
398
         <p>Executes a SQL UPDATE statement, returning no result.</p>
399
      </description>
400
      <status>
401
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
402
            href="/sql-extension/instructions/update">sql:update</a>. The <code>saxon-sql-10.#.jar</code> file,
403
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
404
            are used.</p>
405
      </status>
406
      <xpath>3.1</xpath>
407
      <saxon-edition>9.9 PE/EE J</saxon-edition>
408
      <details>
409
         <p>The function executes a SQL UPDATE statement, and returns no result.</p>
410
         
411
         <p>The <code>$table</code> argument names the database table to be updated, and the
412
         <code>$predicate</code> argument is a SQL predicate that selects which rows are to be updated. For each
413
         of these rows, new values for selected columns are taken from the <code>$changes</code> map.</p>
414
         
415
         <p>The entries in the <code>$changes</code> map are (name, value) pairs, where the name is a column
416
         name in the table being updated, and the value is the new value for the column. The value should be
417
         an appropriate data type for the column (for example, <code>xs:string</code> for a <code>VARCHAR</code>
418
         column, <code>xs:decimal</code> for a <code>DECIMAL</code> column, etc).</p>
419
         
420
         <p>Although the declared return type is <code>item()?</code>, the function always returns an empty
421
            sequence. The declared return type is designed to prevent the optimizer making unwarranted assumptions.</p>
422
         
423
         <p>Because this function is executed for its side-effects, it is recommended to invoke it using
424
            the <a class="bodylink code" href="/extensions/instructions/do">saxon:do</a> extension instruction.</p>
425
         
426
         <p>Example:</p>
427
         <samp><![CDATA[<saxon:do action="sql:update($connection, 'book', 'stock = 0', map{'out-of-print': true()}"/>]]></samp>
428
      </details>
429
   </function>
430
   
431
 
432
</section>
src/userdoc/sql-extension/sql-functions.xml
1
<?xml version="1.0" encoding="UTF-8"?>
2
<section xmlns:doc="http://www.saxonica.com/ns/doc"
3
         id="sql"
4
         title="Namespace sql">
5
   <h1>Namespace sql</h1>
6
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
7
      <name namespace="http://saxon.sf.net/sql">connect</name>
8
      <signatures>
9
         <proto return-type="javatype:java.sql.Connection">
10
            <arg name="options"
11
                 type="map(*)"
12
                 desc="Database connection parameters"/>
13
         </proto>
14
      </signatures>
15
      <description>
16
         <p>Returns an external object representing a connection to a SQL database. This object is used as the first
17
         argument of other functions such as <code>sql:query</code>, <code>sql:insert</code>, etc.</p>
18
      </description>
19
      <status>
20
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
21
            href="/sql-extension/instructions/connect">sql:connect</a>. The <code>saxon-sql-10.#.jar</code> file,
22
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
23
            are used.</p>
24
      </status>
25
      <xpath>3.1</xpath>
26
      <saxon-edition>9.9 PE/EE J</saxon-edition>
27
      <details>
28
         <p>Returns an external object representing a connection to a SQL database. This object is used as the first
29
            argument of other functions such as <code>sql:query</code>, <code>sql:insert</code>, etc.</p>
30
         <p>The argument is a map, which follows the option parameter conventions. The defined
31
            options are:</p>
32
         <table>
33
            <thead>
34
               <tr>
35
                  <th>Keyword</th>
36
                  <th>Type</th>
37
                  <th>Value</th>
38
               </tr>
39
            </thead>
40
            <tbody>
41
               <tr>
42
                  <td>driver</td>
43
                  <td><code>xs:string</code></td>
44
                  <td>The Java class name of the JDBC driver to be used, for example <code>sun.jdbc.odbc.JdbcOdbcDriver</code></td>
45
               </tr>
46
               <tr>
47
                  <td>database</td>
48
                  <td><code>xs:string</code></td>
49
                  <td>The name of the database: naming conventions depend on the driver in use</td>
50
               </tr>
51
               <tr>
52
                  <td>user</td>
53
                  <td><code>xs:string</code></td>
54
                  <td>Username to be used for authentication</td>
55
               </tr>
56
               <tr>
57
                  <td>password</td>
58
                  <td><code>xs:string</code></td>
59
                  <td>Password to be used for authentication</td>
60
               </tr>
61
               <tr>
62
                  <td>autoCommit</td>
63
                  <td><code>xs:boolean</code></td>
64
                  <td>Sets or unsets the auto-commit option on the connection that is established</td>
65
               </tr>
66
            </tbody>
67
         </table>
68
         <p>For example:</p>
69
         
70
         <samp><![CDATA[<xsl:variable name="connection" select="sql:connect(map{
71
        'database':'jdbc:mysql://localhost/saxontest',
72
        'driver':'com.mysql.jdbc.Driver',
73
        'user':'dbadmin',
74
        'password':$password,
75
        'autoCommit':true()})"/>
76
]]></samp>
77
         <p>It will often be appropriate to bind the result of the call to a global variable.</p>
78
         <p>A dynamic error is thrown in the event of any connection failure. Improved diagnostics on the reason
79
            for failure are output if the configuration option <code>Feature.TIMING</code> is set (<code>-t</code>
80
            on the command line).</p>
81
         <p>The connection object acts as a map and it is possible to call methods as dynamic function calls using the entries
82
            in this map. For example <code>$connection?isClosed()</code> will return a boolean indicating whether
83
            the connection has been closed.</p>
84
         <p>For actions that have side-effects, it is recommended to invoke them using the <a class="bodylink code"
85
            href="/extensions/instructions/do">saxon:do</a> extension instruction: for example to close the connection use the
86
            instruction:</p>
87
         
88
         <samp><![CDATA[<saxon:do action="$connection?close()"/>]]></samp>        
89
         
90
         <p>JDBC connections are not thread-safe. It is therefore advisable when using JDBC connections to
91
            suppress Saxon multi-threading by setting the configuration option <code>Feature.ALLOW_MULTI_THREADING</code>
92
            to false. A warning is issued if this is not done.</p>
93
         
94
         <p>Note that the Saxon extension functions make no attempt to validate or verify the SQL statements being
95
         passed through the interface. In particular, there is no attempt to prevent SQL injection attacks: this is
96
         entirely the application's responsibility.</p>
97
      </details>
98
   </function>
99
   
100
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
101
      <name namespace="http://saxon.sf.net/sql">delete</name>
102
      <signatures>
103
         <proto return-type="item()?">
104
            <arg name="connection"
105
               type="javatype:java.sql.Connection"
106
               desc="A JDBC database connection established using sql:connect"/>
107
            <arg name="table" type="xs:string" desc="The database table from which rows are to be deleted"/>
108
            <arg name="predicate" type="xs:string" desc="A SQL predicate identifying the rows to be deleted"/>
109
         </proto>
110
      </signatures>
111
      <description>
112
         <p>Executes a SQL DELETE statement, returning no result.</p>
113
      </description>
114
      <status>
115
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
116
           href="/sql-extension/instructions/delete">sql:delete</a>. The <code>saxon-sql-10.#.jar</code> file,
117
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
118
            are used.</p>
119
      </status>
120
      <xpath>3.1</xpath>
121
      <saxon-edition>9.9 PE/EE J</saxon-edition>
122
      <details>
123
         <p>The function executes a SQL DELETE statement, and returns no result.</p>
124
         
125
         <p>The statement is constructed simply by concatenating the arguments: <code>DELETE {$table} WHERE {$predicate}</code>.</p>
126
         
127
         <p>Although the declared return type is <code>item()?</code>, the function always returns an empty
128
            sequence. The declared return type is designed to prevent the optimizer making unwarranted assumptions.</p>
129
         
130
         <p>Because this function is executed for its side-effects, it is recommended to invoke it using
131
            the <a class="bodylink code" href="/extensions/instructions/do">saxon:do</a> extension instruction.</p>
132
         
133
         <p>Example:</p>
134
         <samp><![CDATA[<saxon:do action="sql:delete($connection, 'book', 'price > 20.00')"/>]]></samp>
135
      </details>
136
   </function>
137
   
138
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
139
      <name namespace="http://saxon.sf.net/sql">execute</name>
140
      <signatures>
141
         <proto return-type="item()?">
142
            <arg name="connection"
143
               type="javatype:java.sql.Connection"
144
               desc="A JDBC database connection established using sql:connect"/>
145
            <arg name="statement" type="xs:string" desc="The SQL statement to be executed"/>
146
         </proto>
147
      </signatures>
148
      <description>
149
         <p>Executes a SQL statement, returning no result.</p>
150
      </description>
151
      <status>
152
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
153
           href="/sql-extension/instructions/execute">sql:execute</a>. The <code>saxon-sql-10.#.jar</code> file,
154
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
155
            are used.</p>
156
      </status>
157
      <xpath>3.1</xpath>
158
      <saxon-edition>9.9 PE/EE J</saxon-edition>
159
      <details>
160
         <p>The function executes a SQL statement, and returns no result.</p>
161
         
162
         <p>If the statement is <code>COMMIT WORK</code> or <code>ROLLBACK WORK</code> (exactly as written)
163
         then it is intercepted and translated into a call on <code>$connection?commit()</code>
164
         or <code>$connection?rollback_0()</code>.</p>
165
         
166
         <p>Although the declared return type is <code>item()?</code>, the function always returns an empty
167
         sequence. The declared return type is designed to prevent the optimizer making unwarranted assumptions.</p>
168
         
169
         <p>Because this function is executed for its side-effects, it is recommended to invoke it using
170
            the <a class="bodylink code" href="/extensions/instructions/do">saxon:do</a> extension instruction.</p>
171
         
172
         <p>Example:</p>
173
         <samp><![CDATA[<saxon:do action="sql:execute($connection, 'DROP TABLE book')"/>]]></samp>
174
      </details>
175
   </function>
176
   
177
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
178
      <name namespace="http://saxon.sf.net/sql">insert</name>
179
      <signatures>
180
         <proto return-type="item()?">
181
            <arg name="connection"
182
               type="javatype:java.sql.Connection"
183
               desc="A JDBC database connection established using sql:connect"/>
184
            <arg name="table" type="xs:string" desc="The database table from which rows are to be updated"/>
185
            <arg name="values" type="map(*)" desc="A map holding values for the new row"/>
186
         </proto>
187
      </signatures>
188
      <description>
189
         <p>Executes a SQL INSERT statement (adding a new row to a table), returning no result.</p>
190
      </description>
191
      <status>
192
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
193
           href="/sql-extension/instructions/insert">sql:insert</a>. The <code>saxon-sql-10.#.jar</code> file,
194
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
195
            are used.</p>
196
      </status>
197
      <xpath>3.1</xpath>
198
      <saxon-edition>9.9 PE/EE J</saxon-edition>
199
      <details>
200
         <p>The function executes a SQL INSERT statement (adding a new row to a table), and returns no result.</p>
201
         
202
         <p>The <code>$table</code> argument names the database table to be updated. 
203
            New values for selected columns are taken from the <code>$values</code> map.</p>
204
         
205
         <p>The entries in the <code>$values</code> map are (name, value) pairs, where the name is a column
206
            name in the table being updated, and the value is the value for the column. The value should be
207
            an appropriate data type for the column (for example, <code>xs:string</code> for a <code>VARCHAR</code>
208
            column, <code>xs:decimal</code> for a <code>DECIMAL</code> column, etc).</p>
209
         
210
         <p>Although the declared return type is <code>item()?</code>, the function always returns an empty
211
            sequence. The declared return type is designed to prevent the optimizer making unwarranted assumptions.</p>
212
         
213
         <p>Because this function is executed for its side-effects, it is recommended to invoke it using
214
            the <a class="bodylink code" href="/extensions/instructions/do">saxon:do</a> extension instruction.</p>
215
         
216
         <p>Example:</p>
217
         <samp><![CDATA[<saxon:do action="sql:insert($connection, 'book', 
218
   map{'title': TITLE,
219
       'author': AUTHOR,
220
       'category': $category,
221
       'price': PRICE,
222
       'date': current-date()})"/>
223
]]></samp>
224
      </details>
225
   </function>
226
   
227
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
228
      <name namespace="http://saxon.sf.net/sql">prepared-statement</name>
229
      <signatures>
230
         <proto return-type="function(*)">
231
            <arg name="connection"
232
               type="javatype:java.sql.Connection"
233
               desc="A JDBC database connection established using sql:connect"/>
234
            <arg name="statement"
235
               type="xs:string"
236
               desc="A SQL statement, typically containing question marks as place-holders for parameters"/>
237
         </proto>
238
      </signatures>
239
      <description>
240
         <p>Prepares a SQL statement for execution, returning a function which can be called with parameters to execute the statement,
241
            returning no result.</p>
242
      </description>
243
      <xpath>3.1</xpath>
244
      <saxon-edition>9.9 PE/EE J</saxon-edition>
245
      <details>
246
         <p>The function prepares a SQL statement for execution. The statement will typically be one that does not return
247
            a result, but which uses question marks as place-holders for parameters, for example <code>INSERT INTO EMP
248
               VALUES (?, ?, ?)</code>. A call on <code>sql:prepared-statement</code> returns a function which can be
249
            called with parameters to execute the statement.</p>
250
         <p>The arity of the returned function (the "invocation function") is equal to the number of 
251
            question-marks appearing in the source statement.</p>
252
         <p>The invocation function expects the supplied arguments to be atomic values. The data types of the arguments
253
            should correspond to the types of the values expected by the SQL statement: for example, <code>xs:string</code>
254
            for a <code>VARCHAR</code> column, <code>xs:decimal</code> for a SQL decimal, and so on.</p>
255
         <p>The invocation function always returns an empty sequence, though to avoid over-aggressive optimization, it is
256
            declared as returning <code>item()?</code>. Because the invocation function is called for its side-effects,
257
            it is recommended to call it in the <code>action</code> expression of a <a class="bodylink code"
258
               href="/extensions/instructions/do">saxon:do</a> instruction.</p>
259
         
260
         <p>Example:</p>
261
         <samp><![CDATA[<xsl:variable name="add-book" as="function(*)" 
262
   select="sql:prepared-statement($connection, 
263
   'INSERT INTO book (isbn, title, author, category) VALUES (?, ?, ?, ?)')"/>        
264
<xsl:for-each select="//book">
265
   <saxon:do action="$add-book(@isbn, @title, @author, @category)"/>
266
</xsl:for-each>
267
]]></samp>
268
      </details>
269
   </function>
270
   
271
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
272
      <name namespace="http://saxon.sf.net/sql">prepared-query</name>
273
      <signatures>
274
         <proto return-type="function(*)">
275
            <arg name="connection"
276
               type="javatype:java.sql.Connection"
277
               desc="A JDBC database connection established using sql:connect"/>
278
            <arg name="query"
279
               type="xs:string"
280
               desc="A SQL SELECT statement, typically containing question marks as place-holders for parameters"/>
281
         </proto>
282
      </signatures>
283
      <description>
284
         <p>Prepares a SQL query for execution, returning a function which can be called with parameters to execute the query.</p>
285
      </description>
286
      <xpath>3.1</xpath>
287
      <saxon-edition>9.9 PE/EE J</saxon-edition>
288
      <details>
289
         <p>The function prepares a SQL query for execution. The query will typically be a SQL SELECT statement using question marks
290
            as place-holders for parameters, for example <code>SELECT * FROM EMP WHERE EMP-ID = ?</code>. A call on
291
            <code>sql:prepared-query</code> returns a function which can be called with parameters to execute the query.</p>
292
         <p>The arity of the returned function (the "invocation function") is equal to the number of 
293
            question-marks appearing in the source query.</p>
294
         <p>The invocation function expects the supplied arguments to be atomic values. The data types of the arguments
295
            should correspond to the types of the values expected by the SQL query: for example, <code>xs:string</code>
296
            for a <code>VARCHAR</code> column, <code>xs:decimal</code> for a SQL decimal, and so on.</p>
297
         <p>The value returned by the invocation function is a sequence of maps. Each row in the query result is
298
            represented by one map in this sequence. The map represents a row in the result using a string-valued key
299
            to represent the column name, and an appropriately-typed atomic value to represent the value of the column.
300
            A SQL <code>NULL</code> value is represented by an empty sequence.</p>
301
         <p>Note that the result is a sequence rather than an array. It can be converted to an array (for example,
302
            for serializing in JSON) using the expression <code>array{$result}</code>.</p>
303
         <p>Example:</p>
304
         <samp><![CDATA[<xsl:variable name="query" as="function(*)" 
305
   select="sql:prepared-query($connection, 'SELECT date, title FROM book WHERE author = ?')"/>
306
        
307
<out>
308
   <xsl:for-each select="$selected-authors">
309
      <xsl:variable name="result" select="$query(.)" as="map(*)*"/>
310
      <author name="{.}">
311
         <xsl:for-each select="$result">
312
            <book date="{?date}" title="{?title}"/>
313
         </xsl:for-each>   
314
      </author>
315
   </xsl:for-each>
316
</out>
317
]]></samp>
318
      </details>
319
   </function>
320
   
321
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
322
      <name namespace="http://saxon.sf.net/sql">query</name>
323
      <signatures>
324
         <proto return-type="map(*)*">
325
            <arg name="connection"
326
               type="javatype:java.sql.Connection"
327
               desc="A JDBC database connection established using sql:connect"/>
328
            <arg name="table" type="xs:string" desc="The name of a table in the database"/>
329
            <arg name="columns" type="xs:string*" desc="A list of required column names, or '*' to select all columns in the table"/>          
330
         </proto>
331
         <proto return-type="map(*)*">
332
            <arg name="connection"
333
               type="javatype:java.sql.Connection"
334
               desc="A JDBC database connection established using sql:connect"/>
335
            <arg name="table" type="xs:string" desc="The name of a table in the database"/>
336
            <arg name="columns" type="xs:string*" desc="A list of required column names, or '*' to select all columns in the table"/>          
337
            <arg name="predicate"
338
               type="xs:string"
339
               desc="A SQL expression suitable for inclusion in the WHERE clause"/>
340
         </proto>
341
      </signatures>
342
      <description>
343
         <p>Executes a SQL query, returning the result as a map.</p>
344
      </description>
345
      <status>
346
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
347
           href="/sql-extension/instructions/query">sql:query</a>. The <code>saxon-sql-10.#.jar</code> file,
348
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
349
            are used.</p>
350
      </status>
351
      <xpath>3.1</xpath>
352
      <saxon-edition>9.9 PE/EE J</saxon-edition>
353
      <details>
354
         <p>The function executes a SQL query. For convenience the query is split across three arguments <code>$table</code>,
355
         <code>$columns</code> and <code>$predicate</code> but the function simply concatenates these together to form
356
         a SQL query which is then executed.</p>
357
         
358
         <p>The 3-argument form of the function retrieves all the rows in a table (the effective predicate is <code>TRUE</code>).</p>
359
  
360
         <p>The value returned by the function is a sequence of maps. Each row in the query result is
361
            represented by one map in this sequence. The map represents a row in the result using a string-valued key
362
            to represent the column name, and an appropriately-typed atomic value to represent the value of the column.
363
            A SQL <code>NULL</code> value is represented by an empty sequence.</p>
364
         <p>Note that the result is a sequence rather than an array. It can be converted to an array (for example,
365
            for serializing in JSON) using the expression <code>array{$result}</code>.</p>
366
         <p>Example:</p>
367
         <samp><![CDATA[<out>
368
   <xsl:for-each select="sql:query($connection, 'book', ('date', 'title'), 
369
      'author = ''J. K. Rowling''')">
370
      <book date="{?date}" title="{?title}"/>
371
   </xsl:for-each>
372
</out>
373
]]></samp>
374
        <p>To avoid problems with nested quotation marks, you could also write:</p>
375
        <samp><![CDATA[<out>
376
   <xsl:variable name="condition" as="xs:string">author = 'J. K. Rowling'</xsl:variable>       
377
   <xsl:for-each select="sql:query($connection, 'book', ('date', 'title'), $condition)">
378
      <book date="{?date}" title="{?title}"/>
379
   </xsl:for-each>
380
</out>
381
]]></samp>
382
      </details>
383
   </function>
384
   
385
   <function xmlns="http://www.saxonica.com/ns/doc/functions">
386
      <name namespace="http://saxon.sf.net/sql">update</name>
387
      <signatures>
388
         <proto return-type="item()?">
389
            <arg name="connection"
390
               type="javatype:java.sql.Connection"
391
               desc="A JDBC database connection established using sql:connect"/>
392
            <arg name="table" type="xs:string" desc="The database table from which rows are to be updated"/>
393
            <arg name="predicate" type="xs:string" desc="A SQL predicate identifying the rows to be updated"/>
394
            <arg name="changes" type="map(*)" desc="A map holding new values for the updated rows"/>
395
         </proto>
396
      </signatures>
397
      <description>
398
         <p>Executes a SQL UPDATE statement, returning no result.</p>
399
      </description>
400
      <status>
401
         <p>Introduced in Saxon 9.9. Designed to supersede the extension instruction <a class="bodylink code"
402
            href="/sql-extension/instructions/update">sql:update</a>. The <code>saxon-sql-10.#.jar</code> file,
403
            distributed alongside the main JAR file, must be added to the classpath when these SQL extension functions
404
            are used.</p>
405
      </status>
406
      <xpath>3.1</xpath>
407
      <saxon-edition>9.9 PE/EE J</saxon-edition>
408
      <details>
409
         <p>The function executes a SQL UPDATE statement, and returns no result.</p>
410
         
411
         <p>The <code>$table</code> argument names the database table to be updated, and the
412
         <code>$predicate</code> argument is a SQL predicate that selects which rows are to be updated. For each
413
         of these rows, new values for selected columns are taken from the <code>$changes</code> map.</p>
414
         
415
         <p>The entries in the <code>$changes</code> map are (name, value) pairs, where the name is a column
416
         name in the table being updated, and the value is the new value for the column. The value should be
417
         an appropriate data type for the column (for example, <code>xs:string</code> for a <code>VARCHAR</code>
418
         column, <code>xs:decimal</code> for a <code>DECIMAL</code> column, etc).</p>
419
         
420
         <p>Although the declared return type is <code>item()?</code>, the function always returns an empty
421
            sequence. The declared return type is designed to prevent the optimizer making unwarranted assumptions.</p>
422
         
423
         <p>Because this function is executed for its side-effects, it is recommended to invoke it using
424
            the <a class="bodylink code" href="/extensions/instructions/do">saxon:do</a> extension instruction.</p>
425
         
426
         <p>Example:</p>
427
         <samp><![CDATA[<saxon:do action="sql:update($connection, 'book', 'stock = 0', map{'out-of-print': true()}"/>]]></samp>
428
      </details>
429
   </function>
430
   
431
 
432
</section>

Also available in: Unified diff