Best way to handle Uniquery and identity autoincrement cloumn of a postgresql Table

Question

I get this error: field "bankid" must have a value.

For solving this I must add the field using Uniquery Fields editor and set "required" property to False, also autogeneratedvalue to arAutoInc .

Is it the only way doing this?adding fields to Uniquery?

Table:

CREATE TABLE public.banks (
  bank    varchar(50),
  branch  varchar(80),
  "no"    varchar(30),
  bankid  integer NOT NULL GENERATED ALWAYS AS IDENTITY,
)
WITH (
    OIDS = FALSE
  );

uniquery component:

  object UniQuery2: TUniQuery
    SQLInsert.Strings = (
      'INSERT INTO "Banks"'
      '  (bank, branch, no)'
      'VALUES'
      '  (:bank, :branch, :no)  RETURNING bankid;')
    SQLUpdate.Strings = (
      'update "Banks" set bank=:bank, branch=:branch, "no"=:no'
      'where bankid=:bankid')
    SQLLock.Strings = (
      '')
    SQLRefresh.Strings = (
      'SELECT bank, branch, no, bankid FROM "Banks"'
      'WHERE'
      '  bankid = :bankid')
    SQLRecCount.Strings = (
      'SELECT count(*) FROM ('
      'SELECT * FROM "Banks"'
      ''
      ') t')
    Connection = UniConnection1
    SQL.Strings = (
      'select * from "Banks" order by bank,branch,"no"')
    Options.ReturnParams = True
    Left = 64
    Top = 80
    object UniQuery2bank: TStringField
      FieldName = 'bank'
      Required = True
      Size = 50
    end
    object UniQuery2branch: TStringField
      FieldName = 'branch'
      Required = True
      Size = 80
    end
    object UniQuery2no: TStringField
      FieldName = 'no'
      Required = True
      Size = 30
    end
    object UniQuery2bankid: TIntegerField
      AutoGenerateValue = arAutoInc
      FieldName = 'bankid'
    end

  end

delphi code:

UniQuery2.Options.DefaultValues := True;
uniquery2.open;
UniQuery2.Append;
UniQuery2.fieldByName('bank').AsString:=sEdit1.Text;
UniQuery2.fieldByName('branch').AsString:=sEdit2.Text;
UniQuery2.fieldByName('no').AsString:=sEdit2.Text;
UniQuery2.Post;

delphi 10.2.3 postgresql 10.8

do it the long way : script := 'insert into public.banks(bank, branch, no) values(' + QuotedStr(sEdit1.Text) + ',' + QuotedStr(sEdit2.Text) + ',' + QuotedStr(sEdit2.Text) + ')'; UniQuery2.Connection := YOUR_CONNECTION; UniQuery2.Execute; — Ago
I use this wat on some other forms using parameters. But in this form unquwet is connected to a dbgrid — Shahram Banazadeh

Shahram Banazadeh Shahram Banazadeh · Accepted Answer · 2019-07-02T22:08:24

parameterized form of Ago's solution could solve the problem when Uniquery is not attached to a data source and Dbgrid

uq.sql.text:='upadte table1 set field1=:f';
uq.parambyname('f').asinteger:=somevalue;
uq.execute;

when uniquery has merged data and calculated fields for using with dbgrid :

uq.append;
uq.fieldbyname('field1').asinteger:=somevalue;
//****
uq.filedbyname('id').required:=false;
//****
uq.post;

Best way to handle Uniquery and identity autoincrement cloumn of a postgresql Table

1 Answers