Page 1 of 1
how to insert files into SQL2008r2
Posted: Tue 01 Jan 2013 10:53
by 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?
Re: how to insert files into SQL2008r2
Posted: Wed 02 Jan 2013 09:59
by 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.
Re: how to insert files into SQL2008r2
Posted: Thu 03 Jan 2013 00:37
by tcflam
Field Type is varBinary. But I change it to Image type that doesn't matter. Why?
Re: how to insert files into SQL2008r2
Posted: Thu 03 Jan 2013 08:53
by 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;
Re: how to insert files into SQL2008r2
Posted: Mon 07 Jan 2013 03:49
by 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?
Re: how to insert files into SQL2008r2
Posted: Wed 09 Jan 2013 11:50
by 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.
Re: how to insert files into SQL2008r2
Posted: Thu 10 Jan 2013 04:32
by tcflam
Thanks a lot!
Re: how to insert files into SQL2008r2
Posted: Thu 10 Jan 2013 08:28
by AndreyZ
Feel free to contact us if you have any further questions about SDAC.