Using Java with MySQL on x10

fguy64

New Member
Messages
218
Reaction score
0
Points
0
greetings. I don't need a how to, I was just hoping someone could save me some time by pointing me in the right direction.

Given that Java, and thus JDBC, is a client side application, and mySQL is server side application, what is the mechanism by which my Java applet can get information from an online database? Presumably there is some additional server side scripting that is required? Maybe I have to use php/html as an intermediary between the java applet and the mySQL database?

thanks.
 

fguy64

New Member
Messages
218
Reaction score
0
Points
0
Thanks Garrett, this looks promising. It'll take some time to decipher the documentation, I'll post back here when I figure some stuff out. Don't hold your breath. :)

Only other question I have is, as far as I can tell, server side java is not part of x10's free hosting service?
 

garrettroyce

Community Support
Community Support
Messages
5,604
Reaction score
245
Points
63
I've read up on it, and no, server side java is not possible on X10, unless you use the paid VPS service.
 

Twinkie

Banned
Messages
1,389
Reaction score
12
Points
0
I am sure you can use PHP as an intermediary like AJAX and PHP do for JavaScript to access a database on free hosting, nothing special required. However, the proper way, like what garrettroyce said, requires special configurations unavailable on free hosting.
 
Last edited:

garrettroyce

Community Support
Community Support
Messages
5,604
Reaction score
245
Points
63
I am sure you can use PHP as an intermediary like AJAX and PHP do for JavaScript to access a database on free hosting, nothing special required. However, the proper way, like what garrettroyce said, requires special configurations unavailable on free hosting.

I thought the MySQL driver for java was a standalone class. I browsed the documentation and that's what I gathered from it. I haven't delved into java much, so it's very likely I'm wrong :p
 

fguy64

New Member
Messages
218
Reaction score
0
Points
0
OK, where I'm going is this. I want my java chess applet to be be able to load and save some kind of chess games.

lets first of all consider what might be happening with the following page, which uses a java applet to present a chess game. It's a very popular site for online viewing of games, and they have an extensive database.

http://www.chessgames.com/perl/chessgame?gid=1282672

here is a key block of code from this page.

Code:
<APPLET 
	codebase="/java/cvd/"
	archive="Viewer-Deluxe.jar"
	code="ChessBoard.class" 
	align="baseline" width="631" height="560"> 
	<PARAM name=LightSquares value="FFCC99"> 
	<PARAM name=DarkSquares  value="CC9966"> 
	<PARAM name=Background   value="EEEEEE"> 
	<PARAM name=ImagesFolder value="images"> 
	<PARAM name=PuzzleMode value="off"> 
	<PARAM name=MayScript value="on"> 
	<PARAM name=PgnGameFile value="/perl/nph-chesspgn.pgn?gid=1282672&inode=5252598"> 
</APPLET>

it would appear that the key line which refers to database is...

<PARAM name=PgnGameFile value="/perl/nph-chesspgn.pgn?gid=1282672&inode=5252598">

pgn is a standard format for storing chess games. a single pgn file can hold just one, or many games. The pgn std is a flat text file with various kinds of delimiters.

given the large number of games these guys have stored, it can't possibly all be a flat text file.

so what conclusions can we draw from the above code block?
 
Last edited:

garrettroyce

Community Support
Community Support
Messages
5,604
Reaction score
245
Points
63
I'm guessing that the .pgn file is a perl script that feeds the data to java.

You probably could use PHP for this as well. Again, I'm no java expert but what I think is happening is java is accessing that file with those parameters and it returns the information in text format that is parsed by java. The data storage is irrelevant, though you're right, a flat text file is extremely slow compared to a SQL server.
 

fguy64

New Member
Messages
218
Reaction score
0
Points
0
Re: Using Java with MySQL on x10 also Perl

I'm guessing that the .pgn file is a perl script that feeds the data to java.

You probably could use PHP for this as well. Again, I'm no java expert but what I think is happening is java is accessing that file with those parameters and it returns the information in text format that is parsed by java. The data storage is irrelevant, though you're right, a flat text file is extremely slow compared to a SQL server.

I like to rephrase stuff, it helps me to understand...

