AUTOINC With Multiples Columns ODBC

questions tips
1

Hello guys,
I would like to ask, how to do an autoincrement in C9, using PGSQL.
I want to do it using two columns.

EXAMPLE:
A B
1 1
1 2
2 1
1 3
2 2

ANY TIPS or CODE?

2

Should be coded in a before insert trigger.for the PG table.

3

An example for the before insert PG trigger code:
IF NEW.b = 0 OR NEW.b IS NULL THEN
SELECT COALESCE(max(b),0)+1 INTO NEW.b FROM mytable WHERE a = NEW.a;
END IF;
RETURN NEW;

4

If you use a serial or big serial data type (itā€™s an integer/dbl with a sequence to initialize the integer), then the db will do it for you.

You can also use a sequence manually, and run a simple query to get the next value.

Or leave such work to the templates, which I avoid with sql.

5

Hi,

There is a recent thread on the softvelocity.clarion.databasedrivers newsgroup started Jan 28 by Ramon Hernandez. If I had to summarize I would say: donā€™t do it like that, itā€™s not worth it. Just use a serial data type for the second one, and if you want to show things consecutively numbered within each column A, do that in a view when you are displaying things to the user.

6

Perhaps a detail that is being lost - serial data type for primary key. Autoinc a different column value for a two column display order. Yes?

7

The original suggestion was this:
EXAMPLE:
A B
1 1
1 2
2 1
1 3
2 2

And Iā€™m suggesting that you could happily have data that looks like this:
A B
1 1
1 2
2 3
1 4
2 5

The B column could, yes, be used as a primary key. It would have unique values, and you would set it up as a serial column (i.e. autonumbered by Postgres). Your Column A presumably gets set some way ā€“ itā€™s a customer ID or something.
And if you want the user to see those items for Customer 1 as items 1,2, and 3 rather than 1,2 and 4, you have a view like:
select a,row_number() over (partition by a order by b) item
from mytable

1 Like
8

I managed to solve it using some functions, Iā€™ll leave an example here:

!PEGA ULTIMO NUMERO
IF SELF.Request = InsertRecord
    CLEAR(DEP:Record,1)! if MASTER is the prefix
    DEP:empresa = GLO:Empresa_Padrao
    SET(DEP:por_empresa_deposito,DEP:por_empresa_deposito)
    PREVIOUS(depositos)
    IF ~ERRORCODE()
        IF DEP:empresa <> GLO:Empresa_Padrao
            LOC:Codigo = 1
        ELSE
            LOC:Codigo = DEP:deposito + 1
        END
    ELSE
        LOC:Codigo = 1
    END
    CLEAR(depositos)
    DEP:empresa = GLO:Empresa_Padrao
    DEP:deposito = LOC:Codigo
END
9

I define primary keys, however, I donā€™t use autoincrement, either from SQL or Clarion.

10

The code you posted is exactly what Clarion would have written for you if you had defined your key in the dictionary as autoincremented, except for the Clarion version is a little better about not overwriting things you already had in the buffer and dealing with conflicts if two stations are trying to add a record at the same time.
So, congratulations, you have now written code there was no need for you to write; you could have just checked auto-increment in the dictionary. Personally, with PG as the backend I would still prefer to have PG do the autonumbering. Itā€™s a lot quicker to ask PG to add in a number from a sequence than it is to do a ā€œSelect max(id)+1 from mytable where category = ā€˜whateverā€™ā€ and have the result passed back to your client program to get the number.

2 Likes
11

Thanks @JonW ,
but I put it in the Dictionary, and even so, it didnā€™t work, I donā€™t know why, even more so with a key that sometimes reaches 3 fields. But it has worked well for me, so thatā€™s what programming is, if it works well for me, itā€™s ok. And so I can have more flexibility in Clarion.
And now, Iā€™m already using a location at runtime, Codigo$ between LOC:Codigo.