Re: serial values and odbc

> I am working with delphi through odbc to get to Postgres, but I have a
> problem:
>
> Everytime I try to make an insertion, I get a message from the odbc driver
> saying a Primary key value cannot be null (which is on purpose since I want
> postgres to use it serial value properties). Can anyone tell me if there's
> something special I have to do on the ODBC configuration, or how do I make an
> insertion through odbc?

I think it isn't ODBC related. But we use the following method:

Wth the DataSet's OnBeforPost method fetch the serial's NextValue and fill in
the Field in the DataSet like this:

QueryBeforPost(TDataSet* DaraSet)
{
if (DataSet->State == dsInsert &&
DataSet->FieldByName("<MySerialField">)->Value == Null)
{
TQuery* q = new TQuery(this);
q->DataBaseName = "<MyBDEDataBaseAliasName>";
q->SQL->Clear();
q->SQL->Add("SELECT nextval('<MyTable><MySerialField>_seq'");

// put these two statments into a try block...
q->Active = true;
DatSet->FieldByName("<MySerialField>")->Value =
q->FieldByName("nextval")->Value;

q->Active = false;
delete q;
}
}

Sorry, I'm a C++ programmer, so I can't write ObjectPascal code but I think it
will help.

This is the recommended method with PostgreSQL and with visual database
actions. You may put this code without the dsInsert condition into the
BeforInsert event to show the user the value but we prefer the one shown.

If the post fails - or the user cancels - the next nextval will send (the
next) safe value (see docs and archive). No matter the unused values. It works
even in a transaction block.

--
Tibor Laszlo
ltibor(at)mail(dot)tiszanet(dot)hu