OK, we're getting somewhere. In this case then, the game was a specific predetermined one for which the site designer had advance knowledge of database keys. SO the designer codes these keys into the html PARAM tage which in turn passes the info to the java applet. So it seems here that Perl acts as an intermediary between HTML and java, and that all the game information is passed to the applet on startup. So java does not concern itself with database access at all, either directly or indirectly.

There are other cases where the user enter search parameters into an html form. presumably if Perl, or maybe php, would get there parameters and do a search and return the info to java via some code between Applet tags.
 

garrettroyce

Community Support
Community Support
Messages
5,604
Reaction score
245
Points
63
Yeah, that wouldn't be an issue at all. You'd have to dynamically generate the param with server side scripting, unless it's possible to use javascript to change the parameters and then restart java, but I have no idea if you can.

You can probably also save if you can use java to set $_GET or $_POST variables and submit the page to a similar perl or php script that will save into the database.
 

fguy64

New Member
Messages
218
Reaction score
0
Points
0
Yeah, that wouldn't be an issue at all. You'd have to dynamically generate the param with server side scripting, unless it's possible to use javascript to change the parameters and then restart java, but I have no idea if you can.

You can probably also save if you can use java to set $_GET or $_POST variables and submit the page to a similar perl or php script that will save into the database.


that I'm not so clear on. I can see how server side scripting can be used to pass information to the java applet running on the client, but it is not at all clear to me that the client could pass info back to the server via the same scripting language. If it did work, it would be as follows...

In saving, a user would be manipulating pieces in a java applet to create the game, the move notation would be generated on the fly within java, then hopefully java can pass the information back to Perl or php which would look after writing the info to the online database.

see what I mean about saving being a little trickier?

edit: maybe it isn't so tricky. ideas are forming, I just need to take time to think it through.
 
Last edited:

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
Though ChessGames' web service at "/perl/nph-chesspgn.pgn" uses CGI as a client-to-server messaging protocol and PGN as a data interchange format, you don't have to. If your client and web service already have PGN generators & parsers, there isn't as much reason to switch to another interchange format (such as XML or JSON), but a heavier messaging protocol such as SOAP or XML-RPC can make coding web services & clients easier because you don't have to parse messages passed between client and server. Even if you want to stick with CGI for messaging, parsing JSON (using JSON on Java client side) will be easier than writing your own PGN parser.

PHP has support for SOAP and XML-RPC, but it's not compiled into the PHP version on the free hosts. Ruby on Rails is great for rapid web service development; if only it were available on the free host. The same goes for Django. Perl and Python have SOAP and XML-RPC packages you could add fairly easily. Actually, Python has the insanely easy to use (if not type) CGIXMLRPCRequestHandler:
games.py (service):
Code:
#!/usr/bin/env python
from SimpleXMLRPCServer import CGIXMLRPCRequestHandler
from localDBConn import user, passwd   # localDBConn.py is mode 0600 & hides user credentials from prying eyes
import chess            # chess package

handler=CGIXMLRPCRequestHandler()
handler.register_instance(chess.GameService(user, passwd))
handler.handle_request()
client:
Code:
#!/usr/bin/env python
import xmlrpclib
games=xmlrpclib.ServerProxy('http://chess.example.com/games')
game = games.get(1282672) # 1282672 is game id
In just a few lines, an HTTP POST turns into a function or method call. Damn, Python is sweet.

The chess.GameService referenced in games.py performs the DB query and is a little more involved. The only problem with it is I'm not certain if there's a way of installing MySQLdb on the free hosts, though you could try uploading a local build.

chess/GameService.py:
Code:
import MySQLdb
from chess import Game
import sql    # sql package needs to be defined

MySQLdb.paramstyle='numeric'            # use (eg) ":1" for query parameters

