]
RH Bugzilla Integration commented on TEIIDDES-2316:
---------------------------------------------------
Debi Rieden <drieden(a)redhat.com> changed the Status of [bug
Excel import creates wrong datatype for ROW_ID column
-----------------------------------------------------
Key: TEIIDDES-2316
URL:
https://issues.jboss.org/browse/TEIIDDES-2316
Project: Teiid Designer
Issue Type: Bug
Components: Import/Export, Modeling, Teiid Integration
Affects Versions: 8.6
Environment: Teiid Designer 8.6.0.Alpha1
Teiid 8.7.1
Reporter: Andrej Šmigala
Assignee: Barry LaFond
Fix For: 8.6
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:
{code:sql}
SELECT * FROM Sheet1
SELECT * FROM Sheet1 WHERE row_id > 2
{code}
but the following query fails:
{code:sql}
SELECT * FROM Sheet1 WHERE row_id = 2
{code}
The error is
bq. 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
{code:sql}
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');
{code}