Delphi: how to efficiently read a large binary by converting it to hex for passing it as a varbinary (max) parameter?
I need to convert a binary file (zip file) to hexadecimal and then send it to sql server as a parameter of varbinary (max) function.
Complete example (using a very small file!):
1) my file contains the following bits 0000111100001111
2) I need a procedure to QUICKLY convert it to 0F0F
3) I will call the sql server function passing 0x0F0F
as a parameter
The problem is that I have large files (up to 100MB even if the average file size is 100KB of files), so I need the fastest way to do this.
Otherwise: I need to create a string
'0x'+BinaryDataInHexadecimalRepresentation
in the most efficient way. (Note: there may be a way to immediately open the file and get the hex string, so in this case I need to use "this path" if there is one).
Related question: passing hex data to sql server
UPDATE : After reading the comments, I think more information needs to be added here. The reason I am trying to use a T-SQL text command to send binary data to a stored procedure is because this way I am deleting some overhead to the server: the stored prcoedure gets the binary data and writes it to a file (this is my ultimate goal ). If I use a DAC component, I can easily send the biray data to the server, but in this case I need to use a temporary table to store the data and then send that data to a stored procedure that writes the file.
So the idea is this:
1) using "long" T-SQL command: more client overhead because I need to read the file and convert it to hex to prepare a long command; less server overhead since sql server only gets binary data and processes it in stored function
2) using DAC: I need to go through the temp table in sql server so have more server overhead
Since I am using a server as my web document server (this is a trick) I want to try and reduce the overhead on the server. Anyway, maybe I am wrong and (2) is better than (1) anyway
a source to share
Well here's an option that will do the conversion as fast as I can think of.
Features of the code:
- Only one allocation per row (so no realloc and no move or copy)
- Fast reading from file.
Since we know that one byte is converted to exactly two hexadecimal characters, we know that our result string must be twice the file size. We allocate a string of the required size and then read from the file in large enough blocks so that the OS can optimize it for us (reading a byte is evil). We use the actual string, but we write to the string using a pointer:
function TEditorDeschidereDeCredite.FileToHex(FileName: string): AnsiString;
var FS:TFileStream;
PS:PAnsiChar;
Block:array[0..1023] of byte; // 1Kb
len,i,pos:Integer;
B:Byte;
const Nibbs: array[0..15] of AnsiChar = ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', 'A', 'B', 'C', 'D', 'E', 'F');
begin
FS := TFileStream.Create(FileName, fmOpenRead);
try
Result := '';
SetLength(Result, FS.Size * 2);
PS := PAnsiChar(Result);
pos := 0; // position into the result string
len := FS.Read(Block, SizeOf(Block));
while len <> 0 do
begin
for i:=0 to len-1 do
begin
B := Block[i];
PS[pos] := Nibbs[B div $F];
Inc(pos);
PS[pos] := Nibbs[B mod $F];
Inc(pos);
end;
len := FS.Read(Block, SizeOf(Block));
end;
finally FS.Free;
end;
end;
PS: I am using AnsiString and PAnsiChar, so the code works with Delphi Unicode too. If you happen to be on Delphi 2010 find a way to use this in the current form (AnsiString) so you can skip conversions.
a source to share