You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by do...@gmx.de on 2022/06/09 22:35:09 UTC
SQL update query with Thin Client not working?
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 = 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));
}
}
Re: SQL update query with Thin Client not working?
Posted by Thomas Kramer <do...@gmx.de>.
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 <do...@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 <do...@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));
>> }
>> }
>>
Re: SQL update query with Thin Client not working?
Posted by Pavel Tupitsyn <pt...@apache.org>.
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 <do...@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 <do...@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 = 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));
>> }
>> }
>>
>>
Re: SQL update query with Thin Client not working?
Posted by Pavel Tupitsyn <pt...@apache.org>.
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 <do...@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 = 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));
> }
> }
>
>