How to call teiid stored procedure in multi-source vdb configuration?

[kchen007]

In the documentation, it mentioned that I need add the SOURCE_NAME column, where shall I add it? In the vdb's meta data definition or in StoredProcedureCall.addNamedArgument()? also do I need pass in the value for the source model as part of the parameter list?

Does someone has an example to show the workflow?

Thanks

Kevin

[rareddy]

Kevin,

If you are using the Designer, you can manually add the column to the table. If you are using dynamic vdb, you can define your schema using the DDL and define the column in there.

Ramesh..

[kchen007]

Thanks Ramesh.

that means, the source_name column should be added into the teiid meta data definition. Can you give me an example on how to add it via using DDL in case of dynamic vdb?

Thanks

Kevin

[rareddy]

Here is some sample code

<vdb name="{vdb-name}" version="1">
    <model name="{model-name}" type="PHYSICAL">
        <source name="AccountsDB" translator-name="oracle" connection-jndi-name="java:/oracleDS"/>
        <metadata type="DDL"><![CDATA[
           CREATE FOREIGN TABLE Customer (id integer PRIMARY KEY, firstname varchar(25), lastname varchar(25),  SOURCE_NAME varchar(25));
           .... (other tables, procedures etc)
         ]]>
        </metadata>
    </model>
</vdb>

Note, that here you are defining the all the metadata about the source. If you are dynamically retrieving the metadata before, that will not work with above. Currently there is no DDL statement "alter table" available to modify the table metadata that retrieved from source.

Steve: should we add a feature to add this column to metadata automatically during the Dynamic VDB scenarios based on some flag?

Thanks

Ramesh..

[kchen007]

Ramesh:

We do not define them in xml file, we retrieve it dynamically in our java code. Do you have other workaround in this case?

Thanks

Kevin

[kchen007]

Ramesh:

I added the source_name column in my stored procedure definiton, as following, but when I call the stored procedure, I got the following exception:

10:07:37,804 INFO  [stdout] (http--0.0.0.0-8080-1) Internal Exception: org.teiid.jdbc.TeiidSQLException: TEIID30492 Remote org.teiid.api.exception.query.QueryValidatorException: TEIID30492 The multisource column or parameter SQLNorthwind.ktestSp1.SOURCE_NAME requires a literal value.

How shall I declare it as constant?

thanks

kevin

[shawkins]

For dynamic or even designer vdbs, see https://community.jboss.org/thread/211729?tstart=0 for using a custom metadata repository to automatically add columns.  Ramesh, yes that can be an option for the existing multi-source logic with another model property and can be added as a sub-issue of https://issues.jboss.org/browse/TEIID-2253

I've updated https://docs.jboss.org/author/display/TEIID/Multi-Source+Models showing more on the expected usage with procedures.  What you're showing above looks like it should work, how did you call it?

Steve

[rareddy]

added https://issues.jboss.org/browse/TEIID-2292

[kchen007]

here is what I called it:

  the prepared statement is {CALL ktestSp1(?, ?)}, the 2nd is for source_name

the bind is       bind => [ALFKI, SQLNorthwind, => outParam], I also has an out parameter.

Thanks very much.

Kevin

[shawkins]

Kevin,

Due to https://issues.jboss.org/browse/TEIID-2255 we are expecting a literal value, so "CALL ktest(?, 'SQLNorthwind')" instead.  We'll address that limitation in 8.3.

Steve

[kchen007]

I had a one more question, in multi-source situation, is the SOURCE_NAME parameter automatically added to the Teiid vdb's stored procedure metadata?

thanks

Kevin

[shawkins]

It must be added manually via Designer or DDL or programatically via a custom metadata repository.

Steve