Saturday, July 7, 2012

Link SFS to a MySQL database using JDBC

Most admins in the SmartFox forums seem to think that connecting to a database is not that complicated, but I came across several hours worth of problems while trying to make it work. Most of these are from coding subtleties that only a person truly versed in JDBC knows. It's my goal here to help you connect to your database with ease. I know that everyone has their own formula for database management (usually the first thing you learn is what you want to use), and it shouldn't be too difficult to follow my example in mind of exchangeable parts. Here is a quick bio of why I'm using JDBC (skip to next paragraph if you don't care). I used it once in my graduate Database Management class at USC. In that class, my teacher taught us the theory, but we had to learn all of the implementation on our own to finish his rigorous assignments. I developed a formula to test all of the MySQL, PHP, HTML, XML Schema, CSS etc! No joke, I had to pull an all nighter every time one of our a coding assignment was due. So, I became extremely good friends with the amazing w3 school tutorials, occasionally reading an entire web language tutorial multiple times. http://www.w3schools.com/ should be your first reference for any web development tutorials. Even with my prior exposure to database management (I'm in no way a guru), I still had trouble making things work with SmartFoxServer.

I am currently managing the server on my own computer using WAMP. I believe Mac users can use MAMP.  This program allows you to manage a local database on your computer and even expose it to the outside world if you so desire. It comes built in with MySQL which is the component that you need for this tutorial. Instead of using PHP or ASP like you would do when developing a web site, we're going to connect to the database with JDBC: get the connector at http://www.mysql.com/products/connector/ and place the .jar in your {SFS2X directory}/Extension/__lib__.  I have created a MySQL database on my local computer using the phpMyAdmin tool and Oracle's MySQL Workbench. You can create a .sql file pretty easily with the Workbench. Design your database visually and use the forward engineer function to make a .sql creation script. Then, create that database on your WAMP server by typing localhost in your browser and clicking phpMyAdmin. You might need to edit your hosts file so that localhost will redirect to 127.0.0.1, and if you already have other programs in control of host, try associating it with a name like localhostwamp. This will show you all the databases you have on your server. Create a new database with the file that you built with the workbench.

Ok, so now you have installed a server that is running MySQL, and you have created a database that at least holds one user entry with a user name and password. You'll want to combine the official instructions http://docs2x.smartfoxserver.com/GettingStarted/howtos#item2
with this tutorial from the forums http://www.smartfoxserver.com/forums/viewtopic.php?f=4&t=11793&p=48400&hilit=jdbc+databasemanager#p48400  Well there are a couple of things that they don't explain. Firstly, the Database driver class doesn't refer to the name of the .jar connector, it refers to the name of the class located inside of the file. So for JDBC it's com.mysql.jdbc.Driver  If you're using WAMP then your MySql server should be running on port 3306. You can check this by clicking WAMP -> MySQL -> my.ini which will show you your password and port. The default user name is root. So for connection string it is jdbc:mysql://127.0.0.1:3306/yourDbName
Change the TestSQL value to "select count(*) from yourUserTable". When you boot up your server, I suggest doing it manually by clicking the SFS2X.bat file in your SFS2X directory.  This will show you a cmd window and alert you of any problems it is having connecting to the database. If it doesn't show any errors, then that means that your TestSQL script worked! Be happy! If not, then perhaps your sql statement isn't following standard syntax or has a typing error. Try getting some help from the post in the forums.

Now that your server is configured to connect with the database, you can start coding your server side extension. Previously, I've showed you how to create a room extension, but only a zone extension can be used to register login events. You'll need to open Net Beans and create a new project. Refer to my other posts about how to create an extension. In the main class of this extension,  you'll want to add this line of code addEventHandler(SFSEventType.USER_LOGIN, LoginEventHandler.class); 
inside of your init function. Make sure to create a Login Event Handler class for that function to call. There is a great snippet of code where someone has made a handler that checks the user name and password against the values in the database. http://www.smartfoxserver.com/forums/viewtopic.php?t=13292 Go with the second portion of code that says
by Zageron» 30 Jan 2012, 16:40  You can reference most of the functions in this documentation by SmartFoxServer and searching for the IDBManager http://docs2x.smartfoxserver.com/api-docs/javadoc/server/

