Re: SQL update query with Thin Client not working?

[Thomas Kramer]

Thanks a lot. That makes it very clear!


On 10.06.22 13:27, Pavel Tupitsyn wrote:

To be honest, that was surprising to me too. Not documented anywhere.
I've filed a ticket to rectify this [1]

> how would I get the update count for this type of SQL queries?

Update count is returned as the only value in the only row:

        SqlFieldsQuery query = new SqlFieldsQuery("UPDATE Person SET
age=age+1");
        long updatedCount = (long)
personCache.query(query).getAll().get(0).get(0);
        System.out.println("Updated " + updatedCount + " rows");


[1] https://issues.apache.org/jira/browse/IGNITE-17153

On Fri, Jun 10, 2022 at 12:15 PM <don.tequ...@gmx.de> wrote:

    Great, that solved it, thank you! Is this by design? I'm asking
    because it's not that clearly documented, or did I miss this?

    Btw, how would I get the update count for this type of SQL queries?



    On 10.06.22 10:22, Pavel Tupitsyn wrote:

    Client query cursor is lazy, it does not perform a query until
    you iterate or call getAll.
    The code works as expected when I do this:
    *personCache.query(query).getAll()*

    On Fri, Jun 10, 2022 at 1:35 AM <don.tequ...@gmx.de> wrote:

        Hi Igniters,

        I'm wondering if a SQL update statement must be different
        when used from a ThinClient? It doesn't seem to have any
        effect for me, nor does it output an error.

        For the below code I get this output:

        Row: [1, Foo, 2]
        Row: [2, Bar, 4]
        Row: [1, Foo, 3]
        Row: [2, Bar, 5]
        Row: [1, Foo, 3]
        Row: [2, Bar, 5]
        Row: [1, Foo, 3]
        Row: [2, Bar, 5]

        But expected should be (difference marked in red):

        Row: [1, Foo, 2]
        Row: [2, Bar, 4]
        Row: [1, Foo, 3]
        Row: [2, Bar, 5]
        Row: [1, Foo, 3]
        Row: [2, Bar, 5]
        Row: [1, Foo, 4]
        Row: [2, Bar, 6]

        Code:

        public class TestThinClientSQL
        {
            static class Person implements Serializable
            {
                @QuerySqlField
                private String name;

                @QuerySqlField
                private int    age;

                public Person withName(String name)
                {
        this.name <http://this.name> = name;
                    return this;
                }

                public Person withAge(int age)
                {
                    this.age = age;
                    return this;
                }
            }

            public static void main(String[] args)
            {
                try (Ignite igniteServer = Ignition.start())
                {
                    CacheConfiguration<Long, Person> cacheCfg = new
        CacheConfiguration<Long, Person>().setName("Person");
                    cacheCfg.setIndexedTypes(Long.class, Person.class);
                    IgniteCache<Long, Person> personCache =
        igniteServer.createCache(cacheCfg);
                    personCache.put(1L, new
        Person().withName("Foo").withAge(2));
                    personCache.put(2L, new
        Person().withName("Bar").withAge(4));

                    showContent(personCache);

                    updateAge(personCache);

                    showContent(personCache);

                    IgniteClient thinClient      =
        Ignition.startClient(new
        ClientConfiguration().setAddresses("127.0.0.1"));
                    ClientCache<Long, Person> personCacheThin =
        thinClient.cache("Person");

                    showContent(personCacheThin);

                    updateAge(personCacheThin);

                    showContent(personCacheThin);
                }
            }

            private static void updateAge(IgniteCache<Long, Person>
        personCache)
            {
                SqlFieldsQuery query = new SqlFieldsQuery("UPDATE
        Person SET age=age+1");
                personCache.query(query);
            }

            private static void showContent(IgniteCache<Long, Person>
        personCache)
            {
                SqlFieldsQuery             query  = new
        SqlFieldsQuery("SELECT _key, name, age FROM Person");
                FieldsQueryCursor<List<?>> cursor =
        personCache.query(query);
                cursor.getAll().forEach(ROW ->
        System.out.println("Row: " + ROW));
            }

            private static void updateAge(ClientCache<Long, Person>
        personCache)
            {
                SqlFieldsQuery query = new SqlFieldsQuery("UPDATE
        Person SET age=age+1");
                personCache.query(query);
            }

            private static void showContent(ClientCache<Long, Person>
        personCache)
            {
                SqlFieldsQuery             query  = new
        SqlFieldsQuery("SELECT _key, name, age FROM Person");
                FieldsQueryCursor<List<?>> cursor =
        personCache.query(query);
                cursor.getAll().forEach(ROW ->
        System.out.println("Row: " + ROW));
            }
        }