Auto Incrementing with PostgreSQL and Windev (Updated 10/3/2012)


Photo by bling_rocks

There are a few challenges when attempting to use auto-incrementing keys with PostgreSQL and Windev. But once we understand what the issues are, its easy enough to work around.

PostgreSQL uses sequences to accomplish auto auto-incrementing fields, for those that normally work with SQL Server this is a little different than the Identity Columns of SQL Server. Sequence are a separate database object that the the Server maintains to provide the next incremental value for a field.  This does allow for some unique advantages, such as having two tables shared the same sequence, for instance to have for one database design I have some invoices that go directly into the system, and others that come through a “prescan” function first. By having both tables share the same sequence, the sysid’s for the prescan table are unique and will not be duplicated in the invoice table, so when the prescan is approved and can be moved into the invoice table its a simple insert into, I don’t have to worry about modifying the ids on all the child records. Another difference is that you are able to insert a value into the field via an update or insert statement without having to do anything special set Set Identity On, like what is required in SQL Server (warning this can get you in trouble with your sequence getting out of sync as we will see in this article).

So now that you know a little bit about sequences lets get started and see how to use them with WinDev.

The first step is to create the table, the special variable type “Serial” tells PostgreSQL to create a sequence and use it as the default value for the field.

CREATE TABLE customer
(
 custsysid serial NOT NULL,
 firstname character varying(20),
 lastname character varying(20),
 CONSTRAINT pkcustomer PRIMARY KEY (custsysid )
);

After executing the create statement if you view the actual DDL you can see that a sequence has been created, and the default value for custsysid is using that sequence:

CREATE SEQUENCE customer_custsysid_seq
START 1;
ALTER TABLE customer ALTER COLUMN custsysid SET DEFAULT nextval('customer_custsysid_seq'::regclass);

One of the nice things about sequences is that they can be controled via SQL statements. To make it easier to see what is happening with the Windev code later I am setting the sequence to start with a value of 100.

ALTER SEQUENCE customer_custsysid_seq RESTART WITH 100

When the table is Imported into the Analysis it is set to use auto-incrementing.

 

The issue is that WX is actually managing and assigning this value instead of using the sequence. Executing the following WX code demonstrates this issue

customer.firstname = "test1"
customer.lastname = "test1"
HAdd(customer)
DS is Data Source
SQLStatement is string = "insert into customer(firstname,lastname) values('test2','test2')"
IF HExecuteSQLQuery(DS ,MyConnection1 ,hQueryWithoutCorrection,SQLStatement) THEN
ELSE
 Error(SQLStatement+" "+HErrorInfo())
END

Taking a look at the data it is obvious that the HAdd statement did not use the sequence, since its value is 1 and the HExecSQLQuery did use the sequence since its value is 100

select * from customer
1;"test1";"test1"
100;"test2";"test2"

This will eventually lead to issues especially if you have any standard SQL code or another application that does use the sequence, if WinDev has inserted a value, that the sequence later increments to a primary key already exists error will be generated.

With some databases such as mySQL having a value of null in a column will cause it to use the default value, however PostgreSQL is a little more restrictive this that. The default value for PostgreSQL will only be use if the column is not specified in the statement such as in my HExecSQLQuery statement above, of if the keyword DEFAULT is used:

insert into customer(custsysid,firstname,lastname) values(DEFAULT,'test','test')

For mySQL manually setting the field to null as part of the HAdd statement will allow the sequence to be used.

customer.custsysid..null = true
customer.firstname = "test1"
customer.lastname = "test1"
HAdd(customer)

As mentioned a null will not cause the default to be used for PostgreSQL so that work around is not help and I have not be able to come up with a method of getting Windev to pass the DEFAULT keyword to PostgreSQL when using the statement Hxxxx commands.

I  best work around I have found for this issue is a slight change in the database design for PostgreSQL, of course depending on you situation this may not be a good solution for you.

My solution involves not creating the sequence automatically using the serial variable type and instead creating it manually and then managing its use via an before insert trigger. The DDL for the table, sequence and trigger follows:

CREATE TABLE customer
(
 custsysid integer,
 firstname character varying(20),
 lastname character varying(20),
 CONSTRAINT pkcustomer primary key (custsysid )
)
CREATE SEQUENCE customer_custsysid_seq
 START 100
CREATE OR REPLACE FUNCTION customerinsert()
 RETURNS trigger AS
$BODY$
BEGIN
 IF new.custsysid is null OR new.custsysid = 0 THEN
 new.custsysid = nextval('customer_custsysid_seq') ;
 END IF;
 return new;
END
$BODY$
 LANGUAGE plpgsql VOLATILE
CREATE TRIGGER customerinsert
 BEFORE INSERT
 ON customer
 FOR EACH ROW
 EXECUTE PROCEDURE customerinsert();

