-
1. Re: How to call teiid stored procedure in multi-source vdb configuration?
rareddy Nov 1, 2012 9:17 PM (in response to kchen007)1 of 1 people found this helpfulKevin,
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..
-
2. Re: How to call teiid stored procedure in multi-source vdb configuration?
kchen007 Nov 2, 2012 10:13 AM (in response to rareddy)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
-
3. Re: How to call teiid stored procedure in multi-source vdb configuration?
rareddy Nov 2, 2012 10:40 AM (in response to kchen007)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..
-
4. Re: How to call teiid stored procedure in multi-source vdb configuration?
kchen007 Nov 2, 2012 10:51 AM (in response to rareddy)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
-
5. Re: How to call teiid stored procedure in multi-source vdb configuration?
kchen007 Nov 2, 2012 11:19 AM (in response to rareddy)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
-
6. Re: How to call teiid stored procedure in multi-source vdb configuration?
shawkins Nov 2, 2012 12:40 PM (in response to kchen007)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
-
8. Re: How to call teiid stored procedure in multi-source vdb configuration?
kchen007 Nov 2, 2012 2:33 PM (in response to shawkins)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
-
9. Re: How to call teiid stored procedure in multi-source vdb configuration?
shawkins Nov 2, 2012 2:54 PM (in response to kchen007)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
-
10. Re: How to call teiid stored procedure in multi-source vdb configuration?
kchen007 Nov 8, 2012 5:17 PM (in response to shawkins)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
-
11. Re: How to call teiid stored procedure in multi-source vdb configuration?
shawkins Nov 9, 2012 7:34 AM (in response to kchen007)It must be added manually via Designer or DDL or programatically via a custom metadata repository.
Steve