Thread: MySQL to Server Integration

Description: Having some things load from a SQL server. The example in this context is loading autospawn from SQL

Difficulty: 2-3/depending on brain capacity

Assumed Knowledge: How to set up a SQL database, copy and paste,

Tested Server: Cleaned v4

Files/Classes Modified: SQL.java, server.java

Procedure
Step 1: Setting up the SQL Server
First of all, get a SQL server. I use db4free.net. Next, add this in server.java

Code:

	public server() {

	try {
		String driverName = "org.gjt.mm.mysql.Driver";
		Class.forName(driverName);

		// Create a connection to the database
		String serverName = "";//your SQL server
		String dbase = "";
		String url = "jdbc:mysql://" + serverName +  "/" + dbase;
		String username = "";
		String password = "";
		connection = DriverManager.getConnection(url, username, password);
		misc.println("connected to :" + serverName +  "/" + dbase);
	} catch (ClassNotFoundException e) {
		misc.println("Class not found error >.<");
		killServer();
	} catch (SQLException e) {
		misc.println("SQL Error AMG");
		killServer();
	}

}

and also add this

Code:

	public static Connection connection = null;

Fill out the information accordingly to how you setup your SQL server at your host.

Step 2: Making the actual SQL file
Make a new class and in it, put this

Code:

import java.sql.*;
import java.io.*;

public class SQL {
	public Connection connection = server.connection;
	public static Connection myConnection = server.connection;

	public static int AutoSpawn() {
		int count = 0;
		try {
			Statement stmt = myConnection.createStatement();
			ResultSet rs = stmt.executeQuery("SELECT * FROM autospawn");
			while (rs.next()) {
				count = rs.getRow();
				server.npcHandler.newNPC(rs.getInt("NpcID"), rs.getInt("StartX"), rs.getInt("StartY"), rs.getInt("StartHeight"), rs.getInt("RangeX1"), rs.getInt("RangeY1"), rs.getInt("RangeX2"), rs.getInt("RangeY2"), rs.getInt("WalkType"), server.npcHandler.GetNpcListHP(rs.getInt("NpcID")));
			}
			stmt.close();
		} catch (Exception e){
			misc.println("Error"+e);
		}	
		return count;
	}
}

Add this

Code:

		misc.println(""+SQL.AutoSpawn()+" Autospawns loaded");

under

Code:

objectHandler = new ObjectHandler();

in server.java. Now, open NPCHandler and quote out

Code:

loadAutoSpawn("autospawn.cfg");

Step 3: Executing the SQL script
Go into your SQL host, and open phpmyadmin. Press the 'Execute SQL Script' button on your far left(should be the middle button, i think) and when the window pops up, execute this script

Code:

CREATE TABLE `autospawn` (
  `NpcID` int(10) NOT NULL default '0',
  `StartX` int(10) NOT NULL default '0',
  `StartY` int(10) NOT NULL default '0',
  `StartHeight` int(10) NOT NULL default '0',
  `RangeX1` int(10) NOT NULL default '0',
  `RangeY1` int(10) NOT NULL default '0',
  `RangeX2` int(10) NOT NULL default '0',
  `RangeY2` int(10) NOT NULL default '0',
  `WalkType` int(10) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

this will create your table where your data will be stored, in this case, autospawn. Now take a look at this code

Code:

INSERT INTO `autospawn` VALUES (1234, xxxx, yyyy, hhhh, xxxx, yyyy, xxxx, yyyy, 1);

1234 = The NPC I.D.
The first xxxx and yyyy are the coordinates of the NPC
The hhhh is the height of your NPC
The second and third xxxx's and yyyy's are the walking range of it
And the 1 is the walktype
Execute this script in the same way you did the other.

Download the attachment and extract the folders to your source folder, compile, and run.

If you understood this tutorial and the example of this tutorial, you could probably integrate it to nearly anything. And you're welcome for the example

Credits: Me