Database Connectors

Jasmine Wiki

Navigation

Wiki Home
Installing Framework
Configuring jasmine.php

Database Module
MySQL
Mysqlix
ODBC
MSSQL

Database Objects
Datagrid
Datasource
Repeater
SQLQuery

Files Module
File
FileSystem
FTP
Attachment
ExcelFile

Graphics Module
Color
Image
Graph

Miscellaneous
Classes:
NumbWordter
Email
Poll
Shield
XMLParser

Functions:
General Functions
HTML Functions
Validation Functions
TrackThisPage

Database Connectors

Database connectors
Jasmine offers three types of database connector classes. MySQL, ODBC and MSSQL. All these classes are derived from the base class, database. So including database class in jasmine.php is a must.

Configuring jasmine.php
A listing of available database modules is given under 'Database connectors' section. Simply comment or uncomment the lines according to your needs.

Types of connectors
There are three types of database connectors. Click on these links to know about individual functionality
- MySQL connector
- ODBC connector
- MSSQL connector

Common Functionalities
The base class, database, offers some functionalities that are common to its child classes. They are
- Connecting and disconnecting database connections
- Caching the results
- Creating datasources

Connecting and disconnecting database connections
After initializing relevant database object, the connections can be handled using connect() and disconnect() methods. Once the database is connected, the connection handler is assigned to the $_conn property of the object. While disconnecting, relevant disconnect functions are called and finally the connection handler is reset.

Suppose $db is the database variable object, then connecting and disconnecting can be done as
$db->connect();
$db->disconnect();

If you want to work on more than one database at the same time, simply create another object of your wish.

Querying
All SQL queries can be executed by using query() method. The optional second parameter in the method specifies whether the fields arae to be indexed or not. You will understand it later down the page.

$sqlQuery='select FirstName, LastName from Customers';
$db->query($sqlQuery, true);

The method internally decides whether result table is expected from the query, based on the type of sql query statement, and stores the result in $rows property. If the result is not expected, then $impact property is set with the no. of rows effected.

(The way in which the query gets executed depends on the type of object. Refer individual connector manuals.)

Retrieving query results
Type of query - SELECT
After executing a SELECT query, the result is converted into an array and stored in $rows property of the object. $rows is a two dimensional array which is numerically indexed. If the second parameter in query() is set, the cell value in a row can be accessed by giving its field name.

Examples to use $rows
1. To access third row, third column - $db->rows[2][2];
2. To access FirstName field in the third row - $db->rows[2]['FirstName'];

The field names are numerically indexed in the $fields propety and are accessible via its key.
e.g.
echo $db->fields[0] . ' - ' . $db->fields[1];

The counts, of rows and columns retrieved, are accesible via $rowCount and $colCount properties.
e.g.
echo $db->rowCount . ' are the rows obtained' ;
echo $db->colCount . ' are the columns obtained' ;

Type of queries:- INSERT, DELETE, UPDATE
These type of queries do not return any rows. So the variable $impact is set to know the number of records affected.

echo $db->impact;

Caching the query results
After executing the second sql query, the results of first sql query are not accessible. In order to retain them, they have to be cached. To use caching feature, the $createCache property must be set true before executing the query.

$db->createCache=true;
$db->query($sqlQuery);

This is applicable only for SELECT sql queries. After the query is executed, the rows will be arrayed into $cache property and can be accessed by specifying the id of the query executed after cache is turned on. This will be again a numerical index starting from 0.

For example, to access the second row's third column of the second query executed after $createCache is turned on, you need to give
$db->cache[1]->rows[1][2];
(Note: While caching, $rows property is arrayed into $cache property. So, it will not be accessible directly, as $db->rows.)

Once the cached results are not needed any more, you can clear the cache using clearCache() method. This doesn't turn off the $createCache, but just clears the cache and the cache id starts from 0.
$db->clearCache();

Creating Datasources
If you want to assign the query results to the dataobjects, such as datagrid or repeater, this feature should be used. For this, $createSource property must be set true, before executing the query.

$db->createCache=true;
$db->query($sqlQuery);

After the query is executed, the rows will be accessible via $source property. So, to access the rows in source, you need to use some thing like $db->source->rows[2][4];

$source usage is different from $cache. $cache is an array and can accomodate many query results. But $source can accomodate only one result at a time. The source can be cleared by using clearSource() method.

$db->clearSource();