New post
Question
· Oct 6, 2023

Microsoft SQL Server 2022 PolyBase : Connect to Caché as an external datasource. Error running query on datatype

#ODBC #Caché

I've created a succesfull connection to Caché from SQL Server.

But when I run a SELECT on a table it gives an error on for example PrijsAkCatV.

Probably a datatype-error.

Does someone has a solution for this problem ?

 

Caché Table :

Property PrijsAkCatVm As Asci.Getal(CAPTION = "Aankoopprijs VM", HINT = "Bruto aankoopprijs in vreemde munt|De bruto aankoopprijs (catalogusprijs) in vreemde munt per eenheid van aankoop.#Prix d'achat brute en devise|Le prix d'achat brute en devise par unité d'achat.", SCALE = 4);
 

Class Asci.Getal Extends %Library.Numeric
{ Parameter HINT; Parameter VIEW = 1; Parameter VISIBLE = 1; Parameter VOLGORDE; ///   Zorgt ervoor dat de output numeriek is.
///   Was een probleem als er getallen in de zone zaten met vooraan een punt vb .23
ClassMethod StorageToLogical(%val As %Library.String) As %Library.Numeric
{
 Q +%val
} }

External Table :

 

CREATE EXTERNAL TABLE Producten(
    ID INT NOT NULL, 
    ProdCode NVARCHAR (255) NULL,
    ProdNaam NVARCHAR (255) NULL,
    PrijsAkCat NUMERIC (15,4) NULL,
    PrijsAkCatVm NUMERIC (15,4) NULL,
    PrijsAkNetto NUMERIC (15,4) NULL,
    PrijsVkexcl NUMERIC (15,4) NULL,
    PrijsVkincl NUMERIC (15,4) NULL
) WITH (
    LOCATION='ASCISQL.DkDis.Producten',
    DATA_SOURCE=intersystems_source
);

Query :

SELECT TOP (1000) [ID]
      ,[ProdCode]
      ,[ProdNaam]
      ,[PrijsAkCat]
      ,[PrijsAkCatVm]
      ,[PrijsAkNetto]
      ,[PrijsVkexcl]
      ,[PrijsVkincl]
  FROM [ASCISQL].[dbo].[Producten]

Error :

Msg 7320, Level 16, State 110, Line 1
Cannot execute the query "Remote Query" against OLE DB provider "MSOLEDBSQL" for linked server "(null)". 105082;Generic ODBC error: OdbcBufferReader.ReadBuffer, error in OdbcReadBuffer: SqlState: IM001, NativeError: 0, 'Error calling: SQLSetDescField(this->GetHdesc(), (SQLSMALLINT)column->idxServerCol, SQL_DESC_TYPE, (VOID*)column->odbcReadType, 0), SQL return code: -1 | SQL Error Info:  Error <1>: ErrorMsg: [Microsoft][ODBC Driver Manager] Driver does not support this function | Error calling: pReadConn->ReadBuffer(pBuffer, bufferOffset, bufferLength, pBytesRead, pRowsRead) | state: FFFF, number: 40, active connections: 1', Connection String: Dsn=ASCISQL;uid=_SYSTEM;server=127.0.0.1;port=1972;database=ASCISQL.
 

Product version: Caché 2018.1
Discussion (3)2
Log in or sign up to continue
Pablo Echeverria · Oct 7, 2023

As the error indicates: Driver does not support this function.

The first thing Polybase does is retrieve the data types from the external table, and if the driver doesn't allow this, then this error is returned. There is no workaround about this, it's the way Polybase works, so if it can't get the datatypes first, it can't continue.

Try using a third party ODBC and see if it works.

0 0
Julius Kavay · Oct 7, 2023

Is my assumption correct, you use for the property "PrijsAkCatVm" an own data type (Asci.Getal) instead of %Numeric, in order to be able to use some extra parameters like HINT, VIEW, VISIBLE etc.
Is that correct? If yes, then there is a much simpler solution where neither  MS nor SQL won't stumble over it. The catchword is PropertyClass.

/// Create a new Cache class.
/// Define here all the extra parameter you need.
///
Class Extra.Props
{
    Parameter HINT;
    Parameter VIEW = 1;
    Parameter VISIBLE = 1;
    Parameter VOLGORDE;
    // etc.
}

/// then take your Cache Table, add the above defined class
/// to your Cache Table, complete the property definitions with
/// the appropriate parameters - Done.
///
Class Your.CacheTable Extends %Persistent [ PropertyClass = Extra.Props ]
{
   Property PrijsAkCatVm As %Numeric (CAPTION="...", "HINT=...", etc.); 
}

Now you can use the above defined (extra) parameters in each and every property  in this class.

0 0