[SOLVED] UniDac on PostgreSQL: no value on serial (autoincrement) field after insert

UniDac on PostgreSQL: no value on serial (autoincrement) field after insert

In my application user have ability to add some records to database. I'am using UniDac 4.6.12 components.

For autoincrement field, we are using serial PostgreSQL type and I don't touch value of this field in my application. But, after Post of record, the value of field is 0. Also, I get 0 from:

Q := dmMain.aqrParts.LastInsertId;

If I refresh the dataset, record will appear with filled serial field value, but it is not comfortable way for user, because table have a lot of records, and to fetch some records to work, for user necessary to set a lot of filters.

I using this kind of properties of dataset:

  object aqrParts: TUniQuery
    Connection = psqlConnection
    SQL.Strings = (
      'SELECT * FROM parts.spareparts'
      'LIMIT 200')
    SpecificOptions.Strings = (
      'PostgreSQL.UnpreparedExecute=True')
    BeforePost = aqrPartsBeforePost
    AfterPost = aqrPartsAfterPost
    Left = 32
    Top = 72
    object aqrPartsId: TIntegerField
      AutoGenerateValue = arAutoInc
      FieldName = 'id'
      Visible = False
    end
...

Is it possible to solve this?

Solution

When you create a field with the serial type, PostgreSQL server automatically creates a sequence, and values from this sequence will be used as default for this field

To fill Id field automatically you can use two ways:

1) Set

aqrParts.Options.DefaultValues := True.

In this case default value for the Id field will be set to "nextval(''parts.spareparts_seq''::regclass)" and the Id field will be filled automatically.

2) Set

  aqrParts.SpecificOptions.Values['KeySequence'] := 'spareparts_seq';
  aqrParts.SpecificOptions.Values['SequenceMode'] := 'smInsert';

In this case field Id will be filled from this sequence.