Globally Set Codeigniter $this->queries?

Question

I am working with a CI site, and MS SQL database, and CRUD operations are requiring me to run a few queries before the operations are actually run.

$this->db->query('SET ANSI_NULLS ON;');
$this->db->query('SET QUOTED_IDENTIFIER ON;');
$this->db->query('SET CONCAT_NULL_YIELDS_NULL ON;'); 
$this->db->query('SET ANSI_WARNINGS ON;');
$this->db->query('SET ANSI_PADDING ON;');       

I'm wondering if there is any where I can plant these so that they always run?

Right now, I'm putting them in my models __construct()

Answer 1

You can create a custom model class that you can then extend in the models.

application/core/MY_Model.php

class MY_Model extends CI_Model{

    function __construct(){
        parent::__construct();
        $this->db->query('SET ANSI_NULLS ON;');
        $this->db->query('SET QUOTED_IDENTIFIER ON;');
        $this->db->query('SET CONCAT_NULL_YIELDS_NULL ON;'); 
        $this->db->query('SET ANSI_WARNINGS ON;');
        $this->db->query('SET ANSI_PADDING ON;');
    }
}

application/models/some_model.php

class Some_model extends MY_Model{

    function __construct(){
        parent::__construct();
    }

    function some_function(){
        //interact with db
    }
}

Answer 2

You can create a database driver extension and place these queries inside a overridden query method. You will first have to create a loader extension to allow for this though, as codeigniter doesn't check for custom DB_drivers on load. Something like this:

Create a file in application/core/MY_Loader.php

<?php

class MY_Loader extends CI_Loader {
/**
     * Database Loader
     *
     * @access    public
     * @param    string    the DB credentials
     * @param    bool    whether to return the DB object
     * @param    bool    whether to enable active record (this allows us to override the config setting)
     * @return    object
     */
    function database($params = '', $return = FALSE, $active_record = FALSE)
    {
            // Do we even need to load the database class?
            if (class_exists('CI_DB') AND $return == FALSE AND $active_record == FALSE)
            {
            return FALSE;
            }

            require_once(BASEPATH.'database/DB'.EXT);

            // Load the DB class
            $db =& DB($params, $active_record);

            $my_driver = config_item('subclass_prefix').'DB_'.$db->dbdriver.'_driver';
            $my_driver_file = APPPATH.'libraries/'.$my_driver.EXT;

            if (file_exists($my_driver_file))
            {
                require_once($my_driver_file);
                $db =& new $my_driver(get_object_vars($db));
            }

            if ($return === TRUE)
            {
                return $db;
            }
            // Grab the super object
            $CI =& get_instance();

            // Initialize the db variable.  Needed to prevent
            // reference errors with some configurations
            $CI->db = '';
            $CI->db = $db;
            // Assign the DB object to any existing models
            $this->_ci_assign_to_models();
    }
}

Create another file in application/core/MY_DB_mssql_driver :

<?php
class MY_DB_mssql_driver extends CI_DB_mssql_driver {

  function __construct($params){
    parent::__construct($params);
    log_message('debug', 'Extended DB driver class instantiated!');
  }

  function query($sql, $binds = FALSE, $return_object = TRUE){
     parent::query('SET ANSI_NULLS ON;');
     parent::query('SET QUOTED_IDENTIFIER ON;');
     parent::query('SET CONCAT_NULL_YIELDS_NULL ON;'); 
     parent::query('SET ANSI_WARNINGS ON;');
     parent::query('SET ANSI_PADDING ON;');

     return parent::query($sql, $binds, $return_object); 
  }

}

now everytime you use $this->db->query it will call these queries. However if you don't want to do it on every query call then instead of overriding the query method, override the initialize method like:

function initialize(){
     if (parent::initialize() === TRUE) {
         parent::query('SET ANSI_NULLS ON;');
         parent::query('SET QUOTED_IDENTIFIER ON;');
         parent::query('SET CONCAT_NULL_YIELDS_NULL ON;'); 
         parent::query('SET ANSI_WARNINGS ON;');
         parent::query('SET ANSI_PADDING ON;');
     }
}