Thank you for your reply! I have another question. If the Person class has a member of another class, say Address. Class Address has two members, int streetNo, String streetName. How can I write the “Create Table” statement to create columns for the two members of Address in Table Person? Do I have to create another table for Address? Thank you.
From: Вячеслав Коптилин [mailto:[email protected]] Sent: 2018年6月20日 14:55 To: [email protected] Subject: Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache Hi, > How should I use BinaryObject in the CREATE TABLE statement? If you want to use binary objects, then there is no need to specify 'VALUE_TYPE'. Please use the following code: String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" + "WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL, WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME + "\""; Thanks! вт, 19 июн. 2018 г. в 16:43, Cong Guo <[email protected]<mailto:[email protected]>>: Hi, How should I use BinaryObject in the CREATE TABLE statement? I try using BinaryObject.class.getName() as the value_type, but get the following exception: class org.apache.ignite.IgniteCheckedException: Failed to initialize property 'ORGID' of type 'java.lang.Long' for key class 'class java.lang.Long' and value class 'interface org.apache.ignite.binary.BinaryObject'. Make sure that one of these classes contains respective getter method or field. I want to use BinaryObject here for some flexibility. Thanks, Cong From: Вячеслав Коптилин [mailto:[email protected]<mailto:[email protected]>] Sent: 2018年6月18日 18:10 To: [email protected]<mailto:[email protected]> Subject: Re: A bug in SQL "CREATE TABLE" and its underlying Ignite cache Hello, It seems that the root cause of the issue is wrong values of 'KEY_TYPE' and 'VALUE_TYPE' parameters. In your case, there is no need to specify 'KEY_TYPE' at all, and 'VALUE_TYPE' should Person.class.getName() I think. Please try the following: String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" + "WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL, WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME + ", VALUE_TYPE=" + Person.class.getName() + "\""; Best regards, Slava. пн, 18 июн. 2018 г. в 21:50, Cong Guo <[email protected]<mailto:[email protected]>>: Hi, I need to use both SQL and non-SQL APIs (key-value) on a single cache. I follow the document in: https://apacheignite-sql.readme.io/docs/create-table I use “CREATE TABLE” to create the table and its underlying cache. I can use both SQL “INSERT” and put to add data to the cache. However, when I run a SqlFieldsQuery, only the row added by SQL “INSERT” can be seen. The Ignite version is 2.4.0. You can reproduce the bug using the following code: CacheConfiguration<Integer, Integer> dummyCfg = new CacheConfiguration<>("DUMMY"); dummyCfg.setSqlSchema("PUBLIC"); try(IgniteCache<Integer, Integer> dummyCache = ignite.getOrCreateCache(dummyCfg)){ String createTableSQL = "CREATE TABLE Persons (id LONG, orgId LONG, firstName VARCHAR, lastName VARCHAR, resume VARCHAR, salary FLOAT, PRIMARY KEY(firstName))" + "WITH \"BACKUPS=1, ATOMICITY=TRANSACTIONAL, WRITE_SYNCHRONIZATION_MODE=PRIMARY_SYNC, CACHE_NAME=" + PERSON_CACHE_NAME + ", KEY_TYPE=String, VALUE_TYPE=BinaryObject\""; dummyCache.query(new SqlFieldsQuery(createTableSQL)).getAll(); SqlFieldsQuery firstInsert = new SqlFieldsQuery("INSERT INTO Persons (id, orgId, firstName, lastname, resume, salary) VALUES (?,?,?,?,?,?)"); firstInsert.setArgs(1L, 1L, "John", "Smith", "PhD", 10000.0d); dummyCache.query(firstInsert).getAll(); try(IgniteCache<String, Person> personCache = ignite.cache(PERSON_CACHE_NAME)){ Person p2 = new Person(2L, 1L, "Hello", "World", "Master", 1000.0d); personCache.put("Hello", p2); IgniteCache<Long, BinaryObject> binaryCache = personCache.<Long, BinaryObject>withKeepBinary(); System.out.println("Size of the cache is: " + binaryCache.size(CachePeekMode.ALL)); binaryCache.query(new ScanQuery<>(null)).forEach(entry -> System.out.println(entry.getKey())); System.out.println("Select results: "); SqlFieldsQuery qry = new SqlFieldsQuery("select * from Persons"); QueryCursor<List<?>> answers = personCache.query(qry); List<List<?>> personList = answers.getAll(); for(List<?> row : personList) { String fn = (String)row.get(2); System.out.println(fn); } } } The output is: Size of the cache is: 2 Hello String [idHash=213193302, hash=-900113201, FIRSTNAME=John] Select results: John The bug is that the SqlFieldsQuery cannot see the data added by “put”.
