how to insert files into SQL2008r2

[tcflam]

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]

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]

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

[AlexP]

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]

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]

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]

Thanks a lot!

[AndreyZ]

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