dynamobi/dynamobi — Issue #221: [LDB-100] bcp definition of SQLDATE being iinterpreted as VARCHAR
Author: dynamobi-build
Created: 2011-12-30 02:22:47+00:00
State: closed
Labels: Flatfile, LDB

[reporter="boris", created="Fri, 26 May 2006 14:46:07 -0500 (GMT-05:00)"]

Error is this:

0: jdbc:luciddb:> insert into tpch.lineitem select * from tpch."lineitem";

Error: At line 1, column 34: Cannot assign to target field 'L_SHIPDATE' of type DATE from source field 'F11' of type VARCHAR(10) (state=,code=0)

---

Do the following:

-----

create foreign data wrapper flatfile_wrapper

library 'class com.lucidera.farrago.namespace.flatfile.FlatFileDataWrapper'

language java;

create server ff_server

foreign data wrapper flatfile_wrapper

options(

directory '/home/boris/depot/playground/boris/projects/tpch/dbgen/data',

file_extension '.tbl',

ctrl_file_extension '.bcp',

field_delimiter '|',

line_delimiter '\n',

quote_char '"',

escape_char '',

-- with_header 'yes',

with_header 'no',

num_rows_scan '1'

);

import foreign schema "BCP" from server ff_server into tpch;

CREATE TABLE TPCH.LINEITEM (

L_ORDERKEY INTEGER,

L_PARTKEY INTEGER NOT NULL,

L_SUPPKEY INTEGER NOT NULL,

L_LINENUMBER INTEGER,

L_QUANTITY DECIMAL(15,2) NOT NULL,

L_EXTENDEDPRICE DECIMAL(15,2) NOT NULL,

L_DISCOUNT DECIMAL(15,2) NOT NULL,

L_TAX DECIMAL(15,2) NOT NULL,

L_RETURNFLAG VARCHAR(1) NOT NULL,

L_LINESTATUS VARCHAR(1) NOT NULL,

L_SHIPDATE DATE NOT NULL,

L_COMMITDATE DATE NOT NULL,

L_RECEIPTDATE DATE NOT NULL,

L_SHIPINSTRUCT VARCHAR(25) NOT NULL,

L_SHIPMODE VARCHAR(10) NOT NULL,

L_COMMENT VARCHAR(44) NOT NULL);

insert into tpch.lineitem select * from tpch."lineitem";