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