3 Replies Latest reply on Mar 30, 2017 8:01 AM by Steven Hawkins

    Getting wrong native Type in case of flat file connection.

    Aakash Sen Newbie

      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

        • 1. Re: Getting wrong native Type in case of flat file connection.
          Steven Hawkins Master

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

          • 2. Re: Getting wrong native Type in case of flat file connection.
            Aakash Sen Newbie

            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

            • 3. Re: Getting wrong native Type in case of flat file connection.
              Steven Hawkins Master

              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.

              1 of 1 people found this helpful