Data Logger Suite: Logging and Monitoring
Export data to Microsoft SQL Server 2015
I have installed the ODBC plug-in and have tested the ODBC connection through the ODBC administrator and everything seems to be working. Now, I wish to parse and write a log of the data to a file in my database.
- Data Logger Suite: Logging and Monitoring Professional, Enterprise, or trial version;
- ODBC Database
It is assumed that:
You've prepared parser items for export.
For this tutorial all items were prepared in the previous part.
Also you may read other examples:
- Writing data to an XLS file without Excel
- Writing data to an XLS file without Excel
- Export data to the MySQL database
It is assumed, that you have access to the database and can manage it. First of all, create a table in a database. Select any existing user database or create a new one. The simplest way is to use the SQL Server Management Studio for Microsoft SQL Server. This software In this example, we created a table called "PBX_LOG_DATA" in the test database "Test". The corresponding SQL script is shown below.
USE [test] GO CREATE TABLE [dbo].[PBX_LOG_DATA]( [ID] [decimal](10, 0) IDENTITY(1,1) NOT NULL, [CALL_TIME] [datetime] NULL, [DURATION_S] [int] NULL, [CALLER_PHONE] [nvarchar](40) NULL, [DIALED_PHONE] [nvarchar](40) NULL, CONSTRAINT [PBX_LOG_DATA_PK] PRIMARY KEY ([ID] ASC) ) ON [PRIMARY] GO
Fig.1. SQL script
Note that 4 columns have been added to the table, for all variables from the parser. You can add more columns if you want or use tables with your structure.
Note: You should specify unique columns names. Names which are reserved words of ANSI92 SQL cannot be used.
Now that our table is ready, the security within SQL Server has to be addressed. Within SQL, each user may be configured for access to specific tables only. In our example a new user could be created, or access could be granted for the table above to an existing user account. It is also possible to connect using an administrator account, which has full access rights. However, it is suggested that the administrator account be used for testing only. We have created the user with the name of "uvt", and set up "Northwind" as the default database for it (see Fig.2 below).
Fig.2. MS SQL export. The new user.
By default, the user has been assigned the "public" role only. Access should be granted for this role for writing to our table (Fig.3). Or execute the corresponding SQL script.
USE [test] GO GRANT SELECT,INSERT ON [dbo].[PBX_LOG_DATA] TO your_user_name GO
Fig.3. MS SQL 2014 export. Graniting role rights.
The "ODBC database" plug-in uses an ODBC alias for accessing the SQL Server 2014 database. This can be configured using the "ODBC data source administrator” control panel applet. You can find this applet in the "Control panel", under "Administration" group in Windows. Once you have located this group, click the "Setup" button on the "Connection" tab (Fig.11 pos. #3 ). In the "ODBC data source administrator" you should create a user or system ODBC alias. System ODBC aliases (select the tab at the pos. #1 on Fig.4) are used with a service mode of the logger. We will add a new user alias. You can do this by clicking the "Add" button (pos. #2 on Fig.4).
Fig.4. MS SQL 2014 export. ODBC data source administrator
A configuration wizard window will appear (Fig.5). On the first page you should select an SQL server driver (pos. #1 on Fig.5) and click "Next" button. If the drivers list does not contain the SQL Server driver, then you should install Microsoft SQL Server client applications from the same CD, as Microsoft SQL Server 2014.
Fig.5. The SQL Server alias setup. Step #1.
On the next page (Fig.6), specify an ODBC alias name (MSSQL-TEST in our case), a small text description and select your SQL server from the drop-down list. If a server does not exist in the list, then check that the server has been started and it is available over a network. After filling out all the options, click the "Next" button.
Fig.6. The SQL Server alias setup. Step #2.
On the next page (Fig.7) we have selected a SQL Server authentication (pos. #1), because a new user has been added. Next enter the user name and password (pos. #2).
Fig.7. The SQL Server alias setup. Step #3.
In the following page (Fig.8) we have selected our test database - "Test" and have left the other options without any changes.
Fig.8. The SQL Server alias setup. Step #4.
On the final page (Fig.9) you should test the connection. Just click the "Test data source" button.
Fig.9. The SQL Server alias setup. The final step.
Ok, the database and table are both ready. Next go to the "ODBC database" plug-in configuration window and open it using the common option on the left of the window (Fig.10).
Fig.10. MS SQL 2014 export. ODBC database. Enabling connection.
The radio button at pos. #1 enables writing to the database and the tick-box at pos. #2 specifies that the database is to be kept open. This serves to minimize the writing time, at the same time locking out other users from writing.
On the second page "Connection" (Fig.11) you can specify the parameters of your database. Here select your ODBC alias name which you created above at pos#1. Note that if you configured an ODBC alias after opening the configuration window, please close it first, re-open it, and then retype your login and password at pos. #2.
Fig.11. MS SQL 2014 export. ODBC database. Connection options.
In our example no additional attributes are specified.
On the third page "Errors handling" of "ODBC database" plug-in (Fig.12), specify how the software should react to errors which occur while writing to a database.
Fig.12. MS SQL 2014 export. ODBC database data logger. Error handling.
In this example, the last option (pos. #1) was checked as the data is important and we require the program to operate regardless of the error. The other variants are normally used when testing the plug-in module with a new database.
The last page "Binding" (Fig.13) is very important. On this page the table name and the fields that you require to be bound to the variables names are specified.
Fig.13. MS SQL 2014 export. ODBC database data logger. Binding.
You can create item by item on this page by pressing the "Add item" button (Fig.13, pos.#2). In this case, before adding an item, the program will ask you for the table name (Fig.13, pos.#1) and then the column name. The name of a table column should be same as that specified while designing the database. Adding each item separately is a tedious and long process. It is simpler to add all items at once by clicking the "Import" button (Fig.13, pos.#3).
However, before clicking the "Import" you should configure a database connection as described above. In this case a dialog window will appear (Fig.14) and you should select the necessary table name (Fig.14, pos.#1) and click the "OK" button.
Fig.14. MS SQL 2014 export. ODBC database. Selecting the table.
Each item on the binding page has a number of properties:
- Parser item name - is a parser variable name, which has been created in the parser configuration. Select the variable name from a drop-down list, which appears while clicking on a link for this property;
- Column data type - is a database column data type. The module will try to convert a variable's data type to a column data type, using standard system functions. If your variable has a data type other than "string", specify the variable data type here;
- Default value - this value is to be used when data does not contain a value for a column or the variable is empty.
Click the "OK" button to close the ODBC database plug-in configuration window as well as the "OK" button in the options window.
Okay, all settings have been completed and we are ready to capture PBX data to the database.
If you configured the ODBC module correctly, then while accessing the database the data logger will display messages in the drop-down box, at the bottom of the main window.