7 Replies Latest reply on Jan 2, 2015 9:24 AM by Steven Hawkins

    how to enable direct procedure executions for sqlserver

    lautaro molinero Newbie

      Hi,

       

      I'm trying to override the JDBC translator to make it support direct procedure executions.

       

      first I tried overriding the translator on a designer vdb. but when i connect to the vdb using the database development tool, i don't see the native procedure in the database.

       

      nativevdb.png

       

      so then i wen't with using a dynamic vdb to define the translator, but i also cannot see the native procedure.

      nativedvdb.png

       

      as you see, no native procedure.

      nonative.png

       

       

      I know native procedures work because i've created my own translator for another thing and used them.

       

      What am I doing wrong??

       

      thanks.


      P.S: I'm connecting to mssqlserver using jdts.

        • 1. Re: how to enable direct procedure executions for sqlserver
          Steven Hawkins Master

          I don't know if Designer has built-in understanding of the direct procedure.  The workarounds would be to manually add the procedure to the target model, or to use the Teiid connection importer.

          • 2. Re: how to enable direct procedure executions for sqlserver
            lautaro molinero Newbie

            Thanks Steve, I know that the designer does not see the native procedure, and also know that i should add it manually to make it visible.

             

             

            but the issue is not in the designer, the VDB schema ( conencting using jdbc) does not include the procedure.  I think it should.

             

            by the way, i added the procedure in the designer and it throws "native" does not exist when a run a view. that makes sense because native is not define in the vdb metadata witch is the issue.

            • 3. Re: how to enable direct procedure executions for sqlserver
              Steven Hawkins Master

              > but the issue is not in the designer, the VDB schema ( conencting using jdbc) does not include the procedure.  I think it should.

               

              Teiid does not inject the source metadata unless a dynamic vdb is used.

               

              > by the way, i added the procedure in the designer and it throws "native" does not exist when a run a view. that makes sense because native is not define in the vdb metadata witch is the issue.

               

              Can you elaborate more here?  If you added the procedure it shouldn't say that it doesn't exist.

              • 4. Re: how to enable direct procedure executions for sqlserver
                lautaro molinero Newbie

                Steve, i'll try to clarify.

                 

                >>but the issue is not in the designer, the VDB schema ( conencting using jdbc) does not include the procedure.  I think it should.

                 

                >Teiid does not inject the source metadata unless a dynamic vdb is used.

                 

                OK, i didn't know that. I did use a dynamic vdb and got the same problem. but i'll re-check if i'm screwing something up.

                 

                Let me check if I understood correctly:

                 

                I should:

                1)  create a source model from an sqlserver database and include only one table.

                2) add a procedure called "native" to that source model, that receives a string as parameter and returns a  one column result set typed object.

                3)create a View model that executes the native procedure i defined in point 2.

                 

                once i deploy the vdb expected results would be:

                 

                4) if a query the view defined in point 3, it should work

                5) if a browse the vdb schema i should see the native procedure I created in the source model.

                6) if I run the native procedure by itself, not using the defined view, should it work?

                 

                 

                 

                 

                >> by the way, i added the procedure in the designer and it throws "native" does not exist when a run a view. that makes sense because native is not define in the vdb metadata witch is the issue.

                 

                >Can you elaborate more here?  If you added the procedure it shouldn't say that it doesn't exist.

                 

                 

                Sorry i wasn't clear. what i meant was that i defined the procedure in the source model, i could use it with no problems in the designer, but when i deployed de vdb and queried the view that called my native procedure. I got a "native does not exists" error.

                I'll try it again, maybe a did something wrong.

                please confirm that my example above is the right way to go.

                 

                Thank you very much for your help.

                • 5. Re: how to enable direct procedure executions for sqlserver
                  Steven Hawkins Master

                  What you describe above should work.  If there is a metadata entry in designer and it seems to work from designer, there is no reason why it should not work on the server.

                  • 6. Re: how to enable direct procedure executions for sqlserver
                    lautaro molinero Newbie

                    It works, the problem was that i was using the wrong property name, should have used SupportsNativeQueries.

                     

                    Is there a way to bypass teiid 4000 character limit for string variables???

                    • 7. Re: how to enable direct procedure executions for sqlserver
                      Steven Hawkins Master

                      > Is there a way to bypass teiid 4000 character limit for string variables???

                       

                      You can either use a clob value or use the system property org.teiid.maxStringLength - System Properties - Teiid 8.10 (draft) - Project Documentation Editor - but keep in mind that increasing this value can significantly affect memory usage.