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 provides a user interface and load/save database configuration. The files appdatabase.cpp​ and appdatabase.h​ are attached to this wiki page and may be copied 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 automatically 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"
",datetm DATETIME"
");";

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

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

Microsoft SQL does not have the "LIMIT" clause that SQLite and MySQL have. This clause is commonly used for showing data in pages with "Next" and "Prev" buttons. Microsoft SQL 2012 and above have "OFFSET <offset> ROWS FETCH NEXT <count> ROWS ONLY" that achieves the same function. (Microsft SQL requires "ORDER BY" clause immediately before this. The library lib825db has a virtual function AppendLimitClause to automatically append the limit clause. For MySQL or SQL this will append the "LIMIT <offset>,<count>" clause, for SQL Server this will append "OFFSET <offset> ROWS FETCH NEXT <count> ROWS ONLY"

// For SQLite or MySQL AppendLimitClause will append " LIMIT offset, count"
// For SQL Server AppendLimitClause will append " OFFSET offset ROWS FETCH NEXT count ROWS ONLY"
appDB->AppendLimitClause(strSQL_, ((m_nCurPage - 1) * m_nItemsPerPage), m_nItemsPerPage);
strSQL_ += ";";