Database Interface

Configuring a database interface with IWS is basically linking tasks from IWS (Alarms, Events or Trends) to tables of external databases via a specific Database Provider that supports the database you have chosen.

Each history task (Alarm, Events or Trend) can be configured to save data either to files with the proprietary format from IWS or to external SQL Relational Databases. Use the Options tab to configure the database to save Alarm and Event history. (See the Trend Folder for instructions for saving history for the trend tasks.)

IWS supports ADO.NET to provide an intuitive, simple, flexible and powerful interface with standard technologies from MDAC (Microsoft Data Access Components) such as OLE-DB (Object Linking Embedded — Database) and ODBC (Open Database Connectivity). By using this capability, you can connect to any database that is MDAC compatible (please see the Conformance Table for the list of databases already tested)

The following tasks support the database interface:

Alarms: The project can save and/or retrieve the alarm history messages in a relational database.
Events: The project can save and/or retrieve the event messages in a relational database.
Trends: The project can save and/or retrieve the Trend history values in a relational database.
Viewer: Database information can be displayed both in table format (Alarm/Event Control and Grid objects) or in a graphical format (Trend Control object).
Web: Because the items listed below are already available in IWS Web interface, you can deploy a project that stores/saves data in a relational database and have it working over the Web.

Using its embedded database interface, IWS can easily provide data from the plant floor to third-party systems (e.g., ERP) or get data from them.

IWS can interface with any relational database supported by a valid ADO.NET Provider, OLE DB provider or ODBC driver. However, the conformance tests were executed with the following databases:

Table 1. Conformance Test Table
Database	Database Version	ADO.NET Provider	Assembly Version
Microsoft SQL Server 2000	8.0	System.Data.SqlClient	1.0.5000.0
Microsoft Access 2000	9.0.3821 SR-1	System.Data.OleDbClient	1.0.5000.0
Microsoft Excel 2000	9.0.3821 SR-1	System.Data.OleDbClient	1.0.5000.0
Oracle	10g Release 1 for Windows	System.Data.OracleClient	1.0.5000.0
Sybase	Anywhere 9.0.1.1751	iAnywhere.Data.AsaClient	9.0.1.1751
MySQL	4.0.20a	ByteFX.MySqlClient	0.7.6.15073

Note: For information about how to configure a specific database, please refer to the following:

SQL Relational Databases

A SQL Relational Database is a set of information stored in tables with fields and registers, which support SQL commands.

Each database can have one or more tables. Each table is composed of fields (columns) and registers (rows). Typically, the fields are pre-defined and the project adds or reads one or more registers, according to the query condition.

IWS uses Database Providers (ADO.NET) to interface with SQL Relational Databases. Database Providers are libraries developed to access data from different databases through SQL commands. The ADO.NET Provider for a specific database can be supplied by the operating system or by the database manufacturer.

The following picture illustrates how IWS can interface with different databases using a different Database Provider for each database.

Figure 1.

The previous picture shows some of the most popular ADO.NET Providers for databases. Notice that the Microsoft ADO.NET Provider for ODBC Drivers allows you to access the database through an ODBC driver. See Database Appendix A: Using ODBC Databases for information about how to use this provider. It is also possible that you do not have an ADO.NET provider, but an OLE DB provider is available. By using the Microsoft ADO.NET Provider for OLE DB you can get access to the database; the Microsoft Jet OLE DB provider gives access to applications in the Microsoft Office package by using this approach.

Note: It is important to note that IWS provides the interface for ADO.NET Providers. However, the ADO.NET Providers and/or the ODBC Driver/OLE DB Provider must be supplied either by the operating system or by the database manufacturer. If your Connection String does not refer to a valid ADO.NET Provider, the OLE.DB Provider will be used.

Although most projects typically link to only one type of database, IWS gives you the flexibility to link each task to a specific database supported by a Database Provider. Furthermore, by using this architecture, you do not need to worry about the specific characteristics of each database (it is mostly handled by the Database Provider for each database or by the IWS Database Gateway interface). Therefore, the project settings are mostly uniform, regardless of the specific database chosen by you.

Linking the Database Through a Remote DB Provider

Depending on the architecture of your project, the ADO.NET Provider for the SQL Relational Database may not be available in the same stations where IWS is running. This scenario is especially common when the project is run on a Windows Embedded target system (currently, most of the Providers are not supported for Windows Embedded). In order to solve this problem, we have designed a flexible solution that allows you to configure distributed systems, as illustrated in the picture below:

The project is running on the Server station (where InduSoft Web Studio or CEView is installed). The project can communicate with the IWS Database Gateway (running in a remote computer) via TCP/IP. The Gateway implements the interface with the Database through the Database Provider available in the computer where it is running.

The Studio Database Gateway does not require complex configuration. Just copy the files STADOSvr.exe and StADOSrv.ini from the \BIN sub-folder of IWS and paste them under any directory of the computer that will be used as the Gateway station and execute the STADOSvr.exe program. There are advanced settings associated with the IWS Database Gateway, but they should be changed only under special circumstances. See Studio Database Gateway for information on how to configure the IWS Database Gateway advanced settings.

Note: The Studio Database Gateway is a TCP/IP Server for the IWS project and it uses the TCP Port 3997 by default. You can specify a different port number when executing the STADOSvr.exe program according to the following syntax:

STADOSvr.exe Port Number

Example: STADOSvr 3998

Studio Database Gateway

The Studio Database Gateway is a TCP/IP server that interacts with databases using the Microsoft .Net Framework. It can run on the same computer that is running the IWS project, or on a different computer. The Database Gateway Host in the Advanced Settings (see Database Configuration dialog) specifies whether the gateway will be running on the local computer or not. If you are using the local computer you should enter either localhost or 127.0.0.1 in the Host name. You do not need to worry about starting or stopping the gateway because it will be done automatically by IWS tasks. On the other hand, when running the gateway remotely, you need to start the gateway manually. To do so, copy the files StADOSvr.exe and StADOSvr.ini from the \BIN folder to the remote computer, and then execute the StADOSvr.exe.

The gateway can be started multiple times for different TCP/IP port numbers. The default port number is 3997, and it is changed by specifying the desired port number in the command prompt (e.g., StADOSvr 1111 ). When running the StADOSvr, it will add the following icon to the system tray:

Figure 2.

When you right-click on the icon, the following shortcut menu is displayed:

Figure 3.

The Hide option controls whether the Studio Database Gateway window is displayed on the desktop. (The gateway software runs continuously after you launch it, regardless of whether the window is displayed.) If you disable the Hide option, then the window is displayed:

Figure 4. Studio Database Gateway

Any failure that occurs during operations with databases will be displayed both in this window and also in the LogWin window. The messages are reported by exceptions generated by the ADO.NET Provider. (Please refer to Database Troubleshooting for more information about error messages in the gateway module.)

You can configure the output in this window by using the Log menu:

Show Log menu option: Shows the IWS Database Gateway log files.
Options menu option: Open the Configure Messages dialog.
Figure 5. Studio Database Gateway: Configure Messages dialog
- Show Messages pane: Select Errors Only to show only error messages in the log, or select All Messages to show all database messages.
- Additional Information pane: Configure to show additional information about each database message.
  - Message Type checkbox: Click (check) this option to show the type of the message.
  - Date/Time checkbox: Click (check) this option to show the timestamp of the message.

Advanced Settings

The Studio Database Gateway has Advanced Settings that are configured in the StADOvr.ini file. If you are having problems interfacing with a specific database, you will probably need to change some of these settings or add new providers to the file. The following parameters are available:

