Database: Exporting data from VIGO6 using ODBC

Working with Database in VIGO6

Exporting data from VIGO6 using ODBC

CONTENTS:

Introduction

This tutorial explains how to export data from the VIGO6 environment to a database management system (DBMS) or an Excel spreadsheet using the ODBC database driver on Windows.

The assumption is that you have a basic knowledge of VIGO6, otherwise see https://www.vigo6.com/ .

The architecture of the system can be seen in Image 1:

Image 1: Architecture of a sample VIGO6 project using the Database_register.

The system runs on a Windows PC/server with ODBC database drivers. They are dynamic link libraries, which create an interface to exchange data between applications and data sources using SQL as a standard. There are both 64-bit and 32-bit drivers, but VIGO6 uses the 32-bit drivers.

There are different ODBC drivers. We have tested the “Microsoft Excel Driver (*.xls)” and the “MySQL ODBC 3.51 Driver”. When you are using the MySQL ODBC driver you can use MySQL or another MySQL alternative as MariaDB or PostgreSQL. We have tested MariaDB and MySQL.

NB! In our experience, one should only use characters between a and z and underscore for naming tables and table elements as some ODBC-drivers cannot handle special characters.

You need a COPP PC module installed on the PC.

The tutorial does not cover how database servers or databases work. A MariaDB database server is used in the example with the MySQL database driver. The database is called “vigo_db_test”, and it has a table called “temperature_regulation”, as seen on Image 2:

Image 2: The database used in the tutorial.

The structure of the table can be found on Image 3:

Image 3: Structure of the table “temperature_regulation” in the database “vigo_db_test”

General

1. In your VIGO6 editor, create a record with the data you want to export (Image 4).

IMPORTANT UNFO on naming:

Avoid naming any of you record fields any reserved keywords, such as : Count, time, max, min, reverse, replace, and, or, insert.

Stick to characters a-z and numbers; underscores are also ok.

 

Image 4: A record in VIGO6 with sample fields.

 

2. Add the Database_register to your component and assign the data field to it.

The output generated by VIGO6 for your Database_register can be configured through different parameters found in the subcomponent, as seen on Image 5:

Image 5: An overview of the configuration parameters for the Database_register.

 

3. Fields in the record need to have correct ranges. If you do not want to specify ranges, you must set “IgnoreValueRange” to true (Image 6).

Image 6: Configuring Value ranges.

 

4. When working with a nested record structure, the “Fieldname_seperator” becomes important. The fieldname_seperator can be used to specify which symbol VIGO6 uses to express the nested structure in a flat string. It is set to “_” in this example (Image 7).

Image 7: Setting fieldname seperator.

To illustrate how it works, a record structure called “PD_820_calib_rec” that uses nested records (Image 8).

Image 8: PD_820_calib_rec, an example of a nested record

 

With “_” as fieldname seperator, the beginning of the nested representation would be as follows:

Device_Device_type,Device_Serial_number,Device_Firmware_info_Buildtime, Device_Firmware_info_GUID, …….”

The output is a flat string, that can be inserted into a flat database.

5. Make sure that you handle time as UTC (Image 9):

Image 9: “Handle_time_as_UTC” set to true.

 

6. Consider how you want to store floating point values. When you set “Store_float_scientific” to true, floats are represented using scientific notation (Image 10). :

Image 10: “Store_float_scientific” set to true.

 

7. You configure your ODBC-driver through a simple text file. The default path is in the folder where your COPP PC is located, and the default name is “Database_config.txt”. The config-file contains a ConnectString with instructions for the ODBC-driver.

If you want to use a path that differs from the default path, you need to specify it in “Configfile_path_and_name”.

You can get an overview of available drivers in the ODBC Data Source Administrator (Image 11).

Image 11: OBDC Data source Administrator window with some of the available drivers.

 

The path is:
Windows button > ODBC Data sources (32-bit) > Drivers

The content of the ConnectString is dependent on your application. Specific examples of ConnectStrings will be shown when using the MySQL and Excel drivers.

For more information about ConnectString see:
https://www.connectionstrings.com/

8. In your component where your register is located, select your db_register, and mark “3rd party interface” (Image 12:):

Image 12: Mark “is available” for 3rd party interface

 

9. Go to your project view, and locate the component with the Database_register in your chosen COPP_PC. Right click on the device, and “Add 3rd party interface to device”. Choose the assembly/component where the db_register is located (Image 13):

