Issue inserting byte objects when size is greater than 8000 and and less than 65535 bytes

[matthewstephenroberts]

Bug Report

seems to be discussed on Gitter.im by Tai.Bui @buianhtai Apr 20 17:40 but hasn't been raised

The root causing this issue is the parameter @P0_data to set varbinary ,

It set @P0_data VARBINARY(MAX) when i upload file size >68KB

2020-04-20 14:27:18.393 DEBUG 39680 --- [actor-tcp-nio-3] i.r2dbc.mssql.client.ReactorNettyClient  : [cid: 0x3] Request: RPCRequest [procName='null', procId=10, optionFlags=io.r2dbc.mssql.message.token.RpcRequest$OptionFlags@3e1b4d27, statusFlags=0, parameterDescriptors=[RpcString [name='null', value=INSERT INTO file_upload (data, uuid, original_name, mime_type) VALUES (@P0_data, @P1_uuid, @P2_originalname, @P3_mimetype) SELECT SCOPE_IDENTITY() AS GENERATED_KEYS], RpcString [name='null', value=**@P0_data VARBINARY(MAX)**,@P1_uuid nvarchar(4000),@P2_originalname nvarchar(4000),@P3_mimetype nvarchar(4000)], EncodedRpcParameter [name='P0_data', value=io.r2dbc.mssql.codec.PlpEncoded@31c90b98], EncodedRpcParameter [name='P1_uuid', value=io.r2dbc.mssql.codec.CharacterEncoder$NvarcharEncoded@7cce8610],
It must be @P0_data VARBINARY(MAX) if file size >8KB

2020-04-20 14:26:56.272 DEBUG 39680 --- [actor-tcp-nio-2] i.r2dbc.mssql.client.ReactorNettyClient  : [cid: 0x2] Request: RPCRequest [procName='null', procId=10, optionFlags=io.r2dbc.mssql.message.token.RpcRequest$OptionFlags@3e1b4d27, statusFlags=0, parameterDescriptors=[RpcString [name='null', value=INSERT INTO file_upload (data, uuid, original_name, mime_type) VALUES (@P0_data, @P1_uuid, @P2_originalname, @P3_mimetype) SELECT SCOPE_IDENTITY() AS GENERATED_KEYS], RpcString [name='null', value=~~@P0_data varbinary(8000)~~,@P1_uuid nvarchar(4000),@P2_originalname nvarchar(4000),@P3_mimetype nvarchar(4000)], EncodedRpcParameter [name='P0_data', value=io.r2dbc.mssql.codec.BinaryCodec$VarbinaryEncoded@12d3825f], EncodedRpcParameter [name='P1_uuid', value=io.r2dbc.mssql.codec.CharacterEncoder$NvarcharEncoded@401a141], EncodedRpcParameter [name='P2_originalname', value=io.r2dbc.mssql.codec.CharacterEncoder$NvarcharEncoded@230bb40d], EncodedRpcParameter [name='P3_mimetype', value=io.r2dbc.mssql.codec.CharacterEncoder$NvarcharEncoded@7c33c9b3]]]

Versions

spring-data-r2dbc:1.1.0.RC2
r2dbc-mssql:0.8.2.RELEASE
r2dbc-spi:0.8.1.RELEASE
r2dbc-pool:0.8.1.RELEASE

Current Behavior

Column defined in MSSQL server database as varbinary(MAX),

If bind byte object is greater than 8000 and less than 65535 then fails.
if bind byte object is equal to or less than 8000 then works as expected.
if bind byte object is greater than or equal to 65535 then works as expected.

[3f59d08b-1] There was an unexpected error (type=Internal Server Error, status=500).
The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@data"): Data type 0xA5 has an invalid data length or metadata length.
io.r2dbc.mssql.ExceptionFactory$MssqlNonTransientException: [8016] [S00024] The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 ("@data"): Data type 0xA5 has an invalid data length or metadata length.
	at io.r2dbc.mssql.ExceptionFactory.createException(ExceptionFactory.java:152)
	Suppressed: reactor.core.publisher.FluxOnAssembly$OnAssemblyException: 
Error has been observed at the following site(s):
	|_ checkpoint ⇢ Handler com.dxc.DCaaS.framework.controller.ApplicationController#insert() [DispatcherHandler]
	|_ checkpoint ⇢ HTTP GET "/insert" [ExceptionHandlingWebHandler]

Stack trace:
		at io.r2dbc.mssql.ExceptionFactory.createException(ExceptionFactory.java:152)
		at io.r2dbc.mssql.ExceptionFactory.createException(ExceptionFactory.java:181)
		at io.r2dbc.mssql.RpcQueryMessageFlow.lambda$exchange$1(RpcQueryMessageFlow.java:148)
		at reactor.core.publisher.FluxHandle$HandleConditionalSubscriber.onNext(FluxHandle.java:303)
		at reactor.core.publisher.EmitterProcessor.drain(EmitterProcessor.java:426)
		at reactor.core.publisher.EmitterProcessor.onNext(EmitterProcessor.java:268)
		at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:192)
		at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:192)
		at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:192)
		at reactor.core.publisher.FluxHandle$HandleSubscriber.onNext(FluxHandle.java:112)
		at reactor.core.publisher.MonoFlatMapMany$FlatMapManyInner.onNext(MonoFlatMapMany.java:242)
		at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:192)
		at reactor.core.publisher.EmitterProcessor.drain(EmitterProcessor.java:426)
		at reactor.core.publisher.EmitterProcessor.onNext(EmitterProcessor.java:268)
		at io.r2dbc.mssql.client.ReactorNettyClient$1.next(ReactorNettyClient.java:237)
		at io.r2dbc.mssql.client.ReactorNettyClient$1.next(ReactorNettyClient.java:197)
		at io.r2dbc.mssql.message.token.Tabular$TabularDecoder.decode(Tabular.java:425)
		at io.r2dbc.mssql.client.ConnectionState$4$1.decode(ConnectionState.java:206)
		at io.r2dbc.mssql.client.StreamDecoder.withState(StreamDecoder.java:137)
		at io.r2dbc.mssql.client.StreamDecoder.decode(StreamDecoder.java:109)
		at io.r2dbc.mssql.client.ReactorNettyClient.lambda$new$6(ReactorNettyClient.java:247)
		at reactor.core.publisher.FluxPeek$PeekSubscriber.onNext(FluxPeek.java:177)
		at reactor.netty.channel.FluxReceive.drainReceiver(FluxReceive.java:220)
		at reactor.netty.channel.FluxReceive.onInboundNext(FluxReceive.java:354)
		at reactor.netty.channel.ChannelOperations.onInboundNext(ChannelOperations.java:352)
		at reactor.netty.channel.ChannelOperationsHandler.channelRead(ChannelOperationsHandler.java:96)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.channel.ChannelInboundHandlerAdapter.channelRead(ChannelInboundHandlerAdapter.java:93)
		at io.r2dbc.mssql.client.ssl.TdsSslHandler.channelRead(TdsSslHandler.java:402)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.AbstractChannelHandlerContext.fireChannelRead(AbstractChannelHandlerContext.java:357)
		at io.netty.channel.DefaultChannelPipeline$HeadContext.channelRead(DefaultChannelPipeline.java:1410)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:379)
		at io.netty.channel.AbstractChannelHandlerContext.invokeChannelRead(AbstractChannelHandlerContext.java:365)
		at io.netty.channel.DefaultChannelPipeline.fireChannelRead(DefaultChannelPipeline.java:919)
		at io.netty.channel.nio.AbstractNioByteChannel$NioByteUnsafe.read(AbstractNioByteChannel.java:163)
		at io.netty.channel.nio.NioEventLoop.processSelectedKey(NioEventLoop.java:714)
		at io.netty.channel.nio.NioEventLoop.processSelectedKeysOptimized(NioEventLoop.java:650)
		at io.netty.channel.nio.NioEventLoop.processSelectedKeys(NioEventLoop.java:576)
		at io.netty.channel.nio.NioEventLoop.run(NioEventLoop.java:493)
		at io.netty.util.concurrent.SingleThreadEventExecutor$4.run(SingleThreadEventExecutor.java:989)
		at io.netty.util.internal.ThreadExecutorMap$2.run(ThreadExecutorMap.java:74)
		at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
		at java.lang.Thread.run(Thread.java:748)

Table schema

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Test](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Data] [varbinary](max) NULL,
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

INSERT INTO Test (Data) VALUES (@data)

Works

    @GetMapping("insert")
public Flux<Integer> insert() throws IOException {
    return Mono.from(connectionFactory.create())
            .flatMapMany(connection -> Flux.from(connection.createStatement("INSERT INTO Test (Data) VALUES (@data)")
                    .bind("@data", new byte[65535])
                    .execute())
                    .map(Result::getRowsUpdated)
                    .flatMap(Mono::from)
                    .doFinally(signalType -> connection.close()));
}

Fails

    @GetMapping("insert")
public Flux<Integer> insert() throws IOException {
    return Mono.from(connectionFactory.create())
            .flatMapMany(connection -> Flux.from(connection.createStatement("INSERT INTO Test (Data) VALUES (@data)")
                    .bind("@data", new byte[65534])
                    .execute())
                    .map(Result::getRowsUpdated)
                    .flatMap(Mono::from)
                    .doFinally(signalType -> connection.close()));
}

Steps to reproduce

Column defined in MSSQL server database as varbinary(MAX),

If bind byte object is greater than 8000 and less than 65535 then fails.
if bind byte object is equal to or less than 8000 then works as expected.
if bind byte object is greater than or equal to 65535 then works as expected.

    @GetMapping("insert")
    public Flux<Integer> insert() throws IOException {
        return Mono.from(connectionFactory.create())
                .flatMapMany(connection -> Flux.from(connection.createStatement("INSERT INTO Test (Data) VALUES (@data)")
                        .bind("@data", new byte[65534])
                        .execute())
                        .map(Result::getRowsUpdated)
                        .flatMap(Mono::from)
                        .doFinally(signalType -> connection.close()));
    }

Expected behavior/code

Byte object should be inserted correctly into MS SQL table

Possible Solution

Related to #99 think the value should be 8000, not 65535
as varbinary is max 8,000 and varbinary(max) should be used for anything above https://docs.microsoft.com/en-us/sql/t-sql/data-types/binary-and-varbinary-transact-sql?view=sql-server-ver15

Additional context

[mp911de]

Thanks a lot for the report. That's fixed now.