Re: How to load 2 tables in a cache

[tusharnakra]

OK, UPDATE:

This method is working, but how do I add setArgs (I mean, I want to pass the
argument for orgname as "Ignite" i.e. to only print person names in Ignite
organization. How to do that??? It's giving error if I simply do as given on
the ignite website:

 private static void sqlFieldsQueryWithJoin() {
IgniteCache, 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> cursor = cache.query(new
SqlFieldsQuery(sql));

// In this particular case each row will have one element with full
name of an employees.
List> res = cursor.getAll();

// Print persons' names and organizations' names.
print("Names of all employees and organizations they belong to:",
res);
}



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4136.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: How to load 2 tables in a cache

[tusharnakra]

Could this error be because I haven't done setIndexedType?:
cfg1.setIndexedTypes(OrganizationKey.class, Organization.class);

OR because I have same column name "orgid" in both the tables??



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4133.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: How to load 2 tables in a cache

[vkulichenko]

Can you please show your CacheConfig class?

-Val



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4113.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: How to load 2 tables in a cache

[tusharnakra]

OK, I changed the SQL query to:  "select Person.firstName  "
+ "from Person, \"OrgCache\".Organization where 
"
+ "Person.orgid = Organization.orgid "
+ "and Organization.orgname = ?");

Getting the following error:

 Failed to execute local query: GridQueryRequest [reqId=1, pageSize=1024,
space=PCache, qrys=[GridCacheSqlQuery [qry=SELECT
PERSON.FIRSTNAME __C0
FROM "PCache".PERSON 
 INNER JOIN "OrgCache".ORGANIZATION
WHERE (ORGANIZATION.ORGNAME = ?1) AND (PERSON.ORGID = ORGANIZATION.ORGID),
params=[Ignite], paramIdxs=[0], paramsSize=1, cols={__C0=GridSqlType
[type=19, scale=0, precision=2147483647, displaySize=2147483647,
sql=OTHER]}, alias=null]], topVer=AffinityTopologyVersion [topVer=1,
minorTopVer=4], extraSpaces=[OrgCache], 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.organization.Demo.main(Demo.java:92)
Caused by: org.h2.jdbc.JdbcSQLException: Hexadecimal string contains non-hex
character: "Ignite"; SQL statement:
SELECT
PERSON.FIRSTNAME __C0
FROM "PCache".PERSON 
 INNER JOIN "OrgCache".ORGANIZATION
WHERE (ORGANIZATION.ORGNAME = ?1) AND (PERSON.ORGID = ORGANIZATION.ORGID)
[90004-175]



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4100.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: How to load 2 tables in a cache

[vkulichenko]

Database first_name name should be mapped to firstName in Java object, and
you should use the Java name when executing SQL queries against the data in
memory. Can you try this?

-Val



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4084.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: How to load 2 tables in a cache

[tusharnakra]

Actually, this is what's happening:

This is the code:

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.query.QueryCursor;
import org.apache.ignite.cache.query.SqlFieldsQuery;
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.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 class MySQLDemoStoreFactory extends
CacheJdbcPojoStoreFactory {
 //{@inheritDoc} 
 @Override public CacheJdbcPojoStore 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 cfg =
CacheConfig.cache("PCache", new
MySQLDemoStoreFactory());

try (IgniteCache pCache =
ignite.getOrCreateCache(cfg)) {
// Preload cache from database.
preload1(pCache);
}

CacheConfiguration cfg1 =
CacheConfig.cache("OrgCache", new
MySQLDemoStoreFactory());

try (IgniteCache orgCache =
ignite.getOrCreateCache(cfg1)) {
// Preload cache from database.
preload(orgCache);
}

try (IgniteCache pCache =
ignite.cache("PCache")) {


SqlFieldsQuery sql = new SqlFieldsQuery(
"select Person.first_name  "
+ "from Person, \"OrgCache\".Organization where 
"
+ "Person.orgId = Organization.orgId "
+ "and Organization.orgname = ?");
System.out.println(sql);

// Execute the query and obtain the query result cursor.
try (QueryCursor> cursor = 
pCache.query(sql.setArgs("Ignite"))) {
for (List row : cursor)
System.out.println("Person name= " + row);
}
}
}
}
private static void preload(IgniteCache
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 org : cache)
System.out.println(">>> Loaded Organization: " + org);
}

private static void preload1(IgniteCache 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 person : cache)
System.out.println(">>> Loaded Person: " + person);
}

   
}

And this is the error I'm getting:

Exception in thread "main" javax.cache.CacheException: class
org.apache.ignite.IgniteException: Failed to parse 

Re: How to load 2 tables in a cache

[Dmitriy Setrakyan]

On Mon, Apr 11, 2016 at 10:29 AM, tusharnakra 
wrote:

> Yes, it makes sense! So, is there any way that the SQL JOIN relations can
> work in gridgain for the 2 tables loaded in different cache?
>

Yes, you can find the example for cross-cache join here:
https://apacheignite.readme.io/docs/sql-queries


>
>
> --
> View this message in context:
> http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4069.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>

Re: How to load 2 tables in a cache

[tusharnakra]

Yes, it makes sense! So, is there any way that the SQL JOIN relations can
work in gridgain for the 2 tables loaded in different cache?



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4069.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: How to load 2 tables in a cache

[tusharnakra]

I don't understand what you mean by data types? I'm talking about 2 database
tables present in the same MySql database/



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4065.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.

Re: How to load 2 tables in a cache

[Vasiliy Sisko]

Hello @tusharnakra

Idiomatic way of Ignite usage is creation of separated cache per data type.

Ignite Schema import is deprecated. You can try to use Web Console, which
already generate separated caches per data types and described at this page
http://ignite.apache.org/addons.html#web-console 



--
View this message in context: 
http://apache-ignite-users.70518.x6.nabble.com/How-to-load-2-tables-in-a-cache-tp4026p4047.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.