Library for Microsft SQL Server database support (lib825mssql)

Comming Soon

lib825mssql provides a CDBMSSQL class inherited from class CDB in lib825db to facilitate apps also being able to use common code for Microsoft SQL databases.

Note this library provides client connectivity. The database will have to be hosted on a SQL Server instance running on a Windows PC or server.

This example shows database startup code to have an app support SQLite, MySQL, or SQL Server:

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 if(appDBcfg.GetDBType() == CAppDatabaseCfg::appDataMSSQL) {
   appDB = new CDBMSSQL(appDBcfg.GetServer(), appDBcfg.GetUser(), appDBcfg.GetPassword(), appDBcfg.GetDatabase());
} else {
   appDB = new CDBSQLite("/mnt/nand/apps/<appdir>/myapp.db3");
}

if(appDB->OpenDatabase() == false) {
   DEBUG_MSG("Error [%s]\n", appDB->GetErrorMsg().c_str());
   DrawErrorBox("Cannot open database", 4);
}

CAppDatabaseCfg 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 a CDBMySQL object for MySQL, a CDBMSSQL object for SQL Server, or a CDBSQLite object for SQLite based on the configuration. The created object is assigned to the pointer CDB* appDB.

A common SQL statement in startup code for 825 projects is "CREATE TABLE IF NOT EXISTS". Microsoft SQL Server does not support this. The "IF OBJECT_ID('tblname', 'U') IS NULL BEGIN CREATE TABLE" may be used instead. This statement initiates a "BEGIN" which must be terminated after the create with "END".

The "INTEGER PRIMARY KEY AUTO_INCREMENT" also will need to be handled differently. Replace this with "INTEGER IDENTITY(1,1) PRIMARY KEY".

if(dynamic_cast<CDBMSSQL*>(appDB)) {
  sql = "IF OBJECT_ID('dbo.axleTrans', 'U') IS NULL BEGIN CREATE TABLE axleTrans (";
} else {
  sql = "CREATE TABLE IF NOT EXISTS axleTrans (";
}

if(dynamic_cast<CDBMySQL*>(appDB)) {
  sql +=        "id INTEGER PRIMARY KEY AUTO_INCREMENT,";
} else if(dynamic_cast<CDBMSSQL*>(appDB)) {
  sql += "id INTEGER IDENTITY(1,1) PRIMARY KEY,";
} else {
  sql += "id INTEGER PRIMARY KEY,"; // SQLite: INTEGER PRIMARY KEY aliases ROWID and auto-increments
}
sql +=
"cost TEXT,"
"pmt_type TEXT,"
"badge TEXT,"
"customer TEXT,";

for(n = 0; n < MAX_DB_AXLES; n++) {
  sql += "axle" + to_string(n + 1) + " REAL,";
}
sql += "grossT REAL" // Total wt (sum of wts)
",datetm DATETIME"
");";

if(dynamic_cast<CDBMSSQL*>(appDB)) {
  sql += " END";
}

if(appDB->Execute(sql) == false) {
  DrawErrorBox("Error creating transaction table", 2);
}