Kryptronic Software Support Forum

You are viewing this forum as a guest. Login to an existing account, or create a new account, to reply to topics and to create new topics.

#1 05-08-2021 08:56:09

zanart
Member
From: bedford
Registered: 04-02-2008
Posts: 1706

Alternate Database Connection

Hi Nick

I have set up an alternate database connection in the software.

Is there any straightforward ways to tell the software I want the functions to use the alternate database connection.

For example, using functions such as $this->CORE_DB->sql_do for the alternate database.

I know when the CORE_DB is loaded, it includes 'CORE_DB_1'.
Does that mean I can use $this->CORE_DB_1->... for the alternate database??

Thanks
Rob


Rob

Offline

 

#2 05-10-2021 08:02:42

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19798
Website

Re: Alternate Database Connection

The software by default uses the primary database connection.  Assuming you have set up an alternate connection, and you want to use that in a web page in K9 to pull data from a table in a different database, you'd add code like this to your web page:

Code:

<?php 

// +--
// | Load objects we'll need and set the id from core_altrdbms for the 
// | alternate connection.
// +--

$this->CORE_DB    =& $this->quick_object('CORE_DB','core','CORE_DB_1');
$this->CORE_Crypt =& $this->quick_object('CORE_Crypt','core');

$dsnid = 'ALTERNATE_DB_ID';

// +--
// | Pull the alternate db info from the core_altrdbms table.
// +--

$sql  = 'SELECT dbtype, hostname, dbname, username, dbpassword FROM core_altrdbms WHERE id=" . $this->CORE_DB->quote($dsnid);

$result = $this->CORE_DB->sql_do(array('sql'   => $sql, 'table' => 'core_altrdbms', 'order' => array('id' => 'ASC')));

// +--
// | Construct the DSN for the alternate connection.
// +--

$dsn             = $result[0];
$dsn['password'] = $this->CORE_Crypt->decrypt($dsn['dbpassword']);

unset($result[0]['dbpassword']);

// +--
// | Connect to the alternate database.
// +--

$this->CORE_DBX =& $this->load_object(array('app' => 'core', 'class' => 'CORE_DB', 'version' => '9.0.0', 'name' => 'CORE_DB_2', 'construct' => $dsn));

$this->CORE_DBX->connect();

// +--
// | Do something.
// +--

$sql = 'SELECT * FROM tablename';

$result = $this->CORE_DBX->sql_do(array('sql'   => $sql, 'table' => 'tablename', 'order' => array('id' => 'ASC')));

foreach ($result as $num => $data) {}

// +--
// | Disconnect from the alternate database.
// +--

$this->CORE_DBX->disconnect();

$this->destroy_object('CORE_DB_2');

?>

Nick Hendler

Offline

 

#3 05-11-2021 01:44:10

zanart
Member
From: bedford
Registered: 04-02-2008
Posts: 1706

Re: Alternate Database Connection

Perfect.
Order tables are now moved to different database via daily cron job.
Alternate database is only accessed when running backend finance reports and backend order search.
Account holders can opt to search orders older than 12 months in the account history.
Thank you


Rob

Offline

 

#4 05-11-2021 08:20:09

webmaster
Administrator
From: York, PA
Registered: 04-20-2001
Posts: 19798
Website

Re: Alternate Database Connection

Perfect.  Sounds exactly like you coded in parts of 9.1.0's archiving system on your own.  Good work.


Nick Hendler

Offline

 

Board footer