Matlab - ODBC Driver Error: Driver unable to retrieve LENGTH for column number: 1

rcb.solar · ‎2024-10-04

Hello,

i'm using Matlab to query my Geo SCADA Database.

The same script was working in my previous environment, but since we have upgraded (Windows/Matlab/ClearSCADA), i'm no longer able to get data from the GeoSCADA Database.

Previous setup (OK): Windows Server 2008 / Matlab 2015b / Clear SCADA 2014

New setup (Not OK) : Windows Server 2019 / Matlab 2023a / Geo SCADA Expert 2019 81.8369.1 Update Dec 2022.

My script is able to connect to the DB with the database function :

DBconnexion=database(dbGS.ID,dbGS.user,dbGS.pwd);

(If i change a parameter i have an error from GeoSCADA)

The query has no issue executing with the select function (if i put something wrong in the query i have an error from GeoSCADA) :

dataAll=select(DBconnexion,sqlQueryAll);

But at the end on this select function i have the exception :

ODBC Driver Error: Driver unable to retrieve LENGTH for column number: 1

I think query is well executed, but the result can't be parsed.

It seems that i'm not the first one having this issue regarding this Matlab post : https://fr.mathworks.com/matlabcentral/answers/584702-unable-to-retrieve-data-from-a-clearscada-data...

Sadly there's no answer to this post 😞

Anyone that could help on this issue ?

thanks

AndrewScott · ‎2024-10-17

All versions of Geo SCADA support ODBC 1.0. No version of Geo SCADA supports ODBC 3.0.

Microsoft automatically map ODBC 3.0 onto ODBC 1.0, but this can't work for new features that are unique to ODBC 3.0 such as the SQL_DESC_OCTET_LENGTH column attribute.

I'm not familiar with JDBC, but there is no JDBC driver in Geo SCADA.

In addition to ODBC 1.0, Geo SCADA has an OLE-DB provider.

My only suggestions are to contact SE support to request a new feature or use to "Ideas" section of this forum to suggest a new feature. I expect adding support for ODBC 3.0 would be a significant development.

Andrew Scott, R&D Principal Technologist, AVEVA

See Answer In Context

AdamWoodlandToo · ‎2024-10-06

What query are you running? Perhaps knowing that could help us know where the problem might be, especially what column 1 refers to

I know in the past there have been some issues with very long strings in queries but given its Matlab I wouldn't have thought you would use strings much?

sbeadle · ‎2024-10-07

You could activate ODBC Tracing (in the Windows ODBC setup tool), not sure if you use 32 or 64 bit, so you may need to try both. Hopefully the logs may help.

rcb.solar · ‎2024-10-07

Even if i try a very simple query i still have the error :

- Original Query :

SELECT TOP(1) "RecordId", "RecordTime" AS "~Time", "Id", "Value", "StateDesc", "Foreground", "Background", "Blink", "FileOffset" AS "_FileOffset" FROM CDBHISTORIC WHERE ("Id" IN (41401)) AND ("~Time" BETWEEN TIMESTAMP ''',datestr(date_start_sql-days(7),'yyyy-mm-dd HH:MM:SS'), ''' AND TIMESTAMP ''', datestr(date_start_sql,'yyyy-mm-dd HH:MM:SS'), ''') ORDER BY "~Time" DESC, "_FileOffset" DESC'

- Test Queries (with same error):

SELECT TOP(1) "Id" FROM CDBHISTORIC

SELECT TOP(1) "Value" FROM CDBHISTORIC

rcb.solar · ‎2024-10-07

I have activated the ODBC Tracing, i find the following error from GeoSCADA :

DIAG [HY000] [Schneider Electric][Geo SCADA Expert database driver]Driver not capable (0)

Complete log :

LangaData 4dc8-44d8 ENTER SQLExecDirectW

HSTMT 0x0000026B0E0C75E0

