0 Replies Latest reply on Dec 28, 2015 8:57 AM by Nishant Agrawal

    How to insert data in flat file

    Nishant Agrawal Novice

      Hi ,

       

      I am trying to create a view from the flat file,I have made a text file "CustomerOrderDetails.txt" which is containing data in below format:

       

      Order_id,customer_id,product_name

      1,1,'TestProduct1'

      2,2,'TestProduct2'

      3,3,'TestProduct3'

      4,4,'TestProduct4'

       

       

      Datasource for text file in standalon-teiid.xml file:

       

       

      <resource-adapters>

                      <resource-adapter id="file">

                          <module slot="main" id="org.jboss.teiid.resource-adapter.file"/>

                          <transaction-support>NoTransaction</transaction-support>

                          <connection-definitions>

                              <connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" jndi-name="java:/TextfileDS" enabled="true" use-java-context="true" pool-name="teiid-file-ds">

                                  <config-property name="ParentDirectory">

                                      C:/Aakash/TDM Document/Files

                                  </config-property>

                                  <config-property name="AllowParentPaths">

                                      true

                                  </config-property>

                              </connection-definition>

                          </connection-definitions>

                      </resource-adapter>

      </resource-adapters>



      vdb file:


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

      <vdb name="DynamicPortfolio1" version="1">

       

       

          <description>A Dynamic VDB</description>

          <property name="UseConnectorMetadata" value="true" />

       

       

          <model name="MarketData" >

             <source name="text-connector" translator-name="file" connection-jndi-name="java:/TextfileDS"/>

          </model>

        <model name="file" visible="true" type="VIRTUAL"> 

              <metadata type="DDL"><![CDATA[      

                  CREATE VIEW FileView (Order_id integer PRIMARY KEY,customer_id integer,product_name varchar(25)) OPTIONS (UPDATABLE true) AS

        SELECT Order_id,customer_id,product_name FROM (EXEC MarketData.getTextFiles('CustomerOrderDetails.txt')) AS f,TEXTTABLE(file COLUMNS Order_id integer,customer_id integer,product_name string HEADER) AS s; 

       

      CREATE TRIGGER ON FileView INSTEAD OF INSERT AS 

                    FOR EACH ROW 

                    BEGIN ATOMIC 

        

      INSERT INTO ['CustomerOrderDetails.txt'] values(NEW.Order_id ,NEW.customer_id ,NEW.product_name);

       

      END

                  ]]> 

              </metadata> 

          </model> 

      </vdb>

       

      I am able to select  the data from the view but unable to insert data on it.

       

      please can you anyone suggest me any other way to insert data into text flat file.