@SelectKey generation at insert with SQL Server

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

@SelectKey generation at insert with SQL Server

Hernán J. González
How to make the insert with identity key setting work in SQL Server? 

According to MyBatisGenerator, http://www.mybatis.org/generator/configreference/generatedKey.html 
one should rely on "SELECT SCOPE_IDENTITY()" for that, but it does not work for me

public class TestMapper {
    @Insert({ "insert into TEST (VAL_INT) values " +
          "(#{valInt,jdbcType=INTEGER})" })
    @SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class)
    int insert(Test record);
}

(more details here: https://stackoverflow.com/questions/47024704/mybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key )

Also, I've seen an old post (2008!) http://ibatis.10938.n7.nabble.com/SELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html 
that seems to conclude that MyBatis executes the insert and the select in different batches (strange!), and that's
why  SELECT SCOPE_IDENTITY() cannot work. 
If that is so, then MyBatisGenerator is wrong in recommending that way, no? Or am I doing something wrong?
Has this ever worked?

BTW, I've use this approach with Postgresql (reading the sequence, instead of SELECT SCOPE_IDENTITY() , of course), without problems.


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: @SelectKey generation at insert with SQL Server

Kazuki Shimizu
I've reproduced this issue using SQLServer on Linux (using Docker image provided Microsoft).
In this case, using @@IDENTITY variable work fine. But we should be careful the specification that @@IDENTITY is returning the last identity on all scopes. 
FOR details for @@IDENTITY, please see reference document.

As an alternative method, I will suggest using the auto-generated keys feature provided JDBC 3.0 rather than the selectKey feature provided MyBatis.

How to use auto-generated Keys feature provided by JDBC 3.0 on MyBatis:

public interface TestMapper {
 
@Insert({ "insert into TEST (VAL_INT) values " +
     
"(#{valInt,jdbcType=INTEGER})" })
 
@Options(useGeneratedKeys = true) // Adding this line instread of @SelectKey
 
int insert(Test record);
}


Note:
I don't know why SCOPE_IDENTITY() return null via JDBC Driver. It works fine via "sqlcmd" (CUI tool provided the SQLServer).

Thanks.


On Tuesday, October 31, 2017 at 8:09:13 AM UTC+9, Hernán J. González wrote:
How to make the insert with identity key setting work in SQL Server? 

According to MyBatisGenerator, <a href="http://www.mybatis.org/generator/configreference/generatedKey.html" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.mybatis.org%2Fgenerator%2Fconfigreference%2FgeneratedKey.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHiwwLuyst3lJJIDDsMY686vF3HUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.mybatis.org%2Fgenerator%2Fconfigreference%2FgeneratedKey.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHiwwLuyst3lJJIDDsMY686vF3HUQ&#39;;return true;">http://www.mybatis.org/generator/configreference/generatedKey.html 
one should rely on "SELECT SCOPE_IDENTITY()" for that, but it does not work for me

public class TestMapper {
    @Insert({ "insert into TEST (VAL_INT) values " +
          "(#{valInt,jdbcType=INTEGER})" })
    @SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class)
    int insert(Test record);
}

(more details here: <a href="https://stackoverflow.com/questions/47024704/mybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F47024704%2Fmybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFcVt6lBYx2w1ngJEuq_ykedttc4g&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F47024704%2Fmybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFcVt6lBYx2w1ngJEuq_ykedttc4g&#39;;return true;">https://stackoverflow.com/questions/47024704/mybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key )

Also, I've seen an old post (2008!) <a href="http://ibatis.10938.n7.nabble.com/SELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fibatis.10938.n7.nabble.com%2FSELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFlkxDnAcLFOrPrzztG4JjDq-CL-Q&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fibatis.10938.n7.nabble.com%2FSELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFlkxDnAcLFOrPrzztG4JjDq-CL-Q&#39;;return true;">http://ibatis.10938.n7.nabble.com/SELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html 
that seems to conclude that MyBatis executes the insert and the select in different batches (strange!), and that's
why  SELECT SCOPE_IDENTITY() cannot work. 
If that is so, then MyBatisGenerator is wrong in recommending that way, no? Or am I doing something wrong?
Has this ever worked?

BTW, I've use this approach with Postgresql (reading the sequence, instead of SELECT SCOPE_IDENTITY() , of course), without problems.


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: @SelectKey generation at insert with SQL Server

Kazuki Shimizu
If PreparedStatment is used, return null. I tried using Statement as follow:

public interface TestMapper {
 
@Insert("insert into TEST (VAL_INT) values (100)") // No use bind variable (=#{..}) in SQL
 
@Options(statementType = StatementType.STATEMENT) // Adding this line
 
@SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class, statementType = StatementType.STATEMENT) // Set statementType = StatementType.STATEMENT
 
int insert(Test record);
}

