Export data to the MySQL database. Tutorial.
I have an application that requires the use of two devices. One will scan bar codes (EAN-13) using an RS232 port. We have some bar code scanner's connected, each one send when read, its identification number (four digits) and bar code information (thirteen 13 digits).
We need to take the scan and weight data and parse it and log all the data into a MySQL database. The data needs to be stored as a single record with time and date stamp.
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:
Note: This configuration process is complex and might prove to be difficult for the unskilled user.
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 MySQL Workbench. This software ships with a standard distribution and you can find it in the "MySQL" menu in the "Programs" group. In this example, we created a table called "data_log" in the test database "test". A structure of the table is shown in Fig.1 below.
USE test; CREATE TABLE `data_log` ( `REC_ID` int(11) NOT NULL AUTO_INCREMENT, `DATESTAMP` datetime DEFAULT NULL, `SCANNER_ID` varchar(25) NOT NULL, `BARCODE` varchar(255) DEFAULT NULL, `WEIGHT` DOUBLE DEFAULT NULL, PRIMARY KEY (`REC_ID`) );
Fig.1. MySQL table. SQL script.
Note that 4 columns have been added to the table, for all variables from both parsers and one ID column, which will contain an auto incremental value. 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 last column. However, as it is a reserved word, the name "DATESTAMP", has been used.
Now that our table is ready, the security within MySQL has to be addressed. Within MySQL, 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 "test" (see Fig.2).
Fig.2. Export to MySQL. The new MySQL user.
By default, the user does not have any rights. Access should be granted for this use for writing to our table (Fig.3).
Fig.3. Export to MySQL. The "uvt" user rights.
The "SQL Database Professional" plug-in uses a direct access method, but the plug-in depends on the "libMySQL.dll" file. You can download this system library from the MySQL home page or from our site and place a file from the archive to your Windows\system32 folder. With the direct access method you should configure connection parameters with your database within this plug-in only.
Ok, the database and table are both ready. Next go to the "SQL Database Professional" plug-in configuration window and open it using the common option on the left of the window (Fig.4).
Fig.4. Export to MySQL. SQL Database Prof. Enabling connection.
The disabled state of the tick-box at pos. #1 enables writing to the database and the radio button at pos. #2 specifies that the database is to be kept open. This serves to minimize the writing time, at the same time may lock out other users from writing. The radio button at pos. #3 allows you to reconnect to a database, when a connection is lost.
On the second page "Connection parameters" (Fig.5) you can specify the parameters of your database. Here select your database type "MySQL" (pos.1), specify the host and database name (pos. 2), the user name and the password (pos.3). You should test the connection. Just click the "Test connection" button (pos.4).
Fig.5. Export to MySQL. SQL Database Prof. Connection parameters.
In our example no additional connection parameters are specified.
On the third page "Handling errors" of "SQL Database Professional" plug-in (Fig.6), specify how the software should react to errors which occur while writing to a database.
Fig.6. Export to MySQL. SQL Database Prof. Handling errors.
In this example, 3rd 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 option at the pos. #2 allows you to write a data for an invalid SQL query to a temporary file and restore it later, when problems with the configuration will be fixed. The options at the pos. #3 allows you specify the restoring mode. If your SQL queue (from the next page) contains more than one SQL query and one query depends on another, then you may select first radio item in this group.
The last page "SQL queue" (Fig.7) is very important. On this page the SQL query and the fields that you require to be bound to the variables names are specified. This plug-in allows you to use your own SQL statements and insert the data as you want. For example, you may use stored procedures or insert the data to multiple tables.
Fig.7. Export to MySQL. SQL Database Prof. SQL queue.
You can create SQL query by SQL query on this page by selection the "Action - Add SQL in to queue " menu item (Fig.7, pos. #3). In this case the plug-in will create an item (Fig.7, pos. #1) and allow you to edit a SQL query (Fig.7 pos. #2). You can access the SQL editor (fig.8) by clicking a button with dots, which appears by clicking on a value field (Fig.7, pos. #5).
Fig.8. Export to MySQL. SQL Database Prof. SQL editor.
You should create a valid SQL statement in the SQL editor. You may use any syntax and commands, which are supported by database engine. You may use parameters in your statements like ":P1". The plug-in will automatically bind variables from the parser to these parameters, before executing the SQL statement.
After clicking the "OK" button the SQL editor the module will create parameter items in the SQL queue (Fig.7 pos. #3).
Each parameter item in the SQL queue has a number of properties:
Click the "OK" button to close the SQL Database Professional 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 the data to the database.
If you configured the SQL Database Professional 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.