Carlos Anjos wrote:
>
> Hi.
> Suppose I have a table like
> table components(
> componentType int,
> description varchar(200),
> serialNo int;
> )
>
> I want to give each component a different id. I could make the serialNo
> of type autoInc, and that could be acomplished automaticaly. However,
> that's not exactly what I want. I want that serial to be incremental FOR
> EACH componentType. For each value of componentType, the serial starts
> at 0. So I could have:
> 0,'comp 1',0
> 0,'comp 2',1
> 0,'comp 3',3
> 1,'comp 4',0
> 1,'comp 5',1
> 2,'comp 6',0
> and so on ...
>
> What is the best to acomplish this ?
> I can only see a way out:
> Use an interceptor with a preInsert method that does a select on the
> used serials for a certain componentType and adds 1 to the last value
> found.
> However it doesn't seem a very elegant solution. And I think it could
> lead to racing problems if 2 users try to add componentes at the same
> time.
- That is true. However, if you declare
primary key (componentType,serialNo)
the second user will be unable to save his row, but gets an error instead.
If he tries again, he will succeed. That compound key will at least prevent
key values from being used twice. (didn't you say you never use this ;) ? )
- If the dbforms application is the only way to store new rows, you
might also read the current maximum key values into an application wide
object at application startup time.
Whenever a new row is to be saved, you use a synchronized
method to read, increment and write back the key value. So you don't
use the locking mechanisms of the database but of java and you do it all
in main memory.
- If you cannot use a java object, maybe because other applications
totally separated from the webapp also insert rows,
I would consider to store current key values within an extra table:
component_type last_key
------------------------
0 3
1 2
2 0
That way you do not have to scan a large table for a maximum value.
And if you want to use the database, not a web application object
to maintain the key values and have to lock the table in order to
prevent problems because of concurrent access, you will
1) lock a small, not a big table
2) lock it a shorter time, because the information can be found faster
3) lock not the data table itself
- Other databases like postgres might support that internally by using
before-insert triggers.
But be aware that dbforms still has problems with some kinds of
automatically generated keys: If the database sets the value, dbforms
will not know the generated value for the key column and then might
have problems to navigate to the new row (even nullpointer excp might
arise).
The base problem is that before JDBC3.0 there was no standard way
to get the value of such a generated key. We still don't use JDBC3
drivers, so we should extend dbforms maybe to support at least some
dbms where a proprietary way of getting the column value is known.
Another topic on the TODO list....
I fear you'll get the same problem if you use database triggers
to generate the key value.
Hope this helps a bit
Regards
Dirk
-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
DbForms Mailing List
http://www.wap-force.net/dbforms
