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

Excel import creates wrong datatype for ROW_ID column

    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');
      

        Gliffy Diagrams

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  blafond Barry LaFond
                  Reporter:
                  asmigala Andrej Smigala
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: