= 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 clause 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_ += ";";	
}}}