class GameService:
    def __init__(self, user, passwd, host="localhost", db="chess"):
        self.conn = MySQLdb.connect (host = host,
                                user = user,
                                passwd = passwd,
                                db = db)
        self.cursor = conn.cursor()
        self.tables {'moves': 'moves', 'tags': 'san'}

    def __del__(self):
        self.cursor.close()  #Cleanliness is next to godliness
        self.conn.close()

    def getTags(self, gid):
        self.cursor.execute("SELECT * FROM %s WHERE id=:1" % self.tables['tags'], (gid,))
        # Pair up column names and field values
        return zip([col[0] for col in self.cursor.description], self.cursor.fetchone())

    def getMoves(self, gid):
        self.cursor.execute("SELECT move FROM %s WHERE id=:1 ORDER BY number" %self.tables['moves'], (gid,))
        return [move[0] for move in self.cursor.fetchall()]

    def get(gid):
        tags=getTags(cursor, gid)
        moves=getMoves(cursor, gid)
        return Game(tags, moves)

    def addGame(tags, moves):
        ids = lookupID(tags)
        if not gameExists(tags):
            self.cursor.execute(sql.insert(self.tables['tags'], tags.keys()), tags.values())
            ids = lookupID(tags)
            if len(ids) == 1:
                id=ids[0]
                moves = [(id, n, m) for n,m in zip(range(1, len(moves)+2),moves)]
                self.cursor.execute(sql.insertMany(self.tables['tags'], ['id', 'number', 'move'], len(moves)), 
                         moves)
                return id
            else: # there are no or more than one rows with tags 'tags'; how did that happen?
                # should probably return an error
                pass
        return ids

    # Given game tags, find all rows with those tags
    def lookupID(tags)
        #...

    def gameExists(tags):
        ids=lookupID(tags)
        return len(ids)
The MySQL access code hasn't been tested, but it shows you the gist. As you see from addGame, to add new functionality to your web service, add new methods to the GameService class. addGame is messy because the GameService isn't sufficiently abstract. If you want to go all out, take an entity model approach, with the Moves entity having a many-to-one relationship to Tags.

In the above, the "moves" table will have many, many short rows. With the primary index on moves.(gid, number), performance should be decent, but you could also denormalize the moves into a single field and combine "tags" and "moves" into a "games" table. This would reduce the load on the DB by making the client's task more complex.

sql.insert and sql.insertMany take Python collections and create parameterized SQL statements to insert them. They aren't predefined classes; you'd need to write them.

To fetch a web resource with client side Java, you create a URL object, open a connection with URL.openConnection and read from the connections input stream. Corey Gold demonstrates the basics when comparing Java to Python. If you want to go the XML-RPC route, grab Apache XML-RPC.

GameClient.java:
Code:
package chess;
import org.apache.xmlrpc.client.XmlRpcClient;
import org.apache.xmlrpc.client.XmlRpcClientConfigImpl;
import org.apache.xmlrpc.XmlRpcException;
import java.io.IOException;
import java.net.MalformedURLException;
import java.net.URL;
import java.util.Arrays;
import java.util.Map;
import java.util.List;
import java.util.Vector;
import chess.Game;

public class GameClient {
    protected final String host;
    protected final String games_path;

    public GameClient(host, path) {
        this.host = host;
        games_path = path;
    }

    public GameClient() {
        this("http://chess.example.com", "/games");
    }
    public Game get(Integer gid) {
	try {
            XmlRpcClient rpcClient;
            XmlRpcClientConfigImpl config;
            
            config = new XmlRpcClientConfigImpl();
            config.setServerURL(new URL(host + games_path));
            rpcClient = new XmlRpcClient();
            rpcClient.setConfig(config);  

	    Vector getParams = new Vector(1);
	    getParams.add(gid);

	    Map<String, Object> gameData = (Map<String, Object>) rpcClient.execute("get", getParams);
            Map<String, String> tags = (Map<String, String>)gameData.get("tags");
            Object[] moves = (Object[]) gameData.get("moves");
	    return new Game(tags, Array.asList(moves));
	} catch (XmlRpcException e) {
            // ...
        } catch (MalformedURLException e) {
           // ...
        } catch (IOException e) {
           // ...
        }
    }
}

For comparison, here's the Python equivalent to the above:
chess/GameClient.py:
Code:
import xmlrpclib

class GameClient:
    def __init__(self, host='http://chess.example.com', path='/games'):
       	self.host=host
        self.path=path
        self.games=xmlrpclib.ServerProxy(host+path)
    def get(id)
        gameData=self.games.get(id)
        return Game(gameData[tags], gameData['moves'])
client:
Code:
#!/usr/bin/env python
import chess
game=chess.GameClient().get(1282672)
 

fguy64

New Member
Messages
218
Reaction score
0
Points
0
Thanks Mission, much appreciated. I will take some time to digest what you have posted.

