How should I call the following stored procedure(sqlserver 2008 r2 with User-Defined Table Type) in springboot, mybatis with annotation

[Ricky Paranoid]

How should I call the following stored procedure(sqlserver 2008 r2) in springboot, mybatis with annotation

    declare @p1 TbType_Order
    insert into @p1 values('00005',20.01)
    insert into @p1 values('00006',15.99)
    exec Gp_UpdateOrderBillDT @tb=@p1,@intFlag=0,@billNumberId=16,@billType=301

the "TbType_Order" is a User-Defined Table Type

   

    CREATE TYPE [dbo].[TbType_Order] AS TABLE(
        [No] [varchar](50) NULL,
        [Price] [numeric](24, 10) NULL
    )
    GO

I try to call using this way:

 

   public interface BillProcedureMapper {
        @Select({
            "<script>" +
            " declare @p1 TbType_Order " +
            " <foreach item=\"item\" collection=\"details\"> ",
            " insert into @p1 values( #{item.no},#{item.price} )" +
            " </foreach> ",
            " exec Gp_UpdateOrderBillDT " +
                "@tb=@p1," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; " +
            "</script>"
        })
        @Options(statementType = StatementType.CALLABLE)
        void savePurchaseOrderDetail(
            @Param("billNumberId") Long billNumberId,
            @Param("details") List<DetailDTO> details
        );
    }

but jdbc say "variable @p1 already declared"
and I try use type handler :

    public class TableTypeOrderHandler extends BaseTypeHandler {
        @Override
        public void setNonNullParameter(
            PreparedStatement ps, int i, Object parameter, JdbcType jdbcType
        ) throws SQLException {

            SQLServerDataTable sourceDataTable = new SQLServerDataTable();

            sourceDataTable.addColumnMetadata("No", Types.VARCHAR);

            sourceDataTable.addColumnMetadata("Price", Types.DECIMAL);

            List<DetailDTO> dataList = (List<DetailDTO>) parameter;

            for (DetailDTO o : dataList) {

                sourceDataTable.addRow(
                    o.getNo(),
                    o.getPrice()
                );

            }

            ps.setObject(i, sourceDataTable);
        }

        @Override
        public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
            return null;
        }
    }

then change the select method:

   

    @Select({
        "exec Gp_UpdateOrderBillDT " +
                "@tb=#{details, jdbcType=VARCHAR, typeHandler=com.xxx.TableTypeOrderHandler}," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; "
    })
    @Options(statementType = StatementType.CALLABLE)
    void savePurchaseOrderDetail(
        @Param("billNumberId") Integer billNumberId,
        @Param("details") List<DetailDTO> details
    );

but jdbc exception is: Operand type conflict: table type is not compatible with int

What should I do to get it right？

--
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].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/59b00afa-98ac-42e2-9fae-1205f419944c%40googlegroups.com.

[ri yo]

test:

debug:error

run:ok

====

config file :

mybatis.type-handlers-package=com.xxxx.xxxxtypehandler

Ricky Paranoid <[hidden email]> 于 2019年12月21日周六 上午11:56写道：

How should I call the following stored procedure(sqlserver 2008 r2) in springboot, mybatis with annotation

    declare @p1 TbType_Order
    insert into @p1 values('00005',20.01)
    insert into @p1 values('00006',15.99)
    exec Gp_UpdateOrderBillDT @tb=@p1,@intFlag=0,@billNumberId=16,@billType=301

the "TbType_Order" is a User-Defined Table Type

   

    CREATE TYPE [dbo].[TbType_Order] AS TABLE(
        [No] [varchar](50) NULL,
        [Price] [numeric](24, 10) NULL
    )
    GO

I try to call using this way:

 

   public interface BillProcedureMapper {
        @Select({
            "<script>" +
            " declare @p1 TbType_Order " +
            " <foreach item=\"item\" collection=\"details\"> ",
            " insert into @p1 values( #{item.no},#{item.price} )" +
            " </foreach> ",
            " exec Gp_UpdateOrderBillDT " +
                "@tb=@p1," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; " +
            "</script>"
        })
        @Options(statementType = StatementType.CALLABLE)
        void savePurchaseOrderDetail(
            @Param("billNumberId") Long billNumberId,
            @Param("details") List<DetailDTO> details
        );
    }

but jdbc say "variable @p1 already declared"
and I try use type handler :

    public class TableTypeOrderHandler extends BaseTypeHandler {
        @Override
        public void setNonNullParameter(
            PreparedStatement ps, int i, Object parameter, JdbcType jdbcType
        ) throws SQLException {

            SQLServerDataTable sourceDataTable = new SQLServerDataTable();

            sourceDataTable.addColumnMetadata("No", Types.VARCHAR);

            sourceDataTable.addColumnMetadata("Price", Types.DECIMAL);

            List<DetailDTO> dataList = (List<DetailDTO>) parameter;

            for (DetailDTO o : dataList) {

                sourceDataTable.addRow(
                    o.getNo(),
                    o.getPrice()
                );

            }

            ps.setObject(i, sourceDataTable);
        }

        @Override
        public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
            return null;
        }
    }

