Difference between revisions of "ODBC Service"
(.) |
|||
(54 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
= Introduction = | = Introduction = | ||
− | [[File:ODBC | + | [[File:ODBC pic2.jpg|RTENOTITLE]] |
Line 7: | Line 7: | ||
Open Database Connectivity (ODBC) is a standard API for access database. It design independent of database systems and operating systems. | 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 | + | ODBC service can become multi-module interface. User just store data in csv file or SQL Server, then ODBC handler can upload data to Cloud. |
+ | |||
+ | ODBC Service currently support database: | ||
+ | |||
+ | *CSV file | ||
+ | *Microsoft SQL Server | ||
+ | |||
+ | <span style="color:#FF0000;">Notice:</span> | ||
+ | |||
+ | *<span style="color:#FF0000;">The maximum number of records for each update process is 10000. Please do not append over 10000 records at a time.</span> | ||
+ | *<span style="color:#FF0000;">ODBC service only support one database at the same time. CAN NOT be configured to support multiple databases.</span> | ||
= ODBC Service = | = ODBC Service = | ||
Line 21: | Line 31: | ||
== Software Requirements == | == Software Requirements == | ||
− | * | + | *MQTT broker V1.0.4 |
− | * | + | *RMM Agent |
== Installation == | == 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 | |
− | == | + | == Edit Config file == |
− | + | In Windows | |
− | + | *Use file explorer to open ODBC Service folder.(Default: C:\Program Files (x86)\Advantech\Agent_ODBC_Handler). | |
+ | *Edit the file ODBC_Handler.ini | ||
+ | *Once config is changed, please using Windows Services Manager to restart Agent_ODBC_Handler service. | ||
− | <span style="font-size:medium;"> | + | == Configuration for support CSV file == |
+ | |||
+ | <span style="font-size:medium;">1.Config the [Text] session.</span> | ||
<span style="font-size:medium;">FilePath: Set path of the database file (CSV file) location.</span> | <span style="font-size:medium;">FilePath: Set path of the database file (CSV file) location.</span> | ||
− | <span style="font-size:medium;">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 maximum length of character refer [http://ess-wiki.advantech.com.tw/view/ODBC_Service#Release Release]. | + | <span style="font-size:medium;">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 maximum length of character refer [http://ess-wiki.advantech.com.tw/view/ODBC_Service#Release Release]. </span> |
− | <span style="font-size:medium;">Support data type: timestamp | + | <span style="color:#FF0000;"><span style="font-size:medium;">Note:</span></span> |
+ | |||
+ | *<span style="color:#FF0000;"><span style="font-size:medium;">The maximum number of fields is 255.</span></span> | ||
+ | *<span style="color:#FF0000;"><span style="font-size:medium;">The maximum number of records is 10000 in single CSV file. If more than 10000 records, please recycle the CSV file, write data from start position.</span></span> | ||
+ | |||
+ | <span style="font-size:medium;">Support data type: timestamp, string, double, boolean.</span> | ||
<span style="font-size:medium;">'''Example :'''</span> | <span style="font-size:medium;">'''Example :'''</span> | ||
Line 62: | Line 81: | ||
FilePath=D:\txt | FilePath=D:\txt | ||
FieldName=TIME,No#1EC,No#1ph,No#2EC | FieldName=TIME,No#1EC,No#1ph,No#2EC | ||
− | FieldDataType=timestamp,double, | + | FieldDataType=timestamp,double,double,string |
</pre> | </pre> | ||
− | <span style="font-size:medium;"> | + | <span style="font-size:medium;">2. Copy CSV file to "FilePath" folder.</span> |
<span style="font-size:medium;">''<u>'''<span style="background-color:#FFFF00;">Attention: Support to load amount of CSV files at a time please refer </span>'''</u>''<u>'''<span style="background-color:#FFFF00;">[http://ess-wiki.advantech.com.tw/view/ODBC_Service#Release Limitation]</span>'''</u>''<u>'''<span style="background-color:#FFFF00;">.</span>'''</u>''</span> | <span style="font-size:medium;">''<u>'''<span style="background-color:#FFFF00;">Attention: Support to load amount of CSV files at a time please refer </span>'''</u>''<u>'''<span style="background-color:#FFFF00;">[http://ess-wiki.advantech.com.tw/view/ODBC_Service#Release Limitation]</span>'''</u>''<u>'''<span style="background-color:#FFFF00;">.</span>'''</u>''</span> | ||
− | <span style="font-size:medium;"> | + | <span style="font-size:medium;">3.Restart service of Agent_ODBC_handler.</span> |
− | <span style="font-size:medium;"> | + | <span style="font-size:medium;">4. Login Cloud site and turn on auto-report.</span> |
<span style="font-size:medium;">[[File:ODBC set config1.PNG|upright|ODBC set config1.PNG]]</span> | <span style="font-size:medium;">[[File:ODBC set config1.PNG|upright|ODBC set config1.PNG]]</span> | ||
− | == Recommend usage == | + | === Recommend usage === |
1. Name of CSV file, field name & data in CSV file don't include blank and symbol (e.g. + - * =.....). | 1. Name of CSV file, field name & data in CSV file don't include blank and symbol (e.g. + - * =.....). | ||
Line 96: | Line 115: | ||
</pre> | </pre> | ||
− | == Write CSV file == | + | 4. If field is for double format, even though data is integer format, please add decimal point. For example, put 0.0 , 2.0 , 3.0 in double format field.. |
+ | |||
+ | === Write CSV file === | ||
<span style="font-size:medium;">'''User need observe the following points:'''</span> | <span style="font-size:medium;">'''User need observe the following points:'''</span> | ||
Line 108: | Line 129: | ||
4.Don't prohibit reading permission when user write data to database. | 4.Don't prohibit reading permission when user write data to database. | ||
− | 5. Please | + | 5. Please follow 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. | NOTE: Time format must be consistency to include millisecond or not in CSV. | ||
Line 119: | Line 140: | ||
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. | 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. | ||
+ | |||
+ | == Configuration for support Microsoft SQL Server == | ||
+ | |||
+ | 1. Please make sure SQL Server driver is installed in your Windows System. | ||
+ | |||
+ | [[File:ODBC Data Source.jpg|RTENOTITLE]] | ||
+ | |||
+ | 2. Edit setting file (ODBC_Handler.ini) as below: | ||
+ | |||
+ | {| border="1" cellspacing="1" cellpadding="1" style="width:500px;" | ||
+ | |- | ||
+ | | | ||
+ | [Setting]<br/>ODBCDriver=SQL Server | ||
+ | |||
+ | |||
+ | |||
+ | [MSSQL]<br/>Server=172.22.12.78<br/>Port=1433<br/>DataBase=advdb<br/>Uid=advuser<br/>Pwd=edgesense<br/>FieldName=ts,name,price,qty<br/>FieldDataType=timestamp,string,double,double | ||
+ | |||
+ | |} | ||
+ | |||
+ | ODBCDriver | ||
+ | |||
+ | *use "SQL Server" , don't need to change. | ||
+ | |||
+ | Server | ||
+ | |||
+ | *Assign Microsoft SQL Server ip | ||
+ | |||
+ | Port | ||
+ | |||
+ | *Assign Microsoft SQL Server port | ||
+ | |||
+ | DataBase | ||
+ | |||
+ | *Assign database name | ||
+ | |||
+ | Uid | ||
+ | |||
+ | *Assign login account for accessing database | ||
+ | |||
+ | Pwd | ||
+ | |||
+ | *Assign login password for accessing database | ||
+ | |||
+ | FieldName | ||
+ | |||
+ | *Assign field name for ODBC Service to monitor. | ||
+ | |||
+ | FieldDataType | ||
+ | |||
+ | *Assign data type for ODBC Service to recognize the field. | ||
+ | *Support data type: timestamp, string, double, boolean. | ||
+ | |||
+ | <span style="color:#FF0000;">Notice:</span> | ||
+ | |||
+ | *<span style="color:#FF0000;">Attention: Database must have a field of timestamp and set it in the first column in setting file.</span> | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | |||
+ | Our Sample database as below picture | ||
+ | |||
+ | *database name is advdb | ||
+ | *have 1 table , table name is ProductTable | ||
+ | *have 4 fields: ts, name, price, qty | ||
+ | |||
+ | [[File:Advdb 2.jpg|RTENOTITLE]] | ||
+ | |||
+ | == Service control == | ||
+ | |||
+ | '''In Windows''' | ||
+ | |||
+ | Check if Agent_ODBC_Handler service is running: | ||
+ | |||
+ | *Use Service to check status of Agent_ODBC_Handler service | ||
+ | |||
+ | Restart Agent_ODBC_Handler service: | ||
+ | |||
+ | *Use Service to restart Agent_ODBC_Handler service | ||
+ | |||
+ | Start Agent_ODBC_Handler service: | ||
+ | |||
+ | *Use Service start Agent_ODBC_Handler service | ||
+ | |||
+ | Stop Agent_ODBC_Handler service: | ||
+ | |||
+ | *Use Service stop Agent_ODBC_Handler service | ||
+ | |||
+ | |||
= Json Data Format = | = Json Data Format = | ||
Line 185: | Line 296: | ||
= Use Case = | = Use Case = | ||
− | + | [[Media:MIRAI_Plant_Factory_201806.docx|ODBC_UseCase_MIRAI_2018-06]] | |
= Release = | = Release = | ||
Line 191: | Line 302: | ||
{| border="1" cellspacing="1" cellpadding="1" style="width: 1000px;" | {| border="1" cellspacing="1" cellpadding="1" style="width: 1000px;" | ||
|- | |- | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" | Index |
− | | style="text-align: center; width: 55px;" | | + | | style="text-align: center; width: 55px;" | Version<br/> |
− | | style="text-align: center; width: 80px;" | | + | | style="text-align: center; width: 80px;" | Date<br/> |
− | | style="text-align: center;" | | + | | style="text-align: center;" | Platform<br/> |
− | | style="text-align: center;" | | + | | style="text-align: center;" | OS<br/> |
− | | style="text-align: center;" | | + | | style="text-align: center;" | Release Note<br/> |
| style="text-align: center; width: 264px;" | Limitation | | style="text-align: center; width: 264px;" | Limitation | ||
| style="text-align: center; width: 156px;" | Download | | style="text-align: center; width: 156px;" | Download | ||
|- | |- | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" | 1 |
− | | style="text-align: center; width: 55px;" | | + | | style="text-align: center; width: 55px;" | 1.1.1 |
− | | style="text-align: center; width: 80px;" | | + | | style="text-align: center; width: 80px;" | 2018/06/07 |
− | | style="text-align: center;" | | + | | style="text-align: center;" | x86_64 CPU<br/> |
− | | style="text-align: center;" | | + | | style="text-align: center;" | Windows<br/> |
− | | | + | | [http://ess-wiki.advantech.com.tw/wiki/images/4/40/ODBC_Handler_Release_note.pdf ODBC_Handler_Release_note.pdf]<br/> |
| style="width: 264px;" | | | style="width: 264px;" | | ||
1. "FieldName" & "FieldDataType" item maximum length of character is 1024. | 1. "FieldName" & "FieldDataType" item maximum length of character is 1024. | ||
Line 213: | Line 324: | ||
3. Load 64 CSV files at a time. | 3. Load 64 CSV files at a time. | ||
− | | style="width: 156px;" | | + | | style="width: 156px;" | |
+ | [file://eossfs/ESS-Release/EdgeSense/Software/ODBCHandler/ Agent_ODBC_Handler_1.1.1] | ||
+ | |||
|- | |- | ||
− | | style="text-align: center;" | | + | | style="text-align: center;" | 2 |
− | | style="text-align: center; width: 55px;" | | + | | style="text-align: center; width: 55px;" | 1.1.2 |
− | | style="text-align: center; width: 80px;" | | + | | style="text-align: center; width: 80px;" | 2019/01/18 |
− | | style="text-align: center;" | | + | | style="text-align: center;" | x86_64 CPU |
− | | style="text-align: center;" | | + | | style="text-align: center;" | Windows |
− | | | + | | [http://ess-wiki.advantech.com.tw/wiki/images/1/18/ODBC_releasenote.pdf ODBC_releasenote.pdf]<br/> |
| style="width: 264px;" | | | style="width: 264px;" | | ||
1. "FieldName" & "FieldDataType" item maximum length of character is 4096. | 1. "FieldName" & "FieldDataType" item maximum length of character is 4096. | ||
Line 228: | Line 341: | ||
3. Load 256 CSV files at a time. | 3. Load 256 CSV files at a time. | ||
− | | style="width: 156px;" | [ | + | | style="width: 156px;" | |
+ | [file://eossfs/ESS-Release/EdgeSense/Software/ODBCHandler/ ODBC-1.1.2.exe] | ||
+ | |||
+ | |- | ||
+ | | style="text-align: center;" | 3 | ||
+ | | style="text-align: center; width: 55px;" | 1.1.3 | ||
+ | | style="text-align: center; width: 80px;" | 2019/7/12 | ||
+ | | style="text-align: center;" | x86_64 CPU<br/> | ||
+ | | style="text-align: center;" | Windows | ||
+ | | | ||
+ | *Support Microsoft SQL Server | ||
+ | *PaaS/EdgeSense 1.0.101 + WISE-Agent 1.2.8 | ||
+ | |||
+ | | style="width: 264px;" | <br/> | ||
+ | | style="width: 156px;" | | ||
+ | [file://eossfs/ESS-Release/EdgeSense/Software/ODBCHandler/ ODBC-1.1.3.exe] | ||
+ | |||
+ | |- | ||
+ | | style="text-align: center;" | 4 | ||
+ | | style="text-align: center; width: 55px;" | 1.1.4 | ||
+ | | style="text-align: center; width: 80px;" | 2020/2/21 | ||
+ | | style="text-align: center;" | x86_64 CPU<br/> | ||
+ | | style="text-align: center;" | Windows | ||
+ | | | ||
+ | *Support data type - double | ||
+ | *<span style="color:#FF0000;">Verified by DeviceOn 4.1.17.20200207</span> | ||
+ | *<span style="color:#FF0000;">Verified by WISE-Agent 1.3.5</span> | ||
+ | |||
+ | | style="width: 264px;" | <br/> | ||
+ | | style="width: 156px;" | | ||
+ | [file://eossfs/ESS-Release/EdgeSense/Software/ODBCHandler/ device-odbc-1.1.4.exe] | ||
+ | |||
|} | |} | ||
+ | |||
+ | Above release files are available at local server, please paste file's url in File Explorer to copy file. Refer to [[EdgeSense_FAQ#Access_to_EdgeSense_release_folder|Access EdgeSesense release folder]] | ||
+ | [[Category:Pages with broken file links]] |
Latest revision as of 10:02, 2 September 2020
Contents
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 or SQL Server, then ODBC handler can upload data to Cloud.
ODBC Service currently support database:
- CSV file
- Microsoft SQL Server
Notice:
- The maximum number of records for each update process is 10000. Please do not append over 10000 records at a time.
- ODBC service only support one database at the same time. CAN NOT be configured to support multiple databases.
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.4
- 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
Edit Config file
In Windows
- Use file explorer to open ODBC Service folder.(Default: C:\Program Files (x86)\Advantech\Agent_ODBC_Handler).
- Edit the file ODBC_Handler.ini
- Once config is changed, please using Windows Services Manager to restart Agent_ODBC_Handler service.
Configuration for support CSV file
1.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 maximum length of character refer Release.
Note:
- The maximum number of fields is 255.
- The maximum number of records is 10000 in single CSV file. If more than 10000 records, please recycle the CSV file, write data from start position.
Support data type: timestamp, string, double, boolean.
Example :
Attention: Database must have field of timestamp and set it in the first column. Every row of data which maximum length refer Limitation.
Database (CSV file)
ODBC_Handler.ini
[Text] FilePath=D:\txt FieldName=TIME,No#1EC,No#1ph,No#2EC FieldDataType=timestamp,double,double,string
2. Copy CSV file to "FilePath" folder.
Attention: Support to load amount of CSV files at a time please refer Limitation.
3.Restart service of Agent_ODBC_handler.
4. Login Cloud 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
4. If field is for double format, even though data is integer format, please add decimal point. For example, put 0.0 , 2.0 , 3.0 in double format field..
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 follow 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.
Configuration for support Microsoft SQL Server
1. Please make sure SQL Server driver is installed in your Windows System.
2. Edit setting file (ODBC_Handler.ini) as below:
[Setting]
[MSSQL] |
ODBCDriver
- use "SQL Server" , don't need to change.
Server
- Assign Microsoft SQL Server ip
Port
- Assign Microsoft SQL Server port
DataBase
- Assign database name
Uid
- Assign login account for accessing database
Pwd
- Assign login password for accessing database
FieldName
- Assign field name for ODBC Service to monitor.
FieldDataType
- Assign data type for ODBC Service to recognize the field.
- Support data type: timestamp, string, double, boolean.
Notice:
- Attention: Database must have a field of timestamp and set it in the first column in setting file.
Our Sample database as below picture
- database name is advdb
- have 1 table , table name is ProductTable
- have 4 fields: ts, name, price, qty
Service control
In Windows
Check if Agent_ODBC_Handler service is running:
- Use Service to check status of Agent_ODBC_Handler service
Restart Agent_ODBC_Handler service:
- Use Service to restart Agent_ODBC_Handler service
Start Agent_ODBC_Handler service:
- Use Service start Agent_ODBC_Handler service
Stop Agent_ODBC_Handler service:
- Use Service stop Agent_ODBC_Handler service
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
Release
Index | Version |
Date |
Platform |
OS |
Release Note |
Limitation | Download |
1 | 1.1.1 | 2018/06/07 | x86_64 CPU |
Windows |
ODBC_Handler_Release_note.pdf |
1. "FieldName" & "FieldDataType" item maximum length of character is 1024. 2. In CSV file, Every row of data which maximum length of character is 1024. 3. Load 64 CSV files at a time. |
|
2 | 1.1.2 | 2019/01/18 | x86_64 CPU | Windows | ODBC_releasenote.pdf |
1. "FieldName" & "FieldDataType" item maximum length of character is 4096. 2. In CSV file, Every row of data which maximum length of character is 4096. 3. Load 256 CSV files at a time. |
|
3 | 1.1.3 | 2019/7/12 | x86_64 CPU |
Windows |
|
|
|
4 | 1.1.4 | 2020/2/21 | x86_64 CPU |
Windows |
|
|
Above release files are available at local server, please paste file's url in File Explorer to copy file. Refer to Access EdgeSesense release folder