From the surface it might appears to be simple but under the hood Raxan includes a lot of features that will make your web application development a lot easier. In this tutorial we will be using the RaxanPDO class library to execute a stored procedure on a Microsoft SQL Server.
When calling a stored procedure (or SP) from an SQL Server database there are a few things that you need to know before you execute the call:
- The order in which to pass the parameters to the SP
- The data type for the input/output parameters
It's also important to make sure that the database login account has the necessary permissions to execute the stored procedure.
In this tutorial, we will use the following T-SQL code to create an SP on the server:
CREATE PROC dbo.GetTables
@query varchar(255) = null,
@count int = null output
AS
SET NOCOUNT ON
IF (@query is not null) SET @query = '%' + @query + '%'
-- get count
SELECT @count = count(*) FROM sys.objects
WHERE (@query IS NULL OR [name] LIKE @query)
AND [type] IN ('U','S')
-- get records
SELECT * FROM sys.objects
WHERE (@query IS NULL OR [name] LIKE @query)
AND [type] IN ('U','S')
The GetTable stored procedure will return a list of tables within the currently selected database.
Calling the SP from within a Raxan web page is very simple and straight forward. The first thing to do is to get a connection to the database:
<?php
protected function _load() {
$dsn = 'sqlsrv: server=localhost; database=demo';
$db = Raxan::connect($dsn,'user','password',true);
}
?>
Note: The PDO Driver for SQL server can be downloaded from this website: http://blogs.msdn.com/b/sqlphp/
Once we have established a connection to the database, we can go right ahead and call any SP within the database. This can be done using RaxanPDO::execProc() method as shown below:
<?php
protected function _load() {
//….. code here ...
// call the SP
$rt = $db->execProc('GetTables');
}
?>
The above execProc() method will return an instance of the RaxanPDOProcResult class. This class contains the returned rowset and output parameters which can be accessed as object properties.
<?php
protected function _load() {
//….. code here ...
$rowset = $rt->rowset;
}
?>
To pass parameters to the SP you can either pass the value or a variable to the execProc in the order expected by the SP. If you would like to specify the data type for an input or output parameter, then the value will have to be passed in as an array:
Input/Outoput Parameter Format:
$param = array($value, $datatype, $length);
In the above format $datatype can be any PDO supported data type.
To pass a value to the SP you will need to do the following:
<?php
protected function _load() {
//….. code here ...
// call the SP and pass a value to it
$rt = $db->execProc('GetTables' , 'sysowners');
// get results from SP
$rowset = $rt->rowset;
?>
To pass a value to the SP and retrieve the output parameter you will need to do the following:
<?php
protected function _load() {
//….. code here ...
// call the SP with input/output parameter
$query = 'sysowners';
// define output parameter
$count = array(null, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);
$rt = $db->execProc('GetTables',$query, $count);
// get results from SP
$rowset = $rt->rowset;
$count = $rt->parameters[1]; // get output parameter
}
?>
You can also use an array to pass multiple parameters to the execProc() method:
execProc('GetTables',$params); // get results from SP $rowset = $rt->rowset; $count = $rt->parameters[1]; // get output parameter } ?>Here's the complete code:
<?php
protected function _load() {
// connect to database
$dsn = 'sqlsrv: server=localhost; database=demo';
$db = Raxan::connect($dsn,'user', 'password', true);
// set up parameters
$query = 'sysowners';
// define output parameter
$count = array(null, PDO::PARAM_INT|PDO::PARAM_INPUT_OUTPUT, 4);
// execute SP
$rt = $db->execProc('GetTables',$query, $count);
// get results from SP
$rowset = $rt->rowset;
$count = $rt->parameters[1]; // get output parameter
}
?>
Note: SQL Server requires that you either consume or skip the rowset in order to get the output parameters.
If your SP returns multiple rowsets then you can use the nextRowSet property to retrieve the next available rowset:
<?php
protected function _load() {
// … code connect to database ...
// get next result from SP result
$rowset = $rt->nextRowSet;
}
?>
That's all there is for today but now that you know how easy it is to execute a stored procedure, you can start building your next web application to connect to your SQL Server Datacenter.
If you have any questions or feedback please feel free to post to your comment below. You can also submit your feedbacks in the Online Forum.
Posts: 13
Reply #12 on : Mon December 01, 2014, 12:44:47
Posts: 13
Reply #11 on : Sat January 03, 2015, 00:57:18
Posts: 13
Reply #10 on : Wed February 24, 2016, 23:03:30
Posts: 13
Reply #9 on : Tue May 10, 2016, 05:29:58
Posts: 13
Reply #8 on : Wed May 11, 2016, 10:15:14
Posts: 13
Reply #7 on : Mon October 01, 2018, 01:55:58
Posts: 13
Reply #6 on : Tue October 16, 2018, 05:37:59
Posts: 13
Reply #5 on : Tue October 30, 2018, 09:01:49
Posts: 13
Reply #4 on : Fri December 28, 2018, 05:21:13
Posts: 13
Reply #13 on : Sat November 22, 2014, 06:54:07