Getting wrong native Type in case of flat file connection.

[aakash03]

Hi,

I am using below schema file.

file schema:

---------------

[testFile.txt]

Format=Delimited("|")

ColNameHeader=False

CharacterSet=ANSI

Col1=CustId Integer

Col2=AccountNum Integer

Col3=AccountType varchar

Col4=FirstName varchar

Col5=LastName varchar

Col6=CompanyName varchar

Col7=Email varchar

Col8=Gender varchar

Col9=PhoneNumber varchar

Col10=City varchar

Col11=State varchar

Col12=ZipCode varchar

Col13=Country varchar

Col14=TransactionAmount Float

Col15=CurrencyCode varchar

Col16=TransactionDate DATETIME MM-dd-yyyy HH:mm:ss.SSS

Using below teiid Configuration:

Jboss wildfly 10.0.0

Teiid 9.1.3

I have successfully created the connection with this file.

Schema returned by AdminApiClient.getSchema() :

CREATE FOREIGN TABLE Test (

  CustId integer OPTIONS (ANNOTATION 'CustId', NAMEINSOURCE '"CustId"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'INTEGER'),

  AccountNum integer OPTIONS (ANNOTATION 'AccountNum', NAMEINSOURCE '"AccountNum"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'INTEGER'),

  AccountType clob OPTIONS (ANNOTATION 'AccountType', NAMEINSOURCE '"AccountType"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  FirstName clob OPTIONS (ANNOTATION 'FirstName', NAMEINSOURCE '"FirstName"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  LastName clob OPTIONS (ANNOTATION 'LastName', NAMEINSOURCE '"LastName"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  CompanyName clob OPTIONS (ANNOTATION 'CompanyName', NAMEINSOURCE '"CompanyName"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  Email clob OPTIONS (ANNOTATION 'Email', NAMEINSOURCE '"Email"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  Gender clob OPTIONS (ANNOTATION 'Gender', NAMEINSOURCE '"Gender"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  PhoneNumber clob OPTIONS (ANNOTATION 'PhoneNumber', NAMEINSOURCE '"PhoneNumber"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  City clob OPTIONS (ANNOTATION 'City', NAMEINSOURCE '"City"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  State clob OPTIONS (ANNOTATION 'State', NAMEINSOURCE '"State"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  ZipCode clob OPTIONS (ANNOTATION 'ZipCode', NAMEINSOURCE '"ZipCode"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  Country clob OPTIONS (ANNOTATION 'Country', NAMEINSOURCE '"Country"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'LONGVARCHAR'),

  TransactionAmount double OPTIONS (ANNOTATION 'TransactionAmount', NAMEINSOURCE '"TransactionAmount"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'DOUBLE'),

  CurrencyCode clob OPTIONS (ANNOTATION 'CurrencyCode', NAMEINSOURCE '"CurrencyCode"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'AdminApiClient.getSchema()'),

  TransactionDate timestamp OPTIONS (ANNOTATION 'TransactionDate', NAMEINSOURCE '"TransactionDate"', CHAR_OCTET_LENGTH 254, NATIVE_TYPE 'TIMESTAMP')

) OPTIONS (ANNOTATION 'Test', NAMEINSOURCE '"Test"', UPDATABLE TRUE);

If we compare input file schema and schema returned by AdminApiClient.getSchema()  ,we found mismatch in native Type as shown in the below example :

for example:

FieldName         nativeType in input Schema file            nativeType in Schema( AdminApiClient.getSchema)

AccountType                     varchar                                             LONGVARCHAR

TransactionAmount           Float                                                  DOUBLE

Please help me on this.

Thanks,

Aakash K Sen

[shawkins]

Can you elaborate on the setup?  What do the translator and resource adapter configurations look like?

[aakash03]

Below are configuration for file connection:

module.xml:

<?xml version="1.0" encoding="UTF-8"?>

<module xmlns="urn:jboss:module:1.0" name="com.hxtt.text">

  <resources>

    <resource-root path="Text_JDBC42_Jan_2017.jar"/>

  </resources>

  <dependencies>

    <module name="javax.api"/>

  <module name="com.jsch.remote"/>

  </dependencies>

</module>

Translator:

<translator name="file" module="org.jboss.teiid.translator.file"/>

dataSource:

<datasource jndi-name="java:/FileDS20170329155712702" pool-name="FileDS20170329155712702">

                    <connection-url>jdbc:text:sftp://userName:password@HostName/filepath?odbcSchemaFile=Test.sch;fileExtension=txt</connection-url>

                    <driver>textfile</driver>

                    <pool>

                        <max-pool-size>20</max-pool-size>

                    </pool>

</datasource>

vdb:

<?xml version="1.0" ?><vdb name="SvcSourceVdb_FileConn" version="1"><description>VDB for: SvcSourceVdb_FileConn, Version: 1</description><connection-type>BY_VERSION</connection-type><model name="FileConn" type="PHYSICAL" visible="true"><property name="importer.tableTypes" value="TABLE"></property><source name="FileConn" translator-name="jdbc-ansi" connection-jndi-name="java:/FileDS20170329155712702"></source></model></vdb>

Please let me know if there is anything else required.

Thanks,

Aakash K Sen

[shawkins]

The file translator is not coming into play here.  This is the JDBC

translator using a htxx file source.  The metadata import logic is very

straight-forward, so the issue you are seeing is likely with how htxx is

reporting the type information.  You can confirm that by creating and using

the connection in other tooling or just inspecting the DatabaseMetadata

that they report.