However, this solution is not practical.
I propose confirming the specification of the SQLServer JDBC Driver.

Thanks.

On Saturday, November 4, 2017 at 11:46:07 AM UTC+9, Kazuki Shimizu wrote:
I've reproduced this issue using <a href="https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Flinux%2Fquickstart-install-connect-docker\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH7O4ldlbA7wjKZt-FW5XIM_WWVaw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Flinux%2Fquickstart-install-connect-docker\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH7O4ldlbA7wjKZt-FW5XIM_WWVaw&#39;;return true;">SQLServer on Linux (using Docker image provided Microsoft).
In this case, using @@IDENTITY variable work fine. But we should be careful the specification that @@IDENTITY is returning the last identity on all scopes. 
FOR details for @@IDENTITY, please see <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fidentity-transact-sql\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHg564lNFIUlLfWincCCLXOGDrL5w&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fidentity-transact-sql\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHg564lNFIUlLfWincCCLXOGDrL5w&#39;;return true;">reference document.

As an alternative method, I will suggest using the auto-generated keys feature provided JDBC 3.0 rather than the selectKey feature provided MyBatis.
The SQL Server JDBC Driver supports it. (<a href="https://docs.microsoft.com/en-us/sql/connect/jdbc/using-auto-generated-keys" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fconnect%2Fjdbc%2Fusing-auto-generated-keys\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGRw_G4qDFq83brqIbnDEwSxVNc6A&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fconnect%2Fjdbc%2Fusing-auto-generated-keys\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGRw_G4qDFq83brqIbnDEwSxVNc6A&#39;;return true;">https://docs.microsoft.com/en-us/sql/connect/jdbc/using-auto-generated-keys)

How to use auto-generated Keys feature provided by JDBC 3.0 on MyBatis:

public interface TestMapper {
 
@Insert({ "insert into TEST (VAL_INT) values " +
     
"(#{valInt,jdbcType=INTEGER})" })
 
@Options(useGeneratedKeys = true) // Adding this line instread of @SelectKey
 
int insert(Test record);
}


Note:
I don't know why SCOPE_IDENTITY() return null via JDBC Driver. It works fine via "sqlcmd" (CUI tool provided the SQLServer).

Thanks.


On Tuesday, October 31, 2017 at 8:09:13 AM UTC+9, Hernán J. González wrote:
How to make the insert with identity key setting work in SQL Server? 

According to MyBatisGenerator, <a href="http://www.mybatis.org/generator/configreference/generatedKey.html" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.mybatis.org%2Fgenerator%2Fconfigreference%2FgeneratedKey.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHiwwLuyst3lJJIDDsMY686vF3HUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.mybatis.org%2Fgenerator%2Fconfigreference%2FgeneratedKey.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHiwwLuyst3lJJIDDsMY686vF3HUQ&#39;;return true;">http://www.mybatis.org/generator/configreference/generatedKey.html 
one should rely on "SELECT SCOPE_IDENTITY()" for that, but it does not work for me

public class TestMapper {
    @Insert({ "insert into TEST (VAL_INT) values " +
          "(#{valInt,jdbcType=INTEGER})" })
    @SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class)
    int insert(Test record);
}

