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.
