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.

nike air max 87 mens
Posts: 13
Comment
Gerald974
Reply #3 on : Mon December 31, 2018, 22:48:31
<a href="http://www.pulsemarketingbangor.com/promotions/nike-white-blue-air-griffey-max-i">nike white blue air griffey max i</a><a href="http://www.pulsemarketingbangor.com/real-deal/nike-air-max-270-volt-black-ah8050-701-03">nike air max 270 volt black ah8050 701 03</a><a href="http://www.pulsemarketingbangor.com/real-on-sale/nike-air-max-red-and-black-white-authentic-online-for-u">nike air max red and black white authentic online for u</a><a href="http://www.pulsemarketingbangor.com/recommended/nike-air-max-zero-qs-shoes-for-women-white-colorful-black">nike air max zero qs shoes for women white colorful black</a>
[url=http://www.royalehairstraightener.com/fake/nike-air-max-87-mens]nike air max 87 mens[/url]
nike air jordan 8 retro aqua 2015 gs donna
Posts: 13
Comment
Integration338
Reply #2 on : Fri March 29, 2019, 11:10:46
<a href="http://www.webuydcmetrohouses.com/mercurial/nike-jordan-air-jordan-1-low-scarpa-da-basket">nike jordan air jordan 1 low scarpa da basket</a><a href="http://www.webuydcmetrohouses.com/new_balance/asics-gel-kayano-trainer-space-dye-knit-hombre">asics gel kayano trainer space dye knit hombre</a><a href="http://www.webuydcmetrohouses.com/nl/nike-air-vapormax-svart-transparent-billigt-online">nike air vapormax svart transparent billigt online</a><a href="http://www.webuydcmetrohouses.com/obtain/new-balance-997s-herre">new balance 997s herre</a>
[url=http://www.gaur-yamunacity.com/premium/nike-air-jordan-8-retro-aqua-2015-gs-donna]nike air jordan 8 retro aqua 2015 gs donna[/url]
?????????????
Posts: 13
Comment
Snippet325
Reply #1 on : Fri April 19, 2019, 06:13:37
??????????????????
???????????????N?????????
?????-????!-????.?????
??????? ??????????
?????????
????????????????????????????????????????
??????????????? ??????????????
? ??????????????????
????????????????? ???????????
?N??????????? ??????
?????????????????????????
?? ???????????
(????
First | Previous | Showing comments 11 to 13 of 13

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.