(more details here: <a href="https://stackoverflow.com/questions/47024704/mybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F47024704%2Fmybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFcVt6lBYx2w1ngJEuq_ykedttc4g&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F47024704%2Fmybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFcVt6lBYx2w1ngJEuq_ykedttc4g&#39;;return true;">https://stackoverflow.com/questions/47024704/mybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key )

Also, I've seen an old post (2008!) <a href="http://ibatis.10938.n7.nabble.com/SELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fibatis.10938.n7.nabble.com%2FSELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFlkxDnAcLFOrPrzztG4JjDq-CL-Q&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fibatis.10938.n7.nabble.com%2FSELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFlkxDnAcLFOrPrzztG4JjDq-CL-Q&#39;;return true;">http://ibatis.10938.n7.nabble.com/SELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html 
that seems to conclude that MyBatis executes the insert and the select in different batches (strange!), and that's
why  SELECT SCOPE_IDENTITY() cannot work. 
If that is so, then MyBatisGenerator is wrong in recommending that way, no? Or am I doing something wrong?
Has this ever worked?

BTW, I've use this approach with Postgresql (reading the sequence, instead of SELECT SCOPE_IDENTITY() , of course), without problems.


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Tim
Reply | Threaded
Open this post in threaded view
|

Re: @SelectKey generation at insert with SQL Server

Tim
Try using just the generatedKeys

In xml I use:

insert id="insertFoo" useGeneratedKeys="true" keyProperty="id" keyColumn="id"

Note that keyProperty and keyColumn are important.

On Fri, Nov 3, 2017 at 11:36 PM, Kazuki Shimizu <[hidden email]> wrote:
If PreparedStatment is used, return null. I tried using Statement as follow:

public interface TestMapper {
 
@Insert("insert into TEST (VAL_INT) values (100)") // No use bind variable (=#{..}) in SQL
 
@Options(statementType = StatementType.STATEMENT) // Adding this line
 
@SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class, statementType = StatementType.STATEMENT) // Set statementType = StatementType.STATEMENT
 
int insert(Test record);
}

However, this solution is not practical.
I propose confirming the specification of the SQLServer JDBC Driver.

Thanks.

On Saturday, November 4, 2017 at 11:46:07 AM UTC+9, Kazuki Shimizu wrote:
I've reproduced this issue using SQLServer on Linux (using Docker image provided Microsoft).
In this case, using @@IDENTITY variable work fine. But we should be careful the specification that @@IDENTITY is returning the last identity on all scopes. 
FOR details for @@IDENTITY, please see reference document.

As an alternative method, I will suggest using the auto-generated keys feature provided JDBC 3.0 rather than the selectKey feature provided MyBatis.

How to use auto-generated Keys feature provided by JDBC 3.0 on MyBatis:

public interface TestMapper {
 
@Insert({ "insert into TEST (VAL_INT) values " +
     
"(#{valInt,jdbcType=INTEGER})" })
 
@Options(useGeneratedKeys = true) // Adding this line instread of @SelectKey
 
int insert(Test record);
}


Note:
I don't know why SCOPE_IDENTITY() return null via JDBC Driver. It works fine via "sqlcmd" (CUI tool provided the SQLServer).

Thanks.


On Tuesday, October 31, 2017 at 8:09:13 AM UTC+9, Hernán J. González wrote:
How to make the insert with identity key setting work in SQL Server? 

one should rely on "SELECT SCOPE_IDENTITY()" for that, but it does not work for me

public class TestMapper {
    @Insert({ "insert into TEST (VAL_INT) values " +
          "(#{valInt,jdbcType=INTEGER})" })
    @SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class)
    int insert(Test record);
}


that seems to conclude that MyBatis executes the insert and the select in different batches (strange!), and that's
why  SELECT SCOPE_IDENTITY() cannot work. 
If that is so, then MyBatisGenerator is wrong in recommending that way, no? Or am I doing something wrong?
Has this ever worked?

BTW, I've use this approach with Postgresql (reading the sequence, instead of SELECT SCOPE_IDENTITY() , of course), without problems.


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Tim
Reply | Threaded
Open this post in threaded view
|

Re: @SelectKey generation at insert with SQL Server

Tim
The annotation equivalent would be similar to what Kazuki posted:

@Insert({ "insert into TEST (VAL_INT) values (#{valInt,jdbcType=INTEGER})" })
@Options(useGeneratedKeys=true, keyProperty="id", keyColumn="id")
This is what I use on SQL Server but if it's not working for you let me know what version of SQL server and which driver you are using (jtds or the official ms one?)

On Sat, Nov 4, 2017 at 11:51 AM, Tim <[hidden email]> wrote:
Try using just the generatedKeys

In xml I use:

insert id="insertFoo" useGeneratedKeys="true" keyProperty="id" keyColumn="id"

Note that keyProperty and keyColumn are important.

On Fri, Nov 3, 2017 at 11:36 PM, Kazuki Shimizu <[hidden email]> wrote:
If PreparedStatment is used, return null. I tried using Statement as follow:

public interface TestMapper {
 
@Insert("insert into TEST (VAL_INT) values (100)") // No use bind variable (=#{..}) in SQL
 
@Options(statementType = StatementType.STATEMENT) // Adding this line
 
@SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class, statementType = StatementType.STATEMENT) // Set statementType = StatementType.STATEMENT
 
int insert(Test record);
}

However, this solution is not practical.
I propose confirming the specification of the SQLServer JDBC Driver.

Thanks.

On Saturday, November 4, 2017 at 11:46:07 AM UTC+9, Kazuki Shimizu wrote:
I've reproduced this issue using SQLServer on Linux (using Docker image provided Microsoft).
In this case, using @@IDENTITY variable work fine. But we should be careful the specification that @@IDENTITY is returning the last identity on all scopes. 
FOR details for @@IDENTITY, please see reference document.

As an alternative method, I will suggest using the auto-generated keys feature provided JDBC 3.0 rather than the selectKey feature provided MyBatis.

How to use auto-generated Keys feature provided by JDBC 3.0 on MyBatis:

public interface TestMapper {
 
@Insert({ "insert into TEST (VAL_INT) values " +
     
"(#{valInt,jdbcType=INTEGER})" })
 
@Options(useGeneratedKeys = true) // Adding this line instread of @SelectKey
 
int insert(Test record);
}


Note:
I don't know why SCOPE_IDENTITY() return null via JDBC Driver. It works fine via "sqlcmd" (CUI tool provided the SQLServer).

Thanks.


On Tuesday, October 31, 2017 at 8:09:13 AM UTC+9, Hernán J. González wrote:
How to make the insert with identity key setting work in SQL Server? 

one should rely on "SELECT SCOPE_IDENTITY()" for that, but it does not work for me

public class TestMapper {
    @Insert({ "insert into TEST (VAL_INT) values " +
          "(#{valInt,jdbcType=INTEGER})" })
    @SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class)
    int insert(Test record);
}


that seems to conclude that MyBatis executes the insert and the select in different batches (strange!), and that's
why  SELECT SCOPE_IDENTITY() cannot work. 
If that is so, then MyBatisGenerator is wrong in recommending that way, no? Or am I doing something wrong?
Has this ever worked?

BTW, I've use this approach with Postgresql (reading the sequence, instead of SELECT SCOPE_IDENTITY() , of course), without problems.


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: @SelectKey generation at insert with SQL Server

Hernán J. González
In reply to this post by Tim
This worked for me, thanks.
A pity that MyBatisGenerator does not generate this...

On Saturday, November 4, 2017 at 12:51:50 PM UTC-3, h3adache wrote:
Try using just the generatedKeys

In xml I use:

insert id="insertFoo" useGeneratedKeys="true" keyProperty="id" keyColumn="id"

Note that keyProperty and keyColumn are important.

On Fri, Nov 3, 2017 at 11:36 PM, Kazuki Shimizu <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="L-4jPbMDBwAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">kazuk...@...> wrote:
If PreparedStatment is used, return null. I tried using Statement as follow:

public interface TestMapper {
 
@Insert("insert into TEST (VAL_INT) values (100)") // No use bind variable (=#{..}) in SQL
 
@Options(statementType = StatementType.STATEMENT) // Adding this line
 
@SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class, statementType = StatementType.STATEMENT) // Set statementType = StatementType.STATEMENT
 
int insert(Test record);
}

However, this solution is not practical.
I propose confirming the specification of the SQLServer JDBC Driver.

Thanks.

