updating info 6

[rvy2k]

Hi everybody,

I have multiple client application that are updating a Microsoft Access database on a server.
Each client have an up-to-date representation of the data on the database.
I would like the client to be notified each time the database is changed by another client so that it can update its representation.
What would be the best way to handle it? I heard about a file modified function, but then isnt it going to slow down the program if it has to query every time to know if the database was modified or not? Moreover it is not real time...

Thanks for your help!

 

[LeonTang]

Hi,

What you might want to do is to create a new method that sends email within your multiple client application itself. Have a global variable(some thing like modified=false which will indicate whether there are any modifications done to the database. When you insert, delete or update a record, just change the value of this variable(maybe from false to true depending on you).

Before your application is closed, call this method. The method will check against the variable and send the email to the necessary people.

Hope this helps,
Leon

 

[rvy2k]

Thanks Leon,

This is a good idea but what would i do exactly to send an email to all the other client application?

 

[LeonTang]

That depends on what is your specifications. 1 way that you can go about doing is to store updated information into the database. So whenever a user logs into the server, the application will read from this database and display the newly updated records done by the other users.

Another solution, which is easier, would be to use JavaMail to send an email to each and every user besides himself. You will have to store the email address of each user for this case.

JavaMail link:-

http://java.sun.com/products/javamail/index.html

Regards,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

 

[rvy2k]

Hey Leon,

The problem with solution 1) is that the user might log into the server once and then use the program for a couple of hours and miss a lot of updates...

I dont think that solution 2) would accomplish what I am looking for. I followed your link and I read that Javamail is an email program.

People using the java program will often update a database located on a server. I need every user of the program to have an updated representation of the database as soon as the database has been updated.

 

[haslo]

Well, just try to make a thread that logs on to the server every 10 minutes or so and so gets all those updates in time. The problem here is that all clients must have a permanent internet connection for that purpose...

For the second solution: JavaMail is an API, not a mail program... ;-)

Another option would be to use JSP and servlets to access the database via JDBC and ODBC - that way you would not even have an update problem...

allow thyself to be the spark that lights the fire
haslo@haslo.ch - www.haslo.ch​

 

[LeonTang]

Sorry, I think I misunderstood your question. I thought that you just wanted to inform the user that there are new records in the database...

Since a user might be able to log in and stay there for a few hours, what you can do is to get your server to inform your clients' that are currently connected to the server that the database has been changed and the client will therefore retrieve the updated database. And everytime a client connects to the server, they will automatically do an update too.

Another way is to do what Haslo has suggested which is to create a thread to retrieve the updated database from the server for an amount of time specified.

Regards,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

 

[rvy2k]

Thanks for both your input.
Dont worry LeonTang, I am not really clear on what I want to explain

The thread solution is a really good idea but I would like to avoid overloading the server with pinging and I would like a database representation uptodate to the minute.

I didnot really inderstand what JavaMail is... Is a mail client? Do I have to give users an email address?

I read that if I could implement some EJB container to handle db interactions, this container will be responsible for notifications as well. I dont know anything about EJB, but is that feasible?

Thanks again for your help.

 

[haslo]

The thing about EJB is that the database is not at the client side but server-sided. If you want an up-to-date-representation every possible second any user wants to access the data this is the best solution anyway - not to hold the database at the client's computer but just creating some kind of frontend by which the clients can access the data on the server. This requires a permanent online connection to the server though. Are you in an intranet? Is it an internet application? For what kinds of clients? Always online / online when needed / sometimes for sending mail?

EJBs are a pretty good solution here, I think. But I don't know if it's a little tough to go there if you did not program quite a lot before... well, basically there are tools that do the tricky and nasty stuff for you (such as remote and home interfaces that are always needed). I'd recommend Together here (

http://www.togethersoft.com).

J2EE handles thread-safety and database connections and pooling as well as ressource handling (by passivating unneeded objects) for you. But it took me half a year to understand it - it's very scalable, but it may be a little unhandy when your database does not have 50 concurrent users and requires a huge amount of resources. And if it would do so, your Access database would not be able to handle all those requests anymore anyway...

