You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Prasad Kommoju <pk...@futurewei.com> on 2021/10/26 02:03:36 UTC
Problem with SqlFieldsQuery
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> !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> 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
Re: Problem with SqlFieldsQuery
Posted by Maksim Timonin <ti...@gmail.com>.
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 <pk...@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 <ti...@gmail.com>
> *Sent:* Tuesday, October 26, 2021 2:36 PM
> *To:* user@ignite.apache.org; Prasad Kommoju <pk...@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 <ti...@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 <pk...@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
>
>
>
>
RE: Problem with SqlFieldsQuery
Posted by Prasad Kommoju <pk...@futurewei.com>.
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 <ti...@gmail.com>
Sent: Tuesday, October 26, 2021 2:36 PM
To: user@ignite.apache.org; Prasad Kommoju <pk...@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 <ti...@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 <pk...@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
Re: Problem with SqlFieldsQuery
Posted by Maksim Timonin <ti...@gmail.com>.
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 <ti...@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 <pk...@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> !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> 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
>>
>>
>>
>
Re: Problem with SqlFieldsQuery
Posted by Maksim Timonin <ti...@gmail.com>.
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 <pk...@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> !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> 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
>
>
>