Re: J2.5 Problems connecting to a different database

[piotr_cz]

Hi,
I've compared your code to mine, where I'm doing same thing and it
seems 100% correct (also on Joomla 2.5) so I'd say the problem is
somewhere else.
Maybe try increasing timeout $options['timeout'], check if such user
really exists in the other DB and has correct priviledges or if the
other DB is configured correctly (if you can access it at all) or you
could try to print_r the $db variable for more information..

BTW: did it work on 1.5?



On Oct 30, 3:34 pm, Matteo Giannone <teoko...@gmail.com> wrote:
> Hi guys,
> I'm migrating a component from j1.5 to j2.5 and I'm having problems getting data from a different database on the same server where joomla is running.
>
> This is the code I'm using, working in j1.5 and taken fromhttp://docs.joomla.org/Connecting_to_an_external_database
>
> <?php
> $option = array(); //prevent problems
> $option['driver']   = 'mysqli';            // Database driver name
> $option['host']     = '127.0.0.1';    // Database host name
> $option['user']     = 'myuser';       // User for database authentication
> $option['password'] = 'xxx';   // Password for database authentication
> $option['database'] = 'DIFFERENT_DATABASE';      // Database name
> $option['prefix']   = '';             // Database prefix (may be empty)
>
>         $db = & JDatabase::getInstance( $option );
>         $query = $db->getQuery(true);
>         $query->select('id, name');
>         $query->from('mytable');
>         return $query;
> ?>
>
> The error I get is:
> Table 'ORIGINAL_DATABASE.mytable' doesn't exist SQL=SELECT id, name FROM mytable
>
> It seems $option array is completely ignored, if I change $option['host'] to a dummy server, I still get a connection to the server database and the same error....
>
> I also tried localhost, localhost:3306, 127.0.0.1:3306 and some other trick but I am still not able to access DIFFERENT_DATABASE.
>
> Thanks in advance.
>
> Matteo Giannone

[Sam Moffatt]

MySQLi could be re-using the same database connection internally which
when you swap databases causes an issue. This was fixed in the MySQL
driver with the new link flag being set on connect but MySQLi doesn't
have that. Not sure if that feature is still there but it might be
worth investigating. Also check if you have persistent connections set
somewhere in the PHP configuration.

Try swapping the driver to MySQL and see if it makes a difference.
Additionally try swapping the core to MySQL and using MySQLi and see
if that changes anything.

It may also pay to double check that for some reason you're not
getting a same copy of the database back but I don't think that would
be the case. Doesn't harm to check. Perhaps var_dump it and see what
it says it's doing and compare that to the normal one.

The only other thing I can think of it is that your server's php.ini
file has some database settings in it that are overriding the
connection. I'm a little hazy on how that all interacts with the
settings but that's also another possibility as an extreme edge case.

Also, the code sample you provided is incomplete. In the fragment you
return the query but don't execute it. Are you sure you're not
providing the query to the original database connection anyway? This
works fine for me:

<?php

<?php
$option = array(); //prevent problems
$option['driver'] = 'mysqli'; // Database driver name
$option['host'] = '127.0.0.1'; // Database host name
$option['user'] = 'myuser'; // User for database authentication

$option['password'] = 'xyz'; // Password for database authentication

$option['database'] = 'DIFFERENT_DATABASE'; // Database name
$option['prefix'] = ''; // Database prefix (may be empty)

$db = & JDatabase::getInstance( $option );
$query = $db->getQuery(true);

$query->select('*');
$query->from('articles');

$db->setQuery($query);
var_dump($db->loadObjectList());
?>


Cheers,

Sam Moffatt
http://pasamio.id.au

> --
> You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
> To post to this group, send an email to joomla-de...@googlegroups.com.
> To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.
>

[elin]

Make sure you are using the right quoting.

Elin

> To unsubscribe from this group, send email to joomla-dev-general+unsub...@googlegroups.com.

[Teo]

The problem seems to be related to JModelList which I'm extending in my model.


The only solution I found is overriding the constructor like this:


class MycomponentModelMycomponent extends JModelList
{
public function __construct($config = array())
{

$option = array(); //prevent problems
$option['driver'] = 'mysqli'; // Database driver name
$option['host'] = '127.0.0.1'; // Database host name
$option['user'] = 'myuser'; // User for database authentication
$option['password'] = 'xxx'; // Password for database authentication
$option['database'] = 'DIFFERENT_DATABASE'; // Database name
$option['prefix'] = ''; // Database prefix (may be empty)

$db = & JDatabase::getInstance( $option );

parent::__construct($config);
parent::setDbo(&$db);
}


If I don't override the constructor, my component try to use the Joomla database.

Hope it helps someone else...

Matteo Giannone

[Prasit Gebsaap]

Just call parent::__construct() like this;

$db = JDatabase::getInstance($option);

$config['dbo'] = $db;

parent::__construct($config);

This way your model will use your own database connection just in one call. It is simple to read and understand.

--
You received this message because you are subscribed to the Google Groups "Joomla! General Development" group.
To post to this group, send an email to joomla-de...@googlegroups.com.

To unsubscribe from this group, send email to joomla-dev-gene...@googlegroups.com.

For more options, visit this group at http://groups.google.com/group/joomla-dev-general?hl=en-GB.

--

Prasit Gebsaap

 

Nonthaburi, Thailand

http://www.joomlant.com, http://www.joomlant.org

http://ktdms.blogspot.com, http://lowcost-it.blogspot,com

 

[Prasit Gebsaap]

Here is one of my constructor, I store external database parameters in Joomla 's database.

    public function __construct($config = array()) 

    {

        if (empty($config['filter_fields'])) {

            $config['filter_fields'] = array(

                'id','e.id',

                'name','e.name',

            'context',

            'unit_id', 'status',

                'state', 'e.state',

                'ipaddr', 'e.ipaddr',

                'context', 'e.context'

            );

        }

        

        $server_id = AstermanBackendHelper::getServerId();

        if (!empty($server_id)) {

            JTable::addIncludePath(JPATH_COMPONENT_ADMINISTRATOR . '/tables');

            $asteriskServer = JTable::getInstance('Server', 'AstermanTable');  

            $asteriskServer->load($server_id);

            

            $options = array (

                'driver' => 'mysql', 

                'host' => $asteriskServer->get('db_host'), 

                'user' => $asteriskServer->get('db_username'), 

                'password' => $asteriskServer->get('db_password'), 

                'database' => $asteriskServer->get('db_name'), 

                'prefix' => $asteriskServer->get('db_table_prefix'));

            $dbo = JDatabase::getInstance($options);

            $config['dbo'] = $dbo;

        }else {

            JError::raiseError('500', 'Cannot get server id, please contact administrator');

            return false;

        }

        parent::__construct($config);

        $this->setState('filter.server_id', $server_id);

        $this->setState('server', $asteriskServer);    

    }