CODEWITHSUNDEEP
ignite
aol
aol

Reputation: 377

Apache Ignite: how to insert into table with IDENTITY key (SQL Server)

I have a table in SQL Server where the primary key is autogenerated (identity column), i.e. 

CREATE TABLE TableName 
(
     table_id INT NOT NULL IDENTITY (1,1),
     some_field VARCHAR(20),

     PRIMARY KEY (table_id)
);

Since table_id is an autogenerated column, when I implemented the SqlFieldQuery INSERT clause I do not set any argument to table_id:

sql = new SqlFieldsQuery("INSERT INTO TableName (some_field) VALUES (?)");
cache.query(sql.setArgs("str");

However at runtime I get the following error:

Exception in thread "main" javax.cache.CacheException: class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to execute DML statement [stmt=INSERT INTO TableName (some_field) VALUES (?), params=["str"]]

at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:807)
at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:765)
...
Caused by: class org.apache.ignite.internal.processors.query.IgniteSQLException: Failed to execute DML statement [stmt=INSERT INTO TableName (some_field) VALUES (?), params=["str"]]

at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1324)
at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1815) at org.apache.ignite.internal.processors.query.GridQueryProcessor$5.applyx(GridQueryProcessor.java:1813) at org.apache.ignite.internal.util.lang.IgniteOutClosureX.apply(IgniteOutClosureX.java:36) at org.apache.ignite.internal.processors.query.GridQueryProcessor.executeQuery(GridQueryProcessor.java:2293) at org.apache.ignite.internal.processors.query.GridQueryProcessor.querySqlFields(GridQueryProcessor.java:1820) at org.apache.ignite.internal.processors.cache.IgniteCacheProxy.query(IgniteCacheProxy.java:795) ... 5 more Caused by: class org.apache.ignite.IgniteCheckedException: Key is missing from query at org.apache.ignite.internal.processors.query.h2.dml.UpdatePlanBuilder.createSupplier(UpdatePlanBuilder.java:331) at org.apache.ignite.internal.processors.query.h2.dml.UpdatePlanBuilder.planForInsert(UpdatePlanBuilder.java:196) at org.apache.ignite.internal.processors.query.h2.dml.UpdatePlanBuilder.planForStatement(UpdatePlanBuilder.java:82) at org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.getPlanForStatement(DmlStatementsProcessor.java:438) at org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.updateSqlFields(DmlStatementsProcessor.java:164) at org.apache.ignite.internal.processors.query.h2.DmlStatementsProcessor.updateSqlFieldsDistributed(DmlStatementsProcessor.java:222) at org.apache.ignite.internal.processors.query.h2.IgniteH2Indexing.queryDistributedSqlFields(IgniteH2Indexing.java:1321) ... 11 more

This is how I planned to implement the insertion because it seemed more tedious to get the max table_id from the cache, increment and insert. I thought I could omit the table_id from the insert and let SQL Server insert the pk, but it doesn't seem to work like this.

Can you please tell me how this should typically be implemented in Ignite? I checked the ignite-examples, unfortunately the examples are too simple (i.e. fixed keys only, like 1 or 2).

Moreover, how does Ignite support the use of sequences?

I am using ignite-core 2.2.0. Any help is appreciated! Thank you.

Upvotes: 2

Views: 3754

Answers (2)

alexmagnus
alexmagnus

Reputation: 1006

That's true that as for now autoincrement fields are not supported.
As an option, you could generate IDs manually via for example Ignite's ID generator.

Upvotes: 5

Andrey Mashenkov
Andrey Mashenkov

Reputation: 260

Ignite doesn't support identity columns [1] yet.

It may be non-obviuos, but Ignite SQL layer is built on top of key value store which can be backed by other CacheStore. Your SQL query will never go though to CacheStore as is.

Ignite internals will execute your query, save data in cache and only then update will be propagated to CacheStore which will create a new SQL query for your SQL server.

So, Ignite need the identity column value (actually a key) be known before data saved in cache.

[1] https://issues.apache.org/jira/browse/IGNITE-5625

Upvotes: 5

Related Questions