Translator and JNDI for DDL Import to Source?

[dsusin]

Hello,

I would like to import a ddl file that simply creates a table and makes some inserts on it. The reason is to add a table with static content that will be used as metadata on my model. The problem is that I do not know which translator or JNDI use in order to access the data I suposedly inserted. Any help on this?

I know I could do this by importing a simple static text file but I would like to reduce as much as possible dependencies on external files. I would like to only worry about deploying the vdb file, not dependencies as external files.

Thanks.

[shawkins]

> I would like to import a ddl file that simply creates a table and makes some inserts on it. The reason is to add a table with static content that will be used as metadata on my model. The problem is that I do not know which translator or JNDI use in order to access the data I suposedly inserted. Any help on this?

Unfortunately there's a rather hard divide between the metadata and runtime systems.  If I understand you correctly though, you can do something like this with a custom metadata repository - https://docs.jboss.org/author/display/TEIID/Custom+Metadata+Repository

You can either make the direct calls to instantiate the metadata - similar to the logic in the other Tranlators, such as the JDBCMetadataProcessor in the JDBC translator.   Or you can use a templatized DDL (which could be listed as the schema text in the VDB.xml itself) and pre-process it in your custom metadata repository and then call to MetadataFactory.parse(Reader) method to load the metadata.

Steve

[dsusin]

Hi, I said I wanted to use the table as metadata, but, actually, it should be a regular table after all. I think a custom metadata repository is far too much complicated just for what I want to achieve... Let's say I want a table in a source (or view, does not matter) model with few rows that will be always the same, thus, static content, for instance:

CREATE TABLE categories (category VARCHAR(255), table_name VARCHAR (255));

INSERT INTO categories VALUES ('category 1', 'table 1');

INSERT INTO categories VALUES ('category 2', 'table 2');

INSERT INTO categories VALUES ('category 3', 'table 3');

INSERT INTO categories VALUES ('category 3', 'table 4');

Is there a simple way to have a table like this without having to use a real data source? If not, I think I'd rather use a simple file source instead of custom metadata repositories.

Thanks.

[rareddy]

So Dyanmic VDB with metadata defined as below works?

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<vdb name="northwind" version="1">
    <model name="nw">
         <source name="northwind" translator-name="mysql" connection-jndi-name="java:/nw-ds"/>
         <metadata type = "DDL"><![CDATA[
               CREATE FOREIGN TABLE customers(id integer, name string);
         ]]>
         </metadata>
    </model>
</vdb>

but the above is expecting the table "customers" in the "mysql" data source. Also DML statements can not be done with VDB metadata, that need to be inserted after the VDB deployed.

[rareddy]

The DDL you can use is defined here https://docs.jboss.org/author/display/TEIID/DDL+Metadata

[rareddy]

Also if the above does not meet your requirements may be using a temp table might work!

[shawkins]

Daniel,

I didn't quite follow what you getting at before.  If you are looking to just define a table you do have more options.

> Is there a simple way to have a table like this without having to use a real data source? If not, I think I'd rather use a simple file source instead of custom metadata repositories.

You can define a view in any of you schemas:

CREATE VIEW categories (category VARCHAR(255), table_name VARCHAR (255)) AS SELECT 'category 1', 'table 1' UNION ALL SELECT 'category 2', 'table 2' ...

You can also use TEXTTABLE with a CSV literal or any other convienent SQL to define the view.  If it is large or uses a somewhat complicated definition, then you can also set it to be materailized:

CREATE VIEW categories (category VARCHAR(255), table_name VARCHAR (255)) OPTIONS (MATERIALIZED true) AS ...

If the data is large then your probably better off using a file source with approriate views on top of that.

Steve

[dsusin]

Thanks, that was it, at the end it it was my rusty SQL's fault. It's a very small table, like 7 or 8 rows.