_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()
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 |
// 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")
size() | Returns the number of rows in the RecordSet | |
get(n) | Retrieve a Record from the RecordSet. "n" is the record number (n < size()) |
getItem(name) | Retrieve the value of the field in the Record object. All values are returned as Strings. |
// 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")
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