You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by Peter Sham <pe...@yahoo.com> on 2018/11/16 01:07:55 UTC
Ignite.Net - Defining SQL Schema without using QuerySqlField custom
attribute
Hello,
I’m trying to learn Ignite.Net and has been following examples bundled with source or binary distribution. However, when I try to define SQL Schema that will match an object either by sql create table statement or using QueryEntity in cache configuration, without resorting to QuerySqlFieldAttribute, I kept hitting an exception which said
class org.apache.ignite.binary.BinaryInvalidTypeException: Unknown pair [platformId=0, typeId=-1854586790]"; SQL statement …
Does anyone has an example that showcase how to define SQL Schema for an object without using custom attribute?
Thank you very much.
Re: Ignite.Net - Defining SQL Schema without using QuerySqlField
custom attribute
Posted by Peter Sham <pe...@yahoo.com>.
Additional findings. Actually the SQL schema defined by running sql create
table works fine except for the date field. So I suspect the error message
"BinaryInvalidTypeException: Unknown pair [platformId=0,
typeId=-1854586790]" would probably mean the framework cannot find a mapping
between Java timestamp to .Net DateTime.
Any help from anyone is most welcome!
--
Sent from: http://apache-ignite-users.70518.x6.nabble.com/
Re: Ignite.Net - Defining SQL Schema without using QuerySqlField
custom attribute
Posted by Peter Sham <pe...@yahoo.com>.
Yes. I just found out the hard way yesterday by debugging through the Ignite.Net source code and reached the same understanding. Thank you.
On Wednesday, November 21, 2018, 6:12:16 AM EST, Pavel Tupitsyn <pt...@apache.org> wrote:
There is a subtle issue with DateTime and SQL [1]
If you always use UTC in Ignite (which you should), the proper thing to do is:var igniteCfg = new IgniteConfiguration
{
BinaryConfiguration = new BinaryConfiguration
{
Serializer = new BinaryReflectiveSerializer
{
ForceTimestamp = true
}
}
};
var ignite = Ignition.Start(igniteCfg);
This way SQL-compatible format is enforced, and non-UTC values will cause an exception.[1] https://apacheignite-net.readme.io/docs/platform-interoperability#section-type-compatibility
On Tue, Nov 20, 2018 at 12:06 AM Peter Sham <pe...@yahoo.com> wrote:
Sure. Hope this won't get too long and will still be readable from e-mail, and thank you for helping. Really appreciate it.
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using Apache.Ignite.Core;using Apache.Ignite.Core.Cache;using Apache.Ignite.Core.Cache.Query;using Apache.Ignite.Core.Cache.Configuration;
namespace SimpleCase{ class SimpleCaseDemoOnIssue { const string SAMPLE_CACHE_NAME = @"simple_case_demo"; const string SAMPLE_TABLE_NAME = @"t_sample_entity";
static void Main(string[] args) { var ignite = Ignition.Start();
Console.WriteLine("This is some simple code demonstrating the issue involving .Net DateTime type"); Console.WriteLine("Sample starts ..."); Console.WriteLine();
var cache = ignite.GetOrCreateCache<object, object>(new CacheConfiguration(SAMPLE_CACHE_NAME) { SqlSchema = "PUBLIC" });
Console.WriteLine("Created cache " + SAMPLE_CACHE_NAME);
var createTalbeSql = $"create table if not exists {SAMPLE_TABLE_NAME} (entityid int, textfield varchar, datefield timestamp, primary key (entityid)) " + $"with \"key_type=int, value_type={typeof(SampleEntity).FullName}\"";
Console.WriteLine("Creating table ..."); Console.WriteLine(createTalbeSql); Console.WriteLine();
cache.Query(new SqlFieldsQuery(createTalbeSql)).GetAll();
Console.WriteLine($"Call ignite.GetCache<int, SampleEntity>(\"SQL_PUBLIC_{SAMPLE_TABLE_NAME.ToUpper()}\")"); Console.WriteLine(); var entityCache = ignite.GetCache<int, SampleEntity>($"SQL_PUBLIC_{SAMPLE_TABLE_NAME.ToUpper()}"); var cfg = entityCache.GetConfiguration();
Console.WriteLine($"Printing Cache configuration of {cfg.Name}"); Console.WriteLine(); foreach(var eachEntity in cfg.QueryEntities) { Console.WriteLine($"QueryEntity: TableName = {eachEntity.TableName} / ValueTypeName = {eachEntity.ValueTypeName}"); Console.WriteLine(string.Format("QueryField: {0,-15} {1,-15} {2,-15}", "Name", "FieldType", "FieldTypeNmae")); foreach (var eachField in eachEntity.Fields) { Console.WriteLine(string.Format(" {0,-15} {1,-15} {2,-15}", eachField.Name, eachField.FieldType.FullName, eachField.FieldTypeName)); } } Console.WriteLine();
Console.WriteLine("Going to put data to cache ..."); Console.WriteLine(); var entity0 = new SampleEntity(); entityCache.Put(entity0.EntityId, entity0);
var entity1 = entityCache.Get(entity0.EntityId); Console.WriteLine("Data put is ... " + entity1); Console.WriteLine();
var sql1 = $"select t.textfield from {SAMPLE_TABLE_NAME} t"; Console.WriteLine("Executing this ==> " + sql1); Console.WriteLine("which should succeed ... "); try { var result1 = entityCache.Query(new SqlFieldsQuery(sql1)).GetAll().FirstOrDefault(); Console.WriteLine("Result = {0}", result1 == null ? "N/A" : result1[0].ToString()); } catch (Exception ex) { Console.WriteLine($"Thrown {ex.GetType().FullName}"); } Console.WriteLine();
var sql2 = $"select t.datefield from {SAMPLE_TABLE_NAME} t"; Console.WriteLine("Executing this ==> " + sql2); Console.WriteLine("which should failed ... "); try { var result2 = entityCache.Query(new SqlFieldsQuery(sql2)).GetAll().FirstOrDefault(); Console.WriteLine("Result = {0}", result2 == null ? "N/A" : result2[0].ToString()); } catch (Exception ex) { Console.WriteLine($"Thrown {ex.GetType().FullName}"); } Console.WriteLine();
var sql3 = $"update {SAMPLE_TABLE_NAME} set datefield = sysdate where entityid = {entity1.EntityId}"; Console.WriteLine("Executing this ==> " + sql3); Console.WriteLine("which should succeed ... "); try { var result3 = entityCache.Query(new SqlFieldsQuery(sql3)).GetAll(); Console.WriteLine("Result returned = " + result3.First()[0]); } catch (Exception ex) { Console.WriteLine($"Thrown {ex.GetType().FullName}"); } Console.WriteLine();
Console.WriteLine("Executing again ==> " + sql2); Console.WriteLine("which should succeed ... "); try { var result4 = entityCache.Query(new SqlFieldsQuery(sql2)).GetAll().FirstOrDefault(); Console.WriteLine("Result = {0}", result4 == null ? "N/A" : result4[0].ToString()); } catch (Exception ex) { Console.WriteLine($"Thrown {ex.GetType().FullName}"); } Console.WriteLine();
Ignition.Stop(null, true); Console.WriteLine("Sample stopped ..."); } }
class SampleEntity { public SampleEntity() { // just setup with dummy value for testing. this.EntityId = 1; this.TextField = "Dummy data for 1"; this.DateField = DateTime.Now.ToUniversalTime(); } public int EntityId { get; set; } public string TextField { get; set; } public DateTime DateField { get; set; } public override string ToString() { return $"EntityId = {this.EntityId}; TextField = {this.TextField}; DateField = {this.DateField.ToString()}"; } }}
Re: Ignite.Net - Defining SQL Schema without using QuerySqlField
custom attribute
Posted by Pavel Tupitsyn <pt...@apache.org>.
There is a subtle issue with DateTime and SQL [1]
If you always use UTC in Ignite (which you should), the proper thing to do
is:
var igniteCfg = new IgniteConfiguration
{
BinaryConfiguration = new BinaryConfiguration
{
Serializer = new BinaryReflectiveSerializer
{
ForceTimestamp = true
}
}
};
var ignite = Ignition.Start(igniteCfg);
This way SQL-compatible format is enforced, and non-UTC values will
cause an exception.
[1] https://apacheignite-net.readme.io/docs/platform-interoperability#section-type-compatibility
On Tue, Nov 20, 2018 at 12:06 AM Peter Sham <pe...@yahoo.com> wrote:
> Sure. Hope this won't get too long and will still be readable from
> e-mail, and thank you for helping. Really appreciate it.
>
> using System;
> using System.Collections.Generic;
> using System.Linq;
> using System.Text;
> using System.Threading.Tasks;
> using Apache.Ignite.Core;
> using Apache.Ignite.Core.Cache;
> using Apache.Ignite.Core.Cache.Query;
> using Apache.Ignite.Core.Cache.Configuration;
>
> namespace SimpleCase
> {
> class SimpleCaseDemoOnIssue
> {
> const string SAMPLE_CACHE_NAME = @"simple_case_demo";
> const string SAMPLE_TABLE_NAME = @"t_sample_entity";
>
> static void Main(string[] args)
> {
> var ignite = Ignition.Start();
>
> Console.WriteLine("This is some simple code demonstrating the issue
> involving .Net DateTime type");
> Console.WriteLine("Sample starts ...");
> Console.WriteLine();
>
> var cache = ignite.GetOrCreateCache<object, object>(new
> CacheConfiguration(SAMPLE_CACHE_NAME) { SqlSchema = "PUBLIC" });
>
> Console.WriteLine("Created cache " + SAMPLE_CACHE_NAME);
>
> var createTalbeSql =
> $"create table if not exists {SAMPLE_TABLE_NAME} (entityid int, textfield
> varchar, datefield timestamp, primary key (entityid)) " +
> $"with \"key_type=int, value_type={typeof(SampleEntity).FullName}\"";
>
> Console.WriteLine("Creating table ...");
> Console.WriteLine(createTalbeSql);
> Console.WriteLine();
>
> cache.Query(new SqlFieldsQuery(createTalbeSql)).GetAll();
>
> Console.WriteLine($"Call ignite.GetCache<int,
> SampleEntity>(\"SQL_PUBLIC_{SAMPLE_TABLE_NAME.ToUpper()}\")");
> Console.WriteLine();
> var entityCache = ignite.GetCache<int,
> SampleEntity>($"SQL_PUBLIC_{SAMPLE_TABLE_NAME.ToUpper()}");
> var cfg = entityCache.GetConfiguration();
>
> Console.WriteLine($"Printing Cache configuration of {cfg.Name}");
> Console.WriteLine();
> foreach(var eachEntity in cfg.QueryEntities)
> {
> Console.WriteLine($"QueryEntity: TableName = {eachEntity.TableName} /
> ValueTypeName = {eachEntity.ValueTypeName}");
> Console.WriteLine(string.Format("QueryField: {0,-15} {1,-15} {2,-15}",
> "Name", "FieldType", "FieldTypeNmae"));
> foreach (var eachField in eachEntity.Fields)
> {
> Console.WriteLine(string.Format(" {0,-15} {1,-15} {2,-15}",
> eachField.Name, eachField.FieldType.FullName, eachField.FieldTypeName));
> }
> }
> Console.WriteLine();
>
> Console.WriteLine("Going to put data to cache ...");
> Console.WriteLine();
> var entity0 = new SampleEntity();
> entityCache.Put(entity0.EntityId, entity0);
>
> var entity1 = entityCache.Get(entity0.EntityId);
> Console.WriteLine("Data put is ... " + entity1);
> Console.WriteLine();
>
> var sql1 = $"select t.textfield from {SAMPLE_TABLE_NAME} t";
> Console.WriteLine("Executing this ==> " + sql1);
> Console.WriteLine("which should succeed ... ");
> try
> {
> var result1 = entityCache.Query(new
> SqlFieldsQuery(sql1)).GetAll().FirstOrDefault();
> Console.WriteLine("Result = {0}", result1 == null ? "N/A" :
> result1[0].ToString());
> }
> catch (Exception ex)
> {
> Console.WriteLine($"Thrown {ex.GetType().FullName}");
> }
> Console.WriteLine();
>
> var sql2 = $"select t.datefield from {SAMPLE_TABLE_NAME} t";
> Console.WriteLine("Executing this ==> " + sql2);
> Console.WriteLine("which should failed ... ");
> try
> {
> var result2 = entityCache.Query(new
> SqlFieldsQuery(sql2)).GetAll().FirstOrDefault();
> Console.WriteLine("Result = {0}", result2 == null ? "N/A" :
> result2[0].ToString());
> }
> catch (Exception ex)
> {
> Console.WriteLine($"Thrown {ex.GetType().FullName}");
> }
> Console.WriteLine();
>
> var sql3 = $"update {SAMPLE_TABLE_NAME} set datefield = sysdate where
> entityid = {entity1.EntityId}";
> Console.WriteLine("Executing this ==> " + sql3);
> Console.WriteLine("which should succeed ... ");
> try
> {
> var result3 = entityCache.Query(new SqlFieldsQuery(sql3)).GetAll();
> Console.WriteLine("Result returned = " + result3.First()[0]);
> }
> catch (Exception ex)
> {
> Console.WriteLine($"Thrown {ex.GetType().FullName}");
> }
> Console.WriteLine();
>
> Console.WriteLine("Executing again ==> " + sql2);
> Console.WriteLine("which should succeed ... ");
> try
> {
> var result4 = entityCache.Query(new
> SqlFieldsQuery(sql2)).GetAll().FirstOrDefault();
> Console.WriteLine("Result = {0}", result4 == null ? "N/A" :
> result4[0].ToString());
> }
> catch (Exception ex)
> {
> Console.WriteLine($"Thrown {ex.GetType().FullName}");
> }
> Console.WriteLine();
>
> Ignition.Stop(null, true);
> Console.WriteLine("Sample stopped ...");
> }
> }
>
> class SampleEntity
> {
> public SampleEntity()
> {
> // just setup with dummy value for testing.
> this.EntityId = 1;
> this.TextField = "Dummy data for 1";
> this.DateField = DateTime.Now.ToUniversalTime();
> }
> public int EntityId { get; set; }
> public string TextField { get; set; }
> public DateTime DateField { get; set; }
> public override string ToString()
> {
> return $"EntityId = {this.EntityId}; TextField = {this.TextField};
> DateField = {this.DateField.ToString()}";
> }
> }
> }
>
>
>
Re: Ignite.Net - Defining SQL Schema without using QuerySqlField
custom attribute
Posted by Peter Sham <pe...@yahoo.com>.
Sure. Hope this won't get too long and will still be readable from e-mail, and thank you for helping. Really appreciate it.
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Threading.Tasks;using Apache.Ignite.Core;using Apache.Ignite.Core.Cache;using Apache.Ignite.Core.Cache.Query;using Apache.Ignite.Core.Cache.Configuration;
namespace SimpleCase{ class SimpleCaseDemoOnIssue { const string SAMPLE_CACHE_NAME = @"simple_case_demo"; const string SAMPLE_TABLE_NAME = @"t_sample_entity";
static void Main(string[] args) { var ignite = Ignition.Start();
Console.WriteLine("This is some simple code demonstrating the issue involving .Net DateTime type"); Console.WriteLine("Sample starts ..."); Console.WriteLine();
var cache = ignite.GetOrCreateCache<object, object>(new CacheConfiguration(SAMPLE_CACHE_NAME) { SqlSchema = "PUBLIC" });
Console.WriteLine("Created cache " + SAMPLE_CACHE_NAME);
var createTalbeSql = $"create table if not exists {SAMPLE_TABLE_NAME} (entityid int, textfield varchar, datefield timestamp, primary key (entityid)) " + $"with \"key_type=int, value_type={typeof(SampleEntity).FullName}\"";
Console.WriteLine("Creating table ..."); Console.WriteLine(createTalbeSql); Console.WriteLine();
cache.Query(new SqlFieldsQuery(createTalbeSql)).GetAll();
Console.WriteLine($"Call ignite.GetCache<int, SampleEntity>(\"SQL_PUBLIC_{SAMPLE_TABLE_NAME.ToUpper()}\")"); Console.WriteLine(); var entityCache = ignite.GetCache<int, SampleEntity>($"SQL_PUBLIC_{SAMPLE_TABLE_NAME.ToUpper()}"); var cfg = entityCache.GetConfiguration();
Console.WriteLine($"Printing Cache configuration of {cfg.Name}"); Console.WriteLine(); foreach(var eachEntity in cfg.QueryEntities) { Console.WriteLine($"QueryEntity: TableName = {eachEntity.TableName} / ValueTypeName = {eachEntity.ValueTypeName}"); Console.WriteLine(string.Format("QueryField: {0,-15} {1,-15} {2,-15}", "Name", "FieldType", "FieldTypeNmae")); foreach (var eachField in eachEntity.Fields) { Console.WriteLine(string.Format(" {0,-15} {1,-15} {2,-15}", eachField.Name, eachField.FieldType.FullName, eachField.FieldTypeName)); } } Console.WriteLine();
Console.WriteLine("Going to put data to cache ..."); Console.WriteLine(); var entity0 = new SampleEntity(); entityCache.Put(entity0.EntityId, entity0);
var entity1 = entityCache.Get(entity0.EntityId); Console.WriteLine("Data put is ... " + entity1); Console.WriteLine();
var sql1 = $"select t.textfield from {SAMPLE_TABLE_NAME} t"; Console.WriteLine("Executing this ==> " + sql1); Console.WriteLine("which should succeed ... "); try { var result1 = entityCache.Query(new SqlFieldsQuery(sql1)).GetAll().FirstOrDefault(); Console.WriteLine("Result = {0}", result1 == null ? "N/A" : result1[0].ToString()); } catch (Exception ex) { Console.WriteLine($"Thrown {ex.GetType().FullName}"); } Console.WriteLine();
var sql2 = $"select t.datefield from {SAMPLE_TABLE_NAME} t"; Console.WriteLine("Executing this ==> " + sql2); Console.WriteLine("which should failed ... "); try { var result2 = entityCache.Query(new SqlFieldsQuery(sql2)).GetAll().FirstOrDefault(); Console.WriteLine("Result = {0}", result2 == null ? "N/A" : result2[0].ToString()); } catch (Exception ex) { Console.WriteLine($"Thrown {ex.GetType().FullName}"); } Console.WriteLine();
var sql3 = $"update {SAMPLE_TABLE_NAME} set datefield = sysdate where entityid = {entity1.EntityId}"; Console.WriteLine("Executing this ==> " + sql3); Console.WriteLine("which should succeed ... "); try { var result3 = entityCache.Query(new SqlFieldsQuery(sql3)).GetAll(); Console.WriteLine("Result returned = " + result3.First()[0]); } catch (Exception ex) { Console.WriteLine($"Thrown {ex.GetType().FullName}"); } Console.WriteLine();
Console.WriteLine("Executing again ==> " + sql2); Console.WriteLine("which should succeed ... "); try { var result4 = entityCache.Query(new SqlFieldsQuery(sql2)).GetAll().FirstOrDefault(); Console.WriteLine("Result = {0}", result4 == null ? "N/A" : result4[0].ToString()); } catch (Exception ex) { Console.WriteLine($"Thrown {ex.GetType().FullName}"); } Console.WriteLine();
Ignition.Stop(null, true); Console.WriteLine("Sample stopped ..."); } }
class SampleEntity { public SampleEntity() { // just setup with dummy value for testing. this.EntityId = 1; this.TextField = "Dummy data for 1"; this.DateField = DateTime.Now.ToUniversalTime(); } public int EntityId { get; set; } public string TextField { get; set; } public DateTime DateField { get; set; } public override string ToString() { return $"EntityId = {this.EntityId}; TextField = {this.TextField}; DateField = {this.DateField.ToString()}"; } }}
Re: Ignite.Net - Defining SQL Schema without using QuerySqlField
custom attribute
Posted by Pavel Tupitsyn <pt...@apache.org>.
Hi, can you please attach a minimal runnable project that reproduces the
issue?
On Fri, Nov 16, 2018 at 4:08 AM Peter Sham <pe...@yahoo.com> wrote:
> Hello,
>
> I’m trying to learn Ignite.Net and has been following examples bundled
> with source or binary distribution. However, when I try to define SQL
> Schema that will match an object either by sql create table statement or
> using QueryEntity in cache configuration, without resorting to
> QuerySqlFieldAttribute, I kept hitting an exception which said
>
> class org.apache.ignite.binary.BinaryInvalidTypeException: Unknown pair
> [platformId=0, typeId=-1854586790]"; SQL statement …
>
> Does anyone has an example that showcase how to define SQL Schema for an
> object without using custom attribute?
>
> Thank you very much.
>