7 Replies Latest reply on Jun 8, 2015 7:55 PM by anilnair

    Sql server StoredProcedure

    anilnair

      Hi All,

      I have a VDB where I have imported some table and storeprocedures from sql server.(This was done through designer 9.1  pointing to teiid instance 8.10.1)

      The stored procedure that i have imported is a simple one which does not accept any parameter and returns a resultset.

      Now after I deploy the VDB I am able to query the tables but when I call the stored procedure  does not exist.(This was done using teiid jdbc driver 8.10.1)

      1. I can see the stored procedure in my model though the storedprocedure name got suffixed with _1(as shown in the image)

      2. is there any configuration that i would have to do once I import the stored procedure from sql server inorder to make it visible and handle the result set it returns

      Thanks

      Anil.

        • 1. Re: Sql server StoredProcedure
          shawkins

          > I can see the stored procedure in my model though the storedprocedure name got suffixed with _1(as shown in the image)

           

          Did you set the useProcedureSpecificName option?  That is generally not needed and could be why you are getting the _1 suffix.  If not then you'll want to check the metadata for sql server directly in a db explorer view, squirrel, etc.

           

          > is there any configuration that i would have to do once I import the stored procedure from sql server inorder to make it visible and handle the result set it returns

           

          The importer is looking for the result set columns in the DatabaseMetaData getProcedureColumns.  If none are reported by sql server, then they will have to be added manually.

          1 of 1 people found this helpful
          • 2. Re: Sql server StoredProcedure
            anilnair

            Thank you Steven for the reply.

             

            The image that i had  attached while importing objects from Sql server that's when it adds a suffix _1.

            So now after selecting "Use fully Qualified Names" option during import I see the names  of the storedprocedure like shown in the attached image.

            still am confused with the name it takes while importing like in this case it takes the

            Name:Companys.dbo.GetEmployeeDetails;1

            Name In Source :"Companys"."dbo"."GetEmployeeDetails;1"

            also I see this storedprocedure marked with a Icon fx.

            I am using java program to connect to this VDB and the client code looks something like this

             

            CallableStatement statement = connection.prepareCall("{call GetEmployeeDetails()}");

            statement.execute();

            ResultSet results = statement.getResultSet();

            While (results.next()) {

            System.out.println(results.getString(1) + "-"+  results.getString(2));

            }

            results.close();

            statement.close();

             

            The result is

            org.teiid.jdbc.TeiidSQLException: TEIID30357 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30357 GetEmployeeDetails does not exist.

            So I am not sure what name should I be using in the code to execute the storedprocedure.

            The second thing is you mentioned

            The importer is looking for the result set columns in the DatabaseMetaData getProcedureColumns.  If none are reported by sql server, then they will have to be added manually.

            I imported the storedprocedure from Sql server and  the text for the storedprocedure is below

            Create proc [dbo].[GetEmployeeDetails]

            AS

            BEGIN

            select firstname,lastname from Employees

            END

            So once i imported I do not see any result set exposed(can be seen in the same image).So according to the comments it means that I would need to manually add these. Is there any example or documentation on how to add these when not exposed.

            attach2.png

            • 3. Re: Sql server StoredProcedure
              rareddy

              That looks like regression somewhere, which version of Designer are you using?

               

              As an alternative, you can try using the "Teiid Connection Importer" in the Designer for importing the model and see if that is better. How you are executing the stored procedure looks correct.

              • 4. Re: Sql server StoredProcedure
                anilnair

                I am using the Teiid Designer 9.1 beta.

                I  have imported the database objects using the importer(from sql server)  and after Importing I can see the stored procedure name in the DataSource section of the xmi file.But the column names corresponding the storedprocedure are missing

                Like I said previously when i do a preview data (designer) this is the exception I get

                 

                select * from ( exec "Companys"."Companys"."dbo"."GetEmployeeDetails1;1"() ) AS X_X

                org.teiid.runtime.client.TeiidClientException: org.teiid.api.exception.query.QueryValidatorException: Remote TEIID30492 Command must project at least one symbol

                I think as per steven :

                The importer is looking for the result set columns in the DatabaseMetaData getProcedureColumns.  If none are reported by sql server, then they will have to be added manually.

                I saw similar thread on resultset not getting mapped Mapping stored procedures from SQL Data Source

                So It would be of great I get pointers to some examples or documentation for mapping resultsets while using stored procedures from SQL server.

                Thanks

                Anil

                • 5. Re: Sql server StoredProcedure
                  rareddy

                  Anil,

                   

                  Basically you can right click on the imported stored procedure and add child->resultset, and add the expected columns in the resultset matching return columns from physical source.

                   

                  Ramesh..

                  • 6. Re: Sql server StoredProcedure
                    anilnair

                    Hi  Ramesh,

                    thank you for the help.  I went ahead and added columns that was expected out of the stored procedure. I have given the "Name in Source" for the column name as that is in the.

                    attach4.png

                    the confusion  I have is what should be the name is source for the result be

                    attach5.png

                     

                    Once again thank your help.

                    Thanks

                    Anil

                    • 7. Re: Sql server StoredProcedure
                      anilnair

                      Thank you Ramesh and Steven.

                      I was able to get it working ..