[El Forum]

[eluser]Unknown[/eluser]
This Msdb Class will help you use MSSQL stored procedures with codeigniter.

Usage:
put this class in your application/library folder and load the class in the usual way:

Code:

$this->load->library('msdb');

in model or controller assign the result to a variable. Note the result is an array.

To select data use:

Code:

$result = $this->msdb->output('SP_Name', array('Param1'=>1, 'Param2'=>2), 'SELECT');

To execute any other query e.g. insert, update, delete...

Code:

$result = $this->msdb->output('SP_Name', array('Param1'=>1, 'Param2'=>2), 'EXECUTE');

Sample stored procedure:

Code:

Alter Procedure [dbo].[SP_Name]
  @Param1 int, @Param2 varchar(500)
  As
  
  BEGIN
  
   Select
    *
   From
    Table_Name
   Where FieldName1 = @Param1 And FieldName2 = @Param2  
  
  End

use

Code:

printf($result);

in your view to see the returned array

NOTE: When inserting or updating a field with data type TEXT in your table parse the parameter as shown:

Code:

$result = $this->msdb->output('SP_Name', array('Param1||'=>1, 'Param2'=>2), 'EXECUTE');

Param1 here is of data type TEXT.

Hope this works for you. If you have any questions please comment to this thread.
Thanks,
@Email: [email protected], [email protected]

Download link: http://www.4shared.com/file/FIXtHIOB/Msdb.html

[El Forum]

[eluser]vinuf666[/eluser]
[quote author="noadek" date="1319128527"]This Msdb Class will help you use MSSQL stored procedures with codeigniter.

Usage:
put this class in your application/library folder and load the class in the usual way:

Code:

$this->load->library('msdb');

in model or controller assign the result to a variable. Note the result is an array.

To select data use:

Code:

$result = $this->msdb->output('SP_Name', array('Param1'=>1, 'Param2'=>2), 'SELECT');

To execute any other query e.g. insert, update, delete...

Code:

$result = $this->msdb->output('SP_Name', array('Param1'=>1, 'Param2'=>2), 'EXECUTE');

Sample stored procedure:

Code:

Alter Procedure [dbo].[SP_Name]
  @Param1 int, @Param2 varchar(500)
  As
  
  BEGIN
  
   Select
    *
   From
    Table_Name
   Where FieldName1 = @Param1 And FieldName2 = @Param2  
  
  End

use

Code:

printf($result);

in your view to see the returned array

NOTE: When inserting or updating a field with data type TEXT in your table parse the parameter as shown:

Code:

$result = $this->msdb->output('SP_Name', array('Param1||'=>1, 'Param2'=>2), 'EXECUTE');

Param1 here is of data type TEXT.

Hope this works for you. If you have any questions please comment to this thread.
Thanks,
@Email: [email protected], [email protected]

Download link: http://www.4shared.com/file/FIXtHIOB/Msdb.html
[/quote]

Hi guys,

I get this error

Code:

"Fatal error: Call to undefined function mssql_init() in C:\wamp\www\dev\application\libraries\Msdb.php on line 159"

When I try to execute an SP. Its a test DB on localhost and I'm using the admin account.

DB Config is given below:

Code:

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'sa';
$db['default']['password'] = 'pa55w0rd';
$db['default']['database'] = 'VM_UI';
$db['default']['dbdriver'] = 'sqlsrv';
$db['default']['dbprefix'] = '';
$db['default']['pconnect'] = TRUE;
$db['default']['db_debug'] = TRUE;
$db['default']['cache_on'] = FALSE;
$db['default']['cachedir'] = '';
$db['default']['char_set'] = 'utf8';
$db['default']['dbcollat'] = 'utf8_general_ci';
$db['default']['swap_pre'] = '';
$db['default']['autoinit'] = TRUE;
$db['default']['stricton'] = FALSE;