WCHAR * 0x0000026B371CDD60 [ -3] "SELECT TOP(1) "Id" FROM CDBHISTORIC\ 0"

SDWORD -3

LangaData 4dc8-44d8 EXIT SQLExecDirectW with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026B0E0C75E0

WCHAR * 0x0000026B371CDD60 [ -3] "SELECT TOP(1) "Id" FROM CDBHISTORIC\ 0"

SDWORD -3

LangaData 4dc8-44d8 ENTER SQLNumResultCols

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC770

LangaData 4dc8-44d8 EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC770 (1)

LangaData 4dc8-44d8 ENTER SQLNumResultCols

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC700

LangaData 4dc8-44d8 EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC700 (1)

LangaData 4dc8-44d8 ENTER SQLColAttribute

SQLHSTMT 0x0000026B0E0C75E0

SQLSMALLINT 1

SQLSMALLINT 18 <SQL_DESC_LABEL>

SQLPOINTER 0x000000E893FFC2C0

SQLSMALLINT 1024

SQLSMALLINT * 0x0000000000000000

SQLPOINTER 0x0000000000000000 (NYI)

LangaData 4dc8-44d8 EXIT SQLColAttribute with return code 0 (SQL_SUCCESS)

SQLHSTMT 0x0000026B0E0C75E0

SQLSMALLINT 1

SQLSMALLINT 18 <SQL_DESC_LABEL>

SQLPOINTER 0x000000E893FFC2C0

SQLSMALLINT 1024

SQLSMALLINT * 0x0000000000000000

SQLPOINTER 0x0000000000000000 (NYI)

LangaData 4dc8-44d8 ENTER SQLNumResultCols

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC770

LangaData 4dc8-44d8 EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC770 (1)

LangaData 4dc8-44d8 ENTER SQLColAttribute

SQLHSTMT 0x0000026B0E0C75E0

SQLSMALLINT 1

SQLSMALLINT 2 <SQL_DESC_CONCISE_TYPE>

SQLPOINTER 0x0000000000000000

SQLSMALLINT 0

SQLSMALLINT * 0x0000000000000000

SQLPOINTER 0x000000E893FFC770

LangaData 4dc8-44d8 EXIT SQLColAttribute with return code 0 (SQL_SUCCESS)

SQLHSTMT 0x0000026B0E0C75E0

SQLSMALLINT 1

SQLSMALLINT 2 <SQL_DESC_CONCISE_TYPE>

SQLPOINTER 0x0000000000000000

SQLSMALLINT 0

SQLSMALLINT * 0x0000000000000000

SQLPOINTER 0x000000E893FFC770 (4) <SQL_INTEGER>

LangaData 4dc8-44d8 ENTER SQLNumResultCols

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC770

LangaData 4dc8-44d8 EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC770 (1)

LangaData 4dc8-44d8 ENTER SQLNumResultCols

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC6F0

LangaData 4dc8-44d8 EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)

HSTMT 0x0000026B0E0C75E0

SWORD * 0x000000E893FFC6F0 (1)

LangaData 4dc8-44d8 ENTER SQLColAttribute

SQLHSTMT 0x0000026B0E0C75E0

SQLSMALLINT 1

SQLSMALLINT 1013 <SQL_DESC_OCTET_LENGTH>

SQLPOINTER 0x0000000000000000

SQLSMALLINT 0

SQLSMALLINT * 0x0000000000000000

SQLPOINTER [Unknown attribute 1013]

LangaData 4dc8-44d8 EXIT SQLColAttribute with return code -1 (SQL_ERROR)

SQLHSTMT 0x0000026B0E0C75E0

SQLSMALLINT 1

SQLSMALLINT 1013 <SQL_DESC_OCTET_LENGTH>

SQLPOINTER 0x0000000000000000

SQLSMALLINT 0

SQLSMALLINT * 0x0000000000000000

SQLPOINTER [Unknown attribute 1013]

DIAG [HY000] [Schneider Electric][Geo SCADA Expert database driver]Driver not capable (0)

