I also had error like this recently.
After investigation I found that there was a bug in Schema Import Utility -
it generate XML / Java for QueryEntites using primitives in fields
description.
I solved my issue by changing all int to Integer (and other primitives too)
in all QueryEntities.
Also I fixed Schema Import Utility generation and pushed fixes to master
branch.
You could build Schema Import Utility from master or use latest nightly
build.
Hope this help.
On Thu, Apr 14, 2016 at 8:23 PM, tusharnakra <tusharna...@hotmail.com>
wrote:
> Hi,
>
> I'm trying to execute the cross-cache sql fields query with join and it
> executes fine as long as I don't do setArgs and pass an argument, when I
> need to pass an argument with the where clause, then it gives error: Failed
> to execute local query: GridQueryRequest [reqId=1, pageSize=1024,
> space=PCache, qrys=[GridCacheSqlQuery [qry=SELECT
> "PCache".PERSON._KEY __C0,
> "PCache".PERSON._VAL __C1
> FROM "PCache".PERSON
> WHERE (SALARY > ?1) AND (SALARY <= ?2), params=[0, 1000], paramIdxs=[0, 1],
> paramsSize=2, cols={__C0=GridSqlType [type=19, scale=0,
> precision=2147483647, displaySize=2147483647, sql=OTHER], __C1=GridSqlType
> [type=19, scale=0, precision=2147483647, displaySize=2147483647,
> sql=OTHER]}, alias=null]], topVer=AffinityTopologyVersion [topVer=1,
> minorTopVer=2], extraSpaces=null, parts=null]
> class org.apache.ignite.IgniteCheckedException: Failed to execute SQL
> query.
> at
>
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQuery(IgniteH2Indexing.java:832)
> at
>
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.executeSqlQueryWithTimer(IgniteH2Indexing.java:855)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onQueryRequest(GridMapQueryExecutor.java:454)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridMapQueryExecutor.onMessage(GridMapQueryExecutor.java:184)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.send(GridReduceQueryExecutor.java:1065)
> at
>
> org.apache.ignite.internal.processors.query.h2.twostep.GridReduceQueryExecutor.query(GridReduceQueryExecutor.java:572)
> at
>
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$2.iterator(IgniteH2Indexing.java:956)
> at
>
> org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61)
> at
>
> org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing$3.iterator(IgniteH2Indexing.java:990)
> at
>
> org.apache.ignite.internal.processors.cache.QueryCursorImpl.iterator(QueryCursorImpl.java:61)
> at
>
> org.apache.ignite.internal.processors.cache.QueryCursorImpl.getAll(QueryCursorImpl.java:73)
> at org.apache.ignite.organization.Demo.sqlQuery(Demo.java:141)
> at org.apache.ignite.organization.Demo.main(Demo.java:90)
> Caused by: org.h2.jdbc.JdbcSQLException: Hexadecimal string with odd number
> of characters: "0"; SQL statement:
>
> Here is my Demo.java:
>
> package org.apache.ignite.organization;
>
> import java.util.List;
> import java.io.BufferedReader;
> import java.io.InputStream;
> import java.io.InputStreamReader;
> import java.security.KeyStore.Entry;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
>
> import javax.cache.Cache;
>
> import org.apache.ignite.Ignite;
> import org.apache.ignite.IgniteCache;
> import org.apache.ignite.IgniteException;
> import org.apache.ignite.Ignition;
> import org.apache.ignite.cache.CacheMode;
> import org.apache.ignite.cache.affinity.AffinityKey;
> import org.apache.ignite.cache.query.QueryCursor;
> import org.apache.ignite.cache.query.SqlFieldsQuery;
> import org.apache.ignite.cache.query.SqlQuery;
> import org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStore;
> import org.apache.ignite.cache.store.jdbc.CacheJdbcPojoStoreFactory;
> import org.apache.ignite.configuration.CacheConfiguration;
> import org.apache.ignite.examples.model.Person;
> import org.apache.ignite.transactions.Transaction;
>
>
> import com.mysql.jdbc.jdbc2.optional.MysqlDataSource;
>
>
> public class Demo {
>
>
> static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
> static final String DB_URL = "jdbc:mysql://localhost/ORG";
> static final String USER = "root";
> static final String PASS = "mysql";
> private static final String ORG_CACHE = "OrgCache";
>
> private static class MySQLDemoStoreFactory<K, V> extends
> CacheJdbcPojoStoreFactory<K, V> {
> //{@inheritDoc}
> @Override public CacheJdbcPojoStore<K, V> create() {
>
>
> MysqlDataSource dataSource = new MysqlDataSource();
> dataSource.setURL("jdbc:mysql://localhost/ORG");
> dataSource.setUser("root");
> dataSource.setPassword("mysql");
> setDataSource(dataSource);
> return super.create();
>
> }
>
> }
>
> /**
> * Executes demo.
> */
> public static void main(String[] args) throws IgniteException {
> System.out.println(">>> Start demo...");
>
>
> // Start Ignite node.
> try (Ignite ignite =
> Ignition.start("examples/config/example-ignite.xml")) {
>
>
> CacheConfiguration<PersonKey, Person> cfg =
> CacheConfig.cache("PCache", new
> MySQLDemoStoreFactory<PersonKey, Person>());
>
>
>
> CacheConfiguration<OrganizationKey, Organization> cfg1 =
> CacheConfig.cache("OrgCache", new
> MySQLDemoStoreFactory<OrganizationKey, Organization>());
>
>
> try (
> IgniteCache<PersonKey, Person> PCache =
> ignite.getOrCreateCache(cfg);
> IgniteCache<OrganizationKey, Organization> orgCache =
> ignite.getOrCreateCache(cfg1)
> ) {
>
>
>
> preload1(PCache);
> preload(orgCache);
>
> sqlQuery();
>
> sqlFieldsQueryWithJoin();
>
> }
> finally {
> ignite.destroyCache("PCache");
> ignite.destroyCache("OrgCache");
> System.out.println("Cache destroyed");
> }
> }
>
>
> }
> private static void preload(IgniteCache<OrganizationKey, Organization>
> cache) {
> System.out.println();
> System.out.println(">>> Loading entries from Organization table.");
>
> // Preload all person keys that are less than or equal to 3.
> cache.loadCache(null, OrganizationKey.class.getName(), "select *
> from organization");
>
>
>
> for (Cache.Entry<OrganizationKey, Organization> org : cache)
> System.out.println(">>> Loaded Organization: " + org);
> }
>
> private static void preload1(IgniteCache<PersonKey, Person> cache) {
> System.out.println();
> System.out.println(">>> Loading entries from Person table.");
>
> // Preload all person keys that are less than or equal to 3.
> cache.loadCache(null, PersonKey.class.getName(), "select * from
> person");
>
>
>
> for (Cache.Entry<PersonKey, Person> person : cache)
> System.out.println(">>> Loaded Person: " + person);
> }
>
>
>
> private static void sqlQuery() {
> IgniteCache<AffinityKey<Long>, Person> cache =
> Ignition.ignite().cache("PCache");
>
> // SQL clause which selects salaries based on range.
> String sql = "salary > ? and salary <= ?";
>
> // Execute queries for salary ranges.
> print("People with salaries between 0 and 1000 (queried with SQL
> query): ",
> cache.query(new SqlQuery<AffinityKey<Long>,
> Person>(Person.class, sql).
> setArgs(0, 1000)).getAll());
>
> print("People with salaries between 1000 and 2000 (queried with SQL
> query): ",
> cache.query(new SqlQuery<AffinityKey<Long>,
> Person>(Person.class, sql).
> setArgs(1000, 2000)).getAll());
> }
>
> private static void sqlFieldsQuery() {
> IgniteCache<AffinityKey<Long>, Person> cache =
> Ignition.ignite().cache("PCache");
>
> // Execute query to get names of all employees.
> QueryCursor<List<?>> cursor = cache.query(new SqlFieldsQuery(
> "select concat(firstName, ' ', lastName) from Person"));
>
> // In this particular case each row will have one element with full
> name of an employees.
> List<List<?>> res = cursor.getAll();
>
> // Print names.
> print("Names of all employees:", res);
> }
>
>
>
> private static void sqlFieldsQueryWithJoin() {
> IgniteCache<AffinityKey<Long>, Person> cache =
> Ignition.ignite().cache("PCache");
> //String oname = "Barclays";
> // Execute query to get names of all employees.
> String sql =
> "select concat(firstName, ' ', lastName), org.orgname " +
> "from Person, \"" + ORG_CACHE + "\".Organization as org " +
> "where Person.orgid = org.orgid";
>
> QueryCursor<List<?>> cursor = cache.query(new
> SqlFieldsQuery(sql));
>
> // In this particular case each row will have one element with full
> name of an employees.
> List<List<?>> res = cursor.getAll();
>
> // Print persons' names and organizations' names.
> print("Names of all employees and organizations they belong to:",
> res);
> }
>
> private static void print(String msg, Iterable<?> col) {
> print(msg);
> print(col);
> }
>
> /**
> * Prints message.
> *
> * @param msg Message to print before all objects are printed.
> */
> private static void print(String msg) {
> System.out.println();
> System.out.println(">>> " + msg);
> }
>
> /**
> * Prints query results.
> *
> * @param col Query results.
> */
> private static void print(Iterable<?> col) {
> for (Object next : col)
> System.out.println(">>> " + next);
> }
> }
>
>
> The sqlFieldsQueryWithJoin method works fine, but the sqlQuery method gives
> the error. How to fix it please?
>
>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/Passing-argument-in-sql-query-under-where-clause-giving-error-tp4164.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
--
Alexey Kuznetsov
GridGain Systems
www.gridgain.com
