Database interactions

[misterX]

hi, it's me again with the server problem!

The problem was that i needed to launch a server in order to redirect people to each other in a multiplayer game using RMI.

After some direction changes, to avoid the server problem, i stopped on the idea to not use a server app but directly a database that is directly read/written by the client's app.
When a person A decides to host a game, a new record including the host IP is added on a database on the web. A person B can "browse" the database to look for "open games". When B decides to join, he recieves the IP and lookup to the host using RMI (i hope it works since i never tested it!).

I found this incredibly simple...
Despite of this, the big problem is again my ignorance on this area!
I've hard to catch the basics of JDBC. I searched long for good doc and examples and tried to connect to a DB with java for hours, but i hadn't any results. This is why i need help.

A question and a request are rising:
- Would the DB solution discribed above work without problems?
- Is someone ready to teach me how to get a damn connection to a DB with java? (I don't even know what sort of DB i must create! And i've problems with obcd as well...)

damn jdbc!

[misterX]

Well, about the database connection:
A simple java example oppening a database lying on the web would be perfect, i don't need more! (preferably with a common DB format)
This would fit perfectly!

[zparticle]

This sounds like a REALLY bad idea to me. You are going to directly expose your database to the internet?

Seems like a better solution is to send commands to an intermediate server and let it talk to the database. At least that way you have a chance to stop any bogus commands.

Games published by our own members! Check 'em out!

[misterX]

A database can be password protected without problems. And the password could be in a mini compiled class that the client app use.
This ensures a minimum security.
Also, the database won't store any personal data, so it's security isn't that important.

Nevertheless, i thought a little on it, and if needed a servlet could be created to dinamically build custom UserName/Password for each client. Like this, there keep unknown since they exist only while the client is connected.

Despite of this, i don't need it yet since it's for testing purposes, and anyway, i don't know if it'll be in the future since they aren't going to store personal data.

Hoping it sets your mind at rest.

[leknor]

Exposing your database to the public is generally considered a bad idea. It's rather difficult to make sure you have everything locked down correctly, worst case someone with write access can insert bogus entries until your server runs out of disk space.

You mentions that you know how to write servlets, I suggest you write two servlets. The first one generates a list of active/open games in XML or maybe the Java property format. The second takes simple paramters and inserts games into the DB. The second should take step to do things like only a few games per IP address and a expire time for each insert.

As for how to use JDBC, I'd start with http://developer.java.sun.com/developer/onlineTraining/Database/JDBC20Intro/ and then use Google and http://jguru.com/ for more info as needed.

[misterX]

Exposing your database to the public is generally considered a bad idea. It's rather difficult to make sure you have everything locked down correctly, worst case someone with write access can insert bogus entries until your server runs out of disk space.

