How to use MobilChange with database, SMS archivation

How to switch Mobilchange to use registry
How to switch Mobilchange to use MS SQL
How to switch Mobilchange to use Oracle
Viewing SMS archive
Viewing accounting data

Mobilchange usually stores settings of all users in the registry key HKLM/SOFTWARE/Datasys/Mobilchange/user. Every user has its own subkey with name derived from user's email address. But you can switch Mobilchange to store user's settings into MS SQL or Oracle database. From Mobilchange version 4.6.09 you can select database type during setup (registry/ MS SQL/ Oracle) or change it after installation using Mobilchange\setup\_database.cmd script (also Start menu - MobilChange - Configuration - Database setup). When using database you can also archive SMS processed by Mobilchange (acording to user settings). Database support is included only in ENTERPRISE version of Mobilchange and it is licensed as add-on.

Database support can be set also manually in HKLM/SOFTWARE/Datasys/Mobilchange/database registry key. All settings described here are related to this key.
IMPORTANT - to be able to use database you must have ADO at least version 2.6 installed on Mobilchange.

How to switch Mobilchange to use registry

Set provider value (REG_SZ) to "C:\MobilChange\bin\dbapireg.dll" (valid if Mobilchange is installed in C:\MobilChange - set according to reality). Set mode (REG_SZ) value to "0". Now restart Mobilchange.Kernel service.
This is usual Mobilchange setting.

How to switch Mobilchange to use MS SQL

Set provider (REG_SZ) value to "C:\MobilChange\bin\mxdbado.dll" (valid if Mobilchange is installed in C:\MobilChange - set according to reality). Set mode (REG_SZ) value to "1".

In ODBC_conn_string value (REG_SZ) set ODBC connection string for database connection in format
"DRIVER={SQL Server};SERVER=xxx;DATABASE=yyy;Trusted_Connection=yes", where set database server name instead of xxx and database name instead of yyy. If Trusted connection is not used, value has format
"DRIVER={SQL Server};SERVER=xxx;DATABASE=%s;Uid=%s;Pwd=%s"
You must fill also service (replaces DATABASE), user (replaces Uid) and pass (replaces Pwd) values.
ATTENTION - pass value is encoded, so first start MobilChange\bin\EncPass.exe application and enter REAL password here. In "Scrambled" textbox encoded password is displayed - THIS value must be entered into the registry (use clipboard to avoid overstrike).

In similar way set also conn_string, what is ADO connection string in format
"Provider=sqloledb;Data Source=xxx;Initial Catalog=yyy;Integrated Security=SSPI" or
"Provider=sqloledb;Data Source=xxx;Initial Catalog=%s;User Id=%s;Password=%s"

Settings for Trusted connection are already prepared in Mobilchange\extended\registry\database_mssql.reg file, you need only import the file into registry and set real values.
Before using it is necessary to create empty database on SQL server and some table inside - use prepared SQL scripts from MobilChange\extended\sql\mssql. "Archiv.sql" creates SMS archive, "sql_user.sql" creates table for user's settings, "defaults.sql" creates default users.
At the end restart Mobilchange.Kernel service.

How to switch Mobilchange to use Oracle

You MUST have Oracle client installed on Mobilchange server to be able to connect Oracle database server.
Set provider (REG_SZ) value to "C:\MobilChange\bin\mxdbado.dll" (valid if Mobilchange is installed in C:\MobilChange - set according to reality). Set mode (REG_SZ) value to "1".

In ODBC_conn_string value (REG_SZ) set ODBC connection string for database connection in format
"Driver={Microsoft ODBC for Oracle};Server=%s;UID=%s;PWD=%s;".
You must fill also service (replaces Server), user (replaces Uid) and pass (replaces PWD) values.
ATTENTION - pass value is encoded, so first start MobilChange\bin\EncPass.exe application and enter REAL password here. In "Scrambled" textbox encoded password is displayed - THIS value must be entered into the registry (use clipboard to avoid overstrike).

The same rules are valid for filling conn_string value in format
"Provider=OraOLEDB.Oracle;Data Source=%s;User Id=%s;Password=%s"