Section of .INI File	Parameter	Accepted Values	Description
Providers	SaveMSec	1 : Disable 2 : Enable 3 : Separate Column	This setting specifies the default behavior for the provider when saving milliseconds. The default can be changed on the Advanced Settings in the Database Configuration Dialogs.
	Assembly	Any string that contains a .Net Framework assembly	Assembly option for all providers. The assembly has all the classes required to interface with the database. Most of the providers are inside the System.Data assembly.
	ConnectionClass	Any connection class inside the assembly	The Connection Class is the one that implements the System.Data.IDbConnection interface.
	DateAdapterClass	Any data adapter class inside the assembly	The Data Adapter class is used on operations where updates to the database are necessary. It must be compatible with the connection class specified and it should implement IDbDataAdapter.
	CommandBuilderClass	Any command builder class inside the assembly	The Command Builder class is also responsible for updates on databases. It must be compatible with the connection class.
	Provider	Name of the provider	One of the parameters in the connection string is the "Provider". The Studio ADO Gateway compares the value on the connection string with the value for this parameter in each provider and defines the proper one to be used.
	ColumnDelimiterPrefix	Any character or group of characters	Specify a character that will be placed before column names on SQL statements
	ColumnDelimiterSuffix	Any character or group of characters	Specify a character that will be placed after column names on SQL statements
	TableDelimiterPrefix	Any character or group of characters	Specify a character that will be placed before table names on SQL statements
	TableDelimiterSuffix	Any character or group of characters	Specify a character that will be placed after table names on SQL statements
	ValueString	Any string	This value indicates how constant values are identified on SQL statements. For Microsoft SQL databases for instance, the value should be @Value, for ODBC question mark (?)
	ValueStringPrefix	Any string	This value indicates a prefix to be used before the values. Oracle values, for instance, require the prefix. The SQL statements use value identifiers by using their prefixes, but the parameters in the Connection class do not use the prefix.
	ValueAddNumber	0 or 1	Indicates whether a sequential number should be added to the ValueString to identify the parameter or not. For Microsoft SQL database, this parameter should have the value 1, because parameters are identified by using @Value1, @Value2, …, @ValueN. For ODBC, this parameter should be 0.
	BoolType	Any string representing a valid data type for the database	When trying to create columns to store boolean values, the data type specified on this parameter will be used. You need to make sure that the data type specified is able to save boolean values.
	IntegerType	Any string representing a valid data type for the database	When trying to create columns to store integer values, the data type specified on this parameter will be used. You need to make sure that the data type specified here is able to store 32 bit values.
	RealType	Any string representing a valid data type for the database	When trying to create columns to store real values, the data type specified on this parameter will be used. You need to make sure that the data type specified here is able to store 64 real values.
	StringType	Any string representing a valid data type for the database	When trying to create columns to store string values, the data type specified on this parameter will be used. You need to make sure that the data type specified is able to save the number of characters that you are willing to save on your project.
	TimeStampType	Any string representing a valid data type for the database	When trying to create columns to store TimeStamp values, the data type specified on this parameter will be used.
	EnableTop	0 or 1	When this field is set to 1, the ADO will place the TOP in the SQL statement to limit the amount of registers required.
	SingleConnection	0 or 1	When this field is set to 1, the ADO will open only one connection with the database, regardless of how many tasks or computers are requesting services from it. The synchronization between the tasks will be performed by the gateway, and they will not be able to be executed simultaneously if this option is enabled.
Communication	TimeOut	2	Time out to perform insert and update operations
	LongTimeOut	5	Time out to perform connection and query updates
	SyncTimeOut	60	Time out to perform synchronization
Connection	RegBufSize	128	Size of the internal buffer used by the database API.

The paramters are grouped into three sections — Providers, Communication, and Connection — but all of the parameters for configuring database providers are listed in the Providers section of the file. The default values are specified in the beginning of the file, using the prefix "Default" in each parameter as shown below:

