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

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

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.
Reply | Threaded
Open this post in threaded view
|

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

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.
Reply | Threaded
Open this post in threaded view
|

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

Iwao AVE!
In reply to this post by Ricky Paranoid
Just for future reference, I left a comment on the same question on StackOverflow:
And the following answer seemed to help (another use case of 'unwrap' method).

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.
Reply | Threaded
Open this post in threaded view
|

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

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;">mybati...@googlegroups.com.
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.