8.19 H2 Embedded database tutorial

In this tutorial we will quickly guide you through the simple steps of connecting and interacting with the embedded H2 Database Engine coming with SmartFoxServer 1.6

If you're not familiar with the configuration of a database connection using SmartFoxServer, we highly recommend that you check the relative tutorials:

• Connecting to databases
• Embedded H2 database engine

» Zone configuration

This is how the DatabaseManager should be configured to access the H2 embedded server:

<Zone name="h2db" customLogin="false">
	<AutoReloadExtensions>true</AutoReloadExtensions>
	
	<Rooms>
		<Room name="Lobby" maxUsers="10" isPrivate="false" isTemp="false" autoJoin="true" />
	</Rooms>
	
	<DatabaseManager active="true">
		<Driver>org.h2.Driver</Driver>
		<ConnectionString>jdbc:h2:tcp://localhost:9009/examples</ConnectionString>
		
		<UserName>sa</UserName>
		<Password></Password>
		
		<TestSQL><![CDATA[SELECT COUNT(*) FROM retrocomputers]]></TestSQL>
		
		<MaxActive>10</MaxActive>
		<MaxIdle>10</MaxIdle>
		
		<OnExhaustedPool>fail</OnExhaustedPool>
		<BlockTime>5000</BlockTime>
	</DatabaseManager>
	
	<Extensions>
		<extension name="h2db_ext"  className="internalDbDemo.as" type="script" /> 
	</Extensions>
</Zone>	

The connections string: jdbc:h2:tcp://localhost:9009/examples specifies that we want to connect via TCP on port 9009 to a database called examples.

In the tutorial we will be working with a table called retrocomputers which catalogues a few popular personal computers of the 8-bit generation (early 80's).

The table is made up of 5 fields called: ID, BRAND, MODEL, CPU, RAM and it was created using the following SQL code:

CREATE TABLE retrocomputers (
	ID INT AUTO_INCREMENT PRIMARY KEY, 
	BRAND VARCHAR(120), 
	MODEL VARCHAR(200), 
	CPU VARCHAR(100), 
	RAM VARCHAR(50)
)
	

» New RecordSet serialization

SmartFoxServer 1.6 introduces new features in the ActionscriptObject class which is used by the Java framework to "simulate" actionscript objects in Java. The class is also used behind the scenes in both ActionScript and Python extensions to serialize and deserialize complex data sent to the client.

The new version allows to directly serialize Java Collections, Maps and ResultSets coming from a database query.

In the following code you will notice how simple is to send a ResultSet to the client side which immediately passes the same object as the dataProvider to a DataGrid UI component.

» The example application

By pressing the Connect button the user is logged into the zone as a guest user and auto-joined to default "Lobby" room.
The Get Data! button will fire a simple request called "getList" to the server side extension:

smartfox.sendXtMessage("h2db_ext", "getList", {})

On the server side this code is executed:

function sendComputerList(user)
{
	var sql = "SELECT * FROM retrocomputers ORDER BY BRAND"		

	if (queryRes != null)
	{
		var response = {}
		response._cmd = "getList"
		response.list = queryRes

		_server.sendResponse(response, -1, null, [user])
	}
}

You will notice that the amount of code necessary to prepare the response is reduced to the minimum and you don't have to "unroll" the ResultSet object into an AS object in order to send it to the client(s).

Back on the client side (onExtensionResponse event) we simply receive the same list array and pass it as the dataProvider to the DataGrid component which will be immediately populated. Piece of cake!

smartfox.onExtensionResponse = function(o:Object)
{
	if (o._cmd = "getList")
		dg_computers.dataProvider = o.list
}