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’]

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

 

• PostgreSQL
• WINDEV