On Saturday, November 4, 2017 at 11:46:07 AM UTC+9, Kazuki Shimizu wrote:
I've reproduced this issue using <a href="https://docs.microsoft.com/en-us/sql/linux/quickstart-install-connect-docker" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Flinux%2Fquickstart-install-connect-docker\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH7O4ldlbA7wjKZt-FW5XIM_WWVaw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Flinux%2Fquickstart-install-connect-docker\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNH7O4ldlbA7wjKZt-FW5XIM_WWVaw&#39;;return true;">SQLServer on Linux (using Docker image provided Microsoft).
In this case, using @@IDENTITY variable work fine. But we should be careful the specification that @@IDENTITY is returning the last identity on all scopes. 
FOR details for @@IDENTITY, please see <a href="https://docs.microsoft.com/en-us/sql/t-sql/functions/identity-transact-sql" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fidentity-transact-sql\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHg564lNFIUlLfWincCCLXOGDrL5w&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Ffunctions%2Fidentity-transact-sql\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHg564lNFIUlLfWincCCLXOGDrL5w&#39;;return true;">reference document.

As an alternative method, I will suggest using the auto-generated keys feature provided JDBC 3.0 rather than the selectKey feature provided MyBatis.
The SQL Server JDBC Driver supports it. (<a href="https://docs.microsoft.com/en-us/sql/connect/jdbc/using-auto-generated-keys" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fconnect%2Fjdbc%2Fusing-auto-generated-keys\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGRw_G4qDFq83brqIbnDEwSxVNc6A&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Fconnect%2Fjdbc%2Fusing-auto-generated-keys\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGRw_G4qDFq83brqIbnDEwSxVNc6A&#39;;return true;">https://docs.microsoft.com/en-us/sql/connect/jdbc/using-auto-generated-keys)

How to use auto-generated Keys feature provided by JDBC 3.0 on MyBatis:

public interface TestMapper {
 
@Insert({ "insert into TEST (VAL_INT) values " +
     
"(#{valInt,jdbcType=INTEGER})" })
 
@Options(useGeneratedKeys = true) // Adding this line instread of @SelectKey
 
int insert(Test record);
}


Note:
I don't know why SCOPE_IDENTITY() return null via JDBC Driver. It works fine via "sqlcmd" (CUI tool provided the SQLServer).

Thanks.


On Tuesday, October 31, 2017 at 8:09:13 AM UTC+9, Hernán J. González wrote:
How to make the insert with identity key setting work in SQL Server? 

According to MyBatisGenerator, <a href="http://www.mybatis.org/generator/configreference/generatedKey.html" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.mybatis.org%2Fgenerator%2Fconfigreference%2FgeneratedKey.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHiwwLuyst3lJJIDDsMY686vF3HUQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fwww.mybatis.org%2Fgenerator%2Fconfigreference%2FgeneratedKey.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHiwwLuyst3lJJIDDsMY686vF3HUQ&#39;;return true;">http://www.mybatis.org/generator/configreference/generatedKey.html 
one should rely on "SELECT SCOPE_IDENTITY()" for that, but it does not work for me

public class TestMapper {
    @Insert({ "insert into TEST (VAL_INT) values " +
          "(#{valInt,jdbcType=INTEGER})" })
    @SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class)
    int insert(Test record);
}

