Revision as of 10:43, 18 January 2019

Introduction

Open Database Connectivity (ODBC) is a standard API for access database. It design independent of database systems and operating systems.

ODBC service can become multi-module interface. User just store data in csv file and ODBC handler can upload data to Cloud.

ODBC Service

ODBC service can easily upload data to the cloud. It will be load the config file and the last data of system record when the service does initialization. The last recorded data Compare with data of database (CSV file) to decide whether to retain index value or not. Every time new data is detected from database, the service will upload new data to cloud and record the last data that is prevent repeat to upload when the service is restarted.

How to

Software Requirements

• MQTT broker  V1.0.3
• RMM Agent

Installation

1.Double click Agent_ODBC_Handler_X.X.X.exe to install.

2.Click "Next" to continue the setup wizard.

3.Select installation folder then click "Next"

4.Click "Next" to begin the installation

5. complete the installation, click "Finish" to exit the setup wizard

Configuration​

1.Use file explorer to open ODBC Service folder.(Default: C:\Program Files (x86)\Advantech\Agent_ODBC_Handler).

2.Edit the file ODBC_Handler.ini

3.Config the [Text] session.

FilePath: Set path of the database file (CSV file) location​.

FieldName & FieldDataType: Define the data type of field name. FieldName is according to database (CSV file) to set. User arrangement according to "FieldName" item to give data type in "FieldDataType" item. Both of the maximun length of character refer Release. The maximum number of fields is 255.

Support data type: timestamp, double, string, float, boolean.

Example :

Attention: Database must have field of timestamp and set it in the first column. Every row of data which maximum length refer Release.

Database (CSV file)

ODBC_Handler.ini

[Text]
FilePath=D:\txt
FieldName=TIME,No#1EC,No#1ph,No#2EC
FieldDataType=timestamp,double,float,string

4. Copy CSV file to "FilePath" folder.

Attention: Support to load amount of CSV files at a time please refer Release.

5.Restart service of Agent_ODBC_handler.

6. Login RMM 3.3 site and turn on auto-report.

Recommend usage

1. Name of CSV file, field name & data in CSV file don't include blank and symbol (e.g. + - * =.....). 

2. Auto report interval is close to the update data to CSV interval.

3. Please follow this data format in CSV.

    The first row puts field name and insert comma between each name, maximum number of fields is 255. ( Note! Don't include newline in field name row)

    The first column puts timastamp.

TIME,No.1EC,No.1ph,No.2EC
2017/10/9 00:18,1.52,5.93,2.26
2017/10/9 00:38,1.52,5.93,2.29
2017/10/9 00:58,1.52,5.93,2.26
2017/10/9 01:18,1.52,5.93,2.25
2017/10/9 01:38,1.52,5.93,2.3

Write CSV file

User need observe the following points:

1.Append data to database: User write data to database (CSV file) please add data after the last index.

2.Add database (CSV file) is invalid at runtime service.

3.Have need of timestamp item and put in the fisrt column about data of database.

4.Don't prohibit reading permission when user write data to database.

5. Please folllow the time format yyyy/MM/dd hh:mm:ss:fff. If you don't need millisecond yyyy/MM/dd hh:mm:ss.

NOTE: Time format must be consistency to include millisecond  or not in CSV.

Recycle:

Please remove all of the data (keep field name row) in csv file.

User should add data from the first row (after field name row) and one by one way to csv file after removing data. Don't add multiple row data at a time.

Json Data Format

{
    "ODBC_Handler": {
        "info": {
            "bn": "info",
            "e": [
                {
                    "n": "name",
                    "sv": "ODBC_Handler"
                },
                {
                    "n": "description",
                    "sv": "This service is ODBC Service"
                },
                {
                    "n": "version",
                    "sv": "1.1.0"
                }
            ]
        },
        "Setting": {
            "bn": "Setting",
            "e": [
                {
                    "n": "ODBCDriver",
                    "sv": "Microsoft Text Driver (*.txt; *.csv)"
                },
                {
                    "n": "ReadOnly",
                    "bv": false
                }
            ]
        },
        "Tables": {
            "bn": "Tables",
            "histdata": {
                "bn": "histdata",
                "e": [
                    {
                        "n": "TIME",
                        "sv": "2017-10-09 03:18:00"
                    },
                    {
                        "n": "No#1EC",
                        "v": 1
                    },
                    {
                        "n": "No#1ph",
                        "v": 5.930000
                    },
                    {
                        "n": "No#2EC",
                        "sv": "2.26"
                    }
                ]
            }
        },
        "opTS": {
            "$date": 1507490280000
        }
    }
}

Use Case

ODBC_UseCase_MIRAI_2018-06

Release