JavaMail is an API (application programmer interface) that allows you (the programmer) to send mail in your program. You can also access other mail servers and download new mail via this API. You can handle it by giving each user a mail address, but you can just as well write a mail to every user's standard mail address. The JavaMail API is everything you need to write your own mail server or mail client or mail whatever.

allow thyself to be the spark that lights the fire
haslo@haslo.ch - www.haslo.ch​

 

[wushutwist]

Why is the client storing the Access Database on their computer? The whole reason behind client/server is to store the information on the server. Sure copying everything to client will decrease load on the network but that is why we have broadband connections and LANs. The solution you are talking about with EJB would surely work but the complexity for this simple task would be 1000 times greater than it needs to be. How many users do you have? What is the transacation rates? If the answers to both those questions is low then I see no reason why you wouldn't run directly against Access on the server. If the answer is alot of users and alot transactions then I DEFINITELY see no reason to do what you are proposing. If this data is that important you should also be moving to a real database (sorry Access).
Wushutwist

 

[LittleWing]

rvy2k, if u will consider using a web based client u will not have any of these problems...if your client is a remote application, refreshing data is quite complicated and the only way forward would be to enable the thread solution.

 

[rvy2k]

Thanks to all of you for your help.

Haslo, thanks for giving me more info about EJB. I might go for the timer solution if I cant find anything else and then learn more about EJB later.
I guess Javamail was not appropriate for the solution =)

wushutwist, I am sorry I didnot made myself clear but the database will be on a server. Will that solve the updating problem? Does Access has any change-listerners or something like that?

LittleWing, I would eventually like to make it a web application later. But then I would like both to be implemented =)

Thanks again!

 

[wushutwist]

If your java Application is accessing the database (on the server) thru JDBC than you are fine. Every client will see the changes as they happen assuming they are using the java App. All bets are off if the clients regularly copy the Access Database off of the server. Things you should probably be aware off are:
1) Access supports a maximum of 30 concurrent users (according to some sources).
2) Access sometimes acts funky(official technical term) with file locking.
3) Access doesn't support transactions.
4) You shouldn't use Access.

Ok number 4 is debatable but I think the other three are pretty sound.
Wushutwist

 

[rvy2k]

I like 4)
Do you have any other suggestions for the database? (not too expensive)

One thing I dont get is how the clients are going to see the changes in the db unless I query it. Am I missing something?

The 3 solutions I have right now are:
I can update using the timer solution, which pings the db every certain amount of time; that would overload the server and wouldnot be up-to-date all the time.
Then there is the EJB solution; it seems too complicated for what I want to use it for.
Finally the web app but I really want to make it a "regular"
app first.

 

[meadandale]

While I highly recommend using a tiered architecture for a system like this (EJB's and servlets to handle business logic) and make the client thin, EJB's aren't gonna give you up to the date info on the client either.

One thing that comes to mind is to use JMS. Everytime the database is updated, the server can queue a message for each of the clients (except possibly the one that made the change) with the change. This will keep network traffic down as each link will only need to pass the message once for each update.

I'd recommend you stay away from Access for the reasons mentioned. In fact, you will have a hard time using Access for an EJB solution since most EJB containers require transaction support and Access doesn't have it. You can try MySQL but it has the same problems as Access on Windoze machines..lack of transaction support, at least with the default DB type. I know that there is another type of DB that you can plug in to MySQL that supports transactions but it is only available on *nix (last time I checked).

(update) I just found the following on ZDNet:

http://www.zdnet.com/products/stories/reviews/0,4161,2690998,00.html

MySQL may be a workable solution after all. Best part is...it's free. Take that Oracle/DB2/Sybase/Informix!

Charles
meadandale@yahoo.com

 

[wushutwist]

Ok I am going to try and resolve your confusion about record updates. Since you say you are not developing a Web Application then I assume your Application will use Swing as the Front-end. I am further assuming the client will only interact with the database through this Swing Application. You could write the Swing Application in two ways:
1) Query the database before changing a row to determine if the row has been previously changed.
2) Periodically query the database to retieve content. (timeout could occur every 5 seconds)

