5 Replies Latest reply on Jun 12, 2015 7:08 PM by Ramesh Reddy

    Dynamic VDB and Stored Procedure approach

    Anil Nair Newbie

      Hi All,

      As mentioned in my first post I am in the process of evaluation of teiid for my organization and hence trying out various approaches of using teiid.

      The question this time is around dynamic VDB and  database stored procedure(Sql Server)

      I have a simple Store procedure in SQL server with the following texts

      CREATE proc [dbo].[GetEmployeeDetails]

      AS

      BEGIN

      select firstname as firstname,lastname as lastname from Employees

      END

      GO

      I have create a dynamic VDB as shown below in order to call the above storedprocedure

      <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
      <vdb name="CompanySP" version="1">
          <description>CompanySP</description>
          <property name="UseConnectionMetadata" value="true"/>
          <model name="Companys">
              <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="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>
        <model name="Employees">
              <property name="importer.useFullSchemaName" value="true"/>
        <source name="CompanySP-sql-connector" translator-name="sqlserver" connection-jndi-name="java:jboss/datasources/CompanysSPDS"/>
        </model>
       </vdb>
      

      The Question is

      1. Is this the right approach for calling a stored procedure using Dynamic VDBs

      2. Does one mode cater to only one type of meta data. In the above scenario I had to use two models, first one for querying the stored procedure and the second model for other tables or is there any better way for this.

      Thanks

      Anil

        • 1. Re: Dynamic VDB and Stored Procedure approach
          Ramesh Reddy Master

          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
            Anil Nair Newbie

            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 exception

            The 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
              Ramesh Reddy Master

              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
                Anil Nair Newbie

                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
                  Ramesh Reddy Master

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