Home

Examples

Download

Documentation

Changelog

Guestbook

Contact



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
  1. // open database
  2. Kompex::SQLiteDatabase *pDatabase = new Kompex::SQLiteDatabase("test.db", SQLITE_OPEN_READWRITE, 0);
  3. // create statement instance for sql queries/statements
  4. Kompex::SQLiteStatement *pStmt = new Kompex::SQLiteStatement(pDatabase);


2. create a new table and fill it with data
  1. // create table and insert some data
  2. pStmt->SqlStatement("CREATE TABLE user                        \
  3.                     (                                         \
  4.                     userID INTEGER NOT NULL PRIMARY KEY,      \
  5.                     lastName VARCHAR(50) NOT NULL,            \
  6.                     firstName VARCHAR(50), age INTEGER        \
  7.                     )");
  8.                     
  9. pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
  10.                      VALUES (1, 'Lehmann', 'Jamie', 20)");
  11. pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
  12.                      VALUES (2, 'Burgdorf', 'Peter', 55)");
  13. pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
  14.                      VALUES (3, 'Lehmann', 'Fernando', 18)");
  15. pStmt->SqlStatement("INSERT INTO user (userID, lastName, firstName, age) \
  16.                      VALUES (4, 'Lehmann', 'Carlene', 17)");


3. use some aggregate functions
  1. pStmt->SqlAggregateFuncResult("SELECT COUNT(*) FROM user WHERE lastName = 'Lehmann';");
  2. pStmt->SqlAggregateFuncResult("SELECT COUNT(weight) FROM user;");
  3. pStmt->SqlAggregateFuncResult("SELECT MAX(age) FROM user;");
  4. pStmt->SqlAggregateFuncResult("SELECT MIN(age) FROM user;");
  5. pStmt->SqlAggregateFuncResult("SELECT AVG(age) FROM user;");
  6. pStmt->SqlAggregateFuncResult("SELECT SUM(age) FROM user;");
  7. pStmt->SqlAggregateFuncResult("SELECT TOTAL(age) FROM user;");


4. execute and process a sql query
  1. pStmt->Sql("SELECT * FROM user WHERE firstName = 'Jamie';");
  2. // process all results
  3. while(pStmt->FetchRow())
  4. {
  5.     std::cout << "first name: " << pStmt->GetColumnInt(0) << std::endl;
  6.     std::cout << "last name: " << pStmt->GetColumnString(1) << std::endl;
  7.     std::cout << "first name: " << pStmt->GetColumnString(2) << std::endl;
  8. }
  9. // do not forget to clean-up
  10. pStmt->FreeQuery();


5. execute a simple transaction
  1. // if an error occurs, a rollback is automatically performed
  2. pStmt->BeginTransaction();
  3. pStmt->Transaction("INSERT INTO user(userID, firstName, age) VALUES (10, 'Makoto', 23)");
  4. pStmt->Transaction("INSERT INTO user(userID, firstName, age) VALUES (11, 'Yura', 20)";
  5. pStmt->Transaction("INSERT INTO user(userID, firstName, age) VALUES (12, 'Peter', 63)");
  6. pStmt->CommitTransaction();


6. here you can see the exception handling
  1. try
  2. {
  3.     // table structure: userID INTEGER NOT NULL PRIMARY KEY, lastName VARCHAR(50)
  4.     pStmt->SqlStatement("INSERT INTO user(userID, lastName) VALUES(1, 'Lehmann')");
  5.     // the following line will throw an exception because the primary key is not unique
  6.     pStmt->SqlStatement("INSERT INTO user(userID, lastName) VALUES(1, 'Bergmann')");
  7. }
  8. catch(Kompex::SQLiteException &exception)
  9. {
  10.     std::cerr << "Exception Occured" << std::endl;
  11.     exception.Show();
  12. }


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


8. get some result values via column name (more flexible and a little bit slower than the common way)
  1. pStmt->Sql("SELECT * FROM user WHERE lastName = 'Lehmann';");
  2.  
  3. // process all results
  4. while(pStmt->FetchRow())
  5. {
  6.     std::cout << "firstName: " << pStmt->GetColumnString("firstName") << std::endl;
  7.     std::cout << "age: " << pStmt->GetColumnInt("age") << std::endl;
  8. }
  9.  
  10. // do not forget to clean-up
  11. pStmt->FreeQuery();    


9. two possibilities to update data in the database
  1. // the first way
  2. pStmt->SqlStatement("UPDATE user SET weight=51.5, age=18 WHERE firstName='Carlene'");
  3.         
  4. // the second way - with a prepared statement
  5. pStmt->Sql("UPDATE user SET lastName=@lastName, age=@age WHERE userID=@userID");
  6.             
  7. // bind an integer to the prepared statement
  8. pStmt->BindString(1, "Urushihara");        // bind lastName
  9. pStmt->BindInt(2, 56);                    // bind age
  10. pStmt->BindInt(3, 2);                    // bind userID
  11.  
  12. // execute it and clean-up
  13. pStmt->ExecuteAndFree();