频道导航

Kompex SQLite Wrapper for C++ - Examples

2020-06-21 Sqlite 前端之家

前端之家收集整理的这篇文章主要介绍了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 @H_403_7@

//opendatabase
Kompex::sqliteDatabase*pDatabase=newKompex::sqliteDatabase("test.db",sqlITE_OPEN_READWRITE,0);
//createstatementinstanceforsqlqueries/statements
Kompex::sqliteStatement*pStmt=newKompex::sqliteStatement(pDatabase);

2. create a new table and fill it with data @H_403_7@

//createtableandinsertsomedata
pStmt->sqlStatement("CREATETABLEuser\
(\
userIDINTEGERNOTNULLPRIMARYKEY,\
lastNameVARCHAR(50)NOTNULL,\
firstNameVARCHAR(50),ageINTEGER\
)");
pStmt->sqlStatement("INSERTINTOuser(userID,lastName,firstName,age)\
VALUES(1,'Lehmann','Jamie',20)");
pStmt->sqlStatement("INSERTINTOuser(userID,age)\
VALUES(2,'Burgdorf','Peter',55)");
pStmt->sqlStatement("INSERTINTOuser(userID,age)\
VALUES(3,'Fernando',18)");
pStmt->sqlStatement("INSERTINTOuser(userID,age)\
VALUES(4,'Carlene',17)");

3. use some aggregate functions @H_403_7@

pStmt->sqlAggregateFuncResult("SELECTCOUNT(*)FROMuserWHERElastName='Lehmann';");
pStmt->sqlAggregateFuncResult("SELECTCOUNT(weight)FROMuser;");
pStmt->sqlAggregateFuncResult("SELECTMAX(age)FROMuser;");
pStmt->sqlAggregateFuncResult("SELECTMIN(age)FROMuser;");
pStmt->sqlAggregateFuncResult("SELECTAVG(age)FROMuser;");
pStmt->sqlAggregateFuncResult("SELECTSUM(age)FROMuser;");
pStmt->sqlAggregateFuncResult("SELECTTOTAL(age)FROMuser;");

4. execute and process a sql query @H_403_7@

pStmt->sql("SELECT*FROMuserWHEREfirstName='Jamie';");
//processallresults
while(pStmt->FetchRow())
{
std::cout<<"firstname:"<<pStmt->GetColumnInt(0)<<std::endl;
std::cout<<"lastname:"<<pStmt->GetColumnString(1)<<std::endl;
std::cout<<"firstname:"<<pStmt->GetColumnString(2)<<std::endl;
}
//donotforgettoclean-up
pStmt->FreeQuery();

5. execute a simple transaction @H_403_7@

//ifanerroroccurs,arollbackisautomaticallyperformed
pStmt->BeginTransaction();
pStmt->Transaction("INSERTINTOuser(userID,age)VALUES(10,'Makoto',23)");
pStmt->Transaction("INSERTINTOuser(userID,age)VALUES(11,'Yura',20)";
pStmt->Transaction("INSERTINTOuser(userID,age)VALUES(12,63)");
pStmt->CommitTransaction();

6. here you can see the exception handling @H_403_7@

try
{
//tablestructure:userIDINTEGERNOTNULLPRIMARYKEY,lastNameVARCHAR(50)
pStmt->sqlStatement("INSERTINTOuser(userID,lastName)VALUES(1,'Lehmann')");
//thefollowinglinewillthrowanexceptionbecausetheprimarykeyisnotunique
pStmt->sqlStatement("INSERTINTOuser(userID,'Bergmann')");
}
catch(Kompex::sqliteException&exception)
{
std::cerr<<"ExceptionOccured"<<std::endl;
exception.Show();
}

7. work with a memory database @H_403_7@

Kompex::sqliteDatabase*pDatabase=newKompex::sqliteDatabase("scores.db",0);
//movedatabasetomemory,sothatweareworkonthememorydatabasehence
pDatabase->MoveDatabaseToMemory();
Kompex::sqliteStatement*pStmt=newKompex::sqliteStatement(pDatabase);
//insertsomedatasetsintothememorydatabase
pStmt->sqlStatement("INSERTINTOscore(id,lastscore,avgscore)VALUES(1,429,341)");
pStmt->sqlStatement("INSERTINTOscore(id,avgscore)VALUES(2,37,44)");
pStmt->sqlStatement("INSERTINTOscore(id,avgscore)VALUES(3,310,280)");
//savethememorydatabasetoafile
//ifyoudon'tdoit,alldatabasechangeswillbelostafterclosingthememorydatabase
pDatabase->SaveDatabaseFromMemoryToFile("newscores.db");

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

pStmt->sql("SELECT*FROMuserWHERElastName='Lehmann';");
//processallresults
while(pStmt->FetchRow())
{
std::cout<<"firstName:"<<pStmt->GetColumnString("firstName")<<std::endl;
std::cout<<"age:"<<pStmt->GetColumnInt("age")<<std::endl;
}
//donotforgettoclean-up
pStmt->FreeQuery();

9. two possibilities to update data in the database @H_403_7@

//thefirstway
pStmt->sqlStatement("UPDATEuserSETweight=51.5,age=18WHEREfirstName='Carlene'");
//thesecondway-withapreparedstatement
pStmt->sql("UPDATEuserSETlastName=@lastName,age=@ageWHEREuserID=@userID");
//bindanintegertothepreparedstatement
pStmt->BindString(1,"Urushihara");//bindlastName
pStmt->BindInt(2,56);//bindage
pStmt->BindInt(3,2);//binduserID
//executeitandclean-up
pStmt->ExecuteAndFree();