How to insert data in flat file
nish18 Dec 28, 2015 8:57 AMHi ,
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.