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 databaseKompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase("test.db", SQLITE_OPEN_READWRITE, 0);// create statement instance for sql queries/statementsKompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);
2. create a new table and fill it with data
// create table and insert some datapStmt->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 resultswhile(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-uppStmt->FreeQuery();
5. execute a simple transaction
// if an error occurs, a rollback is automatically performedpStmt->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 hencepDatabase->MoveDatabaseToMemory();-
Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);// insert some data sets into the memory databasepStmt->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 databasepDatabase->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 resultswhile(pStmt->FetchRow()){ std::cout << "firstName: " << pStmt->GetColumnString("firstName") << std::endl; std::cout << "age: " << pStmt->GetColumnInt("age") << std::endl;}-
// do not forget to clean-uppStmt->FreeQuery();
9. two possibilities to update data in the database
// the first waypStmt->SqlStatement("UPDATE user SET weight=51.5, age=18 WHERE firstName='Carlene'"); // the second way - with a prepared statementpStmt->Sql("UPDATE user SET lastName=@lastName, age=@age WHERE userID=@userID"); // bind an integer to the prepared statementpStmt->BindString(1, "Urushihara"); // bind lastNamepStmt->BindInt(2, 56); // bind agepStmt->BindInt(3, 2); // bind userID-
// execute it and clean-uppStmt->ExecuteAndFree();
.png)
作者：Gezidan
本文版權歸作者和博客園共有，歡迎轉載，但未經作者同意必須保留此段聲明，且在文章頁面明顯位置給出原文連接，否則保留追究法律責任的權利。
posted on 2011-08-02 14:12
日需博客 閱讀(1791)
評論(1) 編輯 收藏 引用 所屬分類:
C C++ 、
技術文章 、
轉載