You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by tusharnakra <tu...@hotmail.com> on 2016/04/14 19:07:43 UTC
UPDATE sql query in ignite
Hi,
Can we use UPDATE SQL query in ignite cache to update the cache and then
write the update back to the database??
I have the following method:
private static void queryChange() {
IgniteCache<AffinityKey<Long>, Person> cache =
Ignition.ignite().cache("PCache");
// Execute query to get names of all employees.
String sql = "update \"" + ORG_CACHE + "\".Organization set orgname
= ? where orgid = ?";
//"update \"" + ORG_CACHE + "\".Organization set orgname = ?
where exists ( select firstName from Person where Person.orgid =
Organization.orgid and Person.firstName = ?";
QueryCursor<List<?>> cursor1 = cache.query(new
SqlFieldsQuery(sql).setArgs("JPM",300));
// In this particular case each row will have one element with full
name of an employees.
List<List<?>> res = cursor1.getAll();
// Print persons' names and organizations' names.
print("Names of all employees and organizations they belong to:",
res);
}
But, it's giving me the following error:
class org.apache.ignite.IgniteException: Unknown query type: update
"OrgCache".Organization set orgname = ? where orgid = ?
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:657)
at org.apache.ignite.organizat.Demo.queryChange(Demo.java:176)
at org.apache.ignite.organizat.Demo.main(Demo.java:94)
Caused by: class org.apache.ignite.IgniteException: Unknown query type:
update "OrgCache".Organization set orgname = ? where orgid = ?
at
org.apache.ignite.internal.processors.query.GridQueryProcessor.queryTwoStep(GridQueryProcessor.java:811)
at
org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:648)
... 2 more
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/UPDATE-sql-query-in-ignite-tp4180.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: UPDATE sql query in ignite
Posted by Prachi Garg <pg...@gridgain.com>.
You can use IgniteCache.put(key,value) method to store key/value pairs in
cache.
On Fri, Apr 15, 2016 at 11:31 AM, tusharnakra <tu...@hotmail.com>
wrote:
> I think you did not understand my question. I want to add a new value to
> the
> cache, one that is not already present in the database table.
>
> Example:
>
> Right now the cache has the following:
>
> [key=PersonKey [id=2], val=Person [id=2, firstName=Marc, lastName=Robinson,
> orgid=300, salary=12000]]
> [key=PersonKey [id=1], val=Person [id=1, firstName=Roy, lastName=Daniel,
> orgid=200, salary=1000]]
> [key=PersonKey [id=3], val=Person [id=3, firstName=Jacob, lastName=Kimpson,
> orgid=300, salary=2000]]
> [key=PersonKey [id=4], val=Person [id=4, firstName=Caleb, lastName=Hass,
> orgid=100, salary=4000]]
>
>
> And I want to add something like:
> [key=PersonKey [id=5], val=Person [id=5, firstName=Christian,
> lastName=Chon, orgid=400, salary=2000]]
>
> How do I do that??
>
> I know that any updates made to the cache will be written to the database
> with write through, that wasn't my question.
>
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/UPDATE-sql-query-in-ignite-tp4180p4234.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: UPDATE sql query in ignite
Posted by tusharnakra <tu...@hotmail.com>.
I think you did not understand my question. I want to add a new value to the
cache, one that is not already present in the database table.
Example:
Right now the cache has the following:
[key=PersonKey [id=2], val=Person [id=2, firstName=Marc, lastName=Robinson,
orgid=300, salary=12000]]
[key=PersonKey [id=1], val=Person [id=1, firstName=Roy, lastName=Daniel,
orgid=200, salary=1000]]
[key=PersonKey [id=3], val=Person [id=3, firstName=Jacob, lastName=Kimpson,
orgid=300, salary=2000]]
[key=PersonKey [id=4], val=Person [id=4, firstName=Caleb, lastName=Hass,
orgid=100, salary=4000]]
And I want to add something like:
[key=PersonKey [id=5], val=Person [id=5, firstName=Christian,
lastName=Chon, orgid=400, salary=2000]]
How do I do that??
I know that any updates made to the cache will be written to the database
with write through, that wasn't my question.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/UPDATE-sql-query-in-ignite-tp4180p4234.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: UPDATE sql query in ignite
Posted by akritibahal91 <ak...@outlook.com>.
I think you did not understand my question. How do I add a new value in my
cache (i.e., a new row) which is not already present in the database?
Example:
Person Cache right now, has:
[key=PersonKey [id=2], val=Person [id=2, firstName=Marc, lastName=Robinson,
orgid=300, salary=12000]]
Entry [key=PersonKey [id=1], val=Person [id=1, firstName=Roy,
lastName=Daniel, orgid=200, salary=1000]]
Entry [key=PersonKey [id=3], val=Person [id=3, firstName=Jacob,
lastName=Kimpson, orgid=300, salary=2000]]
Entry [key=PersonKey [id=4], val=Person [id=4, firstName=Caleb,
lastName=Hass, orgid=100, salary=4000]]
I want to add a new entry to the cache, something like:
Entry [key=PersonKey [id=5], val=Person [id=5, firstName=Chritian,
lastName=Chon, orgid=400, salary=2000]]
How do I do that?
I know that any updates made to the cache with be written to the database,
so that wasn't my question.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/UPDATE-sql-query-in-ignite-tp4180p4233.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: UPDATE sql query in ignite
Posted by vkulichenko <va...@gmail.com>.
Write-through is done automatically if you enable it. Refer to [1] for more
details.
[1] https://apacheignite.readme.io/docs/persistent-store
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/UPDATE-sql-query-in-ignite-tp4180p4231.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: UPDATE sql query in ignite
Posted by tusharnakra <tu...@hotmail.com>.
Could you tell me how do I do the following:
I want to add a new row to the Person cache,which is currently not present
in the Person table. So, first add it to the cache and then write through to
the table. The columns in the Person table are "id, firstName, lastName,
orgid, salary". How do I do that?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/UPDATE-sql-query-in-ignite-tp4180p4228.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: UPDATE sql query in ignite
Posted by vkulichenko <va...@gmail.com>.
SQL updates are not supported. To update two entries atomically you can do
this within a single transaction. But note that to do this, caches have to
work in TRANSACTIONAL atomicity mode.
-Val
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/UPDATE-sql-query-in-ignite-tp4180p4201.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: UPDATE sql query in ignite
Posted by tusharnakra <tu...@hotmail.com>.
No, I know that. I want to know if we can use UPDATE sql query with ignite or
not?
Because, I want to do something like this: I have a table each in 2
different cache, I want to update some column entry in one table as well as
cache by performing cross-cache sqlqueryfield join. How do I do that?
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/UPDATE-sql-query-in-ignite-tp4180p4198.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: UPDATE sql query in ignite
Posted by pgarg <pg...@gridgain.com>.
To update the database, you need to implement CacheStore.write() and enable
writeThrough mode of CacheConfiguration. CacheStore.write() method is called
whenever IgniteCache.put() method is called. See Cache Store section of
Persistent Store documentation[1] for more information and example code.
[1]
https://apacheignite.readme.io/docs/persistent-store#section-load-write-delete-
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/UPDATE-sql-query-in-ignite-tp4180p4181.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.