These settings are already prepared in Mobilchange\extended\registry\database_oracle.reg file, you need only import the file into registry and set real values.
Before using it is necessary to create empty database on Oracle server and some table inside - use prepared SQL scripts from MobilChange\extended\sql\oracle. Run them using sqlplus in this way:

first "mx_db_admin.sql", you must be connected to Oracle as SYS user with SYSDBA role:
sqlplus "sys/your_password@database as sysdba" @mx_db_admin.sql (quotations must be used!)
This script creates tablespace and user MX with password sms - you can change it according your needs
then run scripts in sequence "seqs.sql", "archiv.sql" (creates SMS archive), "sql_user.sql" (creates table with users), "defaults.sql" (creates default users), while you are connected to Oracle as user MX:
sqlplus "MX/sms@database" @script_name (quotations must be used!)

At the end restart Mobilchange.Kernel service.

If you get message about error during opening the database at MxUserManager start, select do not close program and confirm all error messages. Then select Setup - Database location in the menu. Leave "Mobilchange server settings used" in "Database location" field and press ODBC Setup button. Here set Database type = Oracle and Selecting and reading data = Read data in two steps. Exit all dialogs and MxUserManager will be functional.
If you encounter state when MxUserManager can not be started, delete HKCU\Software\Datasys\Mobilchange\MxUserManager registry key and use procedure described above.

Viewing SMS archive

For this purpose there are WWW pages prepared in MobilChange\extended\www\oracle\mxarchview folder for Oracle or in MobilChange\extended\www\mssql\mxarchview folder for MS SQL. You can make them accessible through IIS with ASP support enabled. For correct function you must rewrite GetConnString() function in "conn.inc" file - it must return correct connection string to Mobilchange database.

NOTE - if you are using Oracle, web application can write message like this: "Provider cannot be found. It may not be properly installed". In such case use this action:
Go to your ORACLE_HOME folder (e.g. D:\Oracle\Ora92) in Windows Explorer, and pull up its properties. Then go to Security tab and select the Authenticated Users group. The three permissions that should be checked are Read & Execute, List Folder Contents, and Read. Uncheck Read & Execute then RE-Check it again to reset permissions for everything underneath. Just to be safe, go into the Advanced settings and make sure that Authenticated Users has "This folder, subfolders and files" under the Apply To column. If not, double click on it and change the drop down to say that. Then press OK. Now restart "World Wide Web Publishing Service" (or reboot computer, if it doesn't help) and application should work.

It is suitable to enable access to the application only for appropriate users (admins, management) by setting IIS authentication.

Database of SMS messages can be viewed using the following filters:

Received / sent SMS (In, Out)
Messages in givem time interval (Od - Do)
Sent by (Email)
sent to number / received from number (Telefon)
Priority
Line which was message received / sent
Messages of given group
Messages of given cost center
Searching according to SMTP message ID
Searching inside text

Page only displays data, which can not be edited. When you click to date and time of the message, detail page shows. It contains all available data about message. Result of searching can be exported into CSV file.

Archive view:

Message details:

Viewing accounting data

Another application is used for viewing information how much money was spent by users in specified time interval. Also this application is web based and can be found in MobilChange\extended\www\oracle\mxuserview for Oracle or in MobilChange\extended\www\mssql\mxuserview for MS SQL. You can make it accessible through IIS with ASP support enabled. For correct function you must rewrite GetConnString() function in "conn.inc" file - it must return correct connection string to Mobilchange database. If you are using Oracle and application says "Provider cannot be found. It may not be properly installed", use action described above.

Application allows database viewing and showing SMS cost for individual users and groups and number of SMS sent to individual phone numbers. Data can be showed using these views:

sender's e-mail address
group
recipient

Every view can be filtered by time interval, user ID, cost center ID, number of SMS, total cost.

It is suitable to set access rights at IIS level to enable application using only to correct users (admins, management). Data in database can not be edited - application only displays them. It is possible to save displayed data into CSV file for another processing by pressing Export button.

MxUserView users:

MxUserView groups:

MxUserView recipients: