Database Access
Connecting through the DBDataSet
Describes how to connect to the database through a connecting using DBDataSet.
Table of contents
1. Configuring the Connection to the Database
2. Connecting through the DBDataSet
3. The IIterator interface and the objects for standardized data access.
On this page
Connecting and Reading a DBDataSet
Filtering the data of a DBDataSet
Updating data with a DBDataSet
Using the SQLHelper
Connecting and Reading a DBDataSet
Once a connection string to the database is correctly defined, you can initiate the process of accessing data. Only one DBDataSet is needed for all of the Iterators that were defined.
CSharp
DBDataSet db = new DBDataSet("exemple1", this._context);
IIterator it = db.getIterator("select field1, field2 from table");
while (it.hasNext())
{
SingleRow sr = it.moveNext();
//sr.getField("field1");
}
PHP
// require_once("bin/com.xmlnuke/module.basemodule.class.php");
$db = new DBDataSet("exemple1", $this->_context);
$it = $db->getIterator("select field1, field2 from table");
while ($it->hasNext())
{
$sr = $it->moveNext();
//$sr->getField("field1");
}
Filtering the data of a DBDataSet
When obtaining the GetIterator, the user can submet a query containing all of the required filter clauses. However, for queries to a single table, you can use the IteratorFilter to assist the process.
CSharp
IteratorFilter itf = new IteratorFilter();
itf.addRelation("field1", Relation.Equal, "value");
DbParameters param;
string sql = itf.getSql("table", out param);
IIterator it = guestbook.getIterator(sql, param);
PHP
$itf = new IteratorFilter();
$itf->addRelation("field1", Relation::Equal, "value");
$param = array();
$sql = $itf->getSql("table", $param);
$it = $db->getIterator($sql, $param);
Tip:
You can also use the IteratorFilter for queries to tables connected by an InnerJoin or an OuterJoin, as long as the SQL ANSI model is used. When sending the name of the table to the getSQL method, you can send it the following way:
getSql("table1 inner join table2 on table1.field = table2.field");
Updating data with a DBDataSet
The processing for updating data on the serve is done exclusively through SQL commands.
CSharp
DBDataSet db = new DBDataSet("exemple1", this._context);
string sql = "update table set field1 = [[val1]] where field2 = [[val2]]";
DbParameters params = new DbParameters();
param.Add("val1", System.Data.DbType.String, "any value")
param.Add("val2", System.Data.DbType.Int32, 1);
db.execSQL(sql, params);
PHP
// require_once("bin/com.xmlnuke/module.basemodule.class.php");
$db = new DBDataSet("exemplo1", $this->_context);
$sql = "update table set field1 = [[val1]] where field2 = [[val2]]";
$param = array();
$param["val1"] = "valor qualquer";
$param["val2"] = 1;
$db->execSQL($sql, $param);
Important Note!
XMLNuke adopted a standard for sending parameters that is generic. Thus XMLNuke is capable of making the necessary adjustments for sending parameters depending on the database selected.
Using the SQLHelper
The SQLHelper is a class that allows the SQL commands Insert and Update to be created, aside from the database parameters, in a very simplified way. It also guarantees that the SQL commands that are generated are free from SQL Injection.
In the default data access class (BaseDBAccess) the utilization of the SQLHelper becomes accessible, since this class already implements an instance from the DBDataSet. All that needs to be done then is execute the getSQLHelper() method which returns a Singleton from SQLHelper. Otherwise, the DBDataSet object must be sent in your builder.
CSharp - Using the SQLHelper inside a BaseDBAccess class
// Defining the values to be stored
SQLFieldArray fields = new SQLFieldArray();
fields.Add(SQLFieldType.Number, "fldid", folderid, true); // Key - Used in Filter
fields.Add(SQLFieldType.Text, "fldtext", "SomeValue");
// Obtaining and executing SQL
SQLUpdateData updata = this.getSQLHelper().generateSQL("sometable", fields, SQLType.SQL_UPDATE);
this.executeSQL(update);
PHP - Using the SQLHelper inside a BaseDBAccess class
$param = array();
// Defining the values to be stored
$fields = array();
$fields["fldid"] = array(SQLFieldType::Number, $folderid);
$fields["fldtext"] = array(SQLFieldType::Text, "SomeValue");
// Calling XMLNuke PHP SQL Helper
$sql = $this->getSQLHelper()->generateSQL(
"sometable", $fields, $param, SQLType::SQL_INSERT, "", '.');
$this->executeSQL($sql, $param);
|