Hi All,
I just wanted to know is it possible to run complex queries in the Terracotta caches , like nested queries.
For example the below query works fine,
select * from CacheManager1.Cache1 where ((row_id>=1) and (row_id<=100));
Where row_id is a field in Cache1 of type Integer.
Now we want to execute complex nested queries like,
Select * from CacheManager1.Cache1 where row_id = (select loc_id from CacheManager1.Cache1 where city_code=‘DEL’);
We are not able to execute the queries. Not sure if any syntax error is happening. Please share your inputs on the same.
Thanks and Regards
Abir Banerjee
This is the query that I am trying to run.
qry1 = queryManager.createQuery(“select * from “+cacheName+” where field1 = (select field1 from “+cacheName+” where ((field14=‘ME’) and (field5=‘AUGUSTA’) and (field2=‘AUGUSTA TWS’)))”);
Getting the below error,
com.wm.app.b2b.server.ServiceException: net.sf.ehcache.search.SearchException: net.sf.ehcache.search.parser.ParseException: Encountered " “select” "select “” at line 1, column 51.
Was expecting one of:
“(” …
“(bool)” …
“(byte)” …
“(int)” …
“(short)” …
“(long)” …
“(float)” …
“(double)” …
“(date)” …
“(sqldate)” …
“(char)” …
…
…
Try something like below:
select * from Person where age = 11 // age is of type int
select * from Person where name = ‘Mary’ // name is of type String
select * from Person where gender = (char)‘M’
select * from Person where isMale = (bool)‘true’
select * from Person where age = (byte)11
select * from Person where age = (short)11
select * from Person where age = (long)11
select * from Person where age = (double)11.1
select * from Person where birthDate = (date)‘2003-01-10T14:25:22’
Thanks Mahesh , but that is not the requirement. We have already used this kind of queries. We need to use subqueries for our requirement.
Regards
Abir
Running queries in terracotta cache on production environment is not recommended. This will affect the performance drastically.
Senthil
Hi Senthil,
We have this requirement to use Terracotta APIs to put and query the data into cache. We have Big Memory installed.
Hence performance will not be an issue. The system cache managers and public cache managers have separate memory allocated. All queries are running fine except the complex sub queries. That’s why wanted to know whether the terracotta sql language at all supports nested sub queries or not.
Thanks and Regards
Abir Banerjee
Abir,
sorry, but I am repeating my point.
With the current releases of Terracotta, in production environment executing ‘query’ is ‘not recommended’ due to performance bottlenecks. I did not mean anything on get and put. They work great! Its only about ‘search’ no matter what infrastructure that you have allocated.
-Senthil
Do not expect all the RDBMS SQL capabilities to work in BMSQL !!! 
BMSQL is an option for lighter and easy use but not full and recommeneded!
Hi All,
I am using TSA - Version 1.4.1.2.18 with IS - 9.8
We have two nodes in cluster(Active-Passive). From few days onward everyday my one node of terracotta left the cluster and after few minutes again rejoined.Ia m not getting the reason behind it.
I am getting below errors in terracotta - GC :
com.tc.net.protocol.transport.ServerStackProvider - “Server Cannot Reconnect. Connection attempts from the Terracotta node at 10.67.16.74:49498 are being rejected by the Terracotta server array. Reason: Stack for ConnectionID(0.96208952d7384004b83a27f911638a1b.c3c0465c-5663-4be0-87d0-75be7b2814e3-159e47ee5cc.USER) not found. Reconnect window is probably still open at the other end.”
In wrapper- tsa :
ERROR - com.tc.net.protocol.transport.TransportHandshakeErrorContext: com.tc.net.protocol.transport.TransportHandshakeErrorContext: "Server Cannot Reconnect. Connection attempts from the Terracotta node at 10.67.16.78:43960 are being rejected by the Terracotta server array. Reason: Stack for ConnectionID(8.54e5243e2b274c2cb826a5f9be6d26fd.29847334-0986-4b56-ae49-7bc4003da50e-152195a72be.USER) not found. Reconnect window is probably still open at the other end."Message Class: com.tc.net.protocol.transport.TransportMessageImpl
In terracotta-server.log
com.tc.net.protocol.transport.ServerStackProvider - “Server Cannot Reconnect. Connection attempts from the Terracotta node at 10.67.16.74:49498 are being rejected by the Terracotta server array. Reason: Stack for ConnectionID(0.96208952d7384004b83a27f911638a1b.c3c0465c-5663-4be0-87d0-75be7b2814e3-159e47ee5cc.USER) not found. Reconnect window is probably still open at the other end.”
Please provide me solution for that.
Regards,
Masroor Alam
Masroor,
You should initiate a new thread than writing on an old thread with diff context.
-Senthil