How to create SQLITE BLOB fields with Delphi

Question

I’m developing an android APP through Delphi. recently I have been trying to create a BLOB field in my SQLITE database when I do

ShowMessage(query.FieldByName('imagem').ClassName);

(request field data type) return me as Tlongintfield

I’m creating the table like this:

Gia_1C.ExecuteDirect('CREATE TABLE IF NOT EXISTS Artigos (codigo_arm TEXT, Referencia TEXT PRIMARY KEY, designacao1 TEXT , preco1 TEXT, Imagem  BLOB);');

Answer 1

I found the problem. before I had the insertion method as follows:

procedure Tform33.Insert_data_ART;
var i:integer;
     sql:string;
     ms:TStream;
     blopfield:Tfield;
begin
 ShowMessage(inttostr(num_regs_fb_ART));
  ms:=TMemoryStream.Create;
  for I := 1 to num_regs_fb_ART do
           begin
            try
              Artigos_imgTable.Active:=false;
              Artigos_imgTable.Close;

              sql:= ('INSERT INTO Artigos_img (Codigo_arm,Referencia,Designacao1,'+
                     'preco1,imagem) ' +
                     'VALUES' +
                     ' (''' + Codigo_arm[i] + ''', ' +
                     '''' +Referencia[i] + ''', ' +
                     '''' + Designacao1[i] + ''', ' +
                     '' + preco1[i] + ');' );

            Artigos_imgTable.CommandText:=sql;
             ms.Position:=0;
             try
                Artigos_imgTable.active:=true;
                  with Artigos_imgTable do
                     begin
                     Insert;
                     blopfield:=FieldByName('imagem');
                     ms:=CreateBlobStream(blopfield,bmWrite);
                     BinaryAsBITMap[I].SaveToStream(ms);
                     end;
                except
                on e:exception do
                infotext(e.Message);
              end;
              try
              Artigos_imgTable.CommandText:=sql;
              Artigos_imgTable.ExecSQL(true);
              except on E: Exception do
              ShowMessage(e.message);
              end;
             finally
             ms.Free;
            end;
         end;
      infoText('Dados Atualizados...');
end;

So since it’s type BLOB as the first input data is a 0 or a 1 and as Sqlite shapes the field type to the input data type, the database assumed I was inserting a long int.

The correct way to insert a BLOB is as follows:

procedure Tform33.Insert_data_ART;
var i:integer;
     sql:string;
     ms:TStream;
     LTransaction: TDBXTransaction;
     LParams: TParams;

begin
  for I := 1 to num_regs_fb_ART do
           begin
                  sql:= ('INSERT INTO Artigos (Codigo_arm,Referencia,Designacao1,'+
                     'preco1,Imagem) ' +
                     'VALUES' +
                     ' (''' + Codigo_arm[i] + ''', ' +
                     '''' +Referencia[i] + ''', ' +
                     '''' + Designacao1[i] + ''', ' +
                     '''' + preco1[i] + ''', ' +
                      ':Imagem)' );
            try

            LTransaction:=Gia_1C.BeginTransaction;
            LParams:=TParams.Create(nil);
            ms:=TMemoryStream.Create;
            BinaryAsBITMap[I].SaveToStream(ms);
              try
                LParams.CreateParam(ftBlob,'Imagem',ptinput);
                LParams.ParamByName('Imagem').LoadFromStream(ms,ftblob);
                Gia_1C.Execute(SQL,Lparams);
                Gia_1C.CommitFreeAndNil(LTransaction);
              finally
                FreeAndNil(ms);
                FreeAndNil(Lparams);
              end;
             except on E: Exception do
              infoText(e.Message);
            end;
         end;
      infoText('Dados Atualizados...');
end;

Answer 2

to pick up the field type use:

 query.FieldList.Fields[nro_campo].DataType

and Delphi does not return the data type as we are used to (String / varchar, integer, float, etc). You will have to check how this coming by blob type

When I need to perform this procedure I do it as follows:

if qryBuscaCampos.FieldList.Fields[nro_campo].DataType in [ftString, ftwideString, ftWideMemo] then
   begin
      data_tyoe := 'String';
      tamanho := qryBuscaCampos.FieldList.Fields[nro_campo].Size;
   end
else if qryBuscaCampos.FieldList.Fields[nro_campo].DataType = ftBoolean then
        data_tyoe := 'Boolean'
else if qryBuscaCampos.FieldList.Fields[nro_campo].DataType in [ftFloat, ftcurrency] then
        data_tyoe := 'Float'
else if qryBuscaCampos.FieldList.Fields[nro_campo].DataType in [ftDateTime,ftTimeStamp] then
        data_tyoe := 'DateTime'
else if qryBuscaCampos.FieldList.Fields[nro_campo].DataType = ftDate then
        data_tyoe := 'Date'
else if qryBuscaCampos.FieldList.Fields[nro_campo].DataType = ftTime then
        data_tyoe := 'Time'
else if qryBuscaCampos.FieldList.Fields[nro_campo].DataType in [ftAutoInc, ftInteger, ftSmallInt] then
        data_tyoe := 'Integer'
else if qryBuscaCampos.FieldList.Fields[nro_campo].DataType = ftMemo then
        data_tyoe := 'Text'
else if qryBuscaCampos.FieldList.Fields[nro_campo].DataType = ftsingle then
        data_tyoe := 'Real'
else if qryBuscaCampos.FieldList.Fields[nro_campo].DataType = ftBCD then
        data_tyoe := 'Numeric';

As some datatypes vary according to the DBMS used I recommend you put this code in a repeat command until you identify the correct datatype for your blob field

Note: the field should start at 0