UPDATE sql query in ignite

classic Classic list List threaded Threaded
7 messages Options
tusharnakra tusharnakra
Reply | Threaded
Open this post in threaded view
|

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
pgarg pgarg
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE sql query in ignite

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-
tusharnakra tusharnakra
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE sql query in ignite

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?
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE sql query in ignite

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
tusharnakra tusharnakra
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE sql query in ignite

This post was updated on .
Thanks! 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?
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE sql query in ignite

Write-through is done automatically if you enable it. Refer to [1] for more details.

[1] https://apacheignite.readme.io/docs/persistent-store

-Val
pgarg pgarg
Reply | Threaded
Open this post in threaded view
|

Re: UPDATE sql query in ignite

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 <[hidden email]> 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.