练习使用C++访问SQLite数据库。
Description
In this tutorial we will look at how to access an SQLite database from within your C++ code.
Preliminary
Verify that you are able to access the SQLite database using its sqlite3.exe console app as you did in the previous tutorial. If necessary review those steps before proceeding with this tutorial.
Problem 1: Compiling SQLite Access Into Your C++ Code
Download the SQLite Amalgamation from the sqlite.org downloads page https://www.sqlite.org/download.html and unzip the contents.
Copy the extracted sqlite3.h and sqlite3.c files into your directory where you will be writing and compiling your .cpp code files (i.e. add them to the code provided in the demo_code folder).
SQLite is written in C, not C++. So use the gcc C compiler to compile only and object file from sqlite3.c (Notice this is s gcc compile and not a g++ compile.)
$gcc -c sqlite3.c
This should produce an sqlite3.o object file that we can link in with the main.cpp used for the C++ application.
Look at the main.cpp file provided with the demo code it should have contents like the following. Notice it includes the local sqlite3.h file) For what is to follow it’s helpful to examine the sqlite.org C interface reference to understand the functions being called: http://www.sqlite.org/cintro.html).
1 |
|
Compile main.cpp by itself like this:
g++ -g -Wall main.cpp
Notice the errors that result because of undefined references (unimplemented functions). That’s because the sqlite3.o file compiled previously is not being linked in.
Now compile by linking in the sqlite3.o object file as well including the libraries that were referred to when sqlite3.o was compiled (here we are also including some link options to make things thread-safe and dynamically linkable -these may or may not be needed so include them to be safe):
g++ -g -Wall main.cpp sqlite3.o -lpthread -ldl
This time an a.out should be produced and when run should open a new test.db database.
When you press an key and close the program you should notice a test.db sqlite database file has been created:
You have now successfully connected to a SQLite database.
Problem 2: Adding a Makefile
Next we will create a makefile to help with the compilation.
Create a makefile with the following contents:1
2
3
4
5
6
7
8
9
10
11
12
13OBJ = main.o sqlite3.o
myApp: $(OBJ)
g++ -o myApp $(OBJ) -lpthread -ldl
main.o: main.cpp
g++ -c main.cpp
sqlite3.o: sqlite3.c sqlite3.h
gcc -c sqlite3.c
clean:
rm -f $(OBJ) myApp
Verify that you can compile and run your code using this new makefile by executing:
make clean
make myApp
./myApp
Problem 3: Accessing beatles.db Database
Next lets access the beatles.db sqlite database from our C++ code and print the contents of the recordings table to the console. As a reminder here is the ER model and Schema of the database.
Modify the main.cpp to have the following contents. This code connects to the beatles.db database, executes an SQL query on the data, and calls the “callback” function for each row of the resulting answer table. (A version of this file is provided in the demo code as problem3_main.cpp for you to copy and paste from.)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// main.cpp
using namespace std;
string input; // user input from console
const char * db_file_name = "beatles.db";
sqlite3 *db; // pointer to our database
char * zErrMsg = 0; // pointer to error returned by sqlite
int rc; //return code
const char * sql = "SELECT * FROM recordings"; // SQL Query String
const char * data = "call back function called"; // passed back on callback;
static int callback (
// callback funtion that will be called by sqlite3_exec() when SQL
// query has completed
void * data, // data provided to sqlite3_exec() [4th argument]
int argc, // Number of columns in answer table row
char ** argv, //array of string representing table row values
char ** azColName //array of strings representing column names
){
//cout [[ (const char *) data << "\n";
for (int i=0; i < argc; i++){
cout << argv[i] << ", ";
}
cout << "\n";
return 0;
}
int main(void) {
cout << "Hello SQLite\n";
// establish connection to database
rc = sqlite3_open(db_file_name, &db);
if (rc) {
cout << "ERROR: Could Not Open Database: "
<< sqlite3_errmsg(db);
cout << "\nEXITING\n";
return 0;
}
else {
cout << "\nSUCCESSFULLY OPENED DATABASE: " << db_file_name << "\n\n";
rc = sqlite3_exec(db, sql, callback, (void *) data, &zErrMsg);
if( rc != SQLITE_OK) {
cout << "ERROR EXECUTING SQL: " << zErrMsg;
sqlite3_free(zErrMsg);
}
else {
cout << "\nSQL QUERY SUCCEEDED\n";
}
}
// close database connection
sqlite3_close(db);
// keep console open until user presses a key
cout << "\n\n" << "Press any key to continue";
getline(cin, input);
return 0;
}
Compile and debug the new main.cpp. Once it runs successfully you should see the entries in the beatles.db recordings table printed to the console by your C++ code:
Problem 4: Executing User SQL Querys
Next we want to allow the user of our C++ app to type in an SQL query and have it run against the beatles.db database. We have provide a problem4_main.cpp that should do this. Study that code and the main.cpp file to match that contents. With this code try running some SQL queries against with your app. (Notice when the user types their SQL it does not seem to matter whether they end with a semi-colon or not -why is that?)
Look through the SQL documentation on the sqlite.org website and compose try different queries against the beatles.db database.
When you have completed these exercises show your work to the TA’s to get credit for the tutorial.