1 of 1 people found this helpful
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
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.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");
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.
> 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.
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.
String payload = "SET PAYLOAD " + "Key"+ " " + null ;
Query query = entityManager.createNativeQuery(payload);
Here the null value is being set as "null".
How do we set the null value as in sql.NULL.
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.
Message was edited by: Amit Jahagirdar
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.
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?
> 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.
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  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);
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"?
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)
Thanks Steve. That works.