Help with SQL delete syntax on an Ignite Cache

classic Classic list List threaded Threaded
2 messages Options
limabean limabean
Reply | Threaded
Open this post in threaded view
|

Help with SQL delete syntax on an Ignite Cache

This post has NOT been accepted by the mailing list yet.
Hi,

I am a new Ignite user and appreciate some advice on how to do a SQL delete.
Thank you in advance for the help.


I need to run something like this:
"delete from PretendPerson where companyId = ?"

When I attempt to run a delete statement, I end up with a peculiar error message in the stack trace:

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT ""PretendPerson"".PRETENDPERSON._KEY, ""PretendPerson"".PRETENDPERSON._VAL FROM ""PretendPerson"".PRETENDPERSON WHERE DELETE FROM[*] PRETENDPERSON WHERE COMPANYID = ? "; SQL statement:
SELECT "PretendPerson".PretendPerson._key, "PretendPerson".PretendPerson._val FROM "PretendPerson".PretendPerson WHERE delete from PretendPerson where companyId = ? [42000-175]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)


Here is a method from a sample program I put together to test this:

    private void doWork() {
        try {
            Ignite ignite = Ignition.start(".../examples/config/example-cache.xml");

            CacheConfiguration<Long, PretendPerson> cfg = new CacheConfiguration<>("PretendPerson");

            cfg.setIndexedTypes(Long.class, PretendPerson.class);
            IgniteCache<Long, PretendPerson> cache = ignite.getOrCreateCache(cfg);

            // populate test data
            // this works fine
            populate(cache);

            // do a simple test and get 1 item from the cache
            // this works fine
            System.out.println("One item from cache: " + cache.get(1L).name);

            // select some data
            // this works fine
            SqlQuery<Long, PretendPerson> qry1 =
                    new SqlQuery<>(PretendPerson.class, "select * from PretendPerson where companyId = ?");
            qry1.setArgs(1L);
            List<Cache.Entry<Long, PretendPerson>> res1 = cache.query(qry1).getAll();

            Iterator k = res1.iterator();
            while (k.hasNext()) {
                Cache.Entry<Long, PretendPerson>  cacheEntry = (Cache.Entry<Long, PretendPerson>)k.next();
                PretendPerson aPerson = cacheEntry.getValue();
                System.out.println("Person: " + (String)aPerson.name);
            }

            // pause to prevent stack trace from overlapping the above print out
            Thread.sleep(2000);

             // this is not working
            // delete some data
            SqlQuery<Long, PretendPerson> qry4 =
                    new SqlQuery<>(PretendPerson.class, "delete from PretendPerson where companyId = ?");

            qry4.setArgs(1L);
            cache.query(qry4);
        }
        catch (Exception e) {
            e.printStackTrace();
        }
    }
vkulichenko vkulichenko
Reply | Threaded
Open this post in threaded view
|

Re: Help with SQL delete syntax on an Ignite Cache

Hi,

Can you please properly subscribe to the mailing list so that the community can receive email notifications? Here is the instruction: http://apache-ignite-users.70518.x6.nabble.com/mailing_list/MailingListOptions.jtp?forum=1

limabean wrote
I need to run something like this:
"delete from PretendPerson where companyId = ?"

When I attempt to run a delete statement, I end up with a peculiar error message in the stack trace:

Caused by: org.h2.jdbc.JdbcSQLException: Syntax error in SQL statement "SELECT ""PretendPerson"".PRETENDPERSON._KEY, ""PretendPerson"".PRETENDPERSON._VAL FROM ""PretendPerson"".PRETENDPERSON WHERE DELETE FROM[*] PRETENDPERSON WHERE COMPANYID = ? "; SQL statement:
SELECT "PretendPerson".PretendPerson._key, "PretendPerson".PretendPerson._val FROM "PretendPerson".PretendPerson WHERE delete from PretendPerson where companyId = ? [42000-175]
        at org.h2.message.DbException.getJdbcSQLException(DbException.java:332)
SQL updates are currently not supported, but this is on the roadmap. For now you should use IgniteCache API to remove entries (see 'remove' and 'removeAll' entries).

The exception is indeed confusing, I created a ticket to fix it: https://issues.apache.org/jira/browse/IGNITE-2928

-Val