how to insert files into SQL2008r2

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

how to insert files into SQL2008r2

Post by tcflam » Tue 01 Jan 2013 10:53

I tried to use the following code that insert the file into SQL2008r2. But it returns the error "The statement has been terminated. String or binary data would be truncated".

sqlInsertDoc.Active := false;
sqlInsertDoc.ParamByName('arh_image').LoadFromFile('c:\dplan.xls', ftBlob);
sqlInsertDoc.Execute;

Anybody can help me? And how to save or open the image?

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: how to insert files into SQL2008r2

Post by AlexP » Wed 02 Jan 2013 09:59

Hello,

Please specify the field type you are using in the table for data storage, when using VARBINARY(MAX) your code does not raise an exception.

tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

Re: how to insert files into SQL2008r2

Post by tcflam » Thu 03 Jan 2013 00:37

Field Type is varBinary. But I change it to Image type that doesn't matter. Why?

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: how to insert files into SQL2008r2

Post by AlexP » Thu 03 Jan 2013 08:53

Hello,

This problem might be connected with the fact that the data you are trying to insert into the field are greater than the size of the field.
Below is the table creation script and the code for filling this table, try to execute this sample and check the results with your files.

Code: Select all

CREATE TABLE Test.dbo.test(
  ID INT IDENTITY,
  XLS VARBINARY(MAX) NULL,
  PICT IMAGE NULL,
  CONSTRAINT PK_tcflam PRIMARY KEY (ID)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Code: Select all

  MSQuery1.SQL.Text := 'INSERT INTO test(XLS , PICT) VALUES (:p1, :p2)';
  MSQuery1.ParamByName('p1').DataType := ftBlob;
  MSQuery1.ParamByName('p2').DataType := ftBlob;
  MSQuery1.ParamByName('p1').LoadFromFile('d:\1.xls', ftBlob);
  MSQuery1.ParamByName('p2').LoadFromFile('d:\1.jpg', ftBlob);
  MSQuery1.Execute;

tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

Re: how to insert files into SQL2008r2

Post by tcflam » Mon 07 Jan 2013 03:49

Thanks for your information. But my file size is only around 30K. So it should not the file size problem. But the varbinary size is set 8000, not max. Is it a problem?

AndreyZ

Re: how to insert files into SQL2008r2

Post by AndreyZ » Wed 09 Jan 2013 11:50

Varbinary(8000) columns can store 8000 bytes. The size of your file is 30 kilobytes, that is 30720 bytes. As you can see, you cannot load such file to a varbinary(8000) column without truncation of data. To avoid the problem, you should use varbinary(max) columns.

tcflam
Posts: 58
Joined: Tue 01 Jan 2013 10:48

Re: how to insert files into SQL2008r2

Post by tcflam » Thu 10 Jan 2013 04:32

Thanks a lot!

AndreyZ

Re: how to insert files into SQL2008r2

Post by AndreyZ » Thu 10 Jan 2013 08:28

Feel free to contact us if you have any further questions about SDAC.

Post Reply