The only real difference in the Create table statement is using the int variable type instead of serial. The next statement manually creates the sequence since it will no longer be automatically created. The next two statements create the Before Trigger. SQL Server folks will notice triggers are a little different, but that subject if for another article. For the purpose of this article all you need to understand is that the trigger is setup to execute once for every row inserted into the customer table before the record is actually inserted into the database. If CustSysid is null or 0 then the NextVal() function is used to get the next value from the sequence, which automatically manages all the locking and incrementing of the sequence.

With this table design, the WX Analysis is very similar to the original except it is not setup for auto-incrementing and the  custsysid is setup as a numeric instead of autoincrementing. The original WX code now will execute and use the sequence as desired, no need to use the ..null property. And all of the following SQL statements also perform as expected.

insert into customer values(null,'test1','test2')
insert into customer(firstname,lastname) values('test2','test2')
insert into customer values(default,'test1','test2')
insert into customer values(1,'test3','test3')

Now for the downside, using this technique breaks Windevs feature of having the sysid available after an insert. After the HAdd is executed customer.cstsysid contains 0 instead of the inserted value.

If you have any thoughts on this issue be sure to post them in the comments or send them to me, I will be happy to test any other methods and publish an update is someone has a better way to handle the issue.

Update 10/3/2012 – Having our Cake and Eating it Too!!!!

After doing some thinking and discussing with a few folks on our Windev Skype Group, I came up with an approach I believe solves all the issues!

First, the original table design using the serial data type can be used (remember to change the Imported Analysis to using a numeric instead of autoincrement). This is good news as now there won’t be any issues with deal with a DBA that doesn’t want to tweak the table design and the design will follow best practices. Second there is no need for a trigger, so again the design will follow best practices and make your DBA happy, and we all want to make our DBA happy don’t we?

This method takes advantage of the fact that sequence can be directly accessed.  When you Select NextVal(SequenceName) the next value is returned and the sequence is incremented. So its a simple mater to use HExecuteSQLQuery to call NextVal() and set the id accordingly.

Of course as usual I am going to take it a little further than that:

First I created a generic function to call NextVal()

PROCEDURE GetNextVal(FieldName)
dsSQL is Data Source
SQLStatement is string = [
select nextval('%1') as returnid
]
SQLStatement = StringBuild(SQLStatement,Replace(FieldName,".","_")+"_seq")
IF HExecuteSQLQuery(dsSQL,MyConnection1,hQueryWithoutCorrection+hNoBind,SQLStatement) THEN
 HReadFirst(dsSQL)
 IF NOT HOut() 
 RESULT dsSQL.returnid
 END
ELSE
 Error(HErrorInfo())
END

This function accepts the field name as a parameter, then takes advantage of the fact that the serial feature in PostgreSQL consistently names the sequence, as FileName_FieldName_Seq. It calls NextVal() for the appropriate sequence and returns the value. So to set the id all we have to do is something like this:

customer.custsysid = GetNextVal("customer.custsysid")
customer.firstname = "test1"
customer.lastname = "test1"
HAdd(customer)

As I have mention in previous articles I take advantage of intellisense by typing the filename.fieldname without quotes so intellisense can insure I don’t make a typo, then I surround it with quotes.

Now customer records will be inserted with the correct id, standard SQL statements will behave as we would expect, the ID will be available immediately in our code, and there’s no worry about duplicates etc. as the sequence is managing everything.

… But its still requires a little extra coding versus stand WX code to do an insert. But thanks to Peter Holemans pointing me the right way, I figured out how to using WX’s amazing ability to create WX coded triggers that fire on the client when standard Hxxxx commands are used.

First I create another global procedure specific for the customer table as follows:

PROCEDURE IncCustomer()
customer.custsysid = GetNextVal("customer.custsysid")

You should have noticed this is the exact same line of code that we used above, now you just create a trigger by placing the following line of code in your project code:

HDescribeTrigger(customer,"HADD","IncCustomer",hTriggerBefore)

This command say that whenever the HADD command is executed on the customer table, execute the IncCustomer procedure before the HADD takes place. And when the IncCustomer procedure is called it will call the GetNextVal procedure and set custsysid to the value returned from the sequence. So now our standard Windev code:

customer.firstname = "test1"
customer.lastname = "test1"
HAdd(customer)

Works exactly as expected, the code is stand WX code the same as would be written for any other database, the value is inserted, the sequence is used and updated, and the inserted ID is immediately available to the rest of your code. All that required is one generic GetNextVal procedure, a one line procedure for each table using a sequence to call GetNextVal and set the ID for the table, and one line of code in the project code area for each table to declare the trigger. Enjoy the cake!!!!

PS: I will be working with Andy Stapleton so he can include this logic into his File Manager Classes as well.

[suffusion-the-author display=’author’]

Pete Halsted[suffusion-the-author display=’description’]

 

