-
1. Re: Dynamic VDB and Stored Procedure approach
rareddy Jun 11, 2015 9:12 AM (in response to anilnair)Yes, you can combine both into one like below.
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="CompanySP" version="1"> <description>CompanySP</description> <property name="UseConnectionMetadata" value="true" /> <model name="Employees"> <property name="importer.useFullSchemaName" value="true" /> <property name="importer.importProcedures" value="true" /> <property name="importer.procedureNamePattern" value="GetEmployeeDetails" /> <source name="CompanySP-sql-connector" translator-name="sqlserver" connection-jndi-name="java:jboss/datasources/CompanysSPDS" /> <metadata type="NATIVE"/> <metadata type="DDL"><![CDATA[ CREATE FOREIGN PROCEDURE GetEmployeeDetails23() RETURNS TABLE (Xfirstname string OPTIONS (NAMEINSOURCE '"firstname"',NATIVE_TYPE 'nvarchar'), lastname string OPTIONS (NAMEINSOURCE '"lastname"', NATIVE_TYPE 'nvarchar')) OPTIONS (NAMEINSOURCE '"Companys"."dbo"."GetEmployeeDetails"') ]]> </metadata> </model> </vdb>
See the additional metadata element. See Metadata Repositories - Teiid 8.11 (draft) - Project Documentation Editor
More over we also need to figure out, why SQL server is not returning the stored procedure metadata correctly in the first place, so that you do not have to manually enter the DDL for the stored procedure. Can you see like with tools like SQuirreL the metadata for procedure is presented correctly? You can also try JTDS driver for the SQlServer and see if that helps.
-
2. Re: Dynamic VDB and Stored Procedure approach
anilnair Jun 11, 2015 1:40 PM (in response to rareddy)Thank you Ramesh.
I forgot to mention that I am using jtds driver for connecting to SQL server.
When I only use <property name="importer.importProcedures" value="true" /> without the (Create foreign procedure DDL ) in the VDB and I did see the storedprocedure name in the meta data in the client code
but when I call the storeprocedure with the name , I got null pointer exception.So was thinking that I would need to write the Create foreign approach for stored procedure to work thought I read in the documentation that the former setting would export all stored procedures.
I did try the combined one earlier(exactly the same one that you had send), with this the problem was when I try to query a table instead of storeprocedure I was getting exceptionThe code the I used is
String url = "jdbc:teiid:CompanySP@mm:// localhost:31000"; Class.forName("org.teiid.jdbc.TeiidDriver"); Connection connection = null; try { connection = DriverManager.getConnection(url, "abc", "@abc12345"); // Execute SP CallableStatement statement = connection .prepareCall("call Companys.dbo.GetEmployeeDetails()"); statement.execute(); ResultSet results = statement.getResultSet(); while (results.next()) { System.out.println(results.getString(1) + "-"+ results.getString(2)); } results.close(); statement.close(); //Execute Query String sql = "select firstname, lastname from Company "; Statement statement2 = connection.createStatement(); ResultSet results2 = statement2.executeQuery(sql); while (results2.next()) { System.out.println(results2.getString(1) + "-"+ results2.getString(2)); } results2.close(); statement2.close(); } catch (SQLException e) { e.printStackTrace(); throw e; } finally { try { connection.close(); } catch (SQLException e1) { e1.printStackTrace(); }
The exception is below("Company " in the exception the table name that I am trying to query)
org.teiid.jdbc.TeiidSQLException: Remote org.teiid.api.exception.query.QueryResolverException: Group does not exist: Company
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:135)
at org.teiid.jdbc.TeiidSQLException.create(TeiidSQLException.java:71)
at org.teiid.jdbc.StatementImpl.postReceiveResults(StatementImpl.java:694)
at org.teiid.jdbc.StatementImpl.access$100(StatementImpl.java:64)
at org.teiid.jdbc.StatementImpl$2.onCompletion(StatementImpl.java:533)
at org.teiid.client.util.ResultsFuture.done(ResultsFuture.java:135)
at org.teiid.client.util.ResultsFuture.access$200(ResultsFuture.java:40)
at org.teiid.client.util.ResultsFuture$1.receiveResults(ResultsFuture.java:79)
at org.teiid.net.socket.SocketServerInstanceImpl.receivedMessage(SocketServerInstanceImpl.java:269)
at org.teiid.net.socket.SocketServerInstanceImpl.read(SocketServerInstanceImpl.java:307)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:606)
at org.teiid.net.socket.SocketServerConnectionFactory$ShutdownHandler.invoke(SocketServerConnectionFactory.java:98)
at com.sun.proxy.$Proxy1.read(Unknown Source)
at org.teiid.net.socket.SocketServerInstanceImpl$RemoteInvocationHandler$1.get(SocketServerInstanceImpl.java:406)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:542)
at org.teiid.jdbc.StatementImpl.executeSql(StatementImpl.java:395)
at org.teiid.jdbc.StatementImpl.executeQuery(StatementImpl.java:329)
at teiidConnector.executeDynamicSPs(teiidConnector.java:252)
at teiidConnector.main(teiidConnector.java:23)
for refrence the dynamic VDB that I used is
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="CompanySP" version="1"> <description>CompanySP</description> <property name="UseConnectionMetadata" value="true" /> <model name="Employees"> <property name="importer.useFullSchemaName" value="true" /> <property name="importer.importProcedures" value="true" /> <property name="importer.procedureNamePattern" value="GetEmployeeDetails" /> <source name="CompanySP-sql-connector" translator-name="sqlserver" connection-jndi-name="java:jboss/datasources/CompanysSPDS" /> <metadata type="NATIVE"/> <metadata type="DDL"><![CDATA[ CREATE FOREIGN PROCEDURE GetEmployeeDetails23() RETURNS TABLE (Xfirstname string OPTIONS (NAMEINSOURCE '"firstname"',NATIVE_TYPE 'nvarchar'), lastname string OPTIONS (NAMEINSOURCE '"lastname"', NATIVE_TYPE 'nvarchar')) OPTIONS (NAMEINSOURCE '"Companys"."dbo"."GetEmployeeDetails"') ]]> </metadata> </model> </vdb>
Thanks
Anil
-
3. Re: Dynamic VDB and Stored Procedure approach
rareddy Jun 11, 2015 3:10 PM (in response to anilnair)Anil,
Sorry, I gave the latest 8.11 based syntax, you may not using that yet. which version of Teiid are you using? You may need to adjust as
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <vdb name="CompanySP" version="1"> <description>CompanySP</description> <property name="UseConnectionMetadata" value="true" /> <model name="Employees"> <property name="importer.useFullSchemaName" value="true" /> <property name="importer.importProcedures" value="true" /> <property name="importer.procedureNamePattern" value="GetEmployeeDetails" /> <source name="CompanySP-sql-connector" translator-name="sqlserver" connection-jndi-name="java:jboss/datasources/CompanysSPDS" /> <metadata type="NATIVE,DDL"><![CDATA[ CREATE FOREIGN PROCEDURE GetEmployeeDetails23() RETURNS TABLE (Xfirstname string OPTIONS (NAMEINSOURCE '"firstname"',NATIVE_TYPE 'nvarchar'), lastname string OPTIONS (NAMEINSOURCE '"lastname"', NATIVE_TYPE 'nvarchar')) OPTIONS (NAMEINSOURCE '"Companys"."dbo"."GetEmployeeDetails"') ]]> </metadata> </model> </vdb>
or download the latest 8.11 CR1 and use previous one.
Ideally you dot want to define the DDL if you can import automatically, but I know you had issues importing from your previous thread. I was trying to weed out the the issue with JTDS driver change
Ramesh..
-
4. Re: Dynamic VDB and Stored Procedure approach
anilnair Jun 11, 2015 5:34 PM (in response to rareddy)No worries I should have specified the version in the first place
Thank you Ramesh.
I am using teiid 8.10.1 and JTDS driver (jtds-1.3.1)
Yes even I was under the assumption that all the stored procedures would be imported automatically
With the VDB in your previous post and with the teiid client I would only be able to query the stored procedure where as if I need to query tables then I would have to create another model.
And with the JTDS driver issue yes you might be right. with this VDB when i see the metadata I do not see any user tables getting populated(can see system tables though)
Thanks
Anil
-
5. Re: Dynamic VDB and Stored Procedure approach
rareddy Jun 12, 2015 7:08 PM (in response to anilnair)Anil,
Another model should NOT be required, there is no valid reason for the tables to NOT to show up using the above way and show up in separate model. You can turn on DEBUG on context "org.teiid.CONNECTOR", and you should see messages like
JDBCMetadataProcessor - Importing tables
you can also set "importer.SchemaPatten" to your schema name, to avoid loading the system schema.
If JTDS is behaving this way, try the native one and see if you can fix with that. You can also log issue with JTDS folks that procedure metadata is incomplete with JTDS driver, hopefully they will fix it.
Ramesh..