This is essentially the same problem with web application. If your user base is small then choice two would work. Choice 1 is not a bad solution depending on how you design the interface to the user. You could also set a timeout on data screens (kind of like we do with a session in Web Application) if they haven't performed an action in 15 minutes they probably forgot to logout.

Also, as far as free databases I like PostgreSQL it has true transaction support and foreign keys. I don't understand how a database can claim any amount of Referential Integrity without foreign keys. All things considered though I much perfer the "pay-per-view" databases like Oracle and SQL Server. Just my thoughts I could be wrong.

Wushutwist

 

[LeonTang]

Ok... I think I am getting more confused...got mixed up by the first and fifth post. You said each user has a representation in the database in the first post but said you need every user of the program to have an updated representation of the database in the fifth post.

First of all, are there any databases in the client's pc or is there only 1 database on the server application? This make a big deal of a difference because if the database is at the server, then you will have to update the user's own representation(like you said) and you don't have to update any database at the client side(whether automatically or manually) and if you have database on both server and client side, you will have to update the client's database and you might still have to update the user's representation at the server side database.

If the databases are stored on both the server and client's pc, I would suggest to make minor changes to the program.

- since you are interested in using the Thread method, this means you will need to keep the application running at all times. If so, I don't recommend using the Thread method any more.

- I would suggest to you a new Thread method, which is everytime a user makes any changes to the database at the server, it will inform the server who will inturn inform other applications(in this case, all applications will be connected to the server at all times since it will be true for the previous Thread method).

- You can simply create a Thread for the client application that is consistantly waiting for or reading input from the server and when this 'message' from the server is 'update database', your client application will automatically retrieve the new records(whether modified or created) and insert back to your client database.

- if you require to update the user's representation on the server's database too, then I find that the best solution would be to send an email to all of the users.


If the database is stored only at the server end, then things would be a lot more simple.

- you just need to inform the user to update their representation on the server's database. I would prefer to user sending of email too for this case.

Hope this help,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

 

[LittleWing]

ok, i skipped all the posts that started with "i'm confused" as i didn't want to end up there either so i apologise if this recovers material that has already been suggested.

this is how I would approach the problem. your application knows when it updates the database. all you need is a isDirty flag like some ejb containers implement. except in this case, before you display information to the client you check if thedatabase has been updated. if os, re-read your data and reset the flag

i implemented this before for certain pieces of data, such as users and list that would not change very ofter so i would read these from the database at start up and rarely, if ever again. u could use this approach

 

[rvy2k]

Thanks to you all for your answers.
I know I am not really clear but I think that I found a way to express the problem correctly:
Each time one of the client sends an update to the (only) database, the database/or something else will send a message to all the other client application to query the database to stay uptodate.

LeonTang,
There is one database on a server. Every client have the same representation of their database in a Swing application. The problem is that if one user updates the database, all the other user's interface has to get updated so that they all have the same representation.
I still dont understand how email are going to work. I need the cleint application to be notified that the database server has been changed.

meadandale,
I guess JMS is what I would be looking for. Could you give me more details on it please ?
Thanks for the database suggestions.

wushutwist,
about solution 1), one client would get an update on the database only when it will update it? If its what you mean then if it doesnot update it, it wont see any changes? Just wanted to make sure that I understood you.
Thanks for the database suggestions.

LittleWing
Thanks for the EJB answer. I will have to learn it soon! =)

 

[LittleWing]

ok, this is how i would solve the problem.

1. Swing clients connect to your db server but not the database itself. some app u will have to write will control access to the database and know when it has been updated.

2. This app will store a list of all, say, open sockets connections from the swing apps.

3. On update, your app will have to contact the swing clients by sending a updateDataNow message.

4. your clients get the new data.

i would spend some time seeing if JMS will make this solution easier...i imagine it will.

--------------------

"I'm not normally a religious man, but if you're up there, save me, Superman!" Homer Simpson