Conversions between Database and Cicode Types

Extensibility > Using External Databases > Using Structured Query Language > Conversions between Database and Cicode Types

Conversions between Database and Cicode Types

Any type of database field can be requested in statements. SCADA converts values of the fields to MBCS 8-bit strings which may not always be possible. For example:

Single byte database strings or numbers can be converted to MBCS 8-bit strings, processed and displayed by SCADA
Multi-byte strings can be converted to MBCS. Processing and displaying can be done as far as: SCADA supports MBCS and is properly parameterized, OS has Language packs installed and is properly parameterized
Blobs (binary large objects) cannot be converted.

An error is returned when conversion to 8-bit strings is not possible.

NOTICE

LACK OF DATA CONVERSION

Ensure that all your database fields can be converted. Otherwise, when conversion is possible, some data may not be converted, but there is no indication that has occurred. For example when SCADA isn't properly parameterized.

Failure to follow these instructions can result in equipment damage.

There are a large number of custom DB types that need macros/functions to be called on the database side of the SQL connection for their proper conversion to strings.

MS SQL Server DB types and only their subset

MS SQL Server DB type	Format of the CiCode string returned by either SQLRecordsetGetField or SQLGetField*
CHAR(), NCHAR(), VARCHAR(), NTEXT, NVARCHAR(), TEXT, XML	The text is converted to MBCS 8-bit string.
BIT, BIGINT, INT, SMALLINT, TINYINT	Integer as a text string
DECIMAL(,), FLOAT, NUMERIC(,), REAL, MONEY, SMALLMONEY	Real as a text string.
UNIQUEIDENTIFIER	A unique id stored as a 16-byte binary value and usually converted to a GUID-like string
SQLVARIANT	The sqlvariant is a type which can encapsulate other types. The format of the resultant string depends on the encapsulated type and ability to convert it to a string.
DATE, DATETIME, DATETIME2(), DATETIMEOFFSET, SMALLDATETIME, TIME()	Date and time as a string. Only one format and UTC. If users wish to have date/time in a different format, have to use DB side conversion macros/functions in their SELECT queries.
BINARY(), VARBINARY(), IMAGE, GEOGRAPHY, GEOMETRY, HIERARCHYID, TIMESTAMP	No conversion possible. The returned string is empty. An error is set during the query execution by either SQLExec()/SQLNext() or SQLGetRecordset(). Moreover, Geography, Geometry and HierarchyID need additional CLR types not distributed with .NET platform. If need to use objects of those types in queries directly (without explicit conversions by macros), the CLR types need to be installed either separately or with SQL Server. Otherwise such queries return error 307. A separate setup with the CLR types can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=30440.

MS Access types and only their subset

MS Access DB type	Format of the CiCode string returned by either SQLRecordsetGetField or SQLGetField*
Text, Memo, Hyperlink, Lookup Wizard	The text is converted to MBCS 8-bit string.
Yes/No, AutoNumber	Integer as a text string
Number, Currency	Integer as a text string or (if necessary) Real as a text string.
Date/Time	Date and time as a string. Only one format and UTC. If users wish to have date/time in a different format, have to use DB side conversion macros/functions in their SELECT queries.
OLE Object	No conversion possible. The returned string is empty. An error is set during the query execution by either SQLExec()/SQLNext() or SQLGetRecordset().

* The string has to be shorter than 255 characters, otherwise an error is thrown.

The Cicode runtime engine tries to automatically convert strings to target data types when a cast operation is necessary. The following conversions are potentially allowed: STRING to INT and STRING to REAL.

Published June 2018