Set multiple connections for mssql dbs?

Set multiple connections for mssql dbs? - 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: Set multiple connections for mssql dbs? (/showthread.php?tid=43422)

Set multiple connections for mssql dbs? - El Forum - 07-11-2011

[eluser]ArthurHacker[/eluser]
I can't even set multiple connections for MSSQL Server. I've found an article on this forum where had been described how to set multiple connections, but it didn't work well for me - the mssql_query statement throws an error that the link identifier is not the MSSQL-Link resource etc. I've done something like this: 1) set the database.php - database configuration file:

Code:
$active_group = 'suburb';

$active_record = FALSE;

$db['suburb']['hostname'] = 'XXXXXXXX';

$db['suburb']['username'] = 'XXXX';

$db['suburb']['password'] = 'XXXXXXX';

$db['suburb']['database'] = 'XXXXXXX';

$db['suburb']['dbdriver'] = 'mssql';

$db['suburb']['dbprefix'] = '';

$db['suburb']['pconnect'] = TRUE;

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

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

$db['suburb']['cachedir'] = '';

$db['suburb']['char_set'] = 'utf8';

$db['suburb']['dbcollat'] = 'utf8_general_ci';

$db['suburb']['swap_pre'] = '';

$db['suburb']['autoinit'] = TRUE;

$db['suburb']['stricton'] = FALSE;

$active_group = 'default';

$active_record = FALSE;

$db['default']['hostname'] = 'XXXXXX';

$db['default']['username'] = 'XXXX';

$db['default']['password'] = 'XXXXXXXX';

$db['default']['database'] = 'XXXXXX';

$db['default']['dbdriver'] = 'mssql';

$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;

2) call the load dbs function:

Code:
$this->suburbDb = $this->load->database('suburb', true);

$this->load->database('default', true);

Note: if I try to set default db 1-st then it crashes.
3) created the query:

Code:
$res = mssql_query(" SELECT TOP 100 ID FROM Table", $this->suburbDb);

The CI error message is:
Message: mssql_query(): supplied argument is not a valid MS SQL-Link resource

So what is the problem? Please help.

Set multiple connections for mssql dbs? - El Forum - 07-11-2011

[eluser]salfordscripteR[/eluser]
replace:

Code:
$active_group = 'suburb';

$active_record = FALSE;

$db['suburb']['hostname'] = 'XXXXXXXX';

$db['suburb']['username'] = 'XXXX';

$db['suburb']['password'] = 'XXXXXXX';

$db['suburb']['database'] = 'XXXXXXX';

$db['suburb']['dbdriver'] = 'mssql';

$db['suburb']['dbprefix'] = '';

$db['suburb']['pconnect'] = TRUE;

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

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

$db['suburb']['cachedir'] = '';

$db['suburb']['char_set'] = 'utf8';

$db['suburb']['dbcollat'] = 'utf8_general_ci';

$db['suburb']['swap_pre'] = '';

$db['suburb']['autoinit'] = TRUE;

$db['suburb']['stricton'] = FALSE;

$active_group = 'default';

$active_record = FALSE;

$db['default']['hostname'] = 'XXXXXX';

$db['default']['username'] = 'XXXX';

$db['default']['password'] = 'XXXXXXXX';

$db['default']['database'] = 'XXXXXX';

$db['default']['dbdriver'] = 'mssql';

$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;

with this:

Code:
$active_group = 'suburb';

$active_record = FALSE;

$db['suburb']['hostname'] = 'XXXXXXXX';

$db['suburb']['username'] = 'XXXX';

$db['suburb']['password'] = 'XXXXXXX';

$db['suburb']['database'] = 'XXXXXXX';

$db['suburb']['dbdriver'] = 'mssql';

$db['suburb']['dbprefix'] = '';

$db['suburb']['pconnect'] = TRUE;

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

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

$db['suburb']['cachedir'] = '';

$db['suburb']['char_set'] = 'utf8';

$db['suburb']['dbcollat'] = 'utf8_general_ci';

$db['suburb']['swap_pre'] = '';

$db['suburb']['autoinit'] = TRUE;

$db['suburb']['stricton'] = FALSE;

$db['default']['hostname'] = 'XXXXXX';

$db['default']['username'] = 'XXXX';

$db['default']['password'] = 'XXXXXXXX';

$db['default']['database'] = 'XXXXXX';

$db['default']['dbdriver'] = 'mssql';

$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;

Set multiple connections for mssql dbs? - El Forum - 07-11-2011

[eluser]danmontgomery[/eluser]
No...

You have a few problems here, none of them are the inclusion of extra $active_group/$active_record lines.

First, Passing TRUE as the second parameter in $this->load->database() returns the object, rather than assigning it. You're doing this correctly in your first load statement, but not in your second.

Second, What is returned is an object, not an mssql link id. You should never be using mssql_query() in CI. You should be using the DB object you've loaded for yourself.

Code:
$this->suburbDb = $this->load->database('suburb', TRUE);

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

$res = $this->suburbDb->query('SELECT TOP 100 ID FROM Table');

Connecting to multiple databases is explained (with examples) in the user guide: http://ellislab.com/codeigniter/user-guide/database/connecting.html, as is running queries and generating query results.