MSSQL Database Switching - Printable Version

MSSQL Database Switching - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MSSQL Database Switching (/showthread.php?tid=26157)

MSSQL Database Switching - El Forum - 01-07-2010

[eluser]whobutsb[/eluser]
I've been working with CI and MSSQL Databases for over a year now, and for the most part they seem to work fine together. The one snag I find is the switching between Databases. When I write my applications I usually have my primary DB that I set to the $active_group variable in the database.php config file.

When I write my queries off of this database I use:

Code:
function select_user($username){

        $this->db->select('*'); 

        $this->db->where('username', $username); 

        $query = $this->db->get('tblHFUser');

        return $query->row(); 

    }

But when I switch Databases I always load the database in the function of the model like so:

Code:
function select_businessunits(){

            $NHCGlobal = $this->load->database('NHCGlobal', TRUE); 

            $NHCGlobal->select('*'); 

            $NHCGlobal->order_by('longBU'); 

            $NHCGlobal->where('HarborForce', TRUE); 

            $query = $NHCGlobal->get('tblBusinessUnit'); 

            return $query->result();  

    }

When the application runs, It will sometimes switch back and forth between 5-10 different databases depending on what information the application needs to acquire. Every once and a while the application won't switch to the correct database, mainly the $active_group database, and try running a query on the wrong database, which will throw an error. I figured this out by running the SQL Profiler tool and saw that the CI didn't call the [USE 'Database Name'] command to switch to the correct database.

Is there any settings (pconnect?) I can change in my Database config file or how I call the other (non active group) databases so that it will always use the correct database.

Here is an example of one my Database configs, they are all exactly the same.

Code:
$db['NHCGlobal']['hostname'] = "Apollo";

$db['NHCGlobal']['username'] = "**********";

$db['NHCGlobal']['password'] = "**********";

$db['NHCGlobal']['database'] = "NHCGlobal";

$db['NHCGlobal']['dbdriver'] = "mssql";

$db['NHCGlobal']['dbprefix'] = "";

$db['NHCGlobal']['pconnect'] = FALSE;

$db['NHCGlobal']['db_debug'] = TRUE;

$db['NHCGlobal']['cache_on'] = FALSE;

$db['NHCGlobal']['cachedir'] = "";

$db['NHCGlobal']['char_set'] = "utf8";

$db['NHCGlobal']['dbcollat'] = "utf8_general_ci";

MSSQL Database Switching - El Forum - 01-07-2010

[eluser]rogierb[/eluser]
Why switch at all?

I create methods that can use multiple databases like

Code:
public function some_model_method($database, $var1)

{

  $database->where('varr', $var1)

  return $database->get('some_table')

}

I have a library method that loads databases on the fly when needed. It stores a reference of a database in a constant(of some sort). If the constant does not exsist, it loads the database.

MSSQL Database Switching - El Forum - 01-08-2010

[eluser]whobutsb[/eluser]
I need to switch because I need to grab data from different databases. I find sometimes the issue is if i'm looking on the same server in different databases the, the 'used' database in MSSQL is the wrong one, and CI will be running a query in the wrong database.

Do you have an example of the library that you are using to the load/switch the databases? I would be interested in seeing what it does.

Thanks,
Steve