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 <[email protected]> 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()}";
> }
> }
> }
>
>
>
