Database Access

OWA provides access to the database through the use of the owa_db - a specialized database access layer that implements platform specific database connect and SQL query construction. Use of owa_db ensures that your database queries will execute properly no matter what database platform is in use (MySQL, PostgreSQL, etc.).

Table of Contents Database Access Object Constructing SQL Queries SELECT WHERE GROUP BY ORDER BY ORDER LIMIT OFFSET Executing the Query INSERT Specifying Values Executing the Query UPDATE Specifying the Values to Update Executing the Query Table Creation Table Modification

Database Access Object

All access to the databse is done via OWA's data access object (DAO). You can fetch the DAO via coreAPI by calling the dbSingleton method.

$db = owa_coreAPI::dbSingleton();

Constructing SQL Queries

owa_db provides developers with a facility to construct platform independent SQL queries that will execute properly across all database platforms that OWA supports. owa_db implements many methods that should be used to construct the various parts of a SQl query

SELECT

To construct a select query begin by specifying the table to select from:

$db->selectFrom('name_of_table');

Then specify the columns that you wish to select using the selectColumn method. For example:

$db->selectColumn('col');
$db->selectColumn('col2');
$db->selectColumn('count (col3)');
$db->selectColumn('sum (col4) as column_4');

WHERE

Then construct your WHERE clause by adding constraints using the where method. The where method takes a column name, a value and an alternative operator (the default is '='):

$db->where('column_name', 'value');
$db->where('column_name', 'value', '!=');

You can also set multiple constraints at once by passing multiWhere an array of constraints. For example:

$constraints = array();
$constraints['my_constraint'] = array('col2', 'value');
$constraints['my_constraint'] = array('col3', 'value', '!=');
$db->multiWhere($constraints);

GROUP BY

GROUP BY clauses can be added using the groupBy method:

$db->groupBy('column_name');

ORDER BY

ORDER BY clauses can be added using the orderBy method:

$db->orderBy('column_name');

ORDER

ORDER clauses can be added using the order method. Two constants implement the ascending (OWA_SQL_ASCENDING) and descending (OWA_SQL_DESCENDING) values.

$db->order(OWA_SQL_DESCENDING);

LIMIT

LIMIT clauses can be added using the limit method. The limit method takes a numeric value that represents the number of rows you want to limit the query to returning.

$db->limit(10);

OFFSET

OFFSET clauses can be added using the offset method. The offset method takes a numeric value that represents the offset to limit the query to returning.

$db->offset(10);

Executing the Query

owa_db implements two execution methods for SELECT queries.

The getAllRows method should be used when the query is known to return multiple rows of data:

$db->getAllRows();

The getOneRow method should be used when the query is known to return a single row:

$db->getOneRow();

INSERT

To construct an INSERT query first specify the table into which data will be inserted by using the insertInto method:

$db->insertInto('table_name');

Specifying Values

Next, specify the name of the column and the value that you wish to insert using the set method:

$db->set('name', 'value');

Executing the Query

Once the query is constructed use the executeQuery method to execute it:

$ret = $db->executeQuery();

if ($ret == true) {
     $this->debug('Query executed successfully');
} else {
     $this->debug('Query did not execute successfully');
}

UPDATE

To construct an UPDATE query first specify the table into which data will updated by using the updateTable method:

$db->updateTable('table_name');

Specifying the Values to Update

Next, specify the name of the column and the value that you wish to update using the set method:

$db->set('name', 'value');

Executing the Query

Once the query is constructed use the executeQuery method to execute it:

$ret = $db->executeQuery();

if ($ret == true) {
     $this->debug('Query executed successfully');
} else {
     $this->debug('Query did not execute successfully');
}

Table Creation

Table Modification