db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From pierre madiot <pito...@yahoo.fr>
Subject Derby & webapp devpt - best practice for data integrity
Date Tue, 14 Feb 2006 12:21:03 GMT
Hello,

we are developing a webapp, that accesses a Derby
database.
This webapp, will be deployed on multiple distinct
webcontainers.

our Database contains a handful of tables, each in the
form :
create table T1(
t1_id int not null GENERATED ALWAYS as identity
primary key,
t1_name varchar(100) not null,
...etc....
);

create table T2(
answer_id int not null GENERATED ALWAYS as identity
primary key,
t2_id int not null references T1(t1_id),
t2_name varchar(400)
...etc....
);

we are facing a double problem :

1) Websphere 5.1x doesn't support:
   - getGeneratedKeys() and
   - preparedStatmement(SQLStr,
Statement.RETURN_GENERATED_KEYS)
   Due to this first problem, we are trying to find a
work arround in a global transaction to insert data
rows and get the GENERATED keys, and insert in other
tables related data with that retrieve generated keys.
We mad the assumption that we could use :
select max(t1_id) from t1, to get the generated id.
This could be safe, if the whole jdbc transaction was
thread synchronized to avoid multithreaded servlet
conflicts.
But then we hit the second issue.

2) as we will deploy our webapp accross multiple
distinct webcontainers accessing the same backend.
the jdbc synchroniation safety net, would not be
effective.
So it seems that the handling of this database data
insertion integrity can only be handled at the RDBMS
server itself. using jdbc only as a trigger to to all
the work on the target RDBMs.

we would very much appreciate your help, experience,
pointers to solutions to these 2 problems (considering
potential RDBMs independance see postscriptum).


the websphere appserver used is 5.1.1
derby version is 10.0.2.1

thanks in advance for your help

PS : our DB of choice is derby, but user might choose
another DB vendor like Oracle/MySQL etc...
Any pointers on best practice for this kind of use
case for RDBMs & webapps on multiple webcontainer
would be greatly appreciated.
 




	

	
		
___________________________________________________________________________ 
Nouveau : téléphonez moins cher avec Yahoo! Messenger ! Découvez les tarifs exceptionnels
pour appeler la France et l'international.
Téléchargez sur http://fr.messenger.yahoo.com

Mime
View raw message