[Providers]
DefaultSaveMSec=3
DefaultAssembly=System.Data
DefaultConnectionClass=System.Data.OleDb.OleDbConnection
DefaultDataAdapterClass=System.Data.OleDb.OleDbDataAdapter
DefaultCommandBuilderClass=System.Data.OleDb.OleDbCommandBuilder
DefaultValueString=@Value
DefaultValueAddNumber=1 DefaultBoolType=INTEGER
DefaultIntegerType=INTEGER DefaultRealType=REAL
DefaultStringType=VARCHAR(255) DefaultTimeStampType=DATETIME
DefaultSingleConnection=0

The next item on the file lists the amount of providers:

Count=5

The providers are identified by the "Provider" parameter followed by a number. When connecting to a database, the Provider parameter in the connection string is compared to the provider's identification, in order to determine which provider will be used. If there is no provider with the value on the connection string, all the default values are assumed. Besides its identification, each provider can have its own value per each parameter. Again, if no value is specified, the default is used. Below is an example with seven providers:

Count=7

Provider1=MICROSOFT.JET.OLEDB
SaveMSec1=3
ColumnDelimiterPrefix1=[
ColumnDelimiterSuffix1=]
SingleConnection1=1

Provider2=SQLOLEDB
ConnectionClass2=System.Data.SqlClient.SqlConnection
DataAdapterClass2=System.Data.SqlClient.SqlDataAdapter
CommandBuilderClass2=System.Data.SqlClient.SqlCommandBuilder
ColumnDelimiterPrefix2=[
ColumnDelimiterSuffix2=]
TableDelimiterPrefix2=[
TableDelimiterSuffix2=]
RealType2=FLOAT

Provider3=MSDASQL
ConnectionClass3=System.Data.Odbc.OdbcConnection
DataAdapterClass3=System.Data.Odbc.OdbcDataAdapter
CommandBuilderClass3=System.Data.Odbc.OdbcCommandBuilder
ValueString3=?
ValueAddNumber3=0
StringType3=VARCHAR(128)
EnableTop3=0

Provider4=ORAOLEDB
Assembly4=System.Data.OracleClient
ConnectionClass4=System.Data.OracleClient.OracleConnection
DataAdapterClass4=System.Data.OracleClient.OracleDataAdapter
CommandBuilderClass4=System.Data.OracleClient.OracleCommandBuilder
ValueString4=Value
ValueAddNumber4=1
ValueStringPrefix4=:
BoolType4=Number(1)
IntegerType4=Number(10)
RealType4=Number
StringType4=VARCHAR(255)
TimeStampType4=TIMESTAMP(0)
EnableTop4=0

Provider5=ASAPROV
Assembly5=iAnywhere.Data.AsaClient
ConnectionClass5=iAnywhere.Data.AsaClient.AsaConnection
DataAdapterClass5=iAnywhere.Data.AsaClient.AsaDataAdapter
CommandBuilderClass5=iAnywhere.Data.AsaClient.AsaCommandBuilder
ValueString5=?
ValueAddNumber5=0
ColumnDelimiterPrefix5=[
ColumnDelimiterSuffix5=]
TableDelimiterPrefix5=[
TableDelimiterSuffix5=]

Provider6=MYSQLPROV
Assembly6=ByteFX.MySqlClient
ConnectionClass6=ByteFX.Data.MySqlClient.MySqlConnection
DataAdapterClass6=ByteFX.Data.MySqlClient.MySqlDataAdapter
CommandBuilderClass6=ByteFX.Data.MySqlClient.MySqlCommandBuilder
ValueString6=@Value
ValueAddNumber6=1
StringType6=VARCHAR(128)
EnableTop6=0

Provider7=MSDAORA
Assembly7=System.Data.OracleClient
ConnectionClass7=System.Data.OracleClient.OracleConnection
DataAdapterClass7=System.Data.OracleClient.OracleDataAdapter
CommandBuilderClass7=System.Data.OracleClient.OracleCommandBuilder ValueString7=Value
ValueAddNumber7=1
ValueStringPrefix7=:
BoolType7=Number(1)
IntegerType7=Number(10)
RealType7=Number
StringType7=VARCHAR(255)
TimeStampType7=TIMESTAMP(0)
EnableTop7=0