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
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
1001
1002
1003
1004
1005
1006
1007
1008
1009
1010
1011
1012
1013
1014
1015
1016
1017
1018
1019
1020
1021
1022
1023
1024
1025
1026
1027
1028
1029
1030
1031
1032
1033
1034
1035
1036
1037
1038
1039
1040
1041
1042
1043
1044
1045
1046
1047
1048
1049
1050
1051
1052
1053
1054
1055
1056
1057
1058
1059
1060
1061
1062
1063
1064
1065
1066
1067
1068
1069
1070
1071
1072
1073
1074
1075
1076
1077
1078
1079
1080
1081
1082
1083
1084
1085
1086
1087
1088
1089
1090
1091
1092
1093
1094
1095
1096
1097
1098
1099
1100
1101
1102
1103
1104
1105
1106
1107
1108
1109
1110
1111
1112
1113
1114
1115
1116
1117
1118
1119
1120
1121
1122
1123
1124
1125
1126
1127
1128
1129
1130
1131
1132
1133
1134
1135
1136
1137
1138
1139
1140
1141
1142
1143
1144
1145
1146
1147
1148
1149
1150
1151
1152
1153
1154
1155
1156
1157
1158
1159
1160
1161
1162
1163
1164
1165
1166
1167
1168
1169
1170
1171
1172
1173
1174
1175
1176
1177
1178
1179
1180
1181
1182
1183
1184
1185
1186
1187
1188
1189
1190
1191
1192
1193
1194
1195
1196
1197
1198
1199
1200
1201
1202
1203
1204
1205
1206
1207
1208
1209
1210
1211
1212
1213
1214
1215
1216
1217
1218
1219
1220
1221
1222
1223
1224
1225
1226
1227
1228
1229
1230
1231
1232
1233
1234
1235
1236
1237
1238
1239
1240
1241
1242
1243
1244
1245
1246
1247
1248
1249
1250
1251
1252
1253
1254
1255
1256
1257
1258
1259
1260
|
<!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 Tcl interface to the SQLite library</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>
<h1 align=center>The Tcl interface to the SQLite library</h1>
<p>The SQLite library is designed to be very easy to use from
a <a href="http://www.tcl-lang.org">Tcl or Tcl/Tk</a> script. SQLite
began as a <a href="http://www.tcl-lang.org/doc/tea/">Tcl extension</a>
and the primary <a href="testing.html">test suite</a> for SQLite is written in TCL. SQLite
can be used with any programming language, but its connections to
TCL run deep.</p>
<p>This document gives an overview of the Tcl
programming interface for SQLite.</p>
<h3>The API</h3>
<p>The interface to the SQLite library consists of single
tcl command named <b>sqlite3</b>
Because there is only this
one command, the interface is not placed in a separate
namespace.</p>
<p>The <b>sqlite3</b> command is mostly used as follows
to open or create a database:</p>
<blockquote>
<b>sqlite3</b> <i>dbcmd ?database-name? ?options?</i>
</blockquote>
<p>To get information only, the <b>sqlite3</b> command may be given exactly
one argument, either "-version", "-sourceid" or "-has-codec", which will
return the specified datum with no other effect.
</p>
<p>With other arguments, the <b>sqlite3</b> command opens the database
named in the second non-option argument, or named "" if there is no such.
If the open succeeds, a new Tcl command named by the first argument is created
and "" is returned.
(This approach is similar to the way widgets are created in Tk.)
If the open fails, an error is raised without creating a Tcl command
and an error message string is returned.
</p>
<p>If the database does not already exist, the default behavior
is for it to be created automatically (though this can be changed by
using the "<b>-create <i>false</i></b>" option).
</p>
<p>
The name of the database is usually just the name of a disk file in which
the database is stored. If the name of the database is
the special name "<a href="inmemorydb.html">:memory:</a>", then a new database is created
in memory. If the name of the database is an empty string, then
the database is created in an empty file that is automatically deleted
when the database connection closes. <a href="uri.html">URI filenames</a> can be used if
the "<b>-uri yes</b>" option is supplied on the <b>sqlite3</b> command.
</p>
<p>
Options understood by the <b>sqlite3</b> command include:
</p>
<blockquote>
<dl>
<dt><b>-create</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, then a new database is created if one does not already exist.
If false, then an attempt to open a database file that does not previously
exist raises an error. The default behavior is "true".
</p></dd>
<dt><b>-nomutex</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, then all mutexes for the database connection are disabled.
This provides a small performance boost in single-threaded applications.
</p></dd>
<dt><b>-readonly</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, then open the database file read-only. If false, then the
database is opened for both reading and writing if filesystem permissions
allow, or for reading only if filesystem write permission is denied
by the operating system. The default setting is "false". Note that
if the previous process to have the database did not exit cleanly
and left behind a <a href="fileformat2.html#hotjrnl">hot journal</a>, then the write permission is required
to recover the database after opening, and the database cannot be
opened read-only.
</p></dd>
<dt><b>-uri</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, then interpret the filename argument as a <a href="uri.html">URI filename</a>. If
false, then the argument is a literal filename. The default value is
"false".
</p></dd>
<dt><b>-vfs</b> <i>VFSNAME</i></dt>
<dd><p>
Use an alternative <a href="vfs.html">VFS</a> named by the argument.
</p></dd>
<dt><b>-fullmutex</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, multiple threads can safely attempt to use the database.
If false, such attempts are unsafe. The default value depends upon
how the extension is built.
</p></dd>
<dt><b>-nofollow</b> <i>BOOLEAN</i></dt>
<dd><p>
If true, and the database name refers to a symbolic link,
it will not be followed to open the true database file.
If false, symbolic links will be followed.
The default is "false".
</p></dd>
</dl>
</blockquote>
<p>
Once an SQLite database is open, it can be controlled using
methods of the <i>dbcmd</i>.
There are currently 40 methods
defined.</p>
<table border=0 cellpadding=10 width='100%'>
<tr><td valign='top'><ul>
<li><a href="#authorizer">authorizer</a></li>
<li><a href="#backup">backup</a></li>
<li><a href="#bind_fallback">bind_fallback</a></li>
<li><a href="#busy">busy</a></li>
<li><a href="#cache">cache</a></li>
<li><a href="#changes">changes</a></li>
<li><a href="#close">close</a></li>
<li><a href="#collate">collate</a></li>
<li><a href="#collation_needed">collation_needed</a></li>
<li><a href="#commit_hook">commit_hook</a></li>
<li><a href="#complete">complete</a></li>
<li><a href="#config">config</a></li>
<li><a href="#copy">copy</a></li>
<li><a href="#deserialize">deserialize</a></li>
</ul></td>
<td valign='top'><ul>
<li><a href="#enable_load_extension">enable_load_extension</a></li>
<li><a href="#errorcode">errorcode</a></li>
<li><a href="#eval">eval</a></li>
<li><a href="#exists">exists</a></li>
<li><a href="#function">function</a></li>
<li><a href="#incrblob">incrblob</a></li>
<li><a href="#interrupt">interrupt</a></li>
<li><a href="#last_insert_rowid">last_insert_rowid</a></li>
<li><a href="#nullvalue">nullvalue</a></li>
<li><a href="#onecolumn">onecolumn</a></li>
<li><a href="#preupdate">preupdate</a></li>
<li><a href="#profile">profile</a></li>
<li><a href="#progress">progress</a></li>
<li><a href="#restore">restore</a></li>
</ul></td>
<td valign='top'><ul>
<li><a href="#rollback_hook">rollback_hook</a></li>
<li><a href="#serialize">serialize</a></li>
<li><a href="#status">status</a></li>
<li><a href="#timeout">timeout</a></li>
<li><a href="#total_changes">total_changes</a></li>
<li><a href="#trace">trace</a></li>
<li><a href="#trace_v2">trace_v2</a></li>
<li><a href="#transaction">transaction</a></li>
<li><a href="#unlock_notify">unlock_notify</a></li>
<li><a href="#update_hook">update_hook</a></li>
<li><a href="#version">version</a></li>
<li><a href="#wal_hook">wal_hook</a></li>
</ul></td></tr>
</table>
<p>The use of each of these methods will be explained in the sequel, though
not in the order shown above.</p>
<a name="eval"></a>
<h3>The "eval" method</h3>
<p>
The most useful <i>dbcmd</i> method is "eval". The eval method is used
to execute SQL on the database. The syntax of the eval method looks
like this:</p>
<blockquote>
<i>dbcmd</i> <b>eval</b> ?<i>options</i>? <i>sql</i>
?<i>array-name</i>? ?<i>script</i>?
</blockquote>
<p>
The job of the eval method is to execute the SQL statement or statements
given in the second argument. For example, to create a new table in
a database, you can do this:</p>
<blockquote>
<b>sqlite3 db1 ./testdb<br>
db1 eval {CREATE TABLE t1(a int, b text)}</b>
</blockquote>
<p>The above code creates a new table named <b>t1</b> with columns
<b>a</b> and <b>b</b>. What could be simpler?</p>
<p>Query results are returned as a list of column values. If a
query requests 2 columns and there are 3 rows matching the query,
then the returned list will contain 6 elements. For example:</p>
<blockquote>
<b>db1 eval {INSERT INTO t1 VALUES(1,'hello')}<br>
db1 eval {INSERT INTO t1 VALUES(2,'goodbye')}<br>
db1 eval {INSERT INTO t1 VALUES(3,'howdy!')}<br>
set x [db1 eval {SELECT * FROM t1 ORDER BY a}]</b>
</blockquote>
<p>The variable <b>$x</b> is set by the above code to</p>
<blockquote>
<b>1 hello 2 goodbye 3 howdy!</b>
</blockquote>
<p>You can also process the results of a query one row at a time
by specifying the name of an array variable and a script following
the SQL code. For each row of the query result, the values of all
columns will be inserted into the array variable and the script will
be executed. For instance:</p>
<blockquote>
<b>db1 eval {SELECT * FROM t1 ORDER BY a} values {<br>
parray values<br>
puts ""<br>
}</b>
</blockquote>
<p>This last code will give the following output:</p>
<blockquote><b>
values(*) = a b<br>
values(a) = 1<br>
values(b) = hello<p>
values(*) = a b<br>
values(a) = 2<br>
values(b) = goodbye<p>
values(*) = a b<br>
values(a) = 3<br>
values(b) = howdy!</b>
</blockquote>
<p>
For each column in a row of the result, the name of that column
is used as an index in to array and the value of the column is stored
in the corresponding array entry. (Caution: If two or more columns
in the result set of a query have the same name, then the last column
with that name will overwrite prior values and earlier columns with the
same name will be inaccessible.) The special array index * is
used to store a list of column names in the order that they appear.
</p>
<p>
Normally, NULL SQL results are stored in the array using the
<a href="#nullvalue">nullvalue</a> setting. However, if
the <b>-withoutnulls</b> option is used, then NULL SQL values
cause the corresponding array element to be unset instead.
</p>
<p>
If the array variable name is omitted or is the empty string, then the value of
each column is stored in a variable with the same name as the column
itself. For example:
</p>
<blockquote>
<b>db1 eval {SELECT * FROM t1 ORDER BY a} {<br>
puts "a=$a b=$b"<br>
}</b>
</blockquote>
<p>
From this we get the following output
</p>
<blockquote><b>
a=1 b=hello<br>
a=2 b=goodbye<br>
a=3 b=howdy!</b>
</blockquote>
<a name="varsubst"></a>
<p>
Tcl variable names can appear in the SQL statement of the second argument
in any position where it is legal to put a string or number literal. The
value of the variable is substituted for the variable name. If the
variable does not exist a NULL values is used. For example:
</p>
<blockquote><b>
db1 eval {INSERT INTO t1 VALUES(5,$bigstring)}
</b></blockquote>
<p>
Note that it is not necessary to quote the $bigstring value. That happens
automatically. If $bigstring is a large string or binary object, this
technique is not only easier to write, it is also much more efficient
since it avoids making a copy of the content of $bigstring.
</p>
<p>
If the $bigstring variable has both a string and a "bytearray" representation,
then TCL inserts the value as a string. If it has only a "bytearray"
representation, then the value is inserted as a BLOB. To force a
value to be inserted as a BLOB even if it also has a text representation,
use a "@" character to in place of the "$". Like this:
</p>
<blockquote><b>
db1 eval {INSERT INTO t1 VALUES(5,@bigstring)}
</b></blockquote>
<p>
If the variable does not have a bytearray representation, then "@" works
just like "$". Note that ":" works like "$" in all cases so the following
is another way to express the same statement:
</p>
<blockquote><b>
db1 eval {INSERT INTO t1 VALUES(5,:bigstring)}
</b></blockquote>
<p>The use of ":" instead of "$" before the name of a variable can
sometimes be useful if the SQL text is enclosed in double-quotes "..."
instead of curly-braces {...}.
When the SQL is contained within double-quotes "..." then TCL will do
the substitution of $-variables, which can lead to SQL injection if
extreme care is not used. But TCL will never substitute a :-variable
regardless of whether double-quotes "..." or curly-braces {...} are
used to enclose the SQL, so the use of :-variables adds an extra
measure of defense against SQL
injection.
</p>
<a name="close"></a>
<h3>The "close" method</h3>
<p>
As its name suggests, the "close" method to an SQLite database just
closes the database. This has the side-effect of deleting the
<i>dbcmd</i> Tcl command. Here is an example of opening and then
immediately closing a database:
</p>
<blockquote>
<b>sqlite3 db1 ./testdb<br>
db1 close</b>
</blockquote>
<p>
If you delete the <i>dbcmd</i> directly, that has the same effect
as invoking the "close" method. So the following code is equivalent
to the previous:</p>
<blockquote>
<b>sqlite3 db1 ./testdb<br>
rename db1 {}</b>
</blockquote>
<a name="transaction"></a>
<h3>The "transaction" method</h3>
<p>
The "transaction" method is used to execute a TCL script inside an SQLite
database transaction. The transaction is committed when the script completes,
or it rolls back if the script fails. If the transaction occurs within
another transaction (even one that is started manually using BEGIN) it
is a no-op.
</p>
<p>
The transaction command can be used to group together several SQLite
commands in a safe way. You can always start transactions manually using
BEGIN, of
course. But if an error occurs so that the COMMIT or ROLLBACK are never
run, then the database will remain locked indefinitely. Also, BEGIN
does not nest, so you have to make sure no other transactions are active
before starting a new one. The "transaction" method takes care of
all of these details automatically.
</p>
<p>
The syntax looks like this:
</p>
<blockquote>
<i>dbcmd</i> <b>transaction</b> <i>?transaction-type?</i>
<i>script</i>
</blockquote>
<p>
The <i>transaction-type</i> can be one of <b>deferred</b>,
<b>exclusive</b> or <b>immediate</b>. The default is deferred.
</p>
<a name="cache"></a>
<h3>The "cache" method</h3>
<p>
The "eval" method described <a href="#eval">above</a> keeps a cache of
<a href="c3ref/prepare.html">prepared statements</a>
for recently evaluated SQL commands.
The "cache" method is used to control this cache.
The first form of this command is:</p>
<blockquote>
<i>dbcmd</i> <b>cache size</b> <i>N</i>
</blockquote>
<p>This sets the maximum number of statements that can be cached.
The upper limit is 100. The default is 10. If you set the cache size
to 0, no caching is done.</p>
<p>The second form of the command is this:</p>
<blockquote>
<i>dbcmd</i> <b>cache flush</b>
</blockquote>
<p>The cache-flush method
<a href="c3ref/finalize.html">finalizes</a>
all prepared statements currently
in the cache.</p>
<a name="complete"></a>
<h3>The "complete" method</h3>
<p>
The "complete" method takes a string of supposed SQL as its only argument.
It returns TRUE if the string is a complete statement of SQL and FALSE if
there is more to be entered.</p>
<p>The "complete" method is useful when building interactive applications
in order to know when the user has finished entering a line of SQL code.
This is really just an interface to the
<a href="c3ref/complete.html"><b>sqlite3_complete()</b></a> C
function.
<a name="config"></a>
<h3>The "config" method</h3>
<p>
The "config" method queries or changes certain configuration settings for
the database connection using the <a href="c3ref/db_config.html">sqlite3_db_config()</a> interface.
Run this method with no arguments to get a TCL list of available
configuration settings and their current values:
<blockquote>
<i>dbcmd</i> <b>config</b>
</blockquote>
<p>The above will return something like this:
<blockquote>
defensive 0 dqs_ddl 1 dqs_dml 1 enable_fkey 0 enable_qpsg 0 enable_trigger 1 enable_view 1 fts3_tokenizer 1 legacy_alter_table 0 legacy_file_format 0 load_extension 0 no_ckpt_on_close 0 reset_database 0 trigger_eqp 0 trusted_schema 1 writable_schema 0
</blockquote>
<p>Add the name of an individual configuration setting to query the current
value of that setting. Optionally add a boolean value to change a setting.
<p>
The following four configuration changes are recommended for maximum
application security. Turning off the trust_schema setting prevents
virtual tables and dodgy SQL functions from being used inside of triggers,
views, CHECK constraints, generated columns, and expression indexes.
Turning off the dqs_dml and dqs_ddl settings prevents the use of
double-quoted strings. Turning on defensive prevents direct writes
to shadow tables.
<blockquote><pre>
db config trusted_schema 0
db config defensive 1
db config dqs_dml 0
db config dqs_ddl 0
</pre></blockquote>
<a name="copy"></a>
<h3>The "copy" method</h3>
<p>
The "copy" method copies data from a file into a table.
It returns the number of rows processed successfully from the file.
The syntax of the copy method looks like this:</p>
<blockquote>
<i>dbcmd</i> <b>copy</b> <i>conflict-algorithm</i>
<i>table-name </i> <i>file-name </i>
?<i>column-separator</i>?
?<i>null-indicator</i>?
</blockquote>
<p>Conflict-algorithm must be one of the SQLite conflict algorithms for
the INSERT statement: <i>rollback</i>, <i>abort</i>,
<i>fail</i>,<i>ignore</i>, or <i>replace</i>. See the SQLite Language
section for <a href="lang.html#conflict">ON CONFLICT</a> for more information.
The conflict-algorithm must be specified in lower case.
</p>
<p>Table-name must already exists as a table. File-name must exist, and
each row must contain the same number of columns as defined in the table.
If a line in the file contains more or less than the number of columns defined,
the copy method rollbacks any inserts, and returns an error.</p>
<p>Column-separator is an optional column separator string. The default is
the ASCII tab character \t. </p>
<p>Null-indicator is an optional string that indicates a column value is null.
The default is an empty string. Note that column-separator and
null-indicator are optional positional arguments; if null-indicator
is specified, a column-separator argument must be specified and
precede the null-indicator argument.</p>
<p>The copy method implements similar functionality to the <b>.import</b>
SQLite shell command.
<a name="timeout"></a>
<h3>The "timeout" method</h3>
<p>The "timeout" method is used to control how long the SQLite library
will wait for locks to clear before giving up on a database transaction.
The default timeout is 0 millisecond. (In other words, the default behavior
is not to wait at all.)</p>
<p>The SQLite database allows multiple simultaneous
readers or a single writer but not both. If any process is writing to
the database no other process is allows to read or write. If any process
is reading the database other processes are allowed to read but not write.
The entire database shared a single lock.</p>
<p>When SQLite tries to open a database and finds that it is locked, it
can optionally delay for a short while and try to open the file again.
This process repeats until the query times out and SQLite returns a
failure. The timeout is adjustable. It is set to 0 by default so that
if the database is locked, the SQL statement fails immediately. But you
can use the "timeout" method to change the timeout value to a positive
number. For example:</p>
<blockquote><b>db1 timeout 2000</b></blockquote>
<p>The argument to the timeout method is the maximum number of milliseconds
to wait for the lock to clear. So in the example above, the maximum delay
would be 2 seconds.</p>
<a name="busy"></a>
<h3>The "busy" method</h3>
<p>The "busy" method, like "timeout", only comes into play when the
database is locked. But the "busy" method gives the programmer much more
control over what action to take. The "busy" method specifies a callback
Tcl procedure that is invoked whenever SQLite tries to open a locked
database. A single integer argument is appended to the callback before
it is invoke. The argument is the number of prior calls to the busy
callback for the current locking event. It is intended that
the callback will do some other useful work for a short while
(such as service GUI events) then return
so that the lock can be tried again. The callback procedure should
return "0" if it wants SQLite to try again to open the database and
should return "1" if it wants SQLite to abandon the current operation.
<p>
If the busy method is invoked without an argument, the name of the callback
procedure last set by the busy method is returned. If no callback procedure
has been set, an empty string is returned.
<a name="enable_load_extension"></a>
<h3>The "enable_load_extension" method</h3>
<p>The extension loading mechanism of SQLite (accessed using the
<a href="lang_corefunc.html#load_extension">load_extension()</a> SQL function) is turned off by default. This is
a security precaution. If an application wants to make use of the
<a href="lang_corefunc.html#load_extension">load_extension()</a> function it must first turn the capability on using
this method.</p>
<p>This method takes a single boolean argument which will turn the
extension loading functionality on or off.</p>
<p>For best security, do not use this method unless truly needed,
and run <a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a> or the
"<a href="#config">db config trusted_schema 0</a>" method <u>before</u>
invoking this method.
<p>This method maps to the <a href="c3ref/enable_load_extension.html">sqlite3_enable_load_extension()</a> C/C++
interface.</p>
<a name="exists"></a>
<h3>The "exists" method</h3>
<p>The "exists" method is similar to "onecolumn" and "eval" in that
it executes SQL statements. The difference is that the "exists" method
always returns a boolean value which is TRUE if a query in the SQL
statement it executes returns one or more rows and FALSE if the SQL
returns an empty set.</p>
<p>The "exists" method is often used to test for the existence of
rows in a table. For example:</p>
<blockquote><b>
if {[db exists {SELECT 1 FROM table1 WHERE user=$user}]} {<br>
# Processing if $user exists<br>
} else {<br>
# Processing if $user does not exist<br>
}
</b></blockquote>
<a name="last_insert_rowid"></a>
<h3>The "last_insert_rowid" method</h3>
<p>The "last_insert_rowid" method returns an integer which is the ROWID
of the most recently inserted database row.</p>
<a name="function"></a>
<h3>The "function" method</h3>
<p>The "function" method registers new SQL functions with the SQLite engine.
The arguments are the name of the new SQL function and a TCL command that
implements that function. Arguments to the function are appended to the
TCL command before it is invoked.</p>
<p>
For security reasons, it is recommended that applications first set
<a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a> or run the
"<a href="#config">db config trusted_schema 0</a>"
method before using this method.</p>
<p>
The syntax looks like this:
</p>
<blockquote>
<i>dbcmd</i> <b>function</b>
<i>sql-name</i>
<i>?options?</i>
<i>script</i>
</blockquote>
<p>
The following example creates a new SQL function named "hex" that converts
its numeric argument in to a hexadecimal encoded string:
</p>
<blockquote><b>
db function hex {format 0x%X}
</b></blockquote>
<p>The "function" method accepts the following options:
<blockquote>
<dl>
<dt><b>-argcount</b> <i>INTEGER</i></dt>
<dd><p>
Specify the number of arguments that the SQL function accepts. The default
value of -1 means any number of arguments.
<dt><b>-deterministic</b>
<dd><p>
This option indicates that the function will always return the same
answer given the same argument values. The SQLite query optimizer
uses this information to cache answers from function calls with
constant inputs and reuse the result rather than invoke the function
repeatedly.
</dd>
<dt><b>-directonly</b>
<dd><p>
This option restricts the function to only be usable by direct
top-level SQL statement. The function will not be accessible to
triggers, views, CHECK constraints, generated columns, or index
expressions. This option is recommended for all application-defined
SQL functions, and is <u>highly recommended</u> for any SQL
function that has side effects or that reveals internal state of
the application.
<p style="background-color:#ffff90;"><b>Security Warning:</b>
Without this switch, an attacker might be able to change the
schema of a database file to include the new function inside a trigger
or view or CHECK constraint and thereby trick the application into
running the function with parameters of the attacker's choosing.
Hence, if the new function has side effects or reveals internal
state about the application and the -directonly option is not
used, that is a potential security vulnerability.</p>
</dd>
<dt><b>-innocuous</b>
<dd><p>
This option indicates that the function has no side effects and
does not leak any information that cannot be computed directly
from its input parameters. When this option is specified, the
function may be used in triggers, views, CHECK constraints,
generated columns, and/or index expressions even if
<a href="pragma.html#pragma_trusted_schema">PRAGMA trusted_schema=OFF</a>. The use of this option is discouraged
unless it is truly needed.
</dd>
<dt><b>-returntype integer|real|text|blob|any</b>
<dd><p>
This option is used to configure the type of the result returned by the
function. If this option is set to "any" (the default), SQLite attempts to
determine the type of each value returned by the function implementation based
on the Tcl value's internal type. Or, if it is set to "text" or "blob", the
returned value is always a text or blob value, respectively. If this option is
set to "integer", SQLite attempts to coerce the value returned by the function
to an integer. If this is not possible without data loss, it attempts to coerce
it to a real value, and finally falls back to text. If this option is set to
"real", an attempt is made to return a real value, falling back to text if this
is not possible.
</dd>
</dl>
</blockquote>
<a name="nullvalue"></a>
<h3>The "nullvalue" method</h3>
<p>
The "nullvalue" method changes the representation for NULL returned
as result of the "eval" method.</p>
<blockquote><b>
db1 nullvalue NULL
</b></blockquote>
<p>The "nullvalue" method is useful to differ between NULL and empty
column values as Tcl lacks a NULL representation. The default
representation for NULL values is an empty string.</p>
<a name="onecolumn"></a>
<h3>The "onecolumn" method</h3>
<p>The "onecolumn" method works like
"<a href="#eval">eval</a>" in that it evaluates the
SQL query statement given as its argument. The difference is that
"onecolumn" returns a single element which is the first column of the
first row of the query result.</p>
<p>This is a convenience method. It saves the user from having to
do a "<tt>[lindex ... 0]</tt>" on the results of an "eval"
in order to extract a single column result.</p>
<a name="changes"></a>
<h3>The "changes" method</h3>
<p>The "changes" method returns an integer which is the number of rows
in the database that were inserted, deleted, and/or modified by the most
recent "eval" method.</p>
<a name="total_changes"></a>
<h3>The "total_changes" method</h3>
<p>The "total_changes" method returns an integer which is the number of rows
in the database that were inserted, deleted, and/or modified since the
current database connection was first opened.</p>
<a name="authorizer"></a>
<h3>The "authorizer" method</h3>
<p>The "authorizer" method provides access to the
<a href="c3ref/set_authorizer.html">sqlite3_set_authorizer</a>
C/C++ interface. The argument to authorizer is the name of a procedure that
is called when SQL statements are being compiled in order to authorize
certain operations. The callback procedure takes 5 arguments which describe
the operation being coded. If the callback returns the text string
"SQLITE_OK", then the operation is allowed. If it returns "SQLITE_IGNORE",
then the operation is silently disabled. If the return is "SQLITE_DENY"
then the compilation fails with an error.
</p>
<p>If the argument is an empty string then the authorizer is disabled.
If the argument is omitted, then the current authorizer is returned.</p>
<a name="bind_fallback"></a>
<h3>The "bind_fallback" method</h3>
<p>The "bind_fallback" method gives the application control over how to
handle parameter binding when no TCL variable matches the parameter name.
<p>When the <a href="#eval">eval method</a> sees a named SQL
parameter such as "$abc" or ":def" or "@ghi" in an SQL statement, it tries
to look up a TCL variable with the same name, and it binds the value
of that TCL variable to the SQL parameter. If no such TCL variable exists,
the default behavior is to bind an SQL NULL value to the parameter. However,
if a bind_fallback proc is specified, then that proc is invoked with the
name of the SQL parameter and the return value from the proc is bound to
the SQL parameter. Or if the proc returns an error, then the SQL statement
aborts with that error. If the proc returns with some code other than
TCL_OK or TCL_ERROR, then the SQL parameter is bound to NULL, as it would
be by default.
<p>The "bind_fallback" method has a single optional argument. If the argument
is an empty string, then the bind_fallback is cancelled and the default behavior
is restored. If the argument is a non-empty string, then the argument is a
TCL command (usually the name of a proc) to invoke whenever an SQL parameter
is seen that does not match any TCL variable. If the "bind_fallback" method
is given no arguments, then the current bind_fallback command is returned.
<p>As an example, the following setup causes TCL to throw an error if
an SQL statement contains an parameter that does not match any global
TCL variable:
<blockquote><pre><b>
proc bind_error {nm} {
error "no such variable: $nm"
}
db bind_fallback bind_error
</b></pre></blockquote>
<a name="progress"></a>
<h3>The "progress" method</h3>
<p>This method registers a callback that is invoked periodically during
query processing. There are two arguments: the number of SQLite virtual
machine opcodes between invocations, and the TCL command to invoke.
Setting the progress callback to an empty string disables it.</p>
<p>The progress callback can be used to display the status of a lengthy
query or to process GUI events during a lengthy query.</p>
<a name="collate"></a>
<h3>The "collate" method</h3>
<p>This method registers new text collating sequences. There are
two arguments: the name of the collating sequence and the name of a
TCL procedure that implements a comparison function for the collating
sequence.
</p>
<p>For example, the following code implements a collating sequence called
"NOCASE" that sorts in text order without regard to case:
</p>
<blockquote><pre><b>
proc nocase_compare {a b} {
return [string compare [string tolower $a] [string tolower $b]]
}
db collate NOCASE nocase_compare
</b></pre></blockquote>
<a name="collation_needed"></a>
<h3>The "collation_needed" method</h3>
<p>This method registers a callback routine that is invoked when the SQLite
engine needs a particular collating sequence but does not have that
collating sequence registered. The callback can register the collating
sequence. The callback is invoked with a single parameter which is the
name of the needed collating sequence.</p>
<a name="commit_hook"></a>
<h3>The "commit_hook" method</h3>
<p>This method registers a callback routine that is invoked just before
SQLite tries to commit changes to a database. If the callback throws
an exception or returns a non-zero result, then the transaction rolls back
rather than commit.</p>
<a name="rollback_hook"></a>
<h3>The "rollback_hook" method</h3>
<p>This method registers a callback routine that is invoked just before
SQLite tries to do a rollback. The script argument is run without change.</p>
<a name="status"></a>
<h3>The "status" method</h3>
<p>This method returns status information from the most recently evaluated
SQL statement. The status method takes a single argument which should be
either "steps" or "sorts". If the argument is "steps", then the method
returns the number of full table scan steps that the previous SQL statement
evaluated. If the argument is "sorts", the method returns the number of
sort operations. This information can be used to detect queries that are
not using indices to speed search or sorting.</p>
<p>The status method is basically a wrapper on the
<a href="c3ref/stmt_status.html">sqlite3_stmt_status()</a> C-language interface.</p>
<a name="update_hook"></a>
<h3>The "update_hook" method</h3>
<p>This method registers a callback routine that is invoked just before
each row is modified by an UPDATE, INSERT, or DELETE statement. Four
arguments are appended to the callback before it is invoked:</p>
<ul>
<li>The keyword "INSERT", "UPDATE", or "DELETE", as appropriate</li>
<li>The name of the database which is being changed</li>
<li>The table that is being changed</li>
<li>The rowid of the row in the table being changed</li>
</ul>
<a name="wal_hook"></a>
<h3>The "wal_hook" method</h3>
<p>This method registers a callback routine that is invoked after transaction
commit when the database is in <a href="wal.html">WAL mode</a>. Two arguments are appended to the
callback command before it is invoked:</p>
<ul>
<li>The name of the database on which the transaction was committed
<li>The number of entries in the write-ahead log (WAL) file for that database
</ul>
<p>This method might decide to run a <a href="wal.html#ckpt">checkpoint</a> either itself or as a
subsequent idle callback. Note that SQLite only allows a single WAL hook.
By default this single WAL hook is used for the auto-checkpointing. If you
set up an explicit WAL hook, then that one WAL hook must ensure that checkpoints
are occurring since the auto-checkpointing mechanism will be disabled.</p>
<p>This method should return an integer value that is equivalent to an
SQLite error code (usually 0 for SQLITE_OK in the case of success or 1 for
SQLITE_ERROR if some error occurs). As in <a href="c3ref/wal_hook.html">sqlite3_wal_hook()</a>, the results of
returning an integer that does not correspond to an SQLite error code are
undefined. If the value returned by the script cannot be interpreted as an
integer value, or if the script throws a Tcl exception, no error is returned to
SQLite but a Tcl background-error is raised.
<a name="incrblob"></a>
<h3>The "incrblob" method</h3>
<p>This method opens a TCL channel that can be used to read or write
into a preexisting BLOB in the database. The syntax is like this:</p>
<blockquote>
<i>dbcmd</i> <b>incrblob</b> <b>?-readonly?</b>
<i>?DB? TABLE COLUMN ROWID</i>
</blockquote>
<p>
The command returns a new TCL channel for reading or writing to the BLOB.
The channel is opened using the underlying
<a href="c3ref/blob_open.html">sqlite3_blob_open()</a> C-language
interface. Close the channel using the <b>close</b> command of TCL.
</p>
<a name="errorcode"></a>
<h3>The "errorcode" method</h3>
<p>This method returns the numeric error code that resulted from the most
recent SQLite operation.</p>
<a name="trace"></a>
<h3>The "trace" method</h3>
<p>The "trace" method registers a callback that is invoked as each SQL
statement is compiled. The text of the SQL is appended as a single string
to the command before it is invoked. This can be used (for example) to
keep a log of all SQL operations that an application performs.
</p>
<a name="trace_v2"></a>
<h3>The "trace_v2" method</h3>
<p>The "trace_v2" method registers a callback that is invoked as each SQL
statement is compiled. The syntax is as follows:
<blockquote>
<i>dbcmd</i> <b>trace_v2</b> ?<i>callback</i>? ?<i>mask</i>?
</blockquote>
<p>This command causes the "callback" script to be invoked whenever
certain conditions occurs. The conditions are determined by the <i>mask</i>
argument, which should be a TCL-list of zero or more of the following
keywords:
<ul>
<li> <b>statement</b>
<li> <b>profile</b>
<li> <b>row</b>
<li> <b>close</b>
</ul>
<p>Traces for <b>statement</b> invoke the callback with two arguments
whenever a new SQL statement is run.
The first argument is an integer which is the value of the pointer
to the underlying <a href="c3ref/stmt.html">sqlite3_stmt</a> object. This integer can be used
to correlate SQL statement text with the result of a <b>profile</b>
or <b>row</b> callback. The second argument is the
unexpanded text of the SQL statement being run. By "unexpanded", we
mean that variable substitutions in the text are not expanded into the
variable values. This is different from the behavior of the "trace"
method which does expand variable substitutions.
<p>Traces for <b>profile</b> invoke the callback with two arguments
as each SQL statement finishes. The first argument is an integer which
is the value of the underlying <a href="c3ref/stmt.html">sqlite3_stmt</a> object. The second
argument is the approximate run-time for the statement in nanoseconds.
The run-time is the best estimate available depending on the capabilities
of the platform on which the application is running.
<p>Traces for <b>row</b> invoke the callback with a single argument
whenever a new result row is available from an SQL statement.
The argument is an integer which is the value of the underlying
<a href="c3ref/stmt.html">sqlite3_stmt</a> object pointer.
<p>Traces for <b>close</b> invoke the callback with a single argument
as the database connection is closing. The argument is an integer which
is the value of a pointer to the underlying <a href="c3ref/sqlite3.html">sqlite3</a> object that is
closing.
<p>There can only be a single trace callback registered on a database
connection. Each use of "trace" or "trace_v2" cancels all prior
trace callback.
<a name="backup"></a>
<h3>The "backup" method</h3>
<p>The "backup" method makes a backup copy of a live database. The
command syntax is like this:</p>
<blockquote>
<i>dbcmd</i> <b>backup</b> ?<i>source-database</i>? <i>backup-filename</i>
</blockquote>
<p>The optional <i>source-database</i> argument tells which database in
the current connection should be backed up. The default value is <b>main</b>
(or, in other words, the primary database file). To back up TEMP tables
use <b>temp</b>. To backup an auxiliary database added to the connection
using the <a href="lang_attach.html">ATTACH</a> command, use the name of that database as it was assigned
in the <a href="lang_attach.html">ATTACH</a> command.</p>
<p>The <i>backup-filename</i> is the name of a file into which the backup is
written. <i>Backup-filename</i> does not have to exist ahead of time, but if
it does, it must be a well-formed SQLite database.</p>
<a name="restore"></a>
<h3>The "restore" method</h3>
<p>The "restore" method copies the content from a separate database file
into the current database connection, overwriting any preexisting content.
The command syntax is like this:</p>
<blockquote>
<i>dbcmd</i> <b>restore</b> ?<i>target-database</i>? <i>source-filename</i>
</blockquote>
<p>The optional <i>target-database</i> argument tells which database in
the current connection should be overwritten with new content.
The default value is <b>main</b>
(or, in other words, the primary database file). To repopulate the TEMP tables
use <b>temp</b>. To overwrite an auxiliary database added to the connection
using the <a href="lang_attach.html">ATTACH</a> command, use the name of that database as it was assigned
in the <a href="lang_attach.html">ATTACH</a> command.</p>
<p>The <i>source-filename</i> is the name of an existing well-formed SQLite
database file from which the content is extracted.</p>
<a name="serialize"></a>
<h3>The "serialize" method</h3>
<p>The "serialize" method creates a BLOB which is a complete copy of an
underlying database. The syntax is like this:
<blockquote>
<i>dbcmd</i> <b>serialize</b> ?<i>database</i>?
</blockquote>
<p>The optional argument is the name of the schema or database to be serialized.
The default value is "main".
<p>This routine returns a TCL byte-array that is the complete content of
the identified database. This byte-array can be written into a file and
then used as an ordinary SQLite database, or it can be sent over a TCP/IP
connection to some other application, or passed to the "deserialize" method
of another database connection.
<p>This method only functions if SQLite is compiled with -DSQLITE_ENABLE_DESERIALIZE
<a name="deserialize"></a>
<h3>The "deserialize" method</h3>
<p>The "deserialize" method takes a TCL byte-array that contains an SQLite
database file and adds it to the database connection. The syntax is:
<blockquote>
<i>dbcmd</i> <b>deserialize</b> ?<i>database</i>? <i>value</i>
</blockquote>
<p>The option <i>database</i> argument identifies which attached database
should receive the deserialization. The default is "main".
<p>This command causes SQLite to disconnect from the previous database and
reattach to an in-memory database with the content in <i>value</i>. If <i>value</i>
is not a byte-array containing a well-defined SQLite database, then subsequent
commands will likely return <a href="rescode.html#corrupt">SQLITE_CORRUPT</a> errors.
<p>This method only functions if SQLite is compiled with -DSQLITE_ENABLE_DESERIALIZE
<a name="interrupt"></a>
<h3>The "interrupt" method</h3>
<p>The "interrupt" method invokes the <a href="c3ref/interrupt.html">sqlite3_interrupt()</a> interface, causing
any pending queries to halt.
<a name="version"></a>
<h3>The "version" method</h3>
Return the current library version. For example, "3.23.0".
<a name="profile"></a>
<h3>The "profile" method</h3>
<p>This method is used to profile the execution of SQL statements run by
the application. The syntax is as follows:
</p>
<blockquote>
<i>dbcmd</i> <b>profile</b> ?<i>script</i>?
</blockquote>
<p>Unless <i>script</i> is an empty string, this method arranges for the
<i>script</i> to be evaluated after the execution of each SQL statement.
Two arguments are appended to <i>script</i> before it is invoked: the
text of the SQL statement executed and the time elapsed while executing
the statement, in nanoseconds.
</p>
<p>A database handle may only have a single profile script registered at
any time. If there is already a script registered when the profile method
is invoked, the previous profile script is replaced by the new one. If the
<i>script</i> argument is an empty string, any previously registered
profile callback is canceled but no new profile script is registered.
</p>
<a name="unlock_notify"></a>
<h3>The "unlock_notify" method</h3>
<p>The unlock_notify method is used access the <a href="c3ref/unlock_notify.html">sqlite3_unlock_notify()</a>
interface to the SQLite core library for testing purposes. The use of
this method by applications is discouraged.
</p>
<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/tclsqlite.in?m=dea4497aa28ca1740">2022-12-14 16:19:41</a> UTC </small></i></p>
|
