I'm developing an android APP via delphi. I have recently tried to create a BLOB-type field in my SQLITE database at the moment when I do
ShowMessage(query.FieldByName('imagem').ClassName);
(I ask the data type of the field) returns 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);');
I found the problem. before you 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 as it is of type BLOB as the first data entry is a 0 or a 1 and as SQLite wraps the field type to the data type it enters, the database assumed that 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;
to get the field type use:
query.FieldList.Fields[nro_campo].DataType
and Delphi does not return the data type as we are accustomed (String / varchar, integer, float, etc). You will have to check how you are coming by blob type
When I need to do this I do the following:
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 datatype vary according to the used DBMS I recommend you put this code in a repetition command until you identify the correct datatype for your blob field
Note: nro_field should start at 0