I have used this database table configuration and function with 7.1.4 version and Lazarus 1.6.4 for years, do have some freezing issues from time to time, never detected reason.
With latest installation this exception almosst immidiately appear in debug mode, poiting on this line:
Code: Select all
function TZRowAccessor.GetPAnsiChar(ColumnIndex: Integer; out IsNull: Boolean;
out Len: NativeUInt): PAnsiChar;
var Data: PPointer;
label Set_Results, SetEmpty;
begin
{$R-}
if FBuffer.Columns[FColumnOffsets[ColumnIndex{$IFNDEF GENERIC_INDEX} - 1{$ENDIF}]] = bIsNotNull then begin
IsNull := False; CREATE TABLE [Last_data] (
[datetime] REAL NOT NULL,
[username] VARCHAR(50) NOT NULL,
[device_id] INT NOT NULL,
[pin] TINYINT,
[byte2] TINYINT,
[byte3] TINYINT,
[byte4] TINYINT,
[byte5] TINYINT,
[val] FLOAT DEFAULT 0,
[is_received_from_serial] BOOLEAN DEFAULT 0,
[is_received_from_ip] BOOLEAN DEFAULT 0,
[IP] VARCHAR NOT NULL DEFAULT 'N/A',
[sec_between] INTEGER NOT NULL DEFAULT (-1),
[io] TINYINT DEFAULT 0,
[object_name] VARCHAR,
[alarm] BOOLEAN DEFAULT false);
code
Code: Select all
function Insert_Update_Last_data(conn: TZConnection; ArduinoObjectName: string; datetime: extended; username: string; device_id: integer; VAR_OUT: TIOType;
pin, byte2, byte3, byte4, byte5: byte; val: double; IP: string; sec_between: integer): string;
var
query: TZQuery;
io: byte;
recordexists: boolean;
begin
query := TZQuery.Create(nil);
query.connection := Conn;
conn.AutoCommit := True;
recordexists := False;
query.SQL.Add('Select * from Last_data WHERE object_name=' + QuotedStr(ArduinoObjectName));
try
query.Open;
if query.RecordCount > 0 then
recordexists := True;
except
on e: Exception do
begin
Result := 'Insert_Update_Last_data - ' + e.Message;
FreeAndNil(query);
exit;
end;
end;
if query.Active then
query.Close;
query.SQL.Clear;
if not recordexists then
begin//insert if not exists
query.SQL.Add('INSERT INTO Last_data (datetime,username,device_id,pin,byte2,byte3,byte4,byte5,val,io,IP,sec_between,object_name)');
query.SQL.Add('values(:datetime,:username,:device_id,:pin,:byte2,:byte3,:byte4,:byte5,:val,:io,:IP,:sec_between,:object_name)');
query.Params.ParamByName('datetime').AsFloat := datetime;
query.Params.ParamByName('username').AsString := username;
query.Params.ParamByName('device_id').AsInteger := device_id;
query.Params.ParamByName('pin').AsInteger := pin;
query.Params.ParamByName('byte2').AsInteger := byte2;
query.Params.ParamByName('byte3').AsInteger := byte3;
query.Params.ParamByName('byte4').AsInteger := byte4;
query.Params.ParamByName('byte5').AsInteger := byte5;
query.Params.ParamByName('val').AsFloat := val;
query.Params.ParamByName('io').AsInteger := io;
query.Params.ParamByName('IP').AsString := IP;
query.Params.ParamByName('sec_between').AsInteger := sec_between;
query.Params.ParamByName('object_name').AsString := ArduinoObjectName;
end
else
begin
query.SQL.Add('Update Last_data SET datetime=:datetime,username=:username,device_id=:device_id,pin=:pin,byte2=:byte2,byte3=:byte3,byte4=:byte4,byte5=:byte5,val=:val,IP=:IP,sec_between=:sec_between,io=:io,object_name=:object_name ');
query.SQL.Add('WHERE device_id=' + IntToStr(device_id) + ' and username=' + quotedstr(username) + ' and pin=' + IntToStr(pin) + ' and byte4=' +
IntToStr(byte4) + ' and object_name=' + QuotedStr(ArduinoObjectName));
query.Params.ParamByName('datetime').AsFloat := datetime;
query.Params.ParamByName('username').AsString := username;
query.Params.ParamByName('device_id').AsInteger := device_id;
query.Params.ParamByName('pin').AsInteger := pin;
query.Params.ParamByName('byte2').AsInteger := byte2;
query.Params.ParamByName('byte3').AsInteger := byte3;
query.Params.ParamByName('byte4').AsInteger := byte4;
query.Params.ParamByName('byte5').AsInteger := byte5;
query.Params.ParamByName('val').AsFloat := val;
query.Params.ParamByName('io').AsInteger := io;
query.Params.ParamByName('IP').AsString := IP;
query.Params.ParamByName('sec_between').AsInteger := sec_between;
query.Params.ParamByName('object_name').AsString := ArduinoObjectName;
end;
try
try
query.ExecSQL;
Result := 'ok';
except
on e: Exception do
begin
Result := 'Insert_Update_Last_data_2 - ' + e.Message;
end;
end;
finally
FreeAndNil(query);
end;
end; What in this configuration triggers the error and what params type will be adequate for Sqlite table configuration?
Thanks
