Search code examples
delphipostgresqldelphi-xe2devartunidac

PostgreSQL serial (autoincrement) with UniDac on Delphi XE2

I am migrating our Delphi XE2 application from MSSQL (using ADO components), to PostgreSQL, using UniDAC.

In database, there are some serial type fields (autoincrements). When I append record, I not put any data to this autoincrement field. Formely, with MSSQL/ADO it works automatically, but now I have an exception.

The code:

aqrMsgs.Append;
aqrMsgsUser_From.AsInteger := UserId;
aqrMsgsUser_To.AsString := UserIds[I];
aqrMsgsSubject.AsString := Trim (edtSubject.Text);
aqrMsgsContents.AsString := mmoContents.Text;
aqrMsgsIsDone.AsBoolean := False;
aqrMsgs.Post;

And the exception is:

Exception

Field 'id' is TIntegerField, not TAutoIncrementField.

By the way, if I using DBGrid edit ability (exactly, I using ExpressQuantumGrid), to append records to another table with the same structure, everything working OK.

How possible to solve it? Thanks.

Solution

  • 1) 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. If the serial field is not set when inserting a new record, the server takes a value from the sequence for it. But if you set a value for the serial field, the server will insert this value. Since the sequence doesn't know anything about the value you have inserted to the serial field, then on further inserting records (when using the sequence), the "duplicate key value" error can occur, if the serial field is created with unique constraint. You won't encounter this issue if you don't manually set values for this field.

    2) UniDAC can automatically fill in a field using a sequence. For this, you should set the TUniQuery.KeyFields and TUniQuery.SpecificOptions.Values['KeySequence'] properties as follows:

      UniQuery1.KeyFields := 'id'; 
  UniQuery1.SpecificOptions.Values['KeySequence'] := 'test1_id_seq';

    In addition, using the TUniQuery.SpecificOptions.Values['SequenceMode'] property, you can specify when exactly UniDAC will fill in the field using the sequence: on calling Append/Insert or Post.

    You can find the detailed information about the mentioned properties here: http://www.devart.com/unidac/docs/pgsqlprov_article.htm