How to use file as datasource in DDL Dynamic VDB

[reuseman]

Here is what i have done so far

Setting file resource adapter in JBoss configuration (standalone-teiid.xml)

<subsystem xmlns="urn:jboss:domain:resource-adapters:4.0">
            <resource-adapters>
                <resource-adapter id="file">
                    <module slot="main" id="org.jboss.teiid.resource-adapter.file"/>
                    <connection-definitions>
                        <connection-definition class-name="org.teiid.resource.adapter.file.FileManagedConnectionFactory" jndi-name="java:/jbosslabLocationDS" enabled="true" use-java-context="true" pool-name="jbosslabLocationDS">
                            <config-property name="AllowParentPaths">
                                true
                            </config-property>
                            <config-property name="Parentdirectory">
                                C:/Programs/eap/jboss-eap-7.0-teiid-9/jbosslab/
                            </config-property>
                        </connection-definition>
                    </connection-definitions>
                </resource-adapter>

Create DDL VDB

CREATE DATABASE jbosslab VERSION '1.0.0';
USE DATABASE jbosslab VERSION '1.0.0';

-- Select translator (mysql5)
CREATE FOREIGN DATA WRAPPER mysql5;
-- Create server with datasource
CREATE SERVER "mysql_server" TYPE 'mysql' FOREIGN DATA WRAPPER mysql5 OPTIONS ("jndi-name" 'java:jboss/datasources/JbosslabDS');

-- Select translator (file)
CREATE FOREIGN DATA WRAPPER file;
-- Create server with datasource
CREATE SERVER "file_server" TYPE 'file' FOREIGN DATA WRAPPER file OPTIONS (
ParentDirectory 'C:/Programs/eap/jboss-eap-7.0-teiid-9/jbosslab/',
"jndi-name" 'java:/jbosslabLocationDS'
);
-- Create source schema from mysql
CREATE SCHEMA "mysql_source_schema" SERVER "mysql_server";
-- Create virtual schema contains view layer
CREATE VIRTUAL SCHEMA "report";
-- Create source schema from file
-- CREATE SCHEMA "file_source_schema" SERVER "file_server";

Every time uncomment the last line, the vdb is unaccessible and get the following error in query

Error: TEIID31099 VDB jbosslab.1.0.0[mysql_source_schema{mysql_server=mysql_server, mysql5, java:jboss/datasources/JbosslabDS}, file_source_schema{file_server=file_server, file, file_server}, report{}] is not active, but FAILED.  If loading you can resubmit your query after loading has completed or after the errors have been corrected.
SQLState:  50000
ErrorCode: 31099

What does i have missing?

[rareddy]

Hello Andre,

There are few idiosyncrasies exist with the current DDL format. It expects the DDL to be in some strict flow to be compatible with XML version, we are working on relaxing that in coming versions.  There are couple issues I found in your configuration and VDB.

1) In your resource adapter for "file" configuration, the property is "ParentDirectory" not "Parentdirectory". See the capital "D".

2) You need to also define a "datasource" for mysql with JNDI name  'java:jboss/datasources/JbosslabDS' which I am thinking you already have.

3) The VDB needs to be like

CREATE DATABASE jbosslab VERSION '1.0.0';  
USE DATABASE jbosslab VERSION '1.0.0';  
  
-- Select translator (mysql5)  
CREATE FOREIGN DATA WRAPPER mysql5;  

-- Create server with datasource  
CREATE SERVER "mysql_server" TYPE 'mysql' 
    FOREIGN DATA WRAPPER mysql5 OPTIONS ("jndi-name" 'java:jboss/datasources/ExampleDS');  
  
-- Select translator (file)  
CREATE FOREIGN DATA WRAPPER file;  

-- Create server with datasource  
CREATE SERVER "file_server" TYPE 'file' FOREIGN DATA WRAPPER file OPTIONS (  
    ParentDirectory 'C:/Programs/eap/jboss-eap-7.0-teiid-9/jbosslab/',  
    "jndi-name" 'java:/jbosslabLocationDS'  
);  

-- Create source schema from mysql  
CREATE SCHEMA "mysql_source_schema" SERVER "mysql_server";  

-- Create source schema from file  
CREATE SCHEMA "file_source_schema" SERVER "file_server";

-- Create virtual schema contains view layer  
CREATE VIRTUAL SCHEMA "report";  

-- import schema from mysql
IMPORT FOREIGN SCHEMA public
    FROM SERVER "mysql_server"
    INTO "mysql_source_schema";

-- import schema from file resource    
IMPORT FOREIGN SCHEMA public
    FROM SERVER "file_server"
    INTO "file_source_schema";

-- define the virtual view below.
SET SCHEMA "report";

-- just a dummy view to make vdb load correctly, as it does not allow a schema with empty contents
CREATE VIEW MyView AS SELECT 'foo';

The IMPORT SCHEMA were new from your version of the VDB, those will import the existing metadata from the remote source into the VDB metadata. I created a dummy view called "MyView" but in your case this needs to be something valuable like joining the data from file and mysql data sources.

HTH.

Ramesh..

[reuseman]

Hi Ramesh

Thank you, it is working, my fault was using "Parentdirectory" instead of "ParentDirectory" in resource adapter configuration.

To anyone who go further how to query file check this tutorial:

Text to Table with Teiid

Thank you

Andre Pratama