10 thoughts on “Auto Incrementing with PostgreSQL and Windev (Updated 10/3/2012)

    1. Steve, I played with Firebird many years ago, but have not looked at it recently. Sequences are very handy, and is how Oracle, PostgreSQL, mySQL, Mimer and many other database achieve auto-incrementing. They are part of the ANSI 2003 standard, although they have been around much longer than that.

      Like

  1. Hi Pete, i have just tried your Getnextval trick and it works great except the result always increase by 2. It seems that the sql command run twice. What is wrong ?

    Like

    1. I am not sure but I suspect you are correct somehow your code is executing twice, or the normal sequence insert is being executed as well as the getnextval code.

      Like

      1. I use your GetNextVal code just as is without any modification.

        To make sure about this, i created a test window with “edt_result” control and BTN_button with command : EDT_result=GetNextVal(“tblterima.id”)

        There is no trigger in my project code.

        When i click this button, edt_result always increase +2

        But Executing “select nextval(‘tblterima_id_seq’) as returnid” in PGAdmin return correct increment (+1).

        I really dont know what is going on
        Windev 16 Version 01A160057k
        PostgreSql : 9.2

        Thanks for your help Pete !

        Like

  2. I want my analysis tables creating in postgresql
    I am creating one table and
    I am looking with postgresql, my table name between double quotes
    for example CREATE TABLE “Clients” and
    I use query for example SELECT * FROM Clients
    postgresql said that
    ————————–
    ERROR: relation “Clients” does not exist
    LINE 1: select * from “Clients”
    ————————-/—
    –(cursor signs C character)

    why? because my table is first character big C and so
    Native postgresql provider or postgresql see big C character table name use creating table with double quotes

    ==========================================================================
    big question:My analysis tables How creating with postgresql
    Thanks

    Like

  3. I never allow the Analysis to create my database tables. When using a SQL back-end managing the database and its structure is something that should be the responsibility of someone performing the DBA role in my opinion.

    If you create your tables via PostgreSQL, and in your Analysis having everything as lower case then you will not have an issue.

    Like

  4. I have analysis and my analysis has tables and informations in tables
    this analysis created with (mysql DB,connection by: Native Mysql Access for
    windev,provider hNativeAccessMySQL)

    I want,
    I creating database in postgresql with analysis
    (analysis has tables and informations in tables)
    but I creating database HCreateIfNotFound() functions
    for example HCreateIfNotFound(Clients)

    I am creating one table(use Native PostgreSQL access for WinDev and hNativeAccessPostgreSQL,HCreateIfNotFound(Clients))
    and I am looking in postgresql, my table name between double quotes
    for example CREATE TABLE “Clients” and
    I use query for example SELECT * FROM Clients
    postgresql said that
    ————————–
    ERROR: relation “Clients” does not exist
    LINE 1: select * from “Clients”
    ———————–/—
    –(cursor signs C character)

    why? because my table is first character big C and so
    Native postgresql provider or postgresql see big C character table name use creating table with double quotes

    big question:How I am creating my analysis(analysis has tables and informations in tables) in Postgresql

    Thanks

    Like

  5. I was trying to figure how to retrieve the value an auto-incrementing Postgres primary key as a new record is added in Windev. I revisited this blog post today having last read it nearly 2 years ago. And as we’ve come to expect, you had the the solution for me. Thanks Pete.

    I made a minor amendment in my implementation where the first line of the body of my version of the trigger changes from:
    IF new.custsysid is null OR new.custsysid = 0 THEN
    to
    IF new.custsysid is null OR new.custsysid >= 0 THEN

    The use of greater-than-or-equal-to rather than just equal-to is to prevent the potentially disastrous situation occurring of a user inserting records into the table with a manually generated positive integer value assigned to the the custsysid column which sometime later gets tripped over by an auto-assigned value from Postgres’ Sequence, effectively blocking any further inserts into the DB.

    One useful side effect of the Trigger you described is that we once again have our cake and eat it when it comes to choosing if new a record has an auto-incremented or alternately a pre-assigned value for the custsysid (say if we want to bulk upload customers and their related invoice table data from a spreadsheet)

    This is done by pre-assigning unique negative numbered custsysid’s for our bulk customer-and-related-invoice upload. Alternately any records added with a null, zero or positive integer in the custsysid column will get auto-incremented as expected.

    Finally, I see you use an equal sign when assigning values in your Trigger code:
    new.custsysid = nextval(‘customer_custsysid_seq’)

    Trigger code I had seen elsewhere used equals-colon for assignment like this
    new.custsysid =: nextval(‘customer_custsysid_seq’)

    So should assignments in Trigger Code use =: or is = without the colon always safe to use instead?

    Like

    1. := is very very old school and originally the only assignment operator. It has since been replaced with the “modern” = assigment and the := is just kept for backward compatibility. Very similar to how pcSoft keeps functions for backwards compatibility.

      Storing negative sysid’s for bulk inserts is a novel approach. I tend to handle that on the code side as I like my sysid’s to reflect “true” insert order.

      Thanks for the great comment and sharing your approach!!

      Like

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s