Linked ODBC Table - BigInt data type

Anonymous user · ‎2020-11-11

G'day experts!

Config

SCADA Version: Geo SCADA 2019

OS: MS Server 2019

SQL: 2016 SP1

I'm using "ODBC Linked Tables" to query Microsoft SQL DB via a 64-bit System DSN (Using latest Microsoft ODBC driver, V17).

When I query the Linked table into a Mimic List object using the following query "SELECT * FROM AwesomeTable", I can successfully populate the list with the SQL table contents, with the exception of the ID column. For this column (configured as an SQL Bigint data type), no value is displayed in the list.

Things I've tried unsuccessfully:

type-casting using the SQL query "CAST" - to INT/FLOAT/STRING etc
Using different DSN drivers (both 32-bit and 64-bit variants)
Modifying options in the ODBC/DSN configuration (ANSI settings, etc)

Is there a known limitation retrieving this data type from SQL via ODBC? Any suggestions to remedy this?

Thank you in advance,

V

BevanWeiss · ‎2020-11-12

Yes, ClearSCADA / GeoSCADA Expert does not support BIGINT datatypes.

It doesn't support it using the ODBC Driver, and it doesn't support it using ODBC Linked Tables.

The last way that I handled this in SQL Server was to just create a view on the SQL Server end which returned the data CAST (and wrapped around) to an INT value. This worked for the application which was totalised volumes, since wrap around was already expected, and I just had to adjust the rollover value to be the lower INT value.

YMMV

Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..

See Answer In Context

BevanWeiss · ‎2020-11-12

Yes, ClearSCADA / GeoSCADA Expert does not support BIGINT datatypes.

It doesn't support it using the ODBC Driver, and it doesn't support it using ODBC Linked Tables.

The last way that I handled this in SQL Server was to just create a view on the SQL Server end which returned the data CAST (and wrapped around) to an INT value. This worked for the application which was totalised volumes, since wrap around was already expected, and I just had to adjust the rollover value to be the lower INT value.

YMMV

Lead Control Systems Engineer for Alliance Automation (VIC).
All opinions are my own and do not represent the opinions or policies of my employer, or of my cat..

Anonymous user · ‎2020-11-12

Hi Bevan,

many thanks for your input and suggestions!

For anyone else's interest, I did also discover that using a decimal datatype (instead of BigInt), e.g. Decimal(18,0) within the SQL table column definition, the column may still be configured with the "Is Identity" feature to auto-increment e.g. an index column in my case.

The ODBC driver is able to interpret this data type as a string so can be displayed on a grid and referenced by VBScript to perform any of the SQL CRUD functions I was looking to do.

Thanks again and have a great day!

V