%Next and %Previous MetaSqls

I came across debugging an application engine and got to these two interesting meta sql’s. Thought of sharing it over here so that those who are not already aware of it can make benefit of it when writing an application engine program next time. These two meta sql’s are exclusively for use in application engine programs.

As you have thought about, the sql name itself suggest its intended functionality. This sqls are useful when you are updating or inserting a table with sequence numbers. %Next will return the value in the associated field first and then increment the value of associated field with 1. This you can compare with the a++ methodology in c++ (for those who are aware of c++). The typical example for the usage is as follows.

rem In the PeopleCode section, you may assign the initial value of the bind field;

MY_STATE_RECORD_AET.SEQ_NUM.Value = 1;

rem Now in the Sql Update/Insert statement you may use this bind variable in conjunction with the %Next metasql;

UPDATE MY_TABLE SET SEQ_NUM = %Next(SEQ_NUM) WHERE …

During the first run of the sql, the above statement will update the SEQ_NUM in MY_TABLE with the value 1 and the value of state record field will be updated to 2. So that in the subsequent run, your db table is updated with a value of 2 and bind field with value of 3 and so on. As mentioned above, you may relate it to a c++ code of c = a++;

%Previous metasql does the opposite of what %Next does. It will assign the value to and decrements its own value with 1. This can be related to a—methodology in C++. Take the example code below.

rem PeopleCode section;

MY_STATE_RECORD_AET.SEQ_NUM.Value = 10;

rem SQL action

UPDATE MY_TABLE SET SEQ_NUM = %Previous(SEQ_NUM) WHERE …

After the execution of the code, your db field will be updated with a value of 10 and your bind field will be updated with a value of 9. The corresponding expression in c++ is c = a--;

Though simple, these functions will be extremely useful when working with an application engine program which handles sequencing logic. It helps you to avoid the extra step of handling the bind variable value as it is now system handled.

When using these sqls with DoSelect action, you should be careful as these increment or decrement will happen only with each executions. What this means is, if you have not selected the ReSelect option, then the increment or decrement will happen only once even though your sql returns a thousand rows.