printf("%s = %s\n", azColName[i], argv[i] ? argv[i] : "NULL");

}

printf("\n");

return 0;

}

int main(int argc, char **argv){

sqlite3 *db;

char *zErrMsg = 0;

int rc;

if( argc!=3 ){

fprintf(stderr, "Usage: %s DATABASE SQL-STATEMENT\n", argv[0]);

exit(1);

}

rc = sqlite3_open(argv[1], &db);

if( rc ){

fprintf(stderr, "Can't open database: %s\n", sqlite3_errmsg(db));

sqlite3_close(db);

exit(1);

}

rc = sqlite3_exec(db, argv[2], callback, 0, &zErrMsg);

if( rc!=SQLITE_OK ){

fprintf(stderr, "SQL error: %s\n", zErrMsg);

sqlite3_free(zErrMsg);

}

sqlite3_close(db);

return 0;

}

(1) 建立sqlite_test.c檔案，內容如上。把sqlite_test.c檔案放到mike目錄下。

(2) 編譯sqlite_test.c檔案：

gcc -I /usr/local/include -L /usr/local/lib -o sqlite_test sqlite_test.c -lsqlite3

產生sqlite_test的應用程式。

Gcc解釋：

-Ldir

制定編譯的時候，搜尋庫的路徑。比如你自己的庫，可以用它制定目錄，不然編譯器將只在標準庫的目錄找。這個dir就是目錄的名稱。

-llibrary

制定編譯的時候使用的庫

-Idir

在你是用#include"file"的時候,gcc/g++會先在查詢你所制定的標頭檔案,如果沒有找到,他回到預設的標頭檔案目錄找,如果使用-I制定了目錄,他會先在你所制定的目錄查詢,然後再按常規的順序去找.

對於#include,gcc/g++會到-I制定的目錄查詢,查詢不到,然後將到系統的預設的標頭檔案目錄查詢

(3) 測試sqlite_test程式

test程式接受兩個引數：第一個引數為資料庫檔名，第二個引數為要執行的SQL語句。程式中與SQLite3的API相關的地方主要有四個：第27行的sqlite3_open()，第33行的sqlite3_exec()，第30行和第38行的sqlite3_close()，第36行的sqlite3_free()。關於SQLite3的API介面請參閱文獻[1]。

下面是測試test程式的完整過程，需要注意的是由於命令較長，因此每一個命令都分成了多行輸入，這樣看起來要清楚一些：

[root@Sitsang2 root]$./test xyz.db "create table

> tbl0(name varchar(10), number smallint);"

[root@Sitsang2 root]$./test xyz.db "insert into

> tbl0 values('cyc', 1);"

[root@Sitsang2 root]$./test xyz.db "insert into

> tbl0 values('dzy', 2);"

[root@Sitsang2 root]$./test xyz.db "select *

> from tbl0;"

name = cyc

number = 1

name = dzy

number = 2

解釋一下上面所用的測試命令：第一條命令在xyz.db這個中建立了一個tbl0表，表中包含兩個欄位，欄位name是一個變長字串，欄位number的型別為smallint；第二條命令向資料庫的tbl0表中插入了一條記錄(‘cyc’,1)；第三條命令向資料庫的tbl0表中插入了一條記錄(‘dzy’,2)；第四條命令則是查詢表tbl0中的所有內容，與預期的一樣，這條命令列印除了資料庫中的兩條剛插入的記錄。由此可以得出結論，這幾條命令確實都已經按照預期的目標工作了。

同時，在向資料庫中插入上面所示的資料之後，可以看到xyz.db大小已經發生了變化：

[root@Sitsang2 root]$ll -h xyz.db

-rw-r--r-- 1 root root 2.0k Jan 1 00:18 xyz.db

此時xyz.db的大小為2K。測試結果表明資料庫能夠正常工作。

3.3.1向上移植SQLite 刪除先前的sqlite-amalgamation-3.5.9目錄

解壓縮sqlite-amalgamation-3.5.9.tar.gz

tar zxvf sqlite-amalgamation-3.5.9.tar.gz

進入sqlite目錄

cd sqlite-amalgamation-3.5.9

我們在/home/mike目錄下建立一個目錄sqlite_install，作為後面使用的。

mkdir /home/mike/sqlite_install

配置原始檔，以生成適合於 的Makefile

export PATH=/usr/local/arm-linux/bin:$PATH

./configure --host=arm-linux --prefix=/home/mike/sqlite_install

解釋：linux軟體的安裝方式一般有2種：rpm方式；利用./configure配置，然後make、再make install方式；請查閱相關資料。這裡，--host引數指名編譯出的軟體要在arm-linux平臺上執行；--prefix則指名軟體安裝目錄。Configure執行的結果是產生一個相應的Makefile檔案，該檔案遵從configure後面兩個引數的指定。

編譯

make

安裝

make install

