I was facing the same error while working for car manufacturer
their database reaches 2TB and single technical document could
have upto 40GB per file, I used this approach:
1) setup stored procedures (whatever the names):
SQL Code:
CREATE PROCEDURE INSERT_FILE
AS
BEGIN TRANSACTION
/* insert new file - simplified to for the problem; DATA is column holding your raw data */
INSERT FILES (DATA) VALUES ('')
/* apply */
COMMIT
/* return file index */
RETURN SCOPE_IDENTITY()
GO
SQL Code:
CREATE PROCEDURE WRITE_BUFFER
@INDEX int,
@OFFSET int,
@LENGTH int,
@BUFFER image
AS
BEGIN TRANSACTION
/* write data buffer */
declare @POINTER BINARY(16)
SELECT @POINTER=TEXTPTR(DATA) FROM FILES WHERE INDEX=@INDEX
updatetext FILES.DATA @POINTER @OFFSET @LENGTH @BUFFER
/* apply */
COMMIT
GO
2) I used this routine from Delphi (I stripped unneeded details because it was a commercial product):
Delphi Code:
const
maxBUFFERCACHE=64000;
procedure InsertFile(FileName:AnsiString);
var
INDEX,AMOUNT,OFFSET,NEXT:Integer;
BUFFER:AnsiString;
INPUT:File;
begin
try
AssignFile(INPUT,FileName);
Reset(INPUT,1);
OFFSET:=0;
AMOUNT:=System.FileSize(INPUT);
SetLength(BUFFER,maxBUFFERCACHE);
// Insert file
INDEX:=DB_MODIFY('INSERT_FILE',[]); // executes stored procedure INSERT_FILE with no parameters and catch returning value to INDEX
// Reading to buffer
repeat
if AMOUNT<maxBUFFERCACHE then
begin
SetLength(BUFFER,AMOUNT);
BlockRead(INPUT,BUFFER[1],AMOUNT);
NEXT:=AMOUNT;
AMOUNT:=0;
end else
begin
BlockRead(INPUT,BUFFER[1],maxBUFFERCACHE);
NEXT:=maxBUFFERCACHE;
Dec(AMOUNT,maxBUFFERCACHE);
end;
DB_MODIFY('WRITE_BUFFER',[INDEX,OFFSET,0,BUFFER]); // executes stored procedure WRITE_BUFFER with parameters (INDEX,OFFSET,0,BUFFER)
Inc(OFFSET,NEXT);
until AMOUNT<=0;
finally
CloseFile(INPUT);
end;
end;
I didn't provide the DB_MODIFY function because it's enormous big but it simply
does execution of any stored procedure with defined parameters while returning
index when needed and checking that everything is fine.
Bookmarks