ODBC database¶

BrowseName: ODBCStore
SuperType: Store (Store)
TypeView path: Tipi > DataStores > Database ODBC

Object that represents a local or remote database among those supported with ODBC (Open DataBase Connectivity) protocol.

Applications

Used to interface the project with Microsoft SQL Server 2012 (or higher) and MySQL Server databases.

References

ODBC and DSN driver configuration

In Windows environments, the ODBC drivers used by the DSN (Database Source Name) must already be installed on the system. The following ODBC drivers are supported:

MySQL ANSI
MySQL Unicode
SQL Server

If the DSN property is specified, the ODBC Database connection parameters are taken from the same DSN. Any properties specified in the ODBC Database object are added as parameters to those present in the DSN configuration.

Note

it is not possible to overwrite the parameter values already present in the system DSN.

If not present, the TCP port property is assigned a value with the default value for the DBMS (e.g.: for MySQL the default port is 3306). The Username and Password parameters are not supported inside DSN and must be specified by the relative properties of the ODBC Database object.

Configure a DSN in Windows

Tip

to configure a DSN use ODBC Data Sources in the 64-bit version.

In the ODBC Data Source Administrator window, on the DSN System tab, click Add, choose a supported driver and complete the configuration.

imageF2C93AA4 imageFB12BED6 imageB6B811C6

Supported data types

Below is the mapping between the UNIQO HMI OPC UA data types, the data types supported by Microsoft SQL Server and MySQL databases, and the OPC UA data types resulting from an SQL query in UNIQO HMI.

	MS SQL Server data type	MySQL data type
Boolean	BIT	BIT(1)	Boolean
Integer	BIGINT	BIGINT	Int64
UInteger	BIGINT	BIGINT	Int64
byte	SMALLINT	SMALLINT UNSIGNED	Int16
SByte	SMALLINT	TINYINT	Int16
Int16	SMALLINT	SMALLINT	Int16
UInt16	INT	MEDIUMINT UNSIGNED	Int32
Int32	INT	INT	Int32
UInt32	BIGINT	BIGINT UNSIGNED	Int64
Int64	BIGINT	BIGINT	Int64
UInt64	BIGINT	BIGINT UNSIGNED	Int64
Float	REAL	FLOAT	Double
Double	FLOAT	DOUBLE	Double
DateTime	DATETIME	DATETIME(3)	DateTime
String	TEXT	TEXT	String
NodeId	VARBINARY (max)	BLOB	NodeId

Note

since ODBC uses signed data types, unsigned UInt64 data are mapped and handle up to the maximum value of (2^{63} - 1), due to a bit allocated to the sign.

Note

the DateTime data type is limited to an accuracy of one thousandth of a second.

Properties¶

Name	BrowseName	DataType	Description
DSN	DSN	String	DSN (Database Source Name)
DBMS type	Type	DBMSType	Type of database server, among those supported
Username	Username	String	Name of the user for access to the database
Password	Password	Password	User password for access to the database
Database	Database	String	Name of the desired database in the database server
Server	Server	String	IP address or hostname of the database server in the network
TCP Port	Port	String	TCP port of the database server (if empty, SQL default = 1433, MySQL default = 3306)
Tables	Tables	Collection of ODBCStoreTable	Database tables (Inherited from Store)
Max column name length	MaxColumnNameLength	UInt32	Maximum length of column names (Inherited from Store)
Max columns count	MaxColumnCount	UInt32	Max columns count (Inherited from Store)
Data type compatibility map	DataTypeCompatibilityMap	NodeId	Map of the compatibility for the column data type (Inherited from Store)
Status	Status	StoreStatus	Database connectivity status (Inherited from Store)