_server.getDatabaseManager()

Availability:

SmartFoxServer PRO 1.2.1

Usage:

_server.getDatabaseManager()

Description:

Get a reference to the DatabaseManager object in the current Zone.
The DatabaseManager allows you to connect to external data sources.

For more informations on how to configure and activate the DatabaseManager please check section ... of the documentation.

Parameters:

none

Returns:

The DatabaseManager object

Example:

var dbManager = _server.getDatabaseManager()

The DatabaseManager object exposes 3 methods:

executeQuery   Executes any SQL "SELECT" statement. It returns a "RecordSet".
executeCommand   Execute any other SQL command (INSERT, UPDATE, DELETE ... etc ...). Returns a boolean telling if the operation was successfull
getConnection()   Retrieve a java.sql.Connection object from the database connection pool. This allows advanced database interaction by using the JDBC API
(available since SmartFoxServer 1.5.0

1) Example using executeQuery:

// create a SQL statement
var sql = "SELECT * FROM contacts ORDER BY name"		

// execute query on DB
// queryRes is a ResultSet object
var queryRes = dbase.executeQuery(sql)

// If the queryRes is not null the query was successfull
if (queryRes != null)
{
	// Cycle through all records in the ResultSet
	for (var i = 0; i < queryRes.size(); i++)
	{
		// Get a record
		var tempRow = queryRes.get(i)
		
		trace("Record n." + i)
		trace("Name: " + tempRow.getItem("name"))
		trace("Location: " + tempRow.getItem("location"))
		trace("Email: " + tempRow.getItem("email"))
		trace("-------------------------------------------")
	}
}
else
	trace("DB Query failed")

In the first line the SQL statement is created: a simple SELECT from a contacts database:
» The variable called queryRes will hold the Record Set returned by the DatabaseManager.
» queryRes is going to be null if the SQL statements fails.

The Record Set object is similar to a plain array, it contains all the database rows selected by the SQL statement numbered from 0 to n.
You can use two methods on a RecordSet:

size()   Returns the number of rows in the RecordSet
get(n)   Retrieve a Record from the RecordSet. "n" is the record number (n < size())

The Record object is similar to an associative array where each key represents the database column names (field names).
You have only one method for the Record object:

getItem(name)   Retrieve the value of the field in the Record object. All values are returned as Strings.

Back to the above code, in the for loop we cycle through all records returned by the SQL statement and for each of them we show some fields on screen (name, location, email)


2) Example using executeCommand:

// A simple INSERT SQL statement
var sql = "INSERT INTO contacts (name, location, email) VALUES ('Lapo','Italy','lapo@mail.it')"

// Execute the SQL command
var success = dbase.executeCommand(sql)

if (success)
	trace("Record inserted!")
else
	trace("Ouch, record insertion failed")

If the operation fails an the DatabaseManager will throw an error (warning level) in the SmartFoxServer log.

3) Example using getConnection() and JDBC APIs:

// Obtain a reference to the  databaseManager object
var db = _server.getDatabaseManager()

// Get a connection from the connection pool
var conn = dbase.getConnection()
	
if (conn != null)
{
	try
	{
		// create a statement
		var stmt = conn.createStatement()
		var sql = "SELECT * FROM contacts ORDER BY name"
		
		// Execute the query, the returned object is a java.sql.ResultSet
		var rs = stmt.executeQuery(sql)
		
		// Get the resultset metadata
		var md = rs.getMetaData()
	
		// obtain the number of colums
		var cnt = md.getColumnCount()
		
		// Show each column's name, type and length
		for (var i = 1; i <= cnt; i++)
			trace("Column " + i + ": " +  md.getColumnName(i) + ", " + md.getColumnTypeName(i) + ", " +  md.getColumnDisplaySize(i))
	}
	catch (e)
	{
		trace("Problems executing query: " + e)
	}
	
	// Return the connection to the pool and release the resources
	conn.close()
	stmt.close()
	rs.close()
}	

else
{
	trace( "Can't get a connection to the database")
}

The above example shows how to execute a SQL query using the JDBC API and how to read the column properties from the ResultSet object.
You can learn more about JDBC here. Check also this JDBC in a nutshell article.

Also check the documentation of the objects used in the example:

java.sql.Connection
java.sql.Statement
java.sql.ResultSet
java.sql.ResultSetMetaData