(more details here: <a href="https://stackoverflow.com/questions/47024704/mybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key" rel="nofollow" target="_blank" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F47024704%2Fmybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFcVt6lBYx2w1ngJEuq_ykedttc4g&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fquestions%2F47024704%2Fmybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFcVt6lBYx2w1ngJEuq_ykedttc4g&#39;;return true;">https://stackoverflow.com/questions/47024704/mybatis-with-sql-server-select-scope-identity-does-not-set-the-assigned-key )

Also, I've seen an old post (2008!) <a href="http://ibatis.10938.n7.nabble.com/SELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html" rel="nofollow" target="_blank" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fibatis.10938.n7.nabble.com%2FSELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFlkxDnAcLFOrPrzztG4JjDq-CL-Q&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fibatis.10938.n7.nabble.com%2FSELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNFlkxDnAcLFOrPrzztG4JjDq-CL-Q&#39;;return true;">http://ibatis.10938.n7.nabble.com/SELECT-IDENTITY-vs-SELECT-SCOPE-IDENTITY-td23780.html 
that seems to conclude that MyBatis executes the insert and the select in different batches (strange!), and that's
why  SELECT SCOPE_IDENTITY() cannot work. 
If that is so, then MyBatisGenerator is wrong in recommending that way, no? Or am I doing something wrong?
Has this ever worked?

BTW, I've use this approach with Postgresql (reading the sequence, instead of SELECT SCOPE_IDENTITY() , of course), without problems.


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to <a href="javascript:" target="_blank" gdf-obfuscated-mailto="L-4jPbMDBwAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">mybatis-user...@googlegroups.com.
For more options, visit <a href="https://groups.google.com/d/optout" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/optout&#39;;return true;">https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.
Reply | Threaded
Open this post in threaded view
|

Re: @SelectKey generation at insert with SQL Server

Jeff Butler
Read the manual.  Set sqlStatament="JDBC"


Jeff Butler



On Mon, Nov 6, 2017 at 8:06 PM Hernán J. González <[hidden email]> wrote:
This worked for me, thanks.
A pity that MyBatisGenerator does not generate this...


On Saturday, November 4, 2017 at 12:51:50 PM UTC-3, h3adache wrote:
Try using just the generatedKeys

In xml I use:

insert id="insertFoo" useGeneratedKeys="true" keyProperty="id" keyColumn="id"

Note that keyProperty and keyColumn are important.
On Fri, Nov 3, 2017 at 11:36 PM, Kazuki Shimizu <[hidden email]> wrote:
If PreparedStatment is used, return null. I tried using Statement as follow:

public interface TestMapper {
 
@Insert("insert into TEST (VAL_INT) values (100)") // No use bind variable (=#{..}) in SQL
 
@Options(statementType = StatementType.STATEMENT) // Adding this line
 
@SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class, statementType = StatementType.STATEMENT) // Set statementType = StatementType.STATEMENT
 
int insert(Test record);
}

However, this solution is not practical.
I propose confirming the specification of the SQLServer JDBC Driver.

Thanks.

On Saturday, November 4, 2017 at 11:46:07 AM UTC+9, Kazuki Shimizu wrote:
I've reproduced this issue using SQLServer on Linux (using Docker image provided Microsoft).
In this case, using @@IDENTITY variable work fine. But we should be careful the specification that @@IDENTITY is returning the last identity on all scopes. 
FOR details for @@IDENTITY, please see reference document.

As an alternative method, I will suggest using the auto-generated keys feature provided JDBC 3.0 rather than the selectKey feature provided MyBatis.

How to use auto-generated Keys feature provided by JDBC 3.0 on MyBatis:

public interface TestMapper {
 
@Insert({ "insert into TEST (VAL_INT) values " +
     
"(#{valInt,jdbcType=INTEGER})" })
 
@Options(useGeneratedKeys = true) // Adding this line instread of @SelectKey
 
int insert(Test record);
}


Note:
I don't know why SCOPE_IDENTITY() return null via JDBC Driver. It works fine via "sqlcmd" (CUI tool provided the SQLServer).

Thanks.


On Tuesday, October 31, 2017 at 8:09:13 AM UTC+9, Hernán J. González wrote:
How to make the insert with identity key setting work in SQL Server? 

one should rely on "SELECT SCOPE_IDENTITY()" for that, but it does not work for me

public class TestMapper {
    @Insert({ "insert into TEST (VAL_INT) values " +
          "(#{valInt,jdbcType=INTEGER})" })
    @SelectKey(statement = "SELECT SCOPE_IDENTITY()", keyProperty = "id", before = false, resultType = Integer.class)
    int insert(Test record);
}


that seems to conclude that MyBatis executes the insert and the select in different batches (strange!), and that's
why  SELECT SCOPE_IDENTITY() cannot work. 
If that is so, then MyBatisGenerator is wrong in recommending that way, no? Or am I doing something wrong?
Has this ever worked?

BTW, I've use this approach with Postgresql (reading the sequence, instead of SELECT SCOPE_IDENTITY() , of course), without problems.


--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "mybatis-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [hidden email].
For more options, visit https://groups.google.com/d/optout.