...and how do I call stored procedures which do not have arguments ?? (EXEC sys.sp_who

Thanks in advance guys ! Its a wonderful library

[El Forum]

[eluser]Unknown[/eluser]
If you're using WAMP, make sure that the mssql module is enabled. It will have a checkmark next to its name in the context menu if it is.

To call stored procedures which do not have arguments, just pass an empty array.

[El Forum]

[eluser]vinuf666[/eluser]
I thought I'll share this piece of code that I made for the MS SQL stored procedures if anyone uses 'sqlsrv' as the driver platform instead of mssql in codeIgniter

sqlsrv is quite buggy but is the latest Microsoft SQL server driver for php.

You need to create the model as follows

Code:

class SPTest extends CI_Model {
    function __construct() {
        parent::__construct();
    }
    /**
     * Model class with method for executing Stored procedures of all types
     * Multiple result sets are not supported.
     * @package Matrix
     * @subpackage Model
     * @author Vinu Felix <[email protected]>
     * @return bool|array
     * @param string $spname The name of the stored procedure to be executed
     * @param array $spparams The array in the specific format that will be modified or used as input parameters
     * Example Syntax:
     * sqlsrv_runSP("sp_name");
     * sqlsrv_runSP("sp_name",$array_variable);
     * You CANNOT pass an array directly like sqlsrv_runSP("sp_name",array(array("...first value in...", SQLSRV_PARAM_IN)))
     * Array variable format:
     * $array_variable = array(
     *              array("Some value", SQLSRV_PARAM_IN),
     *              array($var_2, SQLSRV_PARAM_OUT),
     *              array($var_3, SQLSRV_PARAM_INOUT)
     *          );
     *
     * Return types(for info purpose only) for SQLSRV_PARAM_INOUT and SQLSRV_PARAM_OUT are:
     *  SQLSRV_PHPTYPE_INT
     *  SQLSRV_PHPTYPE_DATETIME
     *  SQLSRV_PHPTYPE_FLOAT
     *  SQLSRV_PHPTYPE_STREAM
     *  SQLSRV_PHPTYPE_STRING
     *  For more constants refer http://www.php.net/manual/en/sqlsrv.constants.php
     *
     *
     */
  
    public function sqlsrv_runSP($spname,&$spparams=NULL){
        if($this->db->platform()!="sqlsrv") //if the driver is not sqlsrv the function will fail anyway
        {
           log_message('error', 'The DB driver platform is Incompatible');
           return FALSE;
        };
        
        if(is_null($spparams)){ //This SP does not have parameters. Execute it and return results as array(if any)
            $q1=$this->db->query($spname);
            $resarr=$q1->result_array();
            if(count($resarr) > 0){
                return $resarr;
            }else return TRUE; //No result but query was successful
         };
        if(!is_array($spparams)){// The SP parameters have to be an array
            log_message('error', 'SP Parameters has to be an array if its provided');
            return FALSE;
        };
        if(count($spparams,0) == 0 || ((count($spparams,1)/count($spparams,0))-1) <= 1){
            /*array should be in the format prescribed at http://msdn.microsoft.com/en-us/library/cc626303(v=sql.105).aspx
             * The number of sub arrays should be atleast one and the number of columns in subarray atleast 2
             * array(
             * array("Some value", SQLSRV_PARAM_..)
             * )
             */
            log_message('error',"SP Parameter array is invalid.");
            return FALSE;
        };
        $qmarks="?"; // The previous if checks to make sure that atleast one row is there
        for($c=0;$c<(count($spparams,0)-1);$c++) $qmarks= "?," . $qmarks; // generate '?' placeholders =no. of arguments
        $tsql_callSP = "{call " . $spname . "(" . $qmarks . ")}"; // the final SP to be executed
        //Validate Parameter Array
        reset($spparams);
        foreach ($spparams as $param) {
          if(! ($param[1]==SQLSRV_PARAM_IN || $param[1]==SQLSRV_PARAM_OUT || $param[1]==SQLSRV_PARAM_INOUT)){
             log_message('error', 'SP Parameters array format is invalid');
             return FALSE;
          }  
        };
        reset($spparams);
        // Get CI DB Connection handler for direct query execution
        $q2 = sqlsrv_query($this->db->conn_id, $tsql_callSP, $spparams);
        if(!$q2){
            log_message('error',"Stored Procedure execution failed" . sqlsrv_errors());
            return sqlsrv_errors();
        }else{ //successful execution of Stored Procedure
                $resarr=array();//array_push requires the type to be array to function corectly
                while($ta=sqlsrv_fetch_array ($q2,SQLSRV_FETCH_ASSOC)){
                    array_push($resarr,$ta);
                };
                $resarr=$resarr[0]; //eliminate parent array
                sqlsrv_next_result($q2); //BUG in MS sqlsrv driver. This call is necesary to set the OUT variables
                return $resarr;
            
        }
    }

}

The stored Procedure sample structure we are using here is

Code:

ALTER PROCEDURE [dbo].[TestSP]
-- Add the parameters for the stored procedure here
@Pin1 int,
@Pout decimal(5,2) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT OFF;

    -- Insert statements for procedure here
SELECT @Pout=23456789/@Pin1 FROM OS;

END;

In the controller class you can send the IN, OUT variables like this

Code:

public function index2()
                {
                    $dbname=2;
                    $outp=0.0;
                    $params = array(
                        array($dbname, SQLSRV_PARAM_IN),
                        array($outp,SQLSRV_PARAM_OUT)
                    );      
                    $result["sprdata"]=$this->SPTest->sqlsrv_runSP("TestSP",$params);
                    $result["sprdata"]=array($dbname,$outp);
                    $this->load->view('sptest',$result);
                }

The declaration of variables before passing into the function is necessary as this takes care of
type issues (float int..) in case of numbers.

The view just dumps the variable like this

echo var_dump($sprdata);

I hunted a long way to get this to work as the mssql did not work with my WAMP installation due to missing dll. Hope this info helps someone who is stuck with this issue.

[El Forum]

[eluser]mariepizzer[/eluser]
Hello, your code has been really useful. Thank you so much!


[quote author="vinuf666" date="1372328663"]I thought I'll share this piece of code that I made for the MS SQL stored procedures if anyone uses 'sqlsrv' as the driver platform instead of mssql in codeIgniter

sqlsrv is quite buggy but is the latest Microsoft SQL server driver for php.
[/quote]