then change the select method:

   

    @Select({
        "exec Gp_UpdateOrderBillDT " +
                "@tb=#{details, jdbcType=VARCHAR, typeHandler=com.xxx.TableTypeOrderHandler}," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; "
    })
    @Options(statementType = StatementType.CALLABLE)
    void savePurchaseOrderDetail(
        @Param("billNumberId") Integer billNumberId,
        @Param("details") List<DetailDTO> details
    );

but jdbc exception is: Operand type conflict: table type is not compatible with int

What should I do to get it right？

--
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].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/59b00afa-98ac-42e2-9fae-1205f419944c%40googlegroups.com.

--
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].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CAMs%3DbOjkZ4RuFrfi2rH6-87MBjAHQOM2VemLhdOn%3DTgW%3DKwDVg%40mail.gmail.com.

[Iwao AVE!]

Just for future reference, I left a comment on the same question on StackOverflow:

https://stackoverflow.com/q/59433324

And the following answer seemed to help (another use case of 'unwrap' method).

https://stackoverflow.com/a/56651222/

On Sat, Dec 21, 2019 at 12:56 Ricky Paranoid <[hidden email]> wrote:

How should I call the following stored procedure(sqlserver 2008 r2) in springboot, mybatis with annotation

    declare @p1 TbType_Order
    insert into @p1 values('00005',20.01)
    insert into @p1 values('00006',15.99)
    exec Gp_UpdateOrderBillDT @tb=@p1,@intFlag=0,@billNumberId=16,@billType=301

the "TbType_Order" is a User-Defined Table Type

   

    CREATE TYPE [dbo].[TbType_Order] AS TABLE(
        [No] [varchar](50) NULL,
        [Price] [numeric](24, 10) NULL
    )
    GO

I try to call using this way:

 

   public interface BillProcedureMapper {
        @Select({
            "<script>" +
            " declare @p1 TbType_Order " +
            " <foreach item=\"item\" collection=\"details\"> ",
            " insert into @p1 values( #{item.no},#{item.price} )" +
            " </foreach> ",
            " exec Gp_UpdateOrderBillDT " +
                "@tb=@p1," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; " +
            "</script>"
        })
        @Options(statementType = StatementType.CALLABLE)
        void savePurchaseOrderDetail(
            @Param("billNumberId") Long billNumberId,
            @Param("details") List<DetailDTO> details
        );
    }

but jdbc say "variable @p1 already declared"
and I try use type handler :

    public class TableTypeOrderHandler extends BaseTypeHandler {
        @Override
        public void setNonNullParameter(
            PreparedStatement ps, int i, Object parameter, JdbcType jdbcType
        ) throws SQLException {

            SQLServerDataTable sourceDataTable = new SQLServerDataTable();

            sourceDataTable.addColumnMetadata("No", Types.VARCHAR);

            sourceDataTable.addColumnMetadata("Price", Types.DECIMAL);

            List<DetailDTO> dataList = (List<DetailDTO>) parameter;

            for (DetailDTO o : dataList) {

                sourceDataTable.addRow(
                    o.getNo(),
                    o.getPrice()
                );

            }

            ps.setObject(i, sourceDataTable);
        }

        @Override
        public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
            return null;
        }
    }

then change the select method:

   

    @Select({
        "exec Gp_UpdateOrderBillDT " +
                "@tb=#{details, jdbcType=VARCHAR, typeHandler=com.xxx.TableTypeOrderHandler}," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; "
    })
    @Options(statementType = StatementType.CALLABLE)
    void savePurchaseOrderDetail(
        @Param("billNumberId") Integer billNumberId,
        @Param("details") List<DetailDTO> details
    );

but jdbc exception is: Operand type conflict: table type is not compatible with int

What should I do to get it right？

--
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].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/59b00afa-98ac-42e2-9fae-1205f419944c%40googlegroups.com.

--
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].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/CA%2Buep2SPraoUVzY5ywGaJQG71XBsY9VP4hjYsnUvwxcaJkNJEg%40mail.gmail.com.

[Ricky Paranoid]

Sorry for replying so late
I also asked stackoverflow questions
thanks for your help，it works， great！！！

在 2019年12月23日星期一 UTC+8下午10:40:33，Iwao AVE!写道：

Just for future reference, I left a comment on the same question on StackOverflow:

<a href="https://stackoverflow.com/q/59433324/1261766" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fq%2F59433324%2F1261766\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGWz4mO0ZrecPt5FeZrZ2SfOpGSAw&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fq%2F59433324%2F1261766\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNGWz4mO0ZrecPt5FeZrZ2SfOpGSAw&#39;;return true;">https://stackoverflow.com/q/59433324

And the following answer seemed to help (another use case of 'unwrap' method).