LangaData 4dc8-44d8 ENTER SQLDisconnect

HDBC 0x0000026B0E0C9AF0

LangaData 4dc8-44d8 EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)

HDBC 0x0000026B0E0C9AF0

rcb.solar · ‎2024-10-07

If i activate the ODBC driver log of GeoSCADA (HKEY_CURRENT_USER\SOFTWARE\Schneider Electric\ClearSCADA\DBOdbc\LogEnable = True) i have this :

07-OCT-2024 15:24:28.219 4C34 SQLAllocStmt( IN: Dbc 0000023614B75280)
07-OCT-2024 15:24:28.219 4C34 SQLAllocStmt( OUT: Stmt 0000023610824740, RetCode 0 )
07-OCT-2024 15:24:28.219 4C34 SQLSetStmtOption( IN: Stmt 0000023610824740, option 5 SQL_BIND_TYPE, value 0 )
07-OCT-2024 15:24:28.219 4C34 SQLSetStmtOption( OUT: RetCode 0 )
07-OCT-2024 15:24:28.224 4C34 SQLExecDirect( IN: Stmt 0000023610824740 )
07-OCT-2024 15:24:28.224 4C34 SQL: SELECT TOP(1) "Id" FROM CDBHISTORIC
07-OCT-2024 15:24:28.224 4C34 SQLExecDirect( OUT: RetCode 0 )
07-OCT-2024 15:24:28.224 4C34 SQLNumResultCols( IN: Stmt 0000023610824740 )
07-OCT-2024 15:24:28.224 4C34 SQLNumResultCols( OUT: col_count 1, RetCode 0 )
07-OCT-2024 15:24:28.229 4C34 SQLNumResultCols( IN: Stmt 0000023610824740 )
07-OCT-2024 15:24:28.229 4C34 SQLNumResultCols( OUT: col_count 1, RetCode 0 )
07-OCT-2024 15:24:28.229 4C34 SQLColAttributes( IN: Stmt 0000023610824740, index 1, type 18 SQL_COLUMN_LABEL, value 000002366C801D90, max_len 2048, actual_len 0000007D07FFC0D4, integer 0000000000000000 )
07-OCT-2024 15:24:28.229 4C34 SQLColAttributes( OUT: RetCode 0, Value Id, ActualLen 4 )
07-OCT-2024 15:24:28.229 4C34 SQLNumResultCols( IN: Stmt 0000023610824740 )
07-OCT-2024 15:24:28.229 4C34 SQLNumResultCols( OUT: col_count 1, RetCode 0 )
07-OCT-2024 15:24:28.229 4C34 SQLColAttributes( IN: Stmt 0000023610824740, index 1, type 2 SQL_COLUMN_TYPE, value 0000000000000000, max_len 0, actual_len 0000007D07FFC564, integer 0000007D07FFC670 )
07-OCT-2024 15:24:28.229 4C34 SQLColAttributes( OUT: RetCode 0, Value 4, ActualLen 0 )
07-OCT-2024 15:24:28.259 4C34 SQLNumResultCols( IN: Stmt 0000023610824740 )
07-OCT-2024 15:24:28.259 4C34 SQLNumResultCols( OUT: col_count 1, RetCode 0 )
07-OCT-2024 15:24:28.259 4C34 SQLNumResultCols( IN: Stmt 0000023610824740 )
07-OCT-2024 15:24:28.259 4C34 SQLNumResultCols( OUT: col_count 1, RetCode 0 )
07-OCT-2024 15:24:28.259 4C34 SQLColAttributes( IN: Stmt 0000023610824740, index 1, type 1013 ??, value 0000000000000000, max_len 0, actual_len 0000007D07FFC444, integer 0000007D07FFC4F8 )
07-OCT-2024 15:24:28.259 4C34 SQLColAttributes( OUT: RetCode -1, Value 0, ActualLen 0 )

Still this "type 1013" error when treating the result

