= Library for SQLite and MySQL database support   (lib825db) =

[[TOC(inline, depth=1, noheading, Docs/Prog/Manual/ApplicationLibraries/lib825db/CDB)]]

lib825db provides a base class CDB and inherited class CDBSQLite and CDBMySQL to facilitate apps being able to use common code for SQLite and MySQL databases.

Most of our existing apps using SQLite use the C++ wrapper code namespace sq3 provided by libsql, libsql3_7_9, or lib825sql. To edit this code to use lib825db instead is fairly simple.

Existing apps will have:
{{{#!c++
#include "sqlite3.h"
#include "sq3.hpp"

using namespace sq3;
}}}

Replace with:
{{{#!c++
#include "dbsql.h"
}}}

Existing apps will have global database instance such as:
{{{#!c++
database db;
}}}

Replace with:
{{{#!c++
CDB* appDB = nullptr;
}}}

Existing apps will have code to open the database such as:
{{{#!c++
db.open("/mnt/nand/apps/<appdir>/myapp.db3");
if(!db.is_open) {
   DrawErrorBox("Error opening database", 2);
}
}}}

Replace with code similar to:
{{{#!c++
CAppDatabaseCfg appDBcfg;
string path = appDataPath + CFGFILE_DB;
appDBcfg.SetArchiveFilepath(path);
appDBcfg.ReadConfig();

if(appDBcfg.GetDBType() == CAppDatabaseCfg::appDataMySQL) {
   appDB = new CDBMySQL(appDBcfg.GetServer(), appDBcfg.GetUser(), appDBcfg.GetPassword(), appDBcfg.GetDatabase());
} else {
   path = appDataPath + "ids.db3";
   appDB = new CDBSQLite(path);
}

if(appDB->OpenDatabase() == false) {
   DEBUG_MSG("Error [%s]\n", appDB->GetErrorMsg().c_str());
   DrawErrorBox("Cannot open database", 4);
}
}}}
appDBcfg is not part of lib825db. This code is in the ID Storage app to provide a user interface and load/save database configuration. The files appdatabase.cpp and appdatabase.h may be copied from ID Storage for your own apps and customized if desired.

This instantiates either a CDBMySQL object for MySQL or a CDBSQLite object for SQLite. The CDB* pointer appDB is assigned to the created object.

Existing apps will have SQL execute statements such as:
{{{#!c++
int ct;
string sql = "SELECT COUNT(*) FROM [Trucks];";
db.Execute(sql, ct);
}}}

Replace with:
{{{#!c++
int ct;
string sql = "SELECT COUNT(*) FROM Trucks;";
appDB->Execute(sql, ct);
}}}
To support both SQLite and MySQL the brackets must be removed from table and field names in SQL statements.

Existing apps may have code such as:
{{{#!c++
string sql = "INSERT INTO [Trucks] ([ID],[TareWt]) VALUES ('" + truckID +"'," + IntToStr(tare) + ");"; 
if (rc_is_okay(db.execute(sql)) == false) {
   DrawErrorBox("Cannot insert record", 4);
}
}}}
Replace with:
{{{#!c++
string sql = "INSERT INTO Trucks (ID,TareWt) VALUES ('" + truckID +"'," + IntToStr(tare) + ");"; 
if (appDB->execute(sql) == false) {
   DrawErrorBox("Cannot insert record", 4);
}
}}}

Existing apps will have code such as:
{{{#!c++
string sql = "SELECT * FROM [railcars] WHERE [ID] = '" + strId + "';";
statement st(db, sql);
cursor cur(st.get_cursor());
if(cur.step() == SQLITE_ROW) {
   cur.get("ID", curRailcar.strId);
   cur.get("Target", curRailcar.target);
}
}}}

Replace with:
{{{#!c++
string sql = "SELECT * FROM railcars WHERE ID = '" + strId + "';";
CDBStatement stmt(appDB, sql);
if(stmt.Step() == DBSQL_ROW) {
   stmt.Get("ID", curRailcar.strId);
   stmt.Get("Target", curRailcar.target);
}
}}}
Be sure to replace SQLITE_ROW with DBSQL_ROW.

When app finishs existing apps:
{{{#!c++
db.close;
}}}
Replace with:
{{{#!c++
appDB->Close();
delete appDB;
appDB = nullptr;
}}}

There may be cases where an app needs to format the SQL string differently for SQLite or MySQL. This can be accomplished by checking a dynamic cast of the CDB* pointer such as:
{{{#!c++
string sql;
if(dynamic_cast<CDBMySQL*>(appDB)) {
  // Format SQL for MySQL
} else {
  // Format SQL for SQLite
}
}