Home Apache.Ignite.NET Query Key/Value data via ODBC
Reply: 1

Apache.Ignite.NET Query Key/Value data via ODBC

sam
1#
sam Published in 2018-02-14 06:28:18Z

Ignite has (amongst others) the use cases "in-memory key/value store" and "SQL via ODBC". I wonder if these use cases can be combined.

Let's use this model:

class Person
{
    public string Name { get; set; }
    public int Age { get; set; }
}

Add a 'Person' to the key/value store:

using (var ignite = Ignition.Start())
{
    var cache = ignite.GetOrCreateCache<int, Person>("person-cache");
    cache.Put(1, new Person { Name = "Test", Age=7 });
}

Now, can I do something like

SELECT * FROM Person; 

via an ODBC connection? If yes, code samples preferably in C# / Ignite.NET are very much appreciated. Or is it not possible because are we talking about entirely separate data-stores / caches?

Alexey Popov
2#
Alexey Popov Reply to 2018-02-14 12:11:29Z

Sure, you can do it with Apache Ignite. There are three things to do:

  1. update your Person model with the desired SQL fields:

    class Person
    {
        [QuerySqlField(IsIndexed = true)]
        public string Name { get; set; }
    
        [QuerySqlField]
        public int Age { get; set; }
    }
    
  2. Add SQL query types (Person type) to CacheConfiguration

  3. Don't forget to use a schema name (= Cache name) with quotation marks when accessing a table:

        using (var ignite = Ignition.Start())
        {
            var cache = ignite.GetOrCreateCache<int, Person>(new CacheConfiguration("person-cache", typeof(Person)));
    
            cache.Put(1, new Person { Name = "Test", Age = 7 });
    
            var dbConnection = new OdbcConnection(@"DRIVER={Apache Ignite};ADDRESS=localhost:10800");
            try
            {
                dbConnection.Open();
    
                using (var dbCommand = dbConnection.CreateCommand())
                {
                    dbCommand.CommandText = @"SELECT * FROM ""person-cache"".Person WHERE Name=?";
                    dbCommand.Parameters.Add("Name", OdbcType.Char).Value = "Test";
                    var dbReader = dbCommand.ExecuteReader();
    
                    var fCount = dbReader.FieldCount;
                    while (dbReader.Read())
                    {
                        for (var i = 0; i < fCount; i++)
                        {
                            var val = dbReader.GetString(i);
                            var name = dbReader.GetName(i);
    
                            Console.WriteLine(name + "=" + val);
                        }
                    }
                    dbReader.Close();
                }
            }
            catch (OdbcException ex)
            {
                Console.WriteLine(ex);
            }
    
            Console.ReadKey();
        }
    

Thank you, Alexey

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.300095 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO