Bug 1134006 - Excel import creates wrong datatype for ROW_ID column
Summary: Excel import creates wrong datatype for ROW_ID column
Keywords:
Status: CLOSED CURRENTRELEASE
Alias: None
Product: JBoss Data Virtualization 6
Classification: JBoss
Component: Tooling
Version: 6.1.0
Hardware: Unspecified
OS: Unspecified
unspecified
unspecified
Target Milestone: ER4
: 6.1.0
Assignee: Van Halbert
QA Contact:
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2014-08-26 15:42 UTC by Andrej Smigala
Modified: 2015-11-17 18:06 UTC (History)
2 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
An Excel import created the wrong datatype for the ROW_ID column causing queries to fail. It now creates the correct datatype, so users can now run their queries without encountering problems.
Clone Of:
Environment:
Last Closed: 2015-11-17 18:06:14 UTC
Type: Bug


Attachments (Terms of Use)


Links
System ID Priority Status Summary Last Updated
Red Hat Issue Tracker TEIIDDES-2316 Major Closed Excel import creates wrong datatype for ROW_ID column 2014-11-17 16:25:41 UTC

Description Andrej Smigala 2014-08-26 15:42:01 UTC
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');

Comment 2 JBoss JIRA Server 2014-09-04 20:31:58 UTC
Barry LaFond <blafond@jboss.org> updated the status of jira TEIIDDES-2316 to Resolved

Comment 3 JBoss JIRA Server 2014-11-17 16:25:44 UTC
Barry LaFond <blafond@jboss.org> updated the status of jira TEIIDDES-2316 to Closed


Note You need to log in before you can comment on or make changes to this bug.