Image 13: Adding 3rd party interface to device where Database_register is located.

 

10. The data is written to the database when you assign the data to the Database_register (see line 6 on Image 14). It is not enough to assign the data to the variable exposed in the database register (lines 3-5 on Image 14). Line 6 is mandatory.

Image 14: Simple example of writing to your Database_register when pressing a button. Btn_flag is set to true when the button is pressed.

Exporting data to MariaDB database using MySQL driver

You follow the same steps specified in “General”. The application specific steps is described here.

A MariaDB database server is used in the example with the MySQL database driver. The database is called “vigo_db_test”, and it has a table called “temperature_regulation”, as seen on Image 15:

Image 15: The database used in the tutorial.

 

The structure of the table can be found on Image 16:

Image 16: tructure of the table “temperature_regulation” in the database “vigo_db_test”

 

1. Creating the config file.
Create an empty text file called “Database_config.txt” in the folder where your COPP PC is installed (Image 17):

Image 17: A configfile in a folder with the COPP PC.

 

2. Create the connectString in the config file.

First you need to chose an appropriate driver. We have chosen “MySQL ODBC 5.3 ANSI Driver”.

The server address, port number, user ID and password must be specified:

Image 18: A ConnectString using a MYSQL driver for a MariaDB database server, with a database called “vigo_db_test, with UID=root and PWD=1234.

 

3. The table name from the database is inserted in “Database_tablename”:

Image 19: Specifying the name of the table in “Database_tablename”.

Exporting data to Excel using Microsoft Excel Driver

You follow the same steps specified in “General”. The application specific steps is described here.

1. Create an Excel workbook:

You use the 32-bit OBDC driver for Microsoft Excel that works with Excel 97-2003, that needs to be specified when saving:

Image 20: An excel workbook containing the spreadsheet where data from Vigo6 are exported to.

2. Make sure that the columns are represented in your excel work book:

Image 21: The data structure is shown above, and the corresponding excel spredsheet with the columns and value from the first write operation is shown below.

3. Write the name of the sheet of your excel work book in Vigo6 database register > Database_tablename

If the name of your sheet is sheet1:

Image 22: Example of where to find the sheet name in an open workbook containing 1 spreadsheet called “Sheet 1”.

Write the following [Sheet1$] (Image 23):

Image 23: Specification of where to insert the name of sheet 1 in your Database_register.

4. Creating the config file.
Create an empty text file called “Database_config.txt” in the folder where your COPP PC is installed (Image 24):

Image 24: A configfile in a folder with the COPP PC.

In the connectString, choose the 32-bit OBDC driver for Microsoft Excel, and write the exact path for your excel file:

ConnectString=Driver={Microsoft Excel Driver (*.xls)};

DBQ=C:\arbejdsomr\odbc_excel\db_workbook.xls; READONLY=0;

READONLY = 0 specifies the connection to be updateable.

Parameters for the ConnectString are seperated by ‘;’.

- Hints

The COPP_PC opens the file from the ConnectString when writing to it. While the file is open, it blocks other users from using it. The COPP_PC does not close the file after a successful write operation.

If you open the COPP_PC and write to the excel spreadsheet, the file will be locked by the COPP_PC and you cannot open it, until you close the COPP_PC.

If you open the excel spreadsheet first, and open the COPP_PC afterwards, it will attach to the opened session, and you can have the spreadsheet opened, while writing to it.

Troubleshooting

Last_error

You can use the variable “last_error” in your visualisation, to get a 56 -byte error message from the OBDC-driver.

In the database register, there is a Get/Set method that can be used in your visualisation for getting 56 bytes excerpts of any error messages; ie when there is no connection to the database or there is a syntax error in your SQL statement (Image 25):

Image 25: Top: The Last_error Get/Set method in the Database_registor. Bottom: A visualization for a component with a database register, where Last_error is visualized as a String_name_value

ODBC driver tracer

1. Open the tracer and specify a log file path:
Windows button > ODBC Data sources (32-bit) > tracing

Image 26: Interface for the tracer. We have created our own log file called “ODBC2.txt”.

2. Start the tracer
Start tracing now > apply

3. Open the COPP PC and complete the database operation

4. Click “Stop Tracing now”.

5. Check the log file, and you can find the generated SQL-statement:
"INSERT INTO [Sheet1$] (user_input,test1,test2) VALUES (25.0,26,27)"

6. In the log file you will be able to find both status and error messages, which can be useful since “Last error” from Vigo6 only contains 56 bytes.