You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@ignite.apache.org by ozgurnevres <oz...@gmail.com> on 2017/03/27 08:47:52 UTC
How can I perform SqlFieldsQuery on DateTime field (binary cache)?
My query is:
var rows = _cache.QueryFields(new SqlFieldsQuery("select productid,
recorddate, inventory from product where recorddate > '2016-03-27'"));
and I get the exception below:
Failed to run map query remotely
but this works:
var rows = _cache.QueryFields(new SqlFieldsQuery("select productid,
recorddate, inventory from product where inventory > 10"));
Thanks
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-can-I-perform-SqlFieldsQuery-on-DateTime-field-binary-cache-tp11457.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How can I perform SqlFieldsQuery on DateTime field (binary
cache)?
Posted by ozgurnevres <oz...@gmail.com>.
Hi, Pitupitsyn
Thanks for the quick reply!
After trying a bit more, I realized that there might be another problem.
Here my steps if you want to reproduce (Five steps total):
1- I downloaded Apache.Ignite.Examples sln and working on it.
2- On BinaryCodeExample.cs, I first added a DateTime field to Person like
below:
var cache0 = ignite.GetOrCreateCache<object, object>(new CacheConfiguration
{
Name = CacheName,
QueryEntities = new[]
{
new QueryEntity
{
KeyType = typeof(int),
ValueTypeName = PersonType,
Fields = new[]
{
new QueryField(NameField, typeof(string)),
new QueryField(CompanyIdField, typeof(int)),
new QueryField("birthday", typeof(DateTime))
},
Indexes = new[] {new QueryIndex(false, QueryIndexType.FullText,
NameField)}
},
new QueryEntity
{
KeyType = typeof(int),
ValueTypeName = CompanyType,
Fields = new[]
{
new QueryField(IdField, typeof(int)),
new QueryField(NameField, typeof(string))
}
}
}
});
// Switch to binary mode to work with data in serialized form.
var cache = cache0.WithKeepBinary<int, IBinaryObject>();
// Clean up caches on all nodes before run.
cache.Clear();
// Populate cache with sample data entries.
PopulateCache(cache);
// Run SQL fields query example.
SqlFieldsQueryExample(cache);
3- Then, I modified the PopulateCache method like below:
private static void PopulateCache(ICache<int, IBinaryObject> cache)
{
IBinary binary = cache.Ignite.GetBinary();
// Populate persons.
cache[1] = binary.GetBuilder(PersonType)
.SetField(NameField, "James Wilson")
.SetField(CompanyIdField, -1)
.SetField("birthday", new DateTime(1950, 1, 1))
.Build();
cache[2] = binary.GetBuilder(PersonType)
.SetField(NameField, "Daniel Adams")
.SetField(CompanyIdField, -1)
.SetField("birthday", new DateTime(1960, 1, 1))
.Build();
cache[3] = binary.GetBuilder(PersonType)
.SetField(NameField, "Cristian Moss")
.SetField(CompanyIdField, -1)
.SetField("birthday", new DateTime(1970, 1, 1))
.Build();
cache[4] = binary.GetBuilder(PersonType)
.SetField(NameField, "Allison Mathis")
.SetField(CompanyIdField, -2)
.SetField("birthday", new DateTime(1980, 1, 1))
.Build();
cache[5] = binary.GetBuilder(PersonType)
.SetField(NameField, "Breana Robbin")
.SetField(CompanyIdField, -2)
.SetField("birthday", new DateTime(1990, 1, 1))
.Build();
cache[6] = binary.GetBuilder(PersonType)
.SetField(NameField, "Philip Horsley")
.SetField(CompanyIdField, -2)
.SetField("birthday", new DateTime(2000, 1, 1))
.Build();
cache[7] = binary.GetBuilder(PersonType)
.SetField(NameField, "James Peters")
.SetField(CompanyIdField, -2)
.SetField("birthday", new DateTime(2010, 1, 1))
.Build();
// Populate companies.
cache[-1] = binary.GetBuilder(CompanyType)
.SetField(NameField, "Apache")
.SetField(IdField, -1)
.Build();
cache[-2] = binary.GetBuilder(CompanyType)
.SetField(NameField, "Microsoft")
.SetField(IdField, -2)
.Build();
}
4- Finally, I changed the SqlFieldsQueryExample method like below:
private static void SqlFieldsQueryExample(ICache<int, IBinaryObject>
cache)
{
var qry = cache.QueryFields(new SqlFieldsQuery("select name,
birthday from Person order by name"));
Console.WriteLine();
Console.WriteLine(">>> All person names:");
foreach (IList row in qry)
Console.WriteLine(">>> " + row[0]);
}
Please note that I only added "birthday" to the query text.
5- Then I get the error "Failed to run map query remotely"
If I change the query to below, it works. So, I think, the problem is
"birthday" field, which is DateTime.
var qry = cache.QueryFields(new SqlFieldsQuery("select name from Person
order by name"));
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-can-I-perform-SqlFieldsQuery-on-DateTime-field-binary-cache-tp11457p11469.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How can I perform SqlFieldsQuery on DateTime field (binary
cache)?
Posted by ozgurnevres <oz...@gmail.com>.
Sorry my mistake
it works, thanks!
I wrote "birthdate" instead of "birthday" at the query line.
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-can-I-perform-SqlFieldsQuery-on-DateTime-field-binary-cache-tp11457p11474.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How can I perform SqlFieldsQuery on DateTime field (binary cache)?
Posted by Pavel Tupitsyn <pt...@apache.org>.
Can you attach full exception details, with inner exception and so on?
On Mon, Mar 27, 2017 at 4:29 PM, ozgurnevres <oz...@gmail.com> wrote:
>
>
> Hi, again
> Thanks for the answer.
>
> Now my PopulateCache method is like below (I didn't change any other thing
> from my previous post):
>
> private static void PopulateCache(ICache<int, IBinaryObject> cache)
> {
> IBinary binary = cache.Ignite.GetBinary();
>
> // Populate persons.
> cache[1] = binary.GetBuilder(PersonType)
> .SetField(NameField, "James Wilson")
> .SetField(CompanyIdField, -1)
> .SetTimestampField("birthday", new DateTime(1950, 1,
> 1).ToUniversalTime())
> .Build();
>
> cache[2] = binary.GetBuilder(PersonType)
> .SetField(NameField, "Daniel Adams")
> .SetField(CompanyIdField, -1)
> .SetTimestampField("birthday", new DateTime(1960, 1,
> 1).ToUniversalTime())
> .Build();
>
> cache[3] = binary.GetBuilder(PersonType)
> .SetField(NameField, "Cristian Moss")
> .SetField(CompanyIdField, -1)
> .SetTimestampField("birthday", new DateTime(1970, 1,
> 1).ToUniversalTime())
> .Build();
>
> cache[4] = binary.GetBuilder(PersonType)
> .SetField(NameField, "Allison Mathis")
> .SetField(CompanyIdField, -2)
> .SetTimestampField("birthday", new DateTime(1980, 1,
> 1).ToUniversalTime())
> .Build();
>
> cache[5] = binary.GetBuilder(PersonType)
> .SetField(NameField, "Breana Robbin")
> .SetField(CompanyIdField, -2)
> .SetTimestampField("birthday", new DateTime(1990, 1,
> 1).ToUniversalTime())
> .Build();
>
> cache[6] = binary.GetBuilder(PersonType)
> .SetField(NameField, "Philip Horsley")
> .SetField(CompanyIdField, -2)
> .SetTimestampField("birthday", new DateTime(2000, 1,
> 1).ToUniversalTime())
> .Build();
>
> cache[7] = binary.GetBuilder(PersonType)
> .SetField(NameField, "James Peters")
> .SetField(CompanyIdField, -2)
> .SetTimestampField("birthday", new DateTime(2010, 1,
> 1).ToUniversalTime())
> .Build();
>
> // Populate companies.
> cache[-1] = binary.GetBuilder(CompanyType)
> .SetField(NameField, "Apache")
> .SetField(IdField, -1)
> .Build();
>
> cache[-2] = binary.GetBuilder(CompanyType)
> .SetField(NameField, "Microsoft")
> .SetField(IdField, -2)
> .Build();
> }
>
> I am still getting an exception saying "Failed to parse query: select name,
> birthdate from Person order by name" at the line below:
>
> var qry = cache.QueryFields(new SqlFieldsQuery("select name,
> birthdate from Person order by name"));
>
> ptupitsyn wrote
> > Use SetTimestampField with DateTime instead of SetField, and use
> > ToUniversalTime on values.
> >
> > DateTime can be written in two formats, details:
> > https://apacheignite-net.readme.io/docs/sql-queries#
> section-java-type-name-mapping
>
>
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/How-can-I-perform-SqlFieldsQuery-on-
> DateTime-field-binary-cache-tp11457p11473.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: How can I perform SqlFieldsQuery on DateTime field (binary
cache)?
Posted by ozgurnevres <oz...@gmail.com>.
Hi, again
Thanks for the answer.
Now my PopulateCache method is like below (I didn't change any other thing
from my previous post):
private static void PopulateCache(ICache<int, IBinaryObject> cache)
{
IBinary binary = cache.Ignite.GetBinary();
// Populate persons.
cache[1] = binary.GetBuilder(PersonType)
.SetField(NameField, "James Wilson")
.SetField(CompanyIdField, -1)
.SetTimestampField("birthday", new DateTime(1950, 1,
1).ToUniversalTime())
.Build();
cache[2] = binary.GetBuilder(PersonType)
.SetField(NameField, "Daniel Adams")
.SetField(CompanyIdField, -1)
.SetTimestampField("birthday", new DateTime(1960, 1,
1).ToUniversalTime())
.Build();
cache[3] = binary.GetBuilder(PersonType)
.SetField(NameField, "Cristian Moss")
.SetField(CompanyIdField, -1)
.SetTimestampField("birthday", new DateTime(1970, 1,
1).ToUniversalTime())
.Build();
cache[4] = binary.GetBuilder(PersonType)
.SetField(NameField, "Allison Mathis")
.SetField(CompanyIdField, -2)
.SetTimestampField("birthday", new DateTime(1980, 1,
1).ToUniversalTime())
.Build();
cache[5] = binary.GetBuilder(PersonType)
.SetField(NameField, "Breana Robbin")
.SetField(CompanyIdField, -2)
.SetTimestampField("birthday", new DateTime(1990, 1,
1).ToUniversalTime())
.Build();
cache[6] = binary.GetBuilder(PersonType)
.SetField(NameField, "Philip Horsley")
.SetField(CompanyIdField, -2)
.SetTimestampField("birthday", new DateTime(2000, 1,
1).ToUniversalTime())
.Build();
cache[7] = binary.GetBuilder(PersonType)
.SetField(NameField, "James Peters")
.SetField(CompanyIdField, -2)
.SetTimestampField("birthday", new DateTime(2010, 1,
1).ToUniversalTime())
.Build();
// Populate companies.
cache[-1] = binary.GetBuilder(CompanyType)
.SetField(NameField, "Apache")
.SetField(IdField, -1)
.Build();
cache[-2] = binary.GetBuilder(CompanyType)
.SetField(NameField, "Microsoft")
.SetField(IdField, -2)
.Build();
}
I am still getting an exception saying "Failed to parse query: select name,
birthdate from Person order by name" at the line below:
var qry = cache.QueryFields(new SqlFieldsQuery("select name,
birthdate from Person order by name"));
ptupitsyn wrote
> Use SetTimestampField with DateTime instead of SetField, and use
> ToUniversalTime on values.
>
> DateTime can be written in two formats, details:
> https://apacheignite-net.readme.io/docs/sql-queries#section-java-type-name-mapping
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-can-I-perform-SqlFieldsQuery-on-DateTime-field-binary-cache-tp11457p11473.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How can I perform SqlFieldsQuery on DateTime field (binary cache)?
Posted by Pavel Tupitsyn <pt...@apache.org>.
Use SetTimestampField with DateTime instead of SetField, and use
ToUniversalTime on values.
DateTime can be written in two formats, details:
https://apacheignite-net.readme.io/docs/sql-queries#section-java-type-name-mapping
On Mon, Mar 27, 2017 at 3:02 PM, ozgurnevres <oz...@gmail.com> wrote:
> And I forgot to answer your question, yes, this is Ignite.net
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/How-can-I-perform-SqlFieldsQuery-on-
> DateTime-field-binary-cache-tp11457p11470.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>
Re: How can I perform SqlFieldsQuery on DateTime field (binary
cache)?
Posted by ozgurnevres <oz...@gmail.com>.
And I forgot to answer your question, yes, this is Ignite.net
--
View this message in context: http://apache-ignite-users.70518.x6.nabble.com/How-can-I-perform-SqlFieldsQuery-on-DateTime-field-binary-cache-tp11457p11470.html
Sent from the Apache Ignite Users mailing list archive at Nabble.com.
Re: How can I perform SqlFieldsQuery on DateTime field (binary cache)?
Posted by Pavel Tupitsyn <pt...@apache.org>.
Hi, this is Ignite.NET, right?
You should always use SQL parameters:
var rows = _cache.QueryFields(new SqlFieldsQuery("select productid,
recorddate, inventory from product where inventory > ?", 10));
With DateTime just make sure it is UTC:
var rows = _cache.QueryFields(new SqlFieldsQuery("select productid,
recorddate, inventory from product where recorddate > ?", new
DateTime(2016, 03, 27, 0, 0, 0, DateTimeKind.Utc)));
On Mon, Mar 27, 2017 at 11:47 AM, ozgurnevres <oz...@gmail.com>
wrote:
> My query is:
>
> var rows = _cache.QueryFields(new SqlFieldsQuery("select productid,
> recorddate, inventory from product where recorddate > '2016-03-27'"));
>
> and I get the exception below:
> Failed to run map query remotely
>
> but this works:
> var rows = _cache.QueryFields(new SqlFieldsQuery("select productid,
> recorddate, inventory from product where inventory > 10"));
>
> Thanks
>
>
>
> --
> View this message in context: http://apache-ignite-users.
> 70518.x6.nabble.com/How-can-I-perform-SqlFieldsQuery-on-
> DateTime-field-binary-cache-tp11457.html
> Sent from the Apache Ignite Users mailing list archive at Nabble.com.
>