Welcome to the new Schneider Electric Community

It's your place to connect with experts and peers, get continuous support, and share knowledge.

  • Explore the new navigation for even easier access to your community.
  • Bookmark and use our new, easy-to-remember address (community.se.com).
  • Get ready for more content and an improved experience.

Contact SchneiderCommunity.Support@se.com if you have any questions.

Close
Invite a Co-worker
Send a co-worker an invite to the Exchange portal.Just enter their email address and we’ll connect them to register. After joining, they will belong to the same company.
Send Invite Cancel
84611members
353910posts

Linked ODBC Table - BigInt data type

EcoStruxure Geo SCADA Expert Forum

Find out how SCADA systems and networks, like EcoStruxure Geo SCADA Expert, help industrial organizations maintaining efficiency, processing data for smarter decision making with IoT, RTU and PLC devices.

Solved
vds
Ensign
Ensign
0 Likes
2
1060

Linked ODBC Table - BigInt data type

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

 


Accepted Solutions
BevanWeiss
Spock
Spock
0 Likes
1
1028

Re: Linked ODBC Table - BigInt data type

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

2 Replies 2
BevanWeiss
Spock
Spock
0 Likes
1
1029

Re: Linked ODBC Table - BigInt data type

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..
vds
Ensign
Ensign
0 Likes
0
1014

Re: Linked ODBC Table - BigInt data type

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