MSSQL Database connector

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

MSSQL Database connector

MSSQL Connector
Jasmine offers MSSQL connector to connect to MS SQL Server databases. In order to use this feature, the mssql class should be included in jasmine.php

Object initialization
The class must be instantiated and proper connection variables have to be assigned before connecting. The instantiation can happen in two ways. Assigning connection vars while instantiating or after instantiation. The four connection variables are Host ($host), UserName ($user), Password ($pass) and the database to work on ($db). These have to be set before connecting.

First way:
$myDb=new mssql($mssqlHost, $userID, $password, $mssqlDatabase);

Second way:
$myDb=new mssql();
$myDb->host="mssqlHost";
$myDb->user="userID";
$myDb->pass="password";
$myDb->db="myDatabase";

Connecting and disconnecting
Like the base class, database, connect() and disconnect() methods control the connection.
$myDb->connect();
$myDb->disconnect();

Querying:
Query can be executed using query().
e.g. $db->query($odbcQuery);

Getting Results
If rows are expected from the query, they will be available in $rows property.
e.g. echo 'First customer is ' . $db->rows[0][0] ;

Executing Stored Procedures
Stored procedures can also be executed using Jasmine's MSSQL class, and as usual the rows will be available at $rows. An example is shown here is for illustration purposes.

1) Initializing the procedure
The procedure should be instantiated before executing it. This can be done by declaring the name of the procedure and an id to use in the php scripts. procedure() method is used in this regard. It expects two parameters, the original name of the stored procedure and

e.g. $db->procedure('ComputeTotalBalance', 'proc1');

2) Adding Parameters
After initializing, the procedure handler is available in the array, $procs[], indexed by the id given while initializing. These are the StoredProcedure objects. To add the parameters, if any, param() method of the procedure object should be used. The arguments it takes are three -- the name of the parameter, the value, the type of the data that is binded.

$db->procs['proc1']->param('@CustomerID', '304', SQLVARCHAR);

3) Executing the procedure
The procedure can be executed using execProc() method of the database object (not the procedure object - observe the difference here). The parameters are - the id of the procedure initialised earlier, the optional boolean argument specifying whether to index the fields or not. The second argument functionality is same as that in the query(). See Common functionalities secion in Database connectors.

$db->execProc('proc1', true);

Caching and Sourcing of results is same as described in Common Functionalities section in Database Connectors manual.

Technical info: PHP functions used in the class
- While connecting, the class uses mssql_connect(). While disconnecting, mssql_close() functions are used.
- Executing the query is done using mssql_query().
- Rows are extracted using mssql_fetch_assoc() or mssql_fetch_row() and columns with mssql_fetch_field().
- Stored procedures use mssql_init(), mssql_bind() and mssql_execute();