Forums / Developer / Using external db in extension causes problems

Using external db in extension causes problems

« 
Previous topic
|
Developer
|
Next topic
 »
Author Message

Atle Pedersen

Friday 15 September 2006 12:13:43 am

Hello.

I'm writing an extension where I'm fetching some data from en external database. The problem is that when my function returns, further mysql function calls will be towards this external database. This seems to confuse the rest of eZ publish, which reports several mysql query errors.

When calling mysql_connect(...) a handler is returned. The kind of solution I would prefer was to have a mysql function that returned a copy of the handler to the active database, and another function which used this handler to set the active database again. I would expect to find this in PHP, but can't seem to. Is there no such thing, or is it me that is just blind?

Given that the above solution is not possible, is there some mechanism, a function call, in eZ publish, that will reassociate the mysql function calls with the correct ez publish db?

Any suggestions?

Atle

Xavier Dutoit

Friday 15 September 2006 12:46:46 am

Hi,

I've done it with several dbs and it works (tried it from cronjobs and datatype and modules).

What I do is to close the connection on the external db

      mysql_free_result($result);
      mysql_close($this->_connectionID);

Does it solve your problem ?

X+

http://www.sydesy.com

Atle Pedersen

Friday 15 September 2006 2:47:14 am

Only closing the link/handler didn't seem to be enough in my case. What did seem to do the trick, though, was always to include the handler in every mysql function call regarding the database. It appears to me it's the mysql_select_db(...) that changes database currently in use, and thus confuses eZ. Adding the link resource here solved the problem for me.

Thanks
Atle

Edit: Actually I was a bit premature there. The problem persists. The only thing that seems to work is to make a new call to mysql_select_db(...) with the original database name. Which means I have to start reading ini files... This work when the databases are on the same server, but I'm not sure how this sollution would work if one database is on an external server.

Łukasz Serwatka

Friday 15 September 2006 9:40:40 am

You can connect to external DB using eZDB lib.

Here is an example:

include_once( 'lib/ezdb/classes/ezdb.php' );

$dsn = array(
	'server' 	=> 'localhost',
	'user' 		=> 'user',
	'password'	=> 'mypass',
	'database'	=>	'mydb',
	'show_errors' => true
	);

$extdb =& eZDB::instance( 'ezmysql', $dsn, true  );
$extdb->arrayQuery("SELECT * FROM EXT_TABLE");

Then using eZDB lib you may query your external DB without errors. Witn new instance you can still get access to eZ publish current db.

    include_once( 'lib/ezdb/classes/ezdb.php' );
    $db =& eZDB::instance();

See lib/ezdb/classes/ezdb.php for more details.

Personal website -> http://serwatka.net
Blog (about eZ Publish) -> http://serwatka.net/blog

Atle Pedersen

Monday 18 September 2006 7:34:47 am

Thanks Lukasz,

getting the information about the current db instance solves my probleml. Now it's easy resetting the handler in a generic way, so that eZ doesn't get confused.

Atle

Xavier Dutoit

Monday 18 September 2006 1:24:20 pm

Hi Atte,

Not sure I got you. You mean that you added 

   include_once( 'lib/ezdb/classes/ezdb.php' );
   $db =& eZDB::instance();

After your code and it works fine ?

X+

http://www.sydesy.com

Kristof Coomans

Saturday 28 October 2006 6:55:48 am

The third parameter in $extdb =& eZDB::instance( 'ezmysql', $dsn, true ); is important, it forces the creation of a new instance instead of returning the eZ publish database instance cached in $GLOBALS['eZDBGlobalInstance'];

independent eZ Publish developer and service provider | http://blog.coomanskristof.be | http://ezpedia.org

Atle Pedersen

Thursday 24 May 2007 6:22:33 am

Revisiting an old post.

The problem is that what I'm working with is an external system using its own database mechanisms and calls. So Lukasz Serwatka's solution above is not feasible without rewriting parts of the other system.

When the external code calls

    mysql_close();

this seems to disrupt eZ publish.

I thought I solved it using

mysql_select_db('ezdatabase');

after the other code.

However, when the external code is called more than once for a template, this also fails.

So now, based on rereading the discussion above, I've worked out the following sollution:

    $dsn = array(
             'server'        => $GLOBALS['eZDBGlobalInstance']->Server,
             'user'          => $GLOBALS['eZDBGlobalInstance']->User,
             'password'      => $GLOBALS['eZDBGlobalInstance']->Password,
             'database'      => $GLOBALS['eZDBGlobalInstance']->DB,
             'show_errors' => $GLOBALS['eZDBGlobalInstance']->RecordError
    );

//..... other code including mysql_close(); does its thing here .....

    eZDB::instance( 'ezmysql', $dsn, true );

It feels sort of hackish, and I'm not sure about using the 'RecordError' variable, but it seems to work.

Any comments or suggestions for better ways of doing this?