Exposed is a big word. It's in fact not so "damn easy" to get the dabase open because you don't even know where the database is, nor it's username and password.
I found it's enough security.
(By the way, about bogus entries, passing throught a servlet won't affect this problem.)

You mentions that you know how to write servlets, I suggest you write two servlets. The first one generates a list of active/open games in XML or maybe the Java property format. The second takes simple paramters and inserts games into the DB. The second should take step to do things like only a few games per IP address and a expire time for each insert.

It's an alternative, i know. But working directly with the DB seemed more comfortable to me than passing first through a servlet.

As for how to use JDBC, I'd start with http://developer.java.sun.com/developer/onlineTraining/Database/JDBC20Intro/ and then use Google and http://jguru.com/ for more info as needed.

Already looked at them but their examples/demos needs some manipulations to work... Also, the corresponding DB must be done with OBCD and i get errors or nothing when i try to. I don't even understand what OBCD exactly is!
The result is that i can't get these examples running!  :-/



Another problem is that i planned to make it open source. But once it's done, security and cheating would be a big open door...

[the2bears]

Hi,

You keep finding reasons for your design, or should I say justifications.  Foremost is you feel more comfortable directly accessing the database.

However, it is very poor design.  It does not scale at all - how do you add a second database?  You might not need it to scale though... but what about other changes.

The primary reason for adding tiers to a design is abstraction.  This makes it infinitely easier to change the database (as per your example), add a database, scale, etc as you control the layer where this is done.  Good luck making changes to all the clients afterwards if you don't abstract.

Security through obscurity is none at all.  The script kiddies will scan and get through in no time.

I'll be a little bold... listen to the advice people here are giving you.  You are having trouble with JDBC, which suggests inexperience with Java as it's been around awhile and isn't that hard.  Mistakes early on can cost you alot later.  

Regards,

Bill

[misterX]

I'm far to be an expert and i've never claimed to be one.
I'm quite comfortable with java but not with many of its technologies, including JDBC (and servlets also, and many others)... But that's why we are here, isn't it?
I'm sorry you felt like i was arguing with previous posts. That's not what i wanted. And i thank all you forum people to advice me (and others!).

I know my post looked like justifications, hmm, no, they are in a certain way. But, instead of arguing, let me explain more closely why i choosed to access a database directly. It's not only a matter of easyness.

First: the role of the server or database.
The only meaning of it's existance is to redirect people to game hosts. More precisely, it stores a list of host IPs and gives them to whoever wants to join.
That's why the simple idea of a database came into my mind. It's just an IP pool, nothing else.
Ok, well, i could use a servlet to give back the IP list, and when you join it then flows through the servlet again to inform the database. But i don't see why it's more interesting.

As i was "designing" the "server", i first thought of a servlet that stored all informations Applet<->Servlet. (And it was pretty complicated in fact). Then, i thought that the best way to store the data was a DB. Applet<-Servlet->DB. And a small time after, i wondered why not shortcutting the servlet and accessing directly the DB. Applet<->DB. This solution seemed to simplifies extremely everything and to be... well... a more direct approach. After all, the only thing the servlet had to to was to act as a pipeline, so why not accessing the source directly?


Then arises security problems. This topic can be discussed during several pages, surely without conclusion. Nevertheless, 3 reasons brings me to think it's ok:
- i'm not a guru nor a security champion but that the DB is accessed through an applet and not from a servlet shoudn't change much. You're saying the DB isn't safe, going through a servlet won't change this!
- what additional protection can you perform on a DB than username/password? Or how can you protect them better in the compiled code?
- i remind you of the irrelevant informations contained in the DB and the fact that i'm an amateur. I'm not going to protect a DB for the NASA after all! (hopefully for them!;-))


Now, you are pointing to a scalability problem...
Before we start this topic, i'll explain the program architeture.
The app works on a peer-to-peer basis (where the server or DB should only act as an IP pool to redirect people as discribed before)
Since i'm working with RMI, every class involving distant communication needs to have an interface. So different implementations (or versions) can work nicely together since the interface they are refering to remains the same.
Like thise, I don't see scalability on the client side as a big problem.
On the server side, the BD stores host IPs, game sort and maybe few other infos. When a person wants to act as host, the applet adds a record in the databases. When it wants to join a game, he recieve the list of games(hosts) available and connect to one of those. When the game ended, the record is erased.
Also here, the DB can be extended without many problems. Fields can be added without affecting client's requests. The only problem would happen if it explodes of records, but i don't think it'll ever be so full of people!
And when not using a servlet, i save the time of not needing to change it each time a change is done to the DB.

Before someones points out the detail that disconnected games could stay in the DB, let me say that a servlet will parse the DB looking for disconnected hosts and delete these obsolete records.


I hope this brings light in the discussion. (And don't forget it's not targeted for a nuclear facility!)



After all this trouble, what i'm searching for remains the same: a simple java example opening a database lying on the web. If anyone is able to do this, it would be very appreciated.


Have a nice day!
Thanks for all

[cfmdobbie]

