Kompex SQLite Wrapper for C++ - Examples

Here you can see some examples.
It shows a part of the functionality of the wrapper and how you can use it.
You can find more examples in the example application (contained in download package).

1. open a database and create a statement instance for sql queries/statements

// open database
Kompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase("test.db", SQLITE_OPEN_READWRITE, 0);
// create statement instance for sql queries/statements
Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);

2. create a new table and fill it with data

// create table and insert some data
pStmt->SqlStatement("CREATE TABLE user                        \
                    (                                         \
                    userID INTEGER NOT NULL PRIMARY KEY,      \
                    lastName VARCHAR(50) NOT NULL,            \
                    firstName VARCHAR(50), age INTEGER        \
                    )");
                    
pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
                     VALUES (1, 'Lehmann', 'Jamie', 20)");
pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
                     VALUES (2, 'Burgdorf', 'Peter', 55)");
pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
                     VALUES (3, 'Lehmann', 'Fernando', 18)");
pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
                     VALUES (4, 'Lehmann', 'Carlene', 17)");

3. use some aggregate functions

pStmt->SqlAggregateFuncResult("SELECT COUNT(*) FROM user WHERE lastName = 'Lehmann';");
pStmt->SqlAggregateFuncResult("SELECT COUNT(weight) FROM user;");
pStmt->SqlAggregateFuncResult("SELECT MAX(age) FROM user;");
pStmt->SqlAggregateFuncResult("SELECT MIN(age) FROM user;");
pStmt->SqlAggregateFuncResult("SELECT AVG(age) FROM user;");
pStmt->SqlAggregateFuncResult("SELECT SUM(age) FROM user;");
pStmt->SqlAggregateFuncResult("SELECT TOTAL(age) FROM user;");

4. execute and process a sql query

pStmt->Sql("SELECT * FROM user WHERE firstName = 'Jamie';");
// process all results
while(pStmt->FetchRow())
{
    std::cout << "first name: " << pStmt->GetColumnInt(0) << std::endl;
    std::cout << "last name: " << pStmt->GetColumnString(1) << std::endl;
    std::cout << "first name: " << pStmt->GetColumnString(2) << std::endl;
}
// do not forget to clean-up
pStmt->FreeQuery();

5. execute a simple transaction

// if an error occurs, a rollback is automatically performed
pStmt->BeginTransaction();
pStmt->Transaction("INSERT INTO user(userID, firstName, age) VALUES (10, 'Makoto', 23)");
pStmt->Transaction("INSERT INTO user(userID, firstName, age) VALUES (11, 'Yura', 20)";
pStmt->Transaction("INSERT INTO user(userID, firstName, age) VALUES (12, 'Peter', 63)");
pStmt->CommitTransaction();

6. here you can see the exception handling

try
{
    // table structure: userID INTEGER NOT NULL PRIMARY KEY, lastName VARCHAR(50)
    pStmt->SqlStatement("INSERT INTO user(userID, lastName) VALUES(1, 'Lehmann')");
    // the following line will throw an exception because the primary key is not unique
    pStmt->SqlStatement("INSERT INTO user(userID, lastName) VALUES(1, 'Bergmann')");
}
catch(Kompex::SQLiteException &exception)
{
    std::cerr << "Exception Occured" << std::endl;
    exception.Show();
}

7. work with a memory database

Kompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase("scores.db", SQLITE_OPEN_READWRITE, 0);
// move database to memory, so that we are work on the memory database hence
pDatabase->MoveDatabaseToMemory();
 
Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);
// insert some data sets into the memory database
pStmt->SqlStatement("INSERT INTO score(id, lastScore, avgScore) VALUES(1, 429, 341)");
pStmt->SqlStatement("INSERT INTO score(id, lastScore, avgScore) VALUES(2, 37, 44)");
pStmt->SqlStatement("INSERT INTO score(id, lastScore, avgScore) VALUES(3, 310, 280)");
 
// save the memory database to a file
// if you don't do it, all database changes will be lost after closing the memory database
pDatabase->SaveDatabaseFromMemoryToFile("newScores.db");

8. get some result values via column name (more flexible and a little bit slower than the common way)

pStmt->Sql("SELECT * FROM user WHERE lastName = 'Lehmann';");
 
// process all results
while(pStmt->FetchRow())
{
    std::cout << "firstName: " << pStmt->GetColumnString("firstName") << std::endl;
    std::cout << "age: " << pStmt->GetColumnInt("age") << std::endl;
}
 
// do not forget to clean-up
pStmt->FreeQuery();    

9. two possibilities to update data in the database

// the first way
pStmt->SqlStatement("UPDATE user SET weight=51.5, age=18 WHERE firstName='Carlene'");
        
// the second way - with a prepared statement
pStmt->Sql("UPDATE user SET lastName=@lastName, age=@age WHERE userID=@userID");
            
// bind an integer to the prepared statement
pStmt->BindString(1, "Urushihara");        // bind lastName
pStmt->BindInt(2, 56);                    // bind age
pStmt->BindInt(3, 2);                    // bind userID
 
// execute it and clean-up
pStmt->ExecuteAndFree();

作者：Gezidan

出处：http://www.rixu.net　　　　

本文版权归作者和博客园共有，欢迎转载，但未经作者同意必须保留此段声明，且在文章页面明显位置给出原文连接，否则保留追究法律责任的权利。

posted on 2011-08-02 14:12 日需博客阅读(1646) 评论(1) 编辑收藏引用所属分类: C C++ 、技术文章、转载

留言簿(2)

随笔分类(279)

随笔档案(108)

搜索

最新随笔

最新评论

阅读排行榜

只有注册用户登录后才能发表评论。


相关文章: C++11中的Move语义和右值引用传奇3.0 VC++最终正式版本源码 SQLite3 C/C++ 开发接口简介修改const变量的疑问 GDI+ for VC6 MFC C++自定义发IP包例子 ARP欺骗 C++实现 Windows平台下的内存管理 malloc,new,VirtualAlloc,HeapAlloc性能(速度)比较 VC++下使用ADO编写数据库程序

网站导航: 博客园 IT新闻 BlogJava 知识库博问管理