Bug 1120073
| Summary: | Teiid Connection import fails to parse DDL when table contains VARCHAR column with DEFAULT value | ||||||
|---|---|---|---|---|---|---|---|
| Product: | [JBoss] JBoss Data Virtualization 6 | Reporter: | Andrej Smigala <asmigala> | ||||
| Component: | Tooling | Assignee: | Barry LaFond <blafond> | ||||
| Status: | CLOSED CURRENTRELEASE | QA Contact: | Andrej Smigala <asmigala> | ||||
| Severity: | high | Docs Contact: | |||||
| Priority: | unspecified | ||||||
| Version: | 6.0.0 | CC: | atangrin, blafond, felias, mbaluch, vhalbert | ||||
| Target Milestone: | --- | ||||||
| Target Release: | --- | ||||||
| Hardware: | Unspecified | ||||||
| OS: | Unspecified | ||||||
| Whiteboard: | |||||||
| Fixed In Version: | Doc Type: | Bug Fix | |||||
| Doc Text: | Story Points: | --- | |||||
| Clone Of: | Environment: | ||||||
| Last Closed: | 2015-11-17 18:07:13 UTC | Type: | Bug | ||||
| Regression: | --- | Mount Type: | --- | ||||
| Documentation: | --- | CRM: | |||||
| Verified Versions: | Category: | --- | |||||
| oVirt Team: | --- | RHEL 7.3 requirements from Atomic Host: | |||||
| Cloudforms Team: | --- | Target Upstream Version: | |||||
| Embargoed: | |||||||
| Attachments: |
|
||||||
What is the JDBC database in this use-case?
After discussing with Teiid, apparently the JDBC driver ended up NOT returning the original 'c1' DEFAULT value, but converted to ('c1').
Can you verify in your DB (via squirrel, etc..) what the default ended up being in the DB?
This was with MS SQL 2012 from DB allocator.
Squirrel reports the COLUMN_DEF as ('c1')
I did a quick test with a couple other DBs in allocator and these are the results:
PostgreSQL 9.2:
DDL is
CREATE FOREIGN TABLE "public.t1" (
c1 string(10) DEFAULT '''c1''::character varying' OPTIONS (NAMEINSOURCE '"c1"', NATIVE_TYPE 'varchar')
) OPTIONS (NAMEINSOURCE '"public"."t1"', UPDATABLE TRUE);
The import fails
COLUMN_DEF according to squirrel is 'c1'::character varying
Oracle 12c
DDL is
CREATE FOREIGN TABLE "DBALLO01.T1" (
C1 string(10) DEFAULT '''c1''' OPTIONS (NAMEINSOURCE '"C1"', NATIVE_TYPE 'VARCHAR2')
) OPTIONS (NAMEINSOURCE '"DBALLO01"."T1"', UPDATABLE TRUE, CARDINALITY 0);
The import fails
COLUMN_DEF according to squirrel is 'c1'
MySQL 5.5
DDL is
CREATE FOREIGN TABLE "dballo00.t1" (
c1 string(10) DEFAULT 'c1' OPTIONS (ANNOTATION '', NAMEINSOURCE '`c1`', NATIVE_TYPE 'VARCHAR')
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`dballo00`.`t1`', UPDATABLE TRUE);
The import actually works
COLUMN_DEF according to squirrel is c1
This appears to be a MS Teiid Parsing issue. The work-around is to save the DDL to your filesystem/workspace, remove the offending quotes and re-import using the DDL Importer selecting the Teiid Dialect. Recommend DV 6.3 target Barry LaFond <blafond> updated the status of jira TEIIDDES-2248 to Resolved Andrej Šmigala <asmigala> updated the status of jira TEIIDDES-2248 to Closed |
Created attachment 918351 [details] Screenshot Description of problem: Create a table with CREATE TABLE t1 ( c1 VARCHAR(10) DEFAULT 'c1' ) When trying to import it through Teiid Connection Importer, the importer DDL is shown as CREATE FOREIGN TABLE "dballo00.dbo.t1" ( c1 string(10) DEFAULT '(''c1'')' OPTIONS (NAMEINSOURCE '"c1"', NATIVE_TYPE 'varchar') ) OPTIONS (NAMEINSOURCE '"dballo00"."dbo"."t1"', UPDATABLE TRUE, CARDINALITY 0); but on the next page, the following error is shown (see screenshot): The DDL failed to parse with message: "Unparsable table body" Version-Release number of selected component (if applicable): Teiid Designer 8.3.3 Teeid 8.4.1-redhat-7