Hi Thomas, Yes, SQL and JCache can be used interchangeably, but sometimes it can be tricky.
Best regards, Slava. 2018-01-31 12:10 GMT+03:00 Thomas Isaksen <[email protected]>: > Hi Slava, > > > > Thanks a lot for your help this is working great J > > > > I do wonder If I can use SQL and JCache API interchangeably? > > For example, I do cache.put(key, value) for one row and I insert another > one using SQL INSERT. Can I still retrieve both rows using both SQL or > JCache API? > > The reason I ask is that it seems to me that only the row I stored using > SQL INSERT shows up when I do a SELECT on the table, but both rows shows up > in ignite visor when I do a cache -scan > > > > Thanks! > > > > -- > > Thomas Isaksen > > > > *From:* Вячеслав Коптилин [mailto:[email protected]] > *Sent:* tirsdag 30. januar 2018 13.41 > *To:* [email protected] > *Subject:* Re: Binary type has different affinity key fields > > > > Hi Thomas, > > > > Let's start with the table (I will use java api for that) > > > > > > *// Create dummy cache to act as an entry point for SQL queries (new SQL API > which do not require this// will appear in future versions, JDBC and ODBC > drivers do not require it already).*CacheConfiguration<?, ?> cacheCfg = *new > *CacheConfiguration<>(*DUMMY_CACHE_NAME*).setSqlSchema(*"PUBLIC"*); > IgniteCache<?, ?> dummyCache = ignite.getOrCreateCache(cacheCfg); > > > *// Create UserCache table based on the partitioned > template.*dummyCache.query(*new *SqlFieldsQuery( > *"CREATE TABLE UserCache (id BIGINT, username VARCHAR, password varchar, > PRIMARY KEY (username, password)) " *+ > *"WITH \"template=partitioned," *+ > *"affinitykey=username," *+ > *"cache_name=UserCache," *+ > *"key_type=org.apache.ignite.examples.CredentialsKey," *+ > *"value_type=org.apache.ignite.examples.Credentials\""*)).getAll(); > > > > one important thing that should be mentioned here is that SQL is > case-insensitive and therefore table name and column names will be > automatically converted to *upper case*. > > if you want to preserve the case, you need to put double quotes around the > table name and columns. > > for instance: > > > > *// Create UserCache table based on the partitioned template.* > > dummyCache.query(*new *SqlFieldsQuery( > > *"CREATE TABLE **\"**UserCache**\"** (\"id\" BIGINT, **\"**username* > *\"** VARCHAR, **\"**password**\"** varchar, PRIMARY KEY (**\"**username* > *\"**, **\"**password**\"**)) " *+ > > * "WITH **\"**template=partitioned," *+ > > *"affinitykey=username," *+ > > *"cache_name=UserCache," *+ > > * "key_type=org.apache.ignite.examples.CredentialsKey," *+ > > *"value_type=org.apache.ignite.examples.Credentials**\"**"* > )).getAll(); > > > > optionally, you can create indices on UserCache > > > *// Create indices.*dummyCache.query(*new *SqlFieldsQuery(*"CREATE INDEX on > UserCache (username)"*)).getAll(); > dummyCache.query(*new *SqlFieldsQuery(*"CREATE INDEX on UserCache > (password)"*)).getAll(); > > > > next step is defining CredentialsKey and Credential classes. > > in accordance with the documentation https://apacheignite-sql.readme.io/ > docs/create-table#section-examples > > the PRIMARY KEY columns will be used as the object's key, the rest of the > columns will belong to the value. > > > > *public class *CredentialsKey { > > // Please take into account my note about case-insensitive SQL > @AffinityKeyMapped > *private *String *USERNAME*; > > *private *String *PASSWORD*; > > *public *CredentialsKey(String username, String password) { > *this*.*USERNAME *= username; > *this*.*PASSWORD *= password; > } > > *public *String getUsername() {*return USERNAME*;} > > *public void *setUsername(String username) {*this*.*USERNAME *= username;} > > *public *String getPassword() {*return PASSWORD*;} > > *public void *setPassword(String password) {*this*.*PASSWORD *= password;} > } > > > > *public class *Credentials { > *private long ID*; > > *public *Credentials(*long *id) { > *this*.*ID *= id; > } > > *public long *getId() {*return ID*;} > > *public void *setId(*long *id) {*this*.*ID *= id;} > > @Override *public *String toString() {*return "Credentials=[id=" *+ *ID > *+ *"]"*;} > } > > > > Now, you can populate the cache/table via JCache API > > IgniteCache testCache = ignite.cache(*"UserCache"*); > testCache.put(*new *CredentialsKey(*"username-1"*, *"password-1"*), *new > *Credentials(1L)); > testCache.put(*new *CredentialsKey(*"username-2"*, *"password-2"*), *new > *Credentials(2L)); > testCache.put(*new *CredentialsKey(*"username-3"*, *"password-3"*), *new > *Credentials(3L)); > > or SQL API > > SqlFieldsQuery qry = *new *SqlFieldsQuery(*"INSERT INTO UserCache (id, > username, password) VALUES (?, ?, ?)"*); > dummyCache.query(qry.setArgs(1L, *"username-sql-1"*, > *"password-5"*)).getAll(); > dummyCache.query(qry.setArgs(2L, *"username-sql-2"*, > *"password-6"*)).getAll(); > dummyCache.query(qry.setArgs(3L, *"username-sql-3"*, > *"password-7"*)).getAll(); > > Best regards, > > Slava. > > > > > > 2018-01-26 12:27 GMT+03:00 Thomas Isaksen <[email protected]>: > > Hi Slava > > Thanks for pointing out my mistakes with the template. > I have attached the java classes in question and the ignite config file > that I am using . > > I create the table using DDL as follows: > > CREATE TABLE UserCache ( > id bigint, > username varchar, > password varchar, > PRIMARY KEY (username, password) > ) > WITH "template=userCache, affinitykey=username, cache_name=UserCache, > key_type=no.toyota.gatekeeper.ignite.key.CredentialsKey, > value_type=no.toyota.gatekeeper.authenticate.Credentials"; > > Next I try to put one entry into my cache: > > @Test > Public void testIgnite() > { > Ignition.setClientMode(true); > Ignite ignite = Ignition.start("/config/test-config.xml"); > IgniteCache<CredentialsKey, Credentials> cache = > ignite.cache("UserCache"); > // this blows up > cache.put(new CredentialsKey("foo","bar"), new > Credentials("foo","bar","resourceId")); > } > > I am not sure my code is correct but I get the same error when I try to > insert a row using SQL. > > INSERT INTO UserCache (id,username,password) VALUES (1, 'foo','bar'); > > -- > Thomas Isaksen > > -----Original Message----- > From: slava.koptilin [mailto:[email protected]] > > Sent: torsdag 25. januar 2018 17.39 > To: [email protected] > Subject: RE: Binary type has different affinity key fields > > Hi Thomas, > > CREATE TABLE statement doesn't use your template because you specified the > predefined one - 'partitioned' *template=partitioned* > > In case of using replicated mode, the following property <property > name="backups" value="1"/> does not make sense. > > Could you please share full reproducer? I will try it on my side and come > back to you with findings. > I mean definition CredentialsKey and Credentials and code that can be used > in order to reproduce the exception you mentioned. > > Best regards, > Slava. > > > > > > -- > Sent from: http://apache-ignite-users.70518.x6.nabble.com/ > > >