<a href="https://stackoverflow.com/a/56651222/" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fa%2F56651222%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHsnOv2ADeQvCRnyjP5hMVmdRYPng&#39;;return true;" onclick="this.href=&#39;https://www.google.com/url?q\x3dhttps%3A%2F%2Fstackoverflow.com%2Fa%2F56651222%2F\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNHsnOv2ADeQvCRnyjP5hMVmdRYPng&#39;;return true;">https://stackoverflow.com/a/56651222/

On Sat, Dec 21, 2019 at 12:56 Ricky Paranoid <<a href="javascript:" target="_blank" gdf-obfuscated-mailto="VX2q-QW7AgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">rick...@...> wrote:

How should I call the following stored procedure(sqlserver 2008 r2) in springboot, mybatis with annotation

    declare @p1 TbType_Order
    insert into @p1 values('00005',20.01)
    insert into @p1 values('00006',15.99)
    exec Gp_UpdateOrderBillDT @tb=@p1,@intFlag=0,@billNumberId=16,@billType=301

the "TbType_Order" is a User-Defined Table Type

   

    CREATE TYPE [dbo].[TbType_Order] AS TABLE(
        [No] [varchar](50) NULL,
        [Price] [numeric](24, 10) NULL
    )
    GO

I try to call using this way:

 

   public interface BillProcedureMapper {
        @Select({
            "<script>" +
            " declare @p1 TbType_Order " +
            " <foreach item=\"item\" collection=\"details\"> ",
            " insert into @p1 values( #{<a href="http://item.no" target="_blank" rel="nofollow" onmousedown="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fitem.no\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF4I5yy6FVwE1G_KIiNvrh1KZRReQ&#39;;return true;" onclick="this.href=&#39;http://www.google.com/url?q\x3dhttp%3A%2F%2Fitem.no\x26sa\x3dD\x26sntz\x3d1\x26usg\x3dAFQjCNF4I5yy6FVwE1G_KIiNvrh1KZRReQ&#39;;return true;">item.no},#{item.price} )" +
            " </foreach> ",
            " exec Gp_UpdateOrderBillDT " +
                "@tb=@p1," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; " +
            "</script>"
        })
        @Options(statementType = StatementType.CALLABLE)
        void savePurchaseOrderDetail(
            @Param("billNumberId") Long billNumberId,
            @Param("details") List<DetailDTO> details
        );
    }

but jdbc say "variable @p1 already declared"
and I try use type handler :

    public class TableTypeOrderHandler extends BaseTypeHandler {
        @Override
        public void setNonNullParameter(
            PreparedStatement ps, int i, Object parameter, JdbcType jdbcType
        ) throws SQLException {

            SQLServerDataTable sourceDataTable = new SQLServerDataTable();

            sourceDataTable.addColumnMetadata("No", Types.VARCHAR);

            sourceDataTable.addColumnMetadata("Price", Types.DECIMAL);

            List<DetailDTO> dataList = (List<DetailDTO>) parameter;

            for (DetailDTO o : dataList) {

                sourceDataTable.addRow(
                    o.getNo(),
                    o.getPrice()
                );

            }

            ps.setObject(i, sourceDataTable);
        }

        @Override
        public Object getNullableResult(ResultSet rs, String columnName) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(ResultSet rs, int columnIndex) throws SQLException {
            return null;
        }

        @Override
        public Object getNullableResult(CallableStatement cs, int columnIndex) throws SQLException {
            return null;
        }
    }

then change the select method:

   

    @Select({
        "exec Gp_UpdateOrderBillDT " +
                "@tb=#{details, jdbcType=VARCHAR, typeHandler=com.xxx.TableTypeOrderHandler}," +
                "@intFlag=0," +
                "@billNumberId=#{billNumberId}," +
                "@billType=301; "
    })
    @Options(statementType = StatementType.CALLABLE)
    void savePurchaseOrderDetail(
        @Param("billNumberId") Integer billNumberId,
        @Param("details") List<DetailDTO> details
    );

but jdbc exception is: Operand type conflict: table type is not compatible with int

What should I do to get it right？

--
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="VX2q-QW7AgAJ" rel="nofollow" onmousedown="this.href=&#39;javascript:&#39;;return true;" onclick="this.href=&#39;javascript:&#39;;return true;">[e-mail].
To view this discussion on the web visit <a href="https://groups.google.com/d/msgid/mybatis-user/59b00afa-98ac-42e2-9fae-1205f419944c%40googlegroups.com?utm_medium=email&amp;utm_source=footer" target="_blank" rel="nofollow" onmousedown="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/59b00afa-98ac-42e2-9fae-1205f419944c%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;" onclick="this.href=&#39;https://groups.google.com/d/msgid/mybatis-user/59b00afa-98ac-42e2-9fae-1205f419944c%40googlegroups.com?utm_medium\x3demail\x26utm_source\x3dfooter&#39;;return true;">https://groups.google.com/d/msgid/mybatis-user/59b00afa-98ac-42e2-9fae-1205f419944c%40googlegroups.com.

--
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].
To view this discussion on the web visit https://groups.google.com/d/msgid/mybatis-user/57c5aed4-f5af-40fc-90dc-930f1f454d18%40googlegroups.com.