AndrewScott · ‎2024-10-07

The Geo SCADA ODBC driver doesn't support the SQLColAttributes() method with attribute 1013, hence it returns a "Driver not capable" error response. I cannot find any documentation on attribute 1013. It isn't a standard attribute defined by Microsoft. Attributes above 1000 are defined as "driver specific", so I can only assume this is something specific to Matlab but that doesn't explain why it would be requesting this attribute from Geo SCADA and not handling the unsupported error response.

Andrew Scott, R&D Principal Technologist, AVEVA

rcb.solar · ‎2024-10-10

Hi Andrew,

thanks for your answer, i have opened a support case on Matlab, here is their answer :

The attribute number 1013 is defined by Microsoft. You can find its definition in Microsoft's ODBC specification on the page linked below.
https://github.com/microsoft/ODBC-Specification/blob/master/Windows/inc/sql.h

As suggested in the file above, SQL_DESC_OCTECT_LENGTH is defined as the value 1013. The definition of SQL_DESC_OCTET_LENGTH can be found here:
https://learn.microsoft.com/en-us/sql/odbc/reference/syntax/sqlcolattribute-function?view=sql-server...

MATLAB refers to the name SQL_DESC_OCTECT_LENGTH provided by Microsoft instead of the hardcoded value 1013, and the name SQL_DESC_OCTECT_LENGTH is interpreted as that number due to the definitions in Microsoft's ODBC specification. We need this information to know how much memory to allocate when reading from a column.

We noticed that several things were updated with the setup on your end before the error occurred, including MATLAB releases and SCADA versions. I have confirmed with my colleague in the development team that the attribute SQL_DESC_OCTECT_LENGTH was used in the older release as well. Hence, it is possible that the update from Clear SCADA to Geo SCADA could be the source of the incompatibility you are encountering.

AndrewScott · ‎2024-10-10

The Geo SCADA ODBC driver only supports ODBC 1.0 and therefore only supports the ODBC 1.0 column attributes for the SQLColAttributes() method. These are defined by Microsoft here (see SQL_COLUMN_xxxx starting on line 679):

https://github.com/microsoft/ODBC-Specification/blob/master/Windows/inc/sqlext.h

SQL_DESC_OCTET_LENGTH (1013) is an ODBC 3.0 column attribute, so is not supported by any version of Geo SCADA. The closest supported attribute is SQL_COLUMN_LENGTH.

If MATLAB now only supports ODBC 3.0 then it will not interoperate with Geo SCADA which only supports ODBC 1.0. Did older versions of MATLAB support ODBC 1.0, thus allowing interoperability?

Andrew Scott, R&D Principal Technologist, AVEVA

rcb.solar · ‎2024-10-17

Older versions of Matlab was using a JDBC-ODBC bridge, that's the assumption of Matlab support, but this bridge is now deprecated by Oracle and not used in new version of Matlab.

Is this ODBC 1.0 limitation in GeoSCADA only apply to GeoSCADA 2019 that i use ? I mean, is ODBC 3.0 compatible with a more recent version like GeoSCADA 2023 ?

Is there another way to connect to GeoSCADA DB, for example in JDBC ?

thanks,

Renaud

AndrewScott · ‎2024-10-17

All versions of Geo SCADA support ODBC 1.0. No version of Geo SCADA supports ODBC 3.0.

Microsoft automatically map ODBC 3.0 onto ODBC 1.0, but this can't work for new features that are unique to ODBC 3.0 such as the SQL_DESC_OCTET_LENGTH column attribute.

I'm not familiar with JDBC, but there is no JDBC driver in Geo SCADA.

In addition to ODBC 1.0, Geo SCADA has an OLE-DB provider.

My only suggestions are to contact SE support to request a new feature or use to "Ideas" section of this forum to suggest a new feature. I expect adding support for ODBC 3.0 would be a significant development.

Andrew Scott, R&D Principal Technologist, AVEVA