Hi, Prasad! I created a simple gist with your example, please have a look - https://gist.github.com/timoninmaxim/534d36b23542140555901ddc0e853d3b
Some notes here: 1. QueryEntity has `setTableName` property. Set up it and then you can simply use it in your SQL query instead of a simple class name. It's more clear. 2. Value type is required to be set up with a full class name to correctly match insertion values with related tables. You can create a class PersonSQL in another package, and try to put instances to `cache`. Then ScanQuery will show you them, but they won't be part of your `table` (thanks the specified value type), and `select count(*)` from *table* returns 0. Also I found that you defined QueryEntity with `keyFieldName = name`, but you put items to the cache with `key = ssn`. Just let you know, it may lead to some misses in table / cache. On Wed, Oct 27, 2021 at 12:53 AM Prasad Kommoju <pkomm...@futurewei.com> wrote: > Hi Maksim, > > > > I did “Reply” and that must have retained only user@ignite. > > > > I was redacting the name (replace with blah) but it is the same in both > places. The thing that is not clear to me is should the name in the config > file (and the code) be fully qualified class name or simple name > (PersonSQL). One of them seems to throw failed to set schema and the other > empty results. > > > > > > > > --------------------- > > Regards, > > Prasad Kommoju > > > > *From:* Maksim Timonin <timonin.ma...@gmail.com> > *Sent:* Tuesday, October 26, 2021 2:36 PM > *To:* user@ignite.apache.org; Prasad Kommoju <pkomm...@futurewei.com> > *Subject:* Re: Problem with SqlFieldsQuery > > > > Hi, Prasad! > > > > Looks like you missed the topic, and posted your code to another thread. > But nevertheless, did you check the package name of PersonSQL class in the > CacheConfiguration and in your insertion code? > > > > On Tue, Oct 26, 2021 at 10:15 AM Maksim Timonin <timonin.ma...@gmail.com> > wrote: > > Hi, Prasad! > > > > Could you please show how you insert data to the table? > > > > As I see you defined table with "com.*blah*.sfqx.SqlFieldQueryXML$PersonSQL" > but cache scan returns objects with value type > "com.*futurewei*.sfqx.SqlFieldQueryXML$PersonSQL". > Can this misprint be a reason? > > > > On Tue, Oct 26, 2021 at 5:03 AM Prasad Kommoju <pkomm...@futurewei.com> > wrote: > > I create a cache with QueryEntities (through ignite configuration file) > and use SqlFieldsQuery to query it. > > > > I can see the cache in ignitevisor and it appears as table through sqlline > interface. While ignitevisor shows the data sqlline tool does not. > > > > Here is the configuration: > > > > … > > <property name="defaultDataRegionConfiguration"> > > <bean > class="org.apache.ignite.configuration.DataRegionConfiguration"> > > <property name="persistenceEnabled" value="true"/> > > </bean> > > </property> > > </bean> > > </property> > > > > <property name="cacheConfiguration"> > > <bean > class="org.apache.ignite.configuration.CacheConfiguration"> > > <property name="name" value="PersonSQL"/> > > <!-- Configure query entities --> > > <property name="queryEntities"> > > <list> > > <bean class="org.apache.ignite.cache.QueryEntity"> > > <!-- Setting the type of the key --> > > <property name="keyType" > value="java.lang.String"/> > > > > <property name="keyFieldName" value="name"/> > > > > <!-- Setting type of the value --> > > <property name="valueType" value=" > com.blah.sfqx.SqlFieldQueryXML.PersonSQL"/> > > > > <!-- Defining fields that will be either > indexed or queryable. Indexed fields are added to the 'indexes' list > below.--> > > <property name="fields"> > > <map> > > <entry key="ssn" > value="java.lang.String"/> > > <entry key="name" > value="java.lang.String"/> > > <entry key="address" > value="com.futurewei.sfqx.Address"/> > > </map> > > </property> > > <!-- Defining indexed fields.--> > > <property name="indexes"> > > <!-- Single field (aka. column) index > --> > > <bean > class="org.apache.ignite.cache.QueryIndex"> > > <constructor-arg value="name"/> > > </bean> > > </property> > > … > > > > Here is the ignitevisor output (truncated): > > visor> cache -scan -c=@c1 > > Entries in cache: PersonSQL > > > +========================================================================================================================================================================================================================================================================================================================================================+ > > | Key Class | Key | Value Class > | > Value > | > > > +========================================================================================================================================================================================================================================================================================================================================================+ > > | java.lang.String | 7336-18-3968 | o.a.i.i.binary.BinaryObjectImpl | > com.futurewei.sfqx.SqlFieldQueryXML$PersonSQL [hash=-900842615, > ssn=7336-18-3968, name=uuykixzs, > address=com.blah.sfqx.SqlFieldQueryXML$Address [idHash=302301205, > hash=239196030, houseNumber=2606, streetName=xjzxzzpazdzx, city=uwjitlprkd, > state=dzhiiisjq, zip=73550]] | > > | java.lang.String | 6198-10-5000 | o.a.i.i.binary.BinaryObjectImpl | > com.futurewei.sfqx.SqlFieldQueryXML$PersonSQL [hash=426078934, > ssn=6198-10-5000, name=lwthwezu, > address=com.blah.sfqx.SqlFieldQueryXML$Address [idHash=1460034609, > hash=-1811594149, houseNumber=9161, streetName=npruuwxhwbai, > city=yxexraxvuu, state=cgxcrypcy, zip=91752]] | > > | java.lang.String | 9448-3-1310 | o.a.i.i.binary.BinaryObjectImpl | > com.futurewei.sfqx.SqlFieldQueryXML$PersonSQL [hash=402062681, > ssn=9448-3-1310, name=pyaujxzt, > address=com.blah.sfqx.SqlFieldQueryXML$Address [idHash=710697527, > hash=1654079158, houseNumber=2267, streetName=xpgtnbzngftv, > city=flbtopwban, state=jkdrlxwqj, zip=11144]] | > > | java.lang.String | 9880-7-3532 > > > > Here is the sqlline output: > > 0: jdbc:ignite:thin://127.0.0.1:10800 > <https://nam11.safelinks.protection.outlook.com/?url=http%3A%2F%2F127.0.0.1%3A10800%2F&data=04%7C01%7Cpkommoju%40futurewei.com%7C305a2087d173456d353a08d998c8ad33%7C0fee8ff2a3b240189c753a1d5591fedc%7C1%7C0%7C637708809942434963%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=MAUowveQMDm2AVKUYJg4UnmKk14VV5MD7XnBIYzJMo4%3D&reserved=0>> > !tables > > > +-----------+-------------+-----------------------------+------------+---------+----------+------------+-----------+---------------------------+----------------+ > > | TABLE_CAT | TABLE_SCHEM | TABLE_NAME | TABLE_TYPE | > REMARKS | TYPE_CAT | TYPE_SCHEM | TYPE_NAME | SELF_REFERENCING_COL_NAME | > REF_GENERATION | > > > +-----------+-------------+-----------------------------+------------+---------+----------+------------+-----------+---------------------------+----------------+ > > | IGNITE | PersonSQL | PERSONSQL | TABLE > | | | | | > | | > > | IGNITE | SYS | BASELINE_NODES | VIEW > | | | | | > | | > > | IGNITE | SYS | BINARY_METADATA > > … > > 0: jdbc:ignite:thin://127.0.0.1:10800 > <https://nam11.safelinks.protection.outlook.com/?url=http%3A%2F%2F127.0.0.1%3A10800%2F&data=04%7C01%7Cpkommoju%40futurewei.com%7C305a2087d173456d353a08d998c8ad33%7C0fee8ff2a3b240189c753a1d5591fedc%7C1%7C0%7C637708809942444921%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C1000&sdata=VWyJYTxWsa2E8VHGNpmybS7MgEdxSYUjDz4lBSRAjEs%3D&reserved=0>> > select count (*) from "PersonSQL".PERSONSQL; > > +----------+ > > | COUNT(*) | > > +----------+ > > | 0 | > > > > > > > > > > > > From Java program, I get > > SQL Query failed: Ignite failed to process request [2]: Failed to set > schema for DB connection for thread [schema="PersonSQL"] (server status > code [1]) > > > > If the cache name in the program is changed to “com…..PersonSQL” the error > is Cache does not exist. > > > > Any help in unraveling this problem will be greatly appreciated. > > > > --------------------- > > Regards, > > Prasad Kommoju > > > >