Personally, I'd ignore scalability problems and the like - if it's a hobbyist game then I don't expect server performance to be an issue.  Fine, it may be hard to change afterwards, but it's dead easy to learn from mistakes and do it right the second time around.

After all, the only thing the servlet had to to was to act as a pipeline, so why not accessing the source directly?

Well, it's not quite just a pipeline.  The servlet can detect IP addresses and add them to the DB, and expire settings on timeout etc.  Without the servlet, is the client applet going to work out its IP and add that to the list?  How are you going to handle private IP ranges etc?  An applet may think it's running on a machine called 10.1.4.56, but the rest of the world may disagree. Also, if the servlet detects a large number of requests from one particular machine, it can just ignore them.  No problem.

However, I'm not just labouring the point - there's another good reason for using a servlet to talk to the database:

what i'm searching for remains the same: a simple java example opening a database lying on the web. If anyone is able to do this, it would be very appreciated.

I don't think you'll be able to do this.  Another advantage of the servlet is that the client talks HTTP to it.  If you want the client to directly access a database, you'll need to be sure that intermediate firewalls will allow JDBC traffic through.  I wouldn't bet on this at all!  You'll need something on the server to startup and shutdown the DB anyway, may as well make it the servlet.  Databases are too "raw" for public access anyway - they are almost always (99.99%) accessed via an interface of some kind.


I'm afraid the advice here is good advice, and actually cuts down the amount of work you need to do:

 A single servlet on the webserver using (for example) HSQLDB as a JDBC data source.
 Create an in-memory one-table DB on servlet init.
 Don't worry about usernames/passwords, detect IP addresses automatically for addition to the DB.
 Log the IP and the time the data was added.
 Have the client send a "refresh" message every minute of run-time.
 On a "refresh" message, reset the time against the IP to the current time.
 Every two minutes or so delete all rows from the table where the listed time is over five minutes in the past.

How does that sound?  This way you don't need to worry about ODBC or registering the data source with anyone - it's held entirely internal to the servlet.  Noone else even knows it's there.

(PS: In fact even this is overkill - for storing a list of IPs you may as well stick them in a Java collection or something!  Is there anything else you want the DB there for?)

[leknor]

misterX: I guess experience will have to be your teacher. You can find example code to connect to an ODBC DB in java on this page:
http://www.cs.unc.edu/Courses/wwwp-s98/members/thornett/jdbc/

There are three kinds of people in the world:
The dumb ones who don't learn from their mistakes,
The smart ones who do learn from their mistakes,
and the wise ones, who learn from other people's mistakes.

Right now you've elmiated the third posibility.

By the way: that link is the 5th one in a Google search for "jdbc tutorial". In the future you can save both yours and our time by going to Google before you ask on JGO, espically if you're not intersted in what people have to say.

Games published by our own members! Check 'em out!

[misterX]

Well, if you're all really conviced that Applet<->Servlet is the best solution then i'll try it. No more DB at all, just a servlet storing dynamically the IPs.

An applet may think it's running on a machine called 10.1.4.56, but the rest of the world may disagree.

huh?

[cfmdobbie]

> Well, if you're all really conviced that
> Applet<->Servlet is the best solution then i'll try
> it. No more DB at all, just a servlet storing
> dynamically the IPs.

It'll save you a lot of hassle.  It's the best choice, trust us!


> > An applet may think it's running on a machine
> > called 10.1.4.56, but the rest of the world may
> > disagree.
>
> huh?

IP defines a number of reserved address ranges, and the 10.0.0.0/8 range is for private networks.  Addresses in this range are never exposed to the outside world as they are not globally unique.  A machine may honestly believe that it's own IP address is in that range, but before it gets to your server it will be masqueraded to something else, hence this IP will be incorrect.

See http://ftp://ftp.rfc-editor.org/in-notes/rfc3330.txt for details.

[misterX]

thanks for all