It is a pretty complex thing, but this will work really well and follows pretty good Java coding practices. There are a couple things that might seem strange. One is that he calls the password a hash. This is true. SmartFoxServer will always send a password from the client as a hash. So that is why you have to use the API function to check the password from the database against the one the client sent. The other has to to do with stored procedures. You can't just grab a value from result. You must specify Result.first() or Result.next() to get increment the result iterator for the user's table entry for which you were looking. Prepared statements are good because you can iterate through several rows that are returned each time with Result.next(). Still having problems? Check this article on prepared statements to see why he is including a ? in the code. http://docs.oracle.com/javase/1.4.2/docs/api/java/sql/PreparedStatement.html  
 and then look here http://stackoverflow.com/questions/2120255/java-resultset-exception-before-start-of-result-set

*make sure to enable use custom login for the zone

Here is a little update so that you will know how to proceed when you want to connect to the database from the Room instead of the Zone extension. I do this to load a specific character according to what my user has stored in the database. Since you're following the example of the first person shooter, you should have a world class and a spawn me handler. This is quite a nice gift, so enjoy and make your program connect to your database like a champion!

//This is in your SpawnMeHandler.java class
public void handleClientRequest(User user, ISFSObject data)
{
 World world = RoomHelper.getWorld(this);
 String modelName = null;
 String request = "SELECT * FROM users WHERE username = ?";
 try
 {
  modelName = venue.sqlRequest(request, "characterModel", user.getName());
 }
 catch(Exception ex){}
 
 boolean newPlayer = venue.addPlayer(user, modelName);
 if(newPlayer)
 {
  sendOtherPlayersInfo(user);
 }
}

//Connect to a database in your Room Extension
public String sqlRequest(String sqlRequest, String field, String userName) throws SFSException
{
 IDBManager dbManager = extension.getDbManager();
 try
 {
  Connection connection = dbManager.getConnection();
  PreparedStatement stmt = connection.prepareStatement(sqlRequest);
  stmt.setString(1, userName);
  ResultSet result = stmt.executeQuery();
  if(result.first())
  {
   sqlRequest = result.getString(field);
  }
  
  stmt.close();
  connection.close();
 }
 catch(Exception ex)
 {
  SFSErrorData errData = new SFSErrorData(SFSErrorCode.GENERIC_ERROR);
  errData.addParameter("SQL Error: " + ex.getMessage());
  throw new SFSLoginException("A SQL Error occurred: " + ex.getMessage(), errData);
 }
 
 return sqlRequest;
}

Thank you for reading, and feel free to comment below.

