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
|
/*-
* See the file LICENSE for redistribution information.
*
* Copyright (c) 1997, 2015 Oracle and/or its affiliates. All rights reserved.
*
*/
#include "ex_sql_utils.h"
/*
* This example shows how to use prepare + binding + transaction to do bulk
* insert.
*
* Here we insert 100,000 records using a prepared statement and binding.
*/
/* Example body. */
static int
ex_sql_binding(db)
db_handle *db;
{
const char *sql;
int i, rc;
char uname[64];
int errflag;
int num_of_records = 100000;
sqlite3_stmt* stmt;
/*
* Prepare the statement for insert many times over. The '?' in sql
* expression is the variable for binding.
*/
sql = "INSERT INTO university VALUES"
"(?, ?, 'xxxxx.edu.cn', 'cn', 'Asia', 999,99,999,999);";
/*
* The sqlite3_prepare_v2() interfaces is recommended for all new
* programs. It supports binding and is compatible with
* sqlite3_prepare().
*/
sqlite3_prepare_v2(db, sql, (int)strlen(sql), &stmt, NULL);
error_handler(db);
/*
* When we insert data many times over, we shall use explicit
* transaction to combine the operations so they execute faster. Also,
* using sqlite3_prepare and sqlite3_bind* is a good choice.
*/
exec_sql(db, "BEGIN TRANSACTION");
errflag = 0;
for (i = 1; i <= num_of_records && !errflag; i++) {
/*
* We can bind int, blob, int64, null, text, text16, value
* and zero blob by API.
*/
/* i -> #1 variable */
sqlite3_bind_int(stmt, 1, i);
error_handler(db);
/* uname -> #2 variable */
sprintf(uname, "%d_university", i);
sqlite3_bind_text(stmt, 2, uname, (int)strlen(uname), NULL);
error_handler(db);
/* Execute the query expression */
sqlite3_step(stmt);
/* Reset stmt when SQLITE_DONE. The sqlite3_reset() function
* is called to reset a prepared statement object back to its
* initial state, ready to be re-executed. Any SQL statement
* variables that had values bound to them using the
* sqlite3_bind_*() API retain their values.
*/
rc = sqlite3_errcode(db);
switch(rc) {
case SQLITE_DONE:
sqlite3_reset(stmt);
break;
default:
fprintf(stderr, "ERROR: %s. ERRCODE: %d\n",
sqlite3_errmsg(db), rc);
errflag = 1;
break;
}
if (i%10000 == 0)
printf("Inserted %d rows\n", i);
}
/* Commit if succeed and rollback if failed. */
sql = (errflag) ? "ROLLBACK TRANSACTION" : "COMMIT TRANSACTION";
exec_sql(db, sql);
/* Final cleanup. */
sqlite3_finalize(stmt);
error_handler(db);
/* Display result. */
sql = "SELECT count(*) FROM university;";
exec_sql(db, sql);
return 0;
}
int
main()
{
db_handle *db;
/* Setup environment and preload data. */
db = setup("./ex_sql_binding.db");
load_table_from_file(db, university_sample_data, 1/* Silent */);
/* Run example. */
ex_sql_binding(db);
/* End. */
cleanup(db);
return 0;
}
|
