ignite-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From tusharnakra <tusharna...@hotmail.com>
Subject Passing argument in sql query under where clause giving error
Date Thu, 14 Apr 2016 13:23:53 GMT
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&lt;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&lt;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&lt;Long>,
Person>(Person.class, sql).
                setArgs(1000, 2000)).getAll());
    }
    
    private static void sqlFieldsQuery() {
        IgniteCache<AffinityKey&lt;Long>, Person> cache =
Ignition.ignite().cache("PCache");

        // Execute query to get names of all employees.
        QueryCursor<List&lt;?>> 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&lt;?>> res = cursor.getAll();

        // Print names.
        print("Names of all employees:", res);
    }
    
    
    
    private static void sqlFieldsQueryWithJoin() {
        IgniteCache<AffinityKey&lt;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&lt;?>> cursor = cache.query(new
SqlFieldsQuery(sql));

        // In this particular case each row will have one element with full
name of an employees.
        List<List&lt;?>> 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.

Mime
View raw message