12 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Hello Andy, a bravo from me for these very helpful articles, i wanted to ask this:

    I have wamp server and sfs. I linked wamp to 127.0.0.2 and sfs to localhost. So i putted the jdbc connector at SFS2X\lib is this correct? Maybe i should put it to SFS2X\extensions\__lib__ or jre\lib too? At the admin tool db manager i putted:

    org.gjt.mm.mysql.Driver

    and

    jdbc:mysql://127.0.0.2/mydb

    and as mysql test, the one you suggest. Im asking all this because i want to have an extension that the user sends a request with data to the extension to be inserted into the db. I did a small test and the connection between client and extension works fine. But the problem is with the db. I edited the my.ini file of wamp mysql and entered my own password but it still uses the default that is actually no password, just enter, and root as a username. So i actually don't know where is the problem, query, connection or something else? I hope you can help :)

    ReplyDelete
    Replies
    1. I did some tests and it seems to take 127.0.0.1 and .2 as the same. So why don't the two programs get confused?

      Delete
    2. Still testing, it seems like this doesnt work:

      IDBManager dbManager = getParentExtension().getParentZone().getDBManager();

      Delete
  3. Make sure you've enabled the custom login using your sfs admin tool. It's a simple checkbox, but it allows a custom login. Where have you connected the database manager is another question. The way I was getting it was from room extension. I won't be able to look at it until the end of the day. I ended up having so many problems with Wamp (which only surfaced after a few months) that I started hosting mysql on my web host instead of on my local computer. Try going back through these blog posts I've made and check for any of the subtleties that you may have overlooked.

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. This comment has been removed by the author.

      Delete
    3. Thank you, but i dont want to login from in-game, the player will login from the game site via php. The extensions will manage other things through the db. I progressed a bit and now the problems are two:

      It seems to connect to the db but as i said the IDBManager dbManager = getParentExtension().getParentZone().getDBManager();
      doesnt work.

      I wanted to stest if the dbManager var stays null and i had to send data back to the client but i cant do that either:

      Expansion script:

      package mypackage;
      import com.smartfoxserver.v2.entities.User;
      import com.smartfoxserver.v2.entities.data.ISFSObject;
      import com.smartfoxserver.v2.entities.data.SFSObject;
      import com.smartfoxserver.v2.extensions.BaseClientRequestHandler;

      public class MyClass extends BaseClientRequestHandler {

      @Override
      public void handleClientRequest(User sender, ISFSObject params){

      IDBManager dbManager = getParentExtension().getParentZone().getDBManager();


      ISFSObject resObj = SFSObject.newInstance();

      resObj.putBool("Error", true);

      send("MyCMD", resObj, sender);


      }

      }


      Client Script:

      void OnExtensionResponse(BaseEvent evt){

      ISFSObject parameters = evt.Params as ISFSObject;

      if(parameters.GetBool("Error") == true){
      Application.LoadLevel("Level");
      }

      }

      Delete
    4. I wish that I understood the problem more. I do understand that you are wanting the players to be able to send commands that access the database after having logged in through some kind of website with php. My primary experience with connecting to the database has been for logging in to the zone extension. Have you added in the database manager to the zones or to the rooms?

      Delete
  4. Hello again and sorry for the many posts. I finally solved the problem and i'm posting all the info for the others:

    First of all put the jdbc in SFS2X\lib.

    After that go to db manager, enable it and put these info:

    Database Driver Class: org.gjt.mm.mysql.Driver

    Connection String: jdbc:mysql://127.0.0.2:3306/yourDB

    (Here i used .2 cauz wamp and therefore db is installed on it cauz .1 is taken by sfs.)

    Test: select count(*) from player

    After that we must create the extension. This is the way it works (zone extension, i dunno about room):

    The extension has 1 Main Class and some Sub-Classes. Each sub-class is like a separate "function". Each one does a different job. The main class' work is to understand what the client needs to be done and pass it to the correct sub-class. In order to happen that we must explain the code a-bit:

    Extension:

    Main Class:
    http://pastebin.com/CkyQtjZg

    Example Job Class:
    http://pastebin.com/jZ6bRjYL

    Unity Script (dont forget the event listener):
    http://pastebin.com/0x5JNT3m

    So the logic is that the user sends a request with a name and some data, the expansion finds the sub-class with that name, give's it the data, the sub-class does the magic and sends the data back to the requester with the same name. The requester has many ifs so according to the name, uses the data.

    The request code is: smartFox.Send(new ExtensionRequest("JobName", Data));

    where data is a ISFSObject that is actually a dictionary.

    In the execute update function the null is also a ISFSObject that contains the values of all the "?" that exist in the query, if exist. I had null cause i didn't use ?s.

    If you use the query to retrieve data from the db, the extension must put them in an array and unity to retrieve them as an array. After that each row cell is a SFSObject itself that contains the name of the column and the value. Its like sfception or something dunno... like here:

    http://docs2x.smartfoxserver.com/DevelopmentBasics/database-recipes#resultSets

    Right now my only problem is that a certain query i have doesn't work, but i think i can fix it. Ask any questions, thank you.

    ReplyDelete
    Replies
    1. *Correction, ISFSObjects in SFSArrays are rows, not cells.

      Delete
    2. Everyone here appreciates your comments and aid! Thank you for your interest. I'm just going through and moving my project to a new EC2 server, so I'm having to go through this process again.

      Delete