1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
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>
|
