Devart Forums

Posted: **Thu 26 Apr 2018 10:52**

Hi,

I'm having trouble reading from a varbinary(max) column (using a FileStream). The data is truncated once it is bigger than 10000000bytes.
I tried reading the same data from C# via its SqlConnection/SqlCommand/SqlDataReader classes and it works without a problem.

Table:

Code: Select all

CREATE TABLE [dbo].[TAB](
	[GEN] [int] IDENTITY(1,1) NOT NULL,
	[FILE_ID] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[FILE] [varbinary](max) FILESTREAM  NULL,
 CONSTRAINT [PK_TAB] PRIMARY KEY CLUSTERED 
(
	[GEN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FSG_DMS],
 CONSTRAINT [CO_UNQ_FILE_ID] UNIQUE NONCLUSTERED 
(
	[FILE_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] FILESTREAM_ON [FSG_DMS]

I'm using the following Delphi Code:

Code: Select all

Result := TMemoryStream.Create;
qrySelect := TMSQuery.Create(nil);
qrySelect.CommandText := 'select FILE  from TAB where GEN = 1';
qrySelect.Open;
TBlobField(qrySelect.fctFieldByName('FILE')).SaveToStream(Result);

Afterwards Result has only 10000000bytes worth of data even if the Data stored is bigger than that.

C# Code that works:

Code: Select all

var conn = new SqlConnection(@"Data Source=...;Integrated Security=True;Initial Catalog=...");
conn.Open();
var cmd = new SqlCommand("select FILE  from TAB where GEN = 1", conn);
var reader = cmd.ExecuteReader();
reader.Read();
var blob = reader.GetSqlBinary(0);

Is this a bug or is there a setting with which I can change how much data should be read?
I'm using SDAC 8.0.2 .

Posted: **Thu 03 May 2018 12:00**

Unfortunately, we can't reproduce the issue with truncated binary data. To investigate the problem, please provide us with a test example demonstrating the problem, as well as a script for creating tables.
You can send the sample using the contact form at our site: http://www.devart.com/company/contactform.html

Posted: **Thu 03 May 2018 12:07**

Never mind, I found the issue:

The connection parameter TEXTSIZE was set to 10000000 which also affects the datalength returned by varbinary fields.

Posted: **Thu 03 May 2018 13:13**

We are glad to see that the problem is resolved.
Actually, setting TEXTSIZE specifies the size of varchar(max), nvarchar(max), varbinary(max), text, ntext, and image data returned by a SELECT statement.

Devart Forums

Truncated varbinary(max) Data upon retrieval

Truncated varbinary(max) Data upon retrieval

Re: Truncated varbinary(max) Data upon retrieval

Re: Truncated varbinary(max) Data upon retrieval

Re: Truncated varbinary(max) Data upon retrieval