Chapter 8 Access Database
Content
Define ODBC data source Create Table Template Create Bind List Operate Database
Summary
In this chapter we would tell how to record and query the data to database. We use Access database to record data, time, description of valve, condition of valve. We use SQL in Kingscada3.0 to achieve it.
The KingSCADA SQL access function is used for transferring data between KingSCADA and other ODBC databases. KingSCADA contains the SQL access manager and SQL functions
KingSCADA SQL access manager creates a connection between database columns and KingSCADA tags. It contains a table template and bind list. Create tables in the database by using table templates. Build a connection between database fields and KingSCADA’s variables by using the bind list. Data in database can also be manipulated through SQL functions.
Section 1 Define ODBC data source and database.
Step 1: Create a new database (filename: mydb.mdb).Step 2: In the control Panel of windows, double click the data source (ODBC) option. The ODBC Data source administrator will appears, shown in figure 8-1.
Figure 8-2 Create table template
The data type of the four fields is all string.
Step 2: Click the OK button to finish creating the table template.
Creating table template is to definite format of database, we could create table in Access database automatically by using SQLCreatTable() function.
If we just need to create table once in database, we could create a table in database. Don’t need to create the table template.
Create bind list
Before creating bind list, we need create two string variable: “阀门描述” description of valve, “阀门状态” condition of valve.
To create a list follow the steps described below.
Step 1: Select the SQL access manager in tree directory of the KingSCADA project development environment and select bind list, then item and click the new button in right hand side edit area to open the band list window.
Figure 8-3 Create bind list
The relation between fields and tags is as following. Access database table
fields
tags
data \\local\$Date Time \\local\$Time Description of valve \\local\阀门描述
Condition of valve \\local\阀门状态 PS: record \\local\$Date into data field in Access database, etc. Step 2: Click OK to finish creating the bind list.
Section 3 Operate database
1. Connect database
Step 1: Create a new memory integer variable in tag dictionary of KingSCADA. Variable Name: DeviceID
Variable Type: Memory integer
Step 2: Create a picture named database operation picture and add a button with the text property set as database connection. Input a section of script in the button’s LeftMouseDown event, shown in Figure 8-4:
Figure 8-4 Connect database scripts
The above script function is used to build a connection between KingSCADA and the kingview database (connection to the database.Mdb).
In practical projects, the above script is written in the project development environment by selecting script, system script, application script and finally clicking on OnApplicationStartup. The system will automatically connect to the database when system starts.
2. Disconnect database
Draw a button in picture, configure properties as below: Button text: disconnect database.
Mouse trigger “leftup” animation link as below:
Figure 8-5 Disconnect database scripts
3. Create database table
Add a button with the text property of create database table in the database operation picture. Input a section of script in the button’s LeftMouseDown event, shown in Figure 8-6:
Figure 8-6 Create database table script
4. Insert record
A button can be created with the text property to insert a record in the database operation picture. Input a section of script in the button’s LeftMouseDown event as shown in Figure 8-7:
Figure 8-7 Insert record script
When \\local\activator.valve_gate changes, a record would be inserted in table.
5. query records
Records can be queried in the database through SQLFirst, SQLNext, SQLPrev and SQLLast functions. These functions are described below.
Step 1: To show the database data in a KingSCADA picture, a bind list has to be created. Its field name is consistent with table and field names in the database. Variable types are connected with field types in database. Three memory variables in tag dictionary can be defined in the project development environment.
1. Variable Name：记录日期 Variable Type: Memory string Initial Value: Null
2. Variable Name：记录时间 Variable Type: Memory string Initial Value: Null
Step 3: Create a bind list in the project development environment. The bind list properties window is shown below:
Figure 8-8 bind list properties
Step 4: Add a button with the text property set as get selecting sets in the picture. Input the section of script in the LeftMouseDown event, shown in Figure 8-9:
Figure 8-9 query records scripts
The above script function returns the first record of database table named KingTable according to the Bind2 format
Step 5: Add four buttons to the picture. Set the properties shown as below: 1. Button text：the First record
Script: SQLFirst( DeviceID ); 2. Button text：Next record
Script: SQLNext( DeviceID ); 3. Button text：Previous record Script: SQLPrev ( DeviceID ); 4. Button text：Last record
Script: SQLLast ( DeviceID );
The first, next, previous and last records cab be queried from database by using the above scripts.