= 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:
{{{#!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 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".

{{{#!c++
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 is commonly used for showing data in pages with "Next" and "Prev" buttons. The library has a new function AppendLimitClause to automatically append the correct syntax without having to use an if condition.

{{{#!c++
//char tmp[24];
//snprintf(tmp, sizeof(tmp), " LIMIT %d,%d;", ((m_nCurPage - 1) * m_nItemsPerPage), m_nItemsPerPage);
//strSQL_ += tmp;
appDB->AppendLimitClause(strSQL_, ((m_nCurPage - 1) * m_nItemsPerPage), m_nItemsPerPage);
strSQL_ += ";";	
}}}