RichardChambers
commented
3 years ago I have modified the SQL connect string in the PLU Totals DLL so as to use the SQLite ODBC driver.
I have modified the file PluTtlDb.cpp in the class CnOPluTotalDb to include a compile time decision as to which ODBC driver to use by the appropriate define in the file ecr.h
#if defined(USE_PLU_TOTALS_SQLSERVER)
// We are using shared memory protocol for our connection string. Change made for
// Amtrak because the tablet with its wireless cell card was causing GenPOS to be unable
// to connect to the MSDE or SQL Server instance under some circumstances.
ConnectionStringTemp = L"Provider=MSDASQL;DRIVER={SQL Server};SERVER=lpc:(local);DATABASE=%s;UID=; Password=;";
ConnectionStringNoDB = L"Provider=MSDASQL;DRIVER={SQL Server};SERVER=lpc:(local);DATABASE=;UID=; Password=;";
#elif defined(USE_PLU_TOTALS_SQLITE)
ConnectionStringTemp = L"DRIVER=SQLite3 ODBC Driver;Database=\\FlashDisk\\NCR\\Saratoga\\Database\\%s.db;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;";
ConnectionStringNoDB = L"DRIVER=SQLite3 ODBC Driver;LongNames=0;Timeout=1000;NoTXN=0;SyncPragma=NORMAL;StepAPI=0;";
#elif defined(USE_PLU_TOTALS_SQLSERVER_EMBEDDED)
// ConnectionStringTemp = L"Driver={SQL Server Native Client 10.0};Server=(local);Database=%s;Trusted_Connection=yes;";
// ConnectionStringNoDB = L"Driver={SQL Server Native Client 10.0};Server=(local);Database=;Trusted_Connection=yes;";
ConnectionStringTemp = L"Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\\FlashDisk\\NCR\\Saratoga\\Database\\TOTALPLU.sdf;";
ConnectionStringNoDB = L"Provider=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\\FlashDisk\\NCR\\Saratoga\\Database\\TOTALPLU.sdf;";
#endifAm currently testing with USE_PLU_TOTALS_SQLITE defined in ecr.h to enable using the SQLite ODBC driver.
NHPOS Rel 1.4 used the built in Windows CE database engine for the PLU totals as it was readily available with Windows CE without any additional software installation.
Beginning with NHPOS Rel 2.0 and the move from Windows CE to Windows NT/2000 the decision was made to move to Microsoft SQL Server Express for the PLU totals. The rationale for this decision is murky however it most probably had to do with the Third Party application MWS/CWS was using SQL Server Express as its database engine. Since Windows NT/2000 had a native database engine similar to the one native to Windows CE it would have been logical to move to that instead of SQL Server as the PLU totals are stored in a simple database that does not require the power and flexibility of SQL Server.
Through the years, the dependency on SQL Server Express has been a point of pain from time to time. It has required the installation of another software component which required updates and increased the cybersecurity concerns for NHPOS/GenPOS. The installation required additional effort on the part of the people installing NHPOS/GenPOS as it was a separate step. Also SQL Server required additional resources and the Database folder did not contain the PLU totals databases as those were stored by SQL Server in a separate directory than the rest of the NHPOS/GenPOS files.
Recommend that the PLU Totals functionality use some other database engine than SQL Server Express. Two alternatives are the native Windows database engine and the SQLite database engine.
The SQLite database engine seems to be the best choice due to the simple database schema used by GenPOS.
SQLite is widely used for embedded applications and can be integrated into GenPOS thereby no longer requiring a separate database engine software install.
The SQLite database files can be placed into the Database directory along with the other GenPOS data files.
SQLite is much less resource hungry than SQL Server and since it is embedded within the application the complexity of the interfaces used by SQL Server and the cybersecurity concerns from that complexity are eliminated.