12 Replies Latest reply on Jul 30, 2014 3:51 PM by VISHNU SWAMINATHAN

    Passing payload using ODBC

    Rajni Kumari Newbie

      Hi,

       

       

      I am connecting teiid through ODBC protocol, I was able to make connection using following two appraches but facing issues setting the payload -

       

       

      Through Java -

       

       

      The Statement object we get while connecting through Jdbc-Odbc Protocol, is Jdbc-Odbc statement.I tried casting the statement object to StatementImpl and also to TeiidStatement,but it fails.I am not able to set the PayLoad to Statement.

       

       

       

       

      Through .Net - 

      We have a command here and not sure how to set payload in OdbcCommand?

       

       

      Please let me know if you have any suggestion on how to set the payload.

       

       

      Thanks

      Rajni

        • 1. Re: Passing payload using ODBC
          Ramesh Reddy Master

            The Statement object we get while connecting through Jdbc-Odbc Protocol, is Jdbc-Odbc statement.I tried casting the statement object to StatementImpl and also to TeiidStatement,but it fails.I am not able to set the PayLoad to Statement.

          Why use the ODBC here? try Teiid JDBC driver directly, then you can cast as TeiidStatement. See https://docs.jboss.org/author/display/TEIID/Connecting+to+a+Teiid+Server for JDBC use.

           

           

          We have a command here and not sure how to set payload in OdbcCommand?

          You can use "SET" command through ODBC for this https://docs.jboss.org/author/display/TEIID/SET+Statement

           

           

          Ramesh..

          1 of 1 people found this helpful
          • 2. Re: Passing payload using ODBC
            Rajni Kumari Newbie

            Thanks Ramesh, I was able to set the payload on command on .NET using following code:

                

            OdbcCommand command = new OdbcCommand("SET PAYLOAD x y", conn);

            command.ExecuteNonQuery();

            command.CommandText = "Select * from employee";

            OdbcDataReader dataReader = command.ExecuteReader();

             

            "SET PAYLOAD x y" also worked in JDBC-ODBC Connection.

             

            Statement stmt = connection.createStatement();

            stmt.execute("SET PAYLOAD x y");

            ResultSet result = stmt.executeQuery("Select * from employee");

            • 3. Re: Passing payload using ODBC
              Steven Hawkins Master

              I should mention in 8.4 there are also session variables accessible via system functions: https://issues.jboss.org/browse/TEIID-1092

              • 4. Re: Passing payload using ODBC
                Amit Jahagirdar Newbie

                Hi,

                What is the difference between setting payload via statement as compared to using session variables?

                Session variables are termed as global variables,what is the scope for these?

                Also like we have SET PAYLOAD x y ,I also see Teiid_Session_Set,Teiid_Session_Get as scalar functions.

                How and what is the use of these functions...are they kind of specific to stored procedure.

                 

                Would be great to have answers for all of them.

                 

                Thanks

                Amit

                • 5. Re: Passing payload using ODBC
                  Steven Hawkins Master

                  > What is the difference between setting payload via statement as compared to using session variables?

                   

                  The payload mechanism predates serssion variables, but they are conceptually similar.  The payload mechanism is logicallly maintained on the client side (which for odbc is effectively the server) and associates a serializable object that is retrievable at the translator level.

                   

                  > Session variables are termed as global variables,what is the scope for these?

                   

                  That's from the title of the jira issue, but as you see in the comments and the user request did not need something with greater than a session scope, thus session variables.  It's of course documented that way in the reference.

                   

                  > How and what is the use of these functions...

                   

                  See the reference.   They get and set session variable values. 

                   

                  > are they kind of specific to stored procedure.

                   

                  No.

                   

                  Steve

                  • 6. Re: Passing payload using ODBC
                    Amit Jahagirdar Newbie

                    Hi,

                    Inline with the above mentioned discussion,

                    I would like to know how do we do an unset for the payload.

                    I want to switch the payload values differently say Set Payload x y,then Set Payload x z

                    and also Set Payload x null.

                    e:g

                    String payload = "SET PAYLOAD " + "Key"+ " " + null ;

                    Query query = entityManager.createNativeQuery(payload);

                    query.executeUpdate();

                    Here the null value is being set as "null".

                    ===================================

                    How do we set the null value as in sql.NULL.

                     

                    Translator side:

                    Command payload being a properties object,has the keys/values as String and hence I am getting a "null" instead a simple null value,so have to do equals instead of ==.

                     

                    ***Tried with a Teiid prepared statement on teiidJdbcConnection, ps.setString(1, Type.NULL)...ps.executeUpdate()... but then the payload is not gettin set in the commandPayload within executionContext.

                     

                    Would be great to get some help on this.

                     

                    Thanks

                    Amit

                     

                    Message was edited by: Amit Jahagirdar

                    • 7. Re: Passing payload using ODBC
                      Steven Hawkins Master

                      Amit,

                      The SET syntax is limited and does not accept arbitrary expressions.  The closest you could get without an enhancement would be the empty string: SET PAYLOAD key ''

                       

                      The prepared form will not work since ? will not be recognized as a bind expression.

                       

                      Session variable functions, which are manipulated through standard statements, don't suffer from these issues.  It's valid to do:

                       

                      select teiid_session_set('key', null)

                       

                      or use preparedstatements etc.

                       

                      Steve

                      • 8. Re: Passing payload using ODBC
                        Amit Jahagirdar Newbie

                        A query to this is :

                        In case of multi threaded environment ,is the session thread safe? I am getting confused due to the description which says session variables have  "Global state".

                        What is the scope of session variables?

                        Is it per request? or per execution context? or per connection-one session?

                         

                        Thanks

                        Amit

                        • 9. Re: Passing payload using ODBC
                          Steven Hawkins Master

                          > In case of multi threaded environment ,is the session thread safe?

                           

                          From a client perspective a session is tied to a connection and should only be used in a thread-safe manner.  On the server-side the backing map for the variable storage is thread-safe.

                           

                          > I am getting confused due to the description which says session variables have  "Global state".

                           

                          Again you are just looking at the JIRA title/initial description and not the docs or the JIRA resolution comments.  The issue was logged as a request for "global vairables" however the customer use cases did not actually require variables at a global scope, thus as you follow through to the resolution the issue actually added session scoped variables.  I'll go ahead and change the title of the JIRA.

                           

                          > What is the scope of session variables?

                           

                          The session, which typically is tied to the lifetime of a single connection.  There are some caveats though in a cluster.

                           

                          Steve

                          • 10. Re: Passing payload using ODBC
                            VISHNU SWAMINATHAN Newbie

                            Steve,

                            My question is related to the topic that was discussed and hence iam posting it here.

                             

                            You had mentioned we can make use of system functions Teiid_session_set and Teiid_session_get to set the session variables. Iam able to use these functions with a JDBC client successfully, but i have trouble with ODBC client.

                             

                            The below statement fails with the error ERROR [38000] ERROR: unknown datatype failed to convert, org.teiid.jdbc.TeiidSQLException: unknown datatype failed to convert;

                             

                            OdbcCommand command_1 = new OdbcCommand("SELECT teiid_session_set('x','y')", connection);

                            command_1.ExecuteNonQuery();

                             

                            We are using Teiid 8.4. I was able to make other system functions like CommandPayLoad('variable'), CURRENT_DATABASE() to work with ODBC.

                             

                            Are the functions Teiid_session_set and Teiid_session_get supported for ODBC or should we go with "set payload x y"?

                             

                            Thanks,

                            Vishnu

                            • 11. Re: Passing payload using ODBC
                              Steven Hawkins Master

                              The issue is that our ODBC layer does not support the serialization of OBJECT/UNKNOWN values, which is what is returned by teiid_session_set.  The workaround is to use a cast/convert to make the type explicitly known - SELECT cast(teiid_session_set('x', 'y') as string)