Storing Files in SQL Server Table (varbinary(max) column) using ADOQuery Component-CodePudding

I'm using SQL Server 2019 and Delphi 10.3.

I need to store any kind of files ( like pdf, txt, docx, etc) in a 'Personal_Files' table.

This table is composed by a column with the file extension ( as varchar) and a varbinary(max) column to store the file itself.

I did some research on how to store these files on a table, but without success. Below some example:

var
Input,Output: TStream;
FName:        TFileName;

begin
...
     //Create Streams and encode Base64:
     Input  := TFileStream.Create(FName,fmOpenRead);
     Output := TFileStream.Create(FName 'Temp',fmCreate);

     TNetEncoding.Base64.Encode(Input,Output);

... // Some validations

   // In the ADOQuery component, I did this:
  with ADOQuery, sql do
  begin
    close;
    clear;
    add('INSERT INTO MyDatabase.dbo.MyFilesTable (EXTENSION,FILEBIN)');
    add('VALUES (:wextension, :wfilebin)');
    Parameters.ParamByName('wextension').Value := TPath.GetExtension(FName);
    Parameters.ParamByName('wfilebin').Value   := Output.toString; 
    ExecSQL;
  end;

In this example, I tried to parse the stream as String, after the encode, but when I look in the SQL Table, it's the same stream for all the archives I tried. The parameter doesn't accept TStream type. Thank you in advance.

CodePudding user response：

After some research, and some advices, I found a way to send the file to my SQL Server table with ADOQuery. Altough, I learned that this isn't recommended, so it's just to answer my question directly:

Just a change on the final part of the code answers my question ( but again, it's not the recommended way to store files, as commented on the question.):

with ADOQuery, sql do
begin
  close;
  clear;
  add('INSERT INTO MyDatabase.dbo.MyFilesTable (EXTENSION,FILEBIN)');
  add('VALUES (:wextension, :wfilebin)');
  Parameters.ParamByName('wextension').Value := TPath.GetExtension(FName);
  Parameters.Items[1].LoadFromStream(Output,ftVarBytes); 
 ExecSQL;
end;

Just changing the way I was setting the parameter solved the problem. In this example, using the 'LoadFromStream' on the Paremeters.Items[n], where n is the parameter index, it worked very well. The ftVarBytes is the field type parameter.