2 Replies Latest reply on Sep 27, 2017 6:58 PM by Andre Pratama

    How to use file as datasource in DDL Dynamic VDB

    Andre Pratama Newbie

      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?
        • 1. Re: How to use file as datasource in DDL Dynamic VDB
          Ramesh Reddy Master

          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..

          • 2. Re: How to use file as datasource in DDL Dynamic VDB
            Andre Pratama Newbie

            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