進入/home/sqlite_install觀看，可以發現有三個目錄,分別是include、lib、bin。分別存放了編寫sqlite資料庫應用程式需要的標頭檔案、應用程式編譯和執行時均需要的、可在arm　linux系統上執行的sqlite命令列程式。

3.3.2編寫、編譯使用SQLite函式庫的應用程式 我們利用交叉開發工具鏈對在3.2.2中編寫的程式sqlite_test.c從新編譯，使之可以在上執行。

編譯源程式：

確認sqlite_test.c在mike目錄下，進入mike目錄

arm-linux-gcc -I ./sqlite_install/include/ -L ./ sqlite_install/lib/ -o sqlite_test sqlite_test.c -lsqlite3

執行完畢後，產生arm 版本的sqlite_test檔案

檢視sqlite_test檔案的屬性

file sqlite_test

把sqlite_test檔案複製到的某個目錄，把sqlite_test執行所需要的庫函式，即/home/mike/sqlite_install/lib目錄下的所有檔案複製到根的lib目錄下，重新制作cramfs或者格式的根檔案系統映像，並燒寫到實驗系統中。

系統啟動後，可仿照3.2中的方式對sqlite_test進行測試。附件1 sqlite3: A command-line access program for SQLite database　The SQLite library includes a simple command-line utility named sqlite3that allows the user to manually enter and execute SQL commands against an SQLite database. This document provides a brief introduction on how to use sqlite3.

Getting Started

To start the sqlite3program, just type "sqlite3" followed by the name the file that holds the SQLite database. If the file does not exist, a new one is created automatically. The sqlite3program will then prompt you to enter SQL. Type in SQL statements (terminated by a semicolon), press "Enter" and the SQL will be executed.

For example, to create a new SQLite database named "ex1" with a single table named "tbl1", you might do this:

$ sqlite3 ex1

SQLite version 3.3.10

Enter ".help" for instructions

sqlite> create table tbl1(one varchar(10), two smallint);

sqlite> insert into tbl1 values('hello!',10);

sqlite> insert into tbl1 values('goodbye', 20);

sqlite> select * from tbl1;

hello!|10

goodbye|20

sqlite>

You can terminate the sqlite3 program by typing your systems End-Of-File character (usually a Control-D) or the interrupt character (usually a Control-C).

Make sure you type a semicolon at the end of each SQL command! The sqlite3 program looks for a semicolon to know when your SQL command is complete. If you omit the semicolon, sqlite3 will give you a continuation prompt and wait for you to enter more text to be added to the current SQL command. This feature allows you to enter SQL commands that span multiple lines. For example:

sqlite> CREATE TABLE tbl2 (

...> f1 varchar(30) primary key,

...> f2 text,

...> f3 real

...> );

sqlite>

Aside: Querying the SQLITE_MASTER table

The database schema in an SQLite database is stored in a special table named "sqlite_master". You can execute "SELECT" statements against the special sqlite_master table just like any other table in an SQLite database. For example:

$ sqlite3 ex1

SQlite vresion 3.3.10

Enter ".help" for instructions

sqlite> select * from sqlite_master;

type = table

name = tbl1

tbl_name = tbl1

rootpage = 3

sql = create table tbl1(one varchar(10), two smallint)

sqlite>

But you cannot execute DROP TABLE, UPDATE, INSERT or DELETE against the sqlite_master table. The sqlite_master table is updated automatically as you create or drop tables and indices from the database. You can not make manual changes to the sqlite_master table.

The schema for TEMPORARY tables is not stored in the "sqlite_master" table since TEMPORARY tables are not visible to applications other than the application that created the table. The schema for TEMPORARY tables is stored in another special table named "sqlite_temp_master". The "sqlite_temp_master" table is temporary itself.

Special commands to sqlite3

Most of the time, sqlite3 just reads lines of input and passes them on to the SQLite library for execution. But if an input line begins with a dot ("."), then that line is intercepted and interpreted by the sqlite3 program itself. These "dot commands" are typically used to change the output format of queries, or to execute certain prepackaged query statements.

For a listing of the available dot commands, you can enter ".help" at any time. For example:

sqlite> .help

.bail ON|OFF Stop after hitting an error. Default OFF

.databases List names and files of attached databases

.dump ?TABLE? ... Dump the database in an SQL text format

.echo ON|OFF Turn command echo on or off

.exit Exit this program

.explain ON|OFF Turn output mode suitable for EXPLAIN on or off.

.header(s) ON|OFF Turn display of headers on or off

.help Show this message

.import FILE TABLE Import data from FILE into TABLE

.indices TABLE Show names of all indices on TABLE

.load FILE ?ENTRY? Load an extension library

.mode MODE ?TABLE? Set output mode where MODE is one of:

csv Comma-separated values

column Left-aligned columns. (See .width)

html HTML code and the ending are not written, but all of the intervening s, s, and s are. The html output mode is envisioned as being useful for CGI.

3.4 Berkeley DB

3國產嵌入式資料庫OpenBASE Lite編輯

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28371090/viewspace-1141199/，如需轉載，請註明出處，否則將追究法律責任。