At the moment my program does not have a pgn parser. But that should not be a problem to write.

What I ended up deciding in order to retrieve stored games was the following...

have a pgn database set up in mySQL
Have the users enter search parameters into an HTML form.
A php script queries a the mySQL database and returns a game or set of games.
the php script then generates some HTML code that passes the pgn game(s) back to the java applet via the html APPLET and PARAM tags.

this might not be the best or most flexible, but it's a way to get started. Really my project is just a learning project, so I expect I will find improvments to the above design.

One thing I don't have is an easy way to save pgn that my applet has created back to the mySQL database. The issue is not mySQL it's in passing the information back to php, probably via some sort oh http request. There are a few java classes I am looking in to. Also, it has been suggested that I look into something called Apache's HttpClient lib
 

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
One thing I don't have is an easy way to save pgn that my applet has created back to the mySQL database. The issue is not mySQL it's in passing the information back to php, probably via some sort oh http request. There are a few java classes I am looking in to. Also, it has been suggested that I look into something called Apache's HttpClient lib

Sending data to the server is one nice thing about XML-RPC and the like. In Java, it takes just a few lines to set up a parameters object, then you call "rpcClient.execute(methodName, params);".

To use just HTTP, check out "Reading from and Writing to a URLConnection". You can get an OutputStream for a URLConnection. Anything you write to it is sent to the server. Note that you'll want to use the HTTP POST method rather than GET. To do this you'll need an HttpURLConnection. I believe URL.openConnection() will return one if you're using an HTTP URL; just cast the result. If not, you can construct one explicitly for a given URL rather than calling URL.openConnection(). Call HttpURLConnection.setRequestMethod("POST") .
 

fguy64

New Member
Messages
218
Reaction score
0
Points
0
Sending data to the server is one nice thing about XML-RPC and the like. In Java, it takes just a few lines to set up a parameters object, then you call "rpcClient.execute(methodName, params);".

To use just HTTP, check out "Reading from and Writing to a URLConnection". You can get an OutputStream for a URLConnection. Anything you write to it is sent to the server. Note that you'll want to use the HTTP POST method rather than GET. To do this you'll need an HttpURLConnection. I believe URL.openConnection() will return one if you're using an HTTP URL; just cast the result. If not, you can construct one explicitly for a given URL rather than calling URL.openConnection(). Call HttpURLConnection.setRequestMethod("POST") .

excellent. This looks to be just the sort of info I need. Right now I am working on my PGN parser, then I have some html/php work to do to get games from the database into java. After that I will take a closer look at the info you have provided here.

thanks again.
 

misson

Community Paragon
Community Support
Messages
2,572
Reaction score
72
Points
48
the php script then generates some HTML code that passes the pgn game(s) back to the java applet via the html APPLET and PARAM tags.
Quick question: are you embedding the PGN data in the HTML page, or just the game ID and having the applet fetch the PGN data on its own?
 

fguy64

New Member
Messages
218
Reaction score
0
Points
0
Quick question: are you embedding the PGN data in the HTML page, or just the game ID and having the applet fetch the PGN data on its own?

For now I will be embedding the pgn data in the HTML. Where else would the java applet get the data from? Until such time that I can master some kind of communication channel from the java client back to the mySQL server, I don't really see any other option. Unless I'm missing something simple?
 

garrettroyce

Community Support
Community Support
Messages
5,604
Reaction score
245
Points
63
I think in the way you're using it, there is no advantage over embedding the data or a link to a static data file (even if the file is able to produce data dynamically, you can't change the query sting dynamically to fetch different data). Either way you have the same data. I would think it is easier to fetch the data from a param then from a url. Mission's the pro here, he might disagree, but that's my two cents ;)
 

fguy64

New Member
Messages
218
Reaction score
0
Points
0
I think in the way you're using it, there is no advantage over embedding the data or a link to a static data file (even if the file is able to produce data dynamically, you can't change the query sting dynamically to fetch different data). Either way you have the same data. I would think it is easier to fetch the data from a param then from a url. Mission's the pro here, he might disagree, but that's my two cents ;)

I kind of see things your way too. The only reason I am even thinking about http calls from java is a means to go from java back to php and from there to mySQL. The purpose being to save data that the applet has created. see what I mean? SO far I can only load data, not save it.
 
Top