#431 TZRowAccessor.GetPAnsiChar error

[marsupilami79]

This is a crosspost from the Forums: https://zeoslib.sourceforge.io/viewtopic.php?f=28&t=121115

SQLite, Zeos 7.2.6 stable, Lazarus 2.0.8, everything installed two days ago over Lazarus Online package manager
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:

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; 

SQLite table:
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

  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;

I do have tried with VARCHAR(100) in the SQLite table, query.Params.ParamByName('byte5').AsSmallInt for Byte, but this is original code working for years.

What in this configuration triggers the error and what params type will be adequate for Sqlite table configuration?

Thanks

[marsupilami79]

I asked the reporter, which params get used for calling Insert_Update_Last_data.

Hello,

I created a ticket for this issue in the bug tracker:
https://sourceforge.net/p/zeoslib/tickets/431/

Which params are used when you call Insert_Update_Last_data to generate the error?

Best regards,

Jan

[EgonHugeist]

Hi, in 7.2 and below there is no improvement for the FPC misssing field-types like ftByte/ftShortInt. The user propably try's to assign a value with a range > High(ShortInt) to the field. I could not find any usable docs about SQL-standart for TINYINT fields. Thus i added the "unsigned" keyword to dbc. If the field is declared as "tinyint unsigned" the field is mapped to full byte range. See [R6784]https://sourceforge.net/p/zeoslib/code-0/6784/) . On the other hand the behaviour is expected.
Regards, Michael

[EgonHugeist]

Hello Jan, as stated in the forum, it's about "UN/signed" and to make the user lucky: there is a option TZAbstractRODataSet.DisableZFields. Means fall back to the compiler supported fields. Add it to the 7.3 doc's. I don't see a bug here. Thus, closed.
Regards, Michael