Uploaded image for project: 'Teiid Designer'
  1. Teiid Designer
  2. TEIIDDES-2316

Excel import creates wrong datatype for ROW_ID column

    XMLWordPrintable

Details

    Description

      Following https://community.jboss.org/wiki/MicrosoftExcelDocumentIntoRelationalTable (Option 1 using Teiid Designer), the imported model has the ROW_ID column modeled with datatype "integer", which results in BigInteger runtime datatype. After adding this model to a vdb and deploying, the following queries work ok:

      SELECT * FROM Sheet1
      SELECT * FROM Sheet1 WHERE row_id > 2
      

      but the following query fails:

      SELECT * FROM Sheet1 WHERE row_id = 2
      

      The error is

      org.teiid.runtime.client.TeiidClientException: java.lang.RuntimeException: Remote org.teiid.core.TeiidException: java.math.BigInteger cannot be cast to java.lang.Integer

      After changing the datatype to "int : xs:long" and redeploying, the above query works as expected.

      However, if I copy the generated dynamic vdb (attached) and deploy it on the server (without using Designer), running the query against that works.

      The DDL for import is

      SET NAMESPACE 'http://www.teiid.org/translator/excel/2014' AS teiid_excel;
      
      CREATE FOREIGN TABLE Sheet1 (
      	ROW_ID integer OPTIONS (SEARCHABLE 'All_Except_Like', "teiid_excel:CELL_NUMBER" 'ROW_ID'),
      	FirstName string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '1'),
      	LastName string OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '2'),
      	Age double OPTIONS (SEARCHABLE 'Unsearchable', "teiid_excel:CELL_NUMBER" '3'),
      	CONSTRAINT PK0 PRIMARY KEY(ROW_ID)
      ) OPTIONS ("teiid_excel:FILE" 'names.xlsx', "teiid_excel:FIRST_DATA_ROW_NUMBER" '2');
      

      Attachments

        Issue Links

          Activity

            People

              blafond Barry LaFond
              asmigala@redhat.com Andrej Smigala
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: