diff options
Diffstat (limited to 'www/carray.html')
| -rw-r--r-- | www/carray.html | 255 |
1 files changed, 255 insertions, 0 deletions
diff --git a/www/carray.html b/www/carray.html new file mode 100644 index 0000000..4e18e43 --- /dev/null +++ b/www/carray.html @@ -0,0 +1,255 @@ +<!DOCTYPE html> +<html><head> +<meta name="viewport" content="width=device-width, initial-scale=1.0"> +<meta http-equiv="content-type" content="text/html; charset=UTF-8"> +<link href="sqlite.css" rel="stylesheet"> +<title>The Carray() Table-Valued Function</title> +<!-- path= --> +</head> +<body> +<div class=nosearch> +<a href="index.html"> +<img class="logo" src="images/sqlite370_banner.gif" alt="SQLite" border="0"> +</a> +<div><!-- IE hack to prevent disappearing logo --></div> +<div class="tagline desktoponly"> +Small. Fast. Reliable.<br>Choose any three. +</div> +<div class="menu mainmenu"> +<ul> +<li><a href="index.html">Home</a> +<li class='mobileonly'><a href="javascript:void(0)" onclick='toggle_div("submenu")'>Menu</a> +<li class='wideonly'><a href='about.html'>About</a> +<li class='desktoponly'><a href="docs.html">Documentation</a> +<li class='desktoponly'><a href="download.html">Download</a> +<li class='wideonly'><a href='copyright.html'>License</a> +<li class='desktoponly'><a href="support.html">Support</a> +<li class='desktoponly'><a href="prosupport.html">Purchase</a> +<li class='search' id='search_menubutton'> +<a href="javascript:void(0)" onclick='toggle_search()'>Search</a> +</ul> +</div> +<div class="menu submenu" id="submenu"> +<ul> +<li><a href='about.html'>About</a> +<li><a href='docs.html'>Documentation</a> +<li><a href='download.html'>Download</a> +<li><a href='support.html'>Support</a> +<li><a href='prosupport.html'>Purchase</a> +</ul> +</div> +<div class="searchmenu" id="searchmenu"> +<form method="GET" action="search"> +<select name="s" id="searchtype"> +<option value="d">Search Documentation</option> +<option value="c">Search Changelog</option> +</select> +<input type="text" name="q" id="searchbox" value=""> +<input type="submit" value="Go"> +</form> +</div> +</div> +<script> +function toggle_div(nm) { +var w = document.getElementById(nm); +if( w.style.display=="block" ){ +w.style.display = "none"; +}else{ +w.style.display = "block"; +} +} +function toggle_search() { +var w = document.getElementById("searchmenu"); +if( w.style.display=="block" ){ +w.style.display = "none"; +} else { +w.style.display = "block"; +setTimeout(function(){ +document.getElementById("searchbox").focus() +}, 30); +} +} +function div_off(nm){document.getElementById(nm).style.display="none";} +window.onbeforeunload = function(e){div_off("submenu");} +/* Disable the Search feature if we are not operating from CGI, since */ +/* Search is accomplished using CGI and will not work without it. */ +if( !location.origin || !location.origin.match || !location.origin.match(/http/) ){ +document.getElementById("search_menubutton").style.display = "none"; +} +/* Used by the Hide/Show button beside syntax diagrams, to toggle the */ +function hideorshow(btn,obj){ +var x = document.getElementById(obj); +var b = document.getElementById(btn); +if( x.style.display!='none' ){ +x.style.display = 'none'; +b.innerHTML='show'; +}else{ +x.style.display = ''; +b.innerHTML='hide'; +} +return false; +} +var antiRobot = 0; +function antiRobotGo(){ +if( antiRobot!=3 ) return; +antiRobot = 7; +var j = document.getElementById("mtimelink"); +if(j && j.hasAttribute("data-href")) j.href=j.getAttribute("data-href"); +} +function antiRobotDefense(){ +document.body.onmousedown=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousedown=null; +} +document.body.onmousemove=function(){ +antiRobot |= 2; +antiRobotGo(); +document.body.onmousemove=null; +} +setTimeout(function(){ +antiRobot |= 1; +antiRobotGo(); +}, 100) +antiRobotGo(); +} +antiRobotDefense(); +</script> +<div class=fancy> +<div class=nosearch> +<div class="fancy_title"> +The Carray() Table-Valued Function +</div> +</div> + + + + +<h1 id="overview"><span>1. </span>Overview</h1> + +<p>Carray() is a <a href="vtab.html#tabfunc2">table-valued function</a> with a single column (named +"value") and zero or more rows. +The "value" of each row in the carray() is taken from a C-language array +supplied by the application via <a href="c3ref/bind_blob.html">parameter binding</a>. +In this way, the carray() function provides a convenient mechanism to +bind C-language arrays to SQL queries. + +</p><h1 id="availability"><span>2. </span>Availability</h1> + +<p>The carray() function is not compiled into SQLite by default. +It is available as a <a href="loadext.html">loadable extension</a> in the +<a href="https://www.sqlite.org/src/file/ext/misc/carray.c">ext/misc/carray.c</a> +source file. + +</p><p>The carray() function was first added to SQLite in version 3.14 +(2016-08-08). The sqlite3_carray_bind() interface and the +single-argument variant of carray() was added in SQLite version 3.34.0 +(2020-12-01). The ability to bind an array of <tt>struct iovec</tt> +objects that are interpreted as BLOBs was added in SQLite version 3.41.0 +(2023-02-21). + +</p><h1 id="details"><span>3. </span>Details</h1> + +<p>The carray() function takes one, two, or three arguments. + +</p><p>For the two- and three-argument versions of carray(), +the first argument is a pointer to an array. Since pointer values cannot +be specified directly in SQL, the first argument must be a <a href="lang_expr.html#varparam">parameter</a> that +is bound to a pointer value using the <a href="c3ref/bind_blob.html">sqlite3_bind_pointer()</a> interface +using a pointer-type of "carray". +The second argument is the number of elements in the array. The optional +third argument is a string that determines the datatype of the elements +in the C-language array. Allowed values for the third argument are: + +</p><ol> +<li> 'int32' +</li><li> 'int64' +</li><li> 'double' +</li><li> 'char*' +</li><li> 'struct iovec' +</li></ol> + +<p>The default datatype is 'int32'. + +</p><p>The 'struct iovec' type used for BLOB data is a standard Posix data +structure, normally declared using "<tt>#include <sys/uio.h></tt>". +The format is: + +</p><blockquote><pre> +struct iovec { + void *iov_base; /* Starting address */ + size_t iov_len; /* Number of bytes to transfer */ +}; +</pre></blockquote> + +<a name="onearg"></a> + +<h2 id="single_argument_carray"><span>3.1. </span>Single-Argument CARRAY</h2> + +<p>The single-argument form of carray() requires a special C-language +interface named "sqlite3_carray_bind()" in order to attach values: + +</p><blockquote><pre> + int sqlite3_carray_bind( + sqlite3_stmt *pStmt, /* Statement containing the CARRAY */ + int idx, /* Parameter number for CARRAY argument */ + void *aData, /* Data array */ + int nData, /* Number of entries in the array */ + int mFlags, /* Datatype flag */ + void (*xDestroy)(void*) /* Destructor for aData */ + ); +</pre></blockquote> + +<p>The mFlags parameter to sqlite3_carray_bind() must be one of: + +</p><blockquote><pre> + #define CARRAY_INT32 0 + #define CARRAY_INT64 1 + #define CARRAY_DOUBLE 2 + #define CARRAY_TEXT 3 + #define CARRAY_BLOB 4 +</pre></blockquote> + +<p>Higher order bits of the mFlags parameter must all be zero for now, +though they may be used in future enhancements. The definitions for the +constants that specify the datatype and a prototype for the +sqlite3_carray_bind() function are both available in the auxiliary +header file +<a href="https://www.sqlite.org/src/file/ext/misc/carray.h">ext/misc/carray.h</a>. + +</p><p>The xDestroy argument to sqlite3_carray_bind() routine is a pointer +to a function that frees the input array. SQLite will invoke this +function after it has finished with the data. The xDestroy argument +may optionally be one of the following constants defined in +"sqlite3.h": + +</p><ul> +<li><p> + <a href="c3ref/c_static.html">SQLITE_STATIC</a> → This means that the application that invokes + sqlite3_carray_bind() maintains ownership of the data array and that + the application promises SQLite that it will not change or deallocate + the data until after the prepared statement is finialized. + +</p></li><li><p> + <a href="c3ref/c_static.html">SQLITE_TRANSIENT</a> → This special value instructs SQLite to make + its own private copy of the data before the + sqlite3_carray_bind() interface returns. +</p></li></ul> + + +<h1 id="usage"><span>4. </span>Usage</h1> + +<p>The carray() function can be used in the FROM clause of a query. +For example, to query two entries from the OBJ table using rowids +taken from a C-language array at address $PTR. + +</p><div class="codeblock"><pre>SELECT obj.* FROM obj, carray($PTR, 10) AS x + WHERE obj.rowid=x.value; +</pre></div> + +<p>This query gives the same result: + +</p><div class="codeblock"><pre>SELECT * FROM obj WHERE rowid IN carray($PTR, 10); +</pre></div> +<p align="center"><small><i>This page last modified on <a href="https://sqlite.org/docsrc/honeypot" id="mtimelink" data-href="https://sqlite.org/docsrc/finfo/pages/carray.in?m=5722621d60">2023-02-17 13:24:09</a> UTC </small></i></p> + |