Database

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/

Stored Procedure

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.

vivienne westwood wedding dress
Posts: 6
Comment
WideWeb950
Reply #6 on : Sat November 22, 2014, 01:54:07
Many designs were accessoried with brightly coloured bags, produced in Kenya by female micro-entrepreneurs who live in the slums of Nairobi, under an International Trade Centre project, in conjunction with the UN and World Trade Organization, which Westwood is a passionate supporter of.
vivienne westwood wedding dress http://www.cellact.co.uk/uk/vwdress.html
Anonymous
Posts: 6
Comment
Enterprise319
Reply #5 on : Mon December 01, 2014, 07:44:47
pbxyhfos
http://www.seizenkeiyaku.org/watch/33.html
stone island junior
Posts: 6
Comment
Query549
Reply #4 on : Fri January 02, 2015, 19:57:18
5315 stone island ice jacket camouflage <a href="http://www.kinetico.ie/stone/?stoneisland=12">stone island jeans</a> baby stone island hat http://www.kinetico.ie/stone/?stoneisland=12 black stone island jeans that lift your bottom baby stone island clothes 800027s <a href="http://www.kinetico.ie/stone/?stoneisland=112">kids stone island</a> black stone island jeans 2014 http://www.kinetico.ie/stone/?stoneisland=112 boys stone island gloves baby stone island 7 cinema <a href="http://www.kinetico.ie/stone/?stoneisland=62">stone island mazatlan</a> baby stone island store http://www.kinetico.ie/stone/?stoneisland=62 boys stone island 2014 baby stone island clothes quarters folsom <a href="http://www.kinetico.ie/stone/?stoneisland=162">stone island australia</a> black stone island jeans for women http://www.kinetico.ie/stone/?stoneisland=162 boys stone island jackets ebay baby stone island menswear http://www.kinetico.ie/stone/?stoneisland=37 black stone island polo shirt baby stone island clothes giveaway <a href="http://www.kinetico.ie/stone/?stoneisland=137">stone island knitwear sale</a> black stone island jeans 80% off http://www.kinetico.ie/stone/?stoneisland=137 boys stone island jackets 5t baby stone island clothes 1980 <a href="http://www.kinetico.ie/stone/?stoneisland=87">stone island scarf</a> bagjack x stone island shadow 2010 messenger backpack http://www.kinetico.ie/stone/?stoneisland=87 boys stone island america baby stone island clothes young men <a href="http://www.kinetico.ie/stone/?stoneisland=187">stone island 533093</a> black stone island jeans made in america http://www.kinetico.ie/stone/?stoneisland=187 boys stone island jackets men baby blue stone island jacket <a href="http://www.kinetico.ie/stone/?stoneisland=37">stone island junior</a>
stone island junior http://www.kinetico.ie/stone/?stoneisland=37
Anonymous
Posts: 6
Comment
Re: How to call SQL Server stored procedures from Raxan
Reply #3 on : Wed February 24, 2016, 18:03:30
Spesifikasi Harga http://goo.gl/0uP2bE
Nokia http://goo.gl/hWHbrJ
Panasonic http://goo.gl/jNGNZ4
Kawasaki http://goo.gl/CkI3D3
Yamaha http://goo.gl/m8ia8Z
Forum http://goo.gl/sZcs2O

Youtube http://goo.gl/cHJemC Youtube
Jakarta http://rbl.ms/1PsinER
Honda http://goo.gl/0KuFXm
Harga Motor http://goo.gl/3xVt8a
Honda Scoopy fi http://goo.gl/yNQ75q

terbaru http://goo.gl/BamosD
terbaru http://goo.gl/xu8J20
terbaru http://goo.gl/N8jrYg
terbaru http://goo.gl/RYnYCh
terbaru http://goo.gl/p3uSL1
terbaru http://goo.gl/1GNeqx
terbaru http://goo.gl/ZuxuNR
terbaru http://goo.gl/8n7fSK
terbaru http://goo.gl/IzKeOE
kata kata http://goo.gl/8EFdRW
Lowongan Kerja http://goo.gl/M9vWgP

Lowongan https://goo.gl/nA81Sf
Loker FB https://goo.gl/oN80Wh
Jakarta http://bit.ly/LowonganKerjaJakarta
Lowongan https://goo.gl/Y8Jzu8
Lowongan http://on.fb.me/1Qcvb22
Lowongan http://goo.gl/OmW2Cj
Cara http://goo.gl/pJ12rq
Feed Loker http://goo.gl/NZMbJy
Terbaru http://goo.gl/Ip2ZgC
Lowongan Terbaru http://goo.gl/5vTqAY
Lowongan Aceh http://bit.ly/LowonganKerjaAceh
Lowongan Bali http://bit.ly/LowonganKerjaBali
Lowongan Banten http://bit.ly/LowonganKerjaBanten
Lowongan Bengkulu http://bit.ly/LowonganKerjaBengkulu
Lowongan Yogyakarta http://bit.ly/LowonganKerjaJogja
Lowongan BUMN http://goo.gl/thu306
Lowongan Danamon http://bit.ly/LowonganBankDanamon
Lowongan BNI http://bit.ly/LowonganBNI
Lowongan Astra http://goo.gl/JIXcsL
feed lowongan http://goo.gl/Arp2S0

Resep http://goo.gl/mggIkL
biodata profil http://goo.gl/Ovb0m8
terbaru http://goo.gl/8eSfVC
lagu http://goo.gl/QSdbeF
Loker http://goo.gl/bcj4yZ

cepat hamil http://goo.gl/x6uXw1
pendidikan http://goo.gl/pIyQxT
blogspot http://goo.gl/QEjlQ1
Lowongan http://goo.gl/q8u00B

Cara Mengatasi Kurang Tidur http://bit.ly/21fhKGn
kevin julio http://goo.gl/IQMzpe
gritte agatha http://goo.gl/rNjWgu
kevin aprilio http://goo.gl/wBNJMI
yukikato http://goo.gl/bWIc3t
Indonesia http://goo.gl/v8ES0X
Manca Negara http://goo.gl/XjomMf

scorpio http://goo.gl/iyjmyP
scorpio http://goo.gl/qkzs4q
kawasaki ninja 150 r http://goo.gl/E7yH5x
harga motor ninja http://goo.gl/VDWIZJ
Terbaru http://goo.gl/LqqXdv
Sunny
Posts: 6
Comment
Grade A stuff. I'm u
Reply #2 on : Tue May 10, 2016, 00:29:58
Grade A stuff. I'm untlisqeonabuy in your debt.
Aundre
Posts: 6
Comment
Holaqueria hacer la
Reply #1 on : Wed May 11, 2016, 05:15:14
Holaqueria hacer la siguiente consultaFui a una inmbiliaria para alquilar un inmueble, y solo quieren <a href="http://iarszcap.com">almeularqilo</a> por un año (no por dos como dice la ley) bajo la excusa de que la dueña quiere alquilarlo por un año. Dicen que despues lo renovamos. Pero, esto puede ser una estafa encubierta? puede salir perjudicado el garante?se trata de un monoambiente a 1100 pesos por mes, y lo alquilo tres años una amiga y luego lo alquilo un año otro amigo de ella. Un salduo y garcias!!!!!

Leave a Comment

Required fields are marked with *.
If you have trouble reading the code, click on the code itself to generate a new random code.