Export data to Microsoft SQL Server 2000 with help of "ODBC Database" plug-in
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.
- Advanced Serial Data Logger (ASDL) Professional or a trial version;
- ASCII data parser and query plug-in for Advanced Serial Data Logger;
- DDE server plug-in for the data logger(optional);
- ODBC database plug-in for the data logger.
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:
- Parsing data from barcode scanner and writing to a log file with a date/time stamp (data at fixed positions, no data packet signatures);
- Processing data from barcode scanner (data at fixed positions, data packets with the end signature);
- Parsing data with help of regular expressions (complex data format, regular expressions, data filter);
- Receiving and parsing data from aircraft engine monitor (usage example, custom data parser);
- Processing data from the lab equipment (simple data format, regular expressions);
- Receiving and parsing data from Garmin Geko301 GPS receiver (simple data format, data at fixed positions, data packets with both signatures);
- Parsing data from the filling machine (simple data format, delimited data, data packets with the end signature, data filter);
- Parsing and processing data from PBX (simple data format, data at fixed positions, data packets with the end signature, data filter);
- Logging and parsing data from an intellectual pressure measuring device (sending requests to the device, simple data format, data at fixed positions, data packets with the end signature, data filter);
- Serial data acquisition in the Checkweigher Management Information System (complex data format, regular expressions, filtering data);
- Serial port data aggregation. Combine a data of two different com-ports (simple data format, data at fixed positions, data packets with the end signature, data filter);
- Weight data logger. Parsing weight data from scales (simple data format, data at fixed positions, data packets with the end signature, data filter).
- Send data to a port from a file (data requests, send data by a schedule).
This configuration process is complex and might prove to be difficult for the unskilled user. You may wish to consult an expert. In Australia, contact Power Business Systems on 08 6250 7900.
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 Enterprise Manager of the Microsoft SQL Server. This software ships with a standard distribution and you can find it in the "Microsoft SQL Server" menu in the "Programs" group. In this example, we created a table called "PBX_LOG_DATA" in the test database "Northwind". A structure of the table is shown in Fig.1 below.
Fig.1. MS SQL 2000 export. SQL table.
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. In this example, we could use the name “Datetime” for the first column. However, as it is a reserved word, the prefix "d_", has been added to distinguish it from the reserved word.
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 2000 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).
Fig.3. MS SQL 2000 export. The "Public" role rights.
The "ODBC database" plug-in uses an ODBC alias for accessing the SQL Server 2000 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 2000. 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.1) are used with a service mode of ASDL. 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 2000 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 2000.
Fig.5. The SQL Server alias setup. Step #1.
On the next page (Fig.6), specify an ODBC alias name (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 - "Northwind" 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 2000 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 2000 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 2000 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 2000 export. ODBC database data logger. Binding.
You can create item by item on this page by pressing the "Add item" button (Fig.13, pos.#6). 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 (Fig.13, pos.#2). 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.#7).
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 2000 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.