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.