How to connect to a User DSN using codeigniter?

Question

I am able to connect to a User DSN on my linux box (using ODBC).

How do I get to connect using code igniter?

in my database.php I have the default database:

$active_group = 'default';
$active_record = TRUE;

$db['default']['hostname'] = 'localhost';
$db['default']['username'] = 'user';
$db['default']['password'] = 'pass';
$db['default']['database'] = 'db_main';
$db['default']['dbdriver'] = 'mysql';
$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;

however I also need to simultaneously connect to another database using ODBC:

So I have in my controller:

public function index()
    { 

         $dsn = 'NZSQL://someuser:password@host/somedatabase';
         $this->load->database($dsn);
         $this->db->query("SELECT * FROM Cust Limit 10");
         $result = $this->db->result();
    }

I get the error:

A Database Error Occurred

Error Number: 1146

Table 'db_main.Cust' doesn't exist

SELECT * FROM Cust Limit 10

Filename: /var/www/controllers/netezzatest.php

Line Number: 28

Answer 1

When connecting to the 2nd database, you need to have it return you the object, so you can use it. Passing true as the 2nd parameter to load->database returns you a db object.

$dsn = 'NZSQL://someuser:password@host/somedatabase';
$dsnDB = $this->load->database($dsn, TRUE);
$query = $dsnDB->query("SELECT * FROM Cust Limit 10");
$result = $query>result();

In this case, $this->db is your mysql DB, and $dsnDB is the other one.

Answer 2

For each DB that you're connecting to, Code Igniter will require you to build a new database object. You should build each DB out fully in the config file, then reference them by name (array key) when calling $this->load->database().

See Connecting to Multiple Databases for specifics. But, basically, you need to use this to connect (creating an object for each DB):

$DB1 = $this->load->database('group_one', TRUE);
$DB2 = $this->load->database('group_two', TRUE);

Then this to run queries on each:

$DB1->query();
$DB1->result();

And:

$DB2->query();
$DB2->result();