13.1.2 Settings

Double-click [ODBC] or select [Tools] – [ODBC] to bring up the [ODBC] window.

On the left side of the window, the DSN of the database system, which is connected to UltimateAccess Web, and its queries are displayed in tree form. On the right side of the window, the queries of the selected DSN are listed.

(1) New DBMS

You can configure the DSN of the new DBMS, which will be connected to UltimateAccess Web system. You can bring up the [DBMS Configuration] system by selecting [Edit] – [New DBMS] or icon.

Item	Description
DB Name	Enter the ODBC DSN. The name must be identical to the name of the DSN registered at “Control Panel\System and Security\Administrative Tools\ODBC Data Sources”.
User ID	Enter the User ID to connect the database assigned to the ‘DB Name’. The User ID must have authority to process the queries. If you do not need the User ID, you may leave the field blank.
Password	If you assigned a User ID, enter the password for the ID. If you have not assigned a User ID or it is not necessary to use password, leave the field blank.
Description	Enter the description of the currently configuring database.

(2) New Query

After the registration of new DBMS, press icon to bring up the [Query Configuration] window. You can configure the queries of the DBMS in this window.

Item	Description
Query Name	Enter the name of the Query.
Description	Enter the short description of the Query.
Query Type	Select the Query type between ‘SELECT Statement’ and ‘Others’.
	SELECT Statement		This Query type is often used to read multiple records.
	Others		This Query type is often used to read single record or update, register, deletion of the record.
Create SQL	Brings up the configuration window of the selected Query type.
Query Operation	Regular Interval	Automatically executes the Query at regular interval. The interval can be assigned from 0 to 100 seconds. If you select ‘Running Base’ option, the query is executed at the moment of the UltimateAccess Web execution.
	On Time	Executes the Query at the specified time.
	Event	Executes the Query on the assigned condition. You may use conditional expression to execute the Query.
	Execute By Command	Executes the Query with the function used in the Script or the command expression.

(3) Query Type

[SELECT Statement]

You can configure the SELECT Statement when you have selected the ‘SELECT Statement’ in the [Query Configuration] window. You may configure the SELECT Statement in 4 sections.

When you link a tag, the data type in DBMS and UltimateAccess Web must be identical. Also, be aware of the data capacity when you use string field.
E.g.) If data type in DBMS is ‘SMALLINT’, ‘SMALLINT’ must be selected in ODBC.

Item	Description
No Duplicate Data	Select the checkbox to avoid the duplicated data in the Query results.
New Field	Defines the record field created by the execution of Query. You may bind the record field and the UltimateAccess Web tag.
	Field Name	Enter the field name which is defined in the database table.
	Tag Name	Assign the tag to be bound to the field. The value of the tag may be written to the database or the value from the database can be written to the tag. Generally, the virtual tags are assigned.
	Browse	Displays the list of tags in the UltimateAccess Web. You may select a tag to assign the value to the ‘Tag Name’ field.
	Data Type	Selects the data type of the assigned field. The selected data type must be identical to the data type assigned in the DBMS.
		DBMS	CIMON-SCADA
		Text	CHAR, VARCHAR, LONGVARCHAR
		Number	DECIMAL, SMALLINT, REAL, INTEGER, FLOAT, DOUBLE, NUMERIC, BIT, TINYINT, BIGINT
		Time	DATE, TIME, TIMESTAMP
	Description	Enter the short description of the field.
Edit	You may select the configured field on the list and edit the field.
Delete	Deletes the selected field.
▲ / ▼	Moves the selected field upward or downward.
FROM	Assign the table name where the assigned fields belong to. The table name must be identical to the name used in the actual database.
WHERE	Assign the SQL statement to find the desired data. You may use standard SQL.
ORDER BY	Assign the criterion of the record alignment.

[Others]

You can configure the other statement when you have selected the ‘Others’ in the [Query Configuration] window.

When you link a tag, the data type in DBMS and UltimateAccess Web must be identical. Also, be aware of the data capacity when you use string field.
When you write an SQL statement, you must use quotation marks to the tag value which corresponds to the string field in the DBMS.
E.g.) When there are numeric tags [1], [2] and string tag [3] in the record named ‘DataTable’, write the statement as shown below.
INSERT INTO DataTable
VALUES ([1], [2], ‘[3]’)

Item	Description
Binding	Assign the tag which will correspond to the SQL statements. When you edit the SQL statement, assign the value area as “[n]”(n: Decimal Integer). This area will be replaced with the corresponding tag value.
New Tag	Assign a tag to be bound to the SQL statement.
	Tag Name	Enter the tag name. The assigned tag value will replace the [n] area of the SQL statement.
	Browse	Displays the list of tags in the CIMON-SCADA. You may select a tag to assign the value to the ‘Tag Name’ field.
	Data Type	Selects the data type of the assigned field. The selected data type must be identical to the data type assigned in the DBMS.
		DBMS	CIMON-SCADA
		Text	CHAR, VARCHAR, LONGVARCHAR
		Number	DECIMAL, SMALLINT, REAL, INTEGER, FLOAT, DOUBLE, NUMERIC, BIT, TINYINT, BIGINT
		Time	DATE, TIME, TIMESTAMP
	Description	Enter the short description of the field.
Edit	You may select the configured field on the list and edit the field.
Delete	Deletes the selected field.
▲ / ▼	Moves the selected field upward or downward.
SQL	Write a standard SQL. You may assign the value area as [n]. [n] will be replaced with the tag value of corresponding number.

(4) Edit

Press Edit icon to edit the selected DBMS or queries in the corresponding configuration window.

(5) Delete

Press delete icon to delete the selected DBMS or queries.