CodeCharge Studio
search Register Login  

Web Reports

Visually create Web Reports in PHP, ASP, .NET, Java, Perl and ColdFusion.
CodeCharge.com

YesSoftware Forums -> CodeCharge Studio -> PHP

 Changing databases dynamically (SOLVED)

Print topic Send  topic

Author Message
saseow

Posts: 744
Posted: 08/14/2013, 12:40 AM

There was a solution to this but it apparently does not work anymore: http://forums.codecharge.com/posts.php?post_id=71096

I have a situation where a single site is used by different branches of a company and each branch has it's own database. The users table in all databases is identical so....
All users will log into the head office database but I then want to change the connection so that they will be working in the database belonging to their specific branch.

You cannot place a switch in the Common.php because it gets overwritten when generated so the question is:
How can I change the database connection after a user has logged in?
Any ideas will be most welcome!!!
View profile  Send private message
laneoc

Posts: 154
Posted: 08/14/2013, 12:02 PM

One approach...

Add some code to the bottom of common.php which sets the value for the name of the database.

  
if (ccGetSession("UserID","") == "Joe")  
{   $db = "joedb";  }  
else  
{   $db = "bobdb";  }  
$CCConnectionSettings["yourConnectionName"]["Database"] = $db;  


_________________
Lane
View profile  Send private message
saseow

Posts: 744
Posted: 08/14/2013, 5:53 PM

Hello Ian and thank you for the reply.
I am not sure what you mean with this line: $CCConnectionSettings["yourConnectionName"]["Database"] = $db;

The CCConnectionSettings is an array. Could you elaborate a little?
Thanks,
Trevor
View profile  Send private message
laneoc

Posts: 154
Posted: 08/14/2013, 6:23 PM

Hi Trevor,

You've seen this section of common.php:

  
$CCConnectionSettings = array (      
    "cms" => array(      
        "Type" => "MySQL",      
        "Database" => "$_DB_NAME",      
        "Host" => "$_DB_HOST",      
        "Port" => "$_DB_PORT",      
        "User" => "$_DB_USER",      
        "Password" => "$_DB_PASSWORD",      
        "Encoding" => array("", "utf8"),      
        "Persistent" => true,      
        "DateFormat" => array("yyyy", "-", "mm", "-", "dd", " ", "HH", ":", "nn", ":", "ss"),      
        "BooleanFormat" => array(1, 0, ""),      
        "Uppercase" => false      
    )      
);    

I copied this from the link to which you referred above. CCS 5 has additional elements in the array.

Leave that section alone and add some code at the bottom of the common.php file where it will not be overwritten when you rebuild.

In that code, determine the database name with whatever your rules are. My sample rule checked if the user was Joe. Then set the database name by resetting the value of $CCConnectionSettings["yourConnectionName"]["Database"]


_________________
Lane
View profile  Send private message
saseow

Posts: 744
Posted: 08/14/2013, 9:04 PM

Thanks Ian.
OK, I will give at a try and see what happens. I will post the result back here.
View profile  Send private message
saseow

Posts: 744
Posted: 08/15/2013, 12:35 AM

Nope, this does not work.
The problem is that you cannot seem to get a database connection in the Common.php file in order to see which branch the user works for. You get this error:

Fatal error: Class 'DB_' not found in C:\php webs\ns\db_adapter.php on line 61.

I have tried it using a php_config file and including that at the bottom of the Common.php with the same result.

This is just so damn tricky!!
View profile  Send private message
rho


Posts: 85
Posted: 08/15/2013, 1:20 AM

Hi,

changing the database at runtime can be done easily. The way I do it is to create a general database (in this case MySQL) connection in CodeCharge (for example: DynamicMySQL) and then change it's properties when needed.

To create a new custom database connection in my code, I use:

$db = CreateConnectionMySQL($sDatabase, $sHost, $sPort, $sUser, $sPassword);

function CreateConnectionMySQL($sDatabase, $sHost, $sPort, $sUser, $sPassword) {
$db = new clsDBDynamicMySQL;
$db->DBDatabase = $sDatabase;
$db->DBHost = $sHost;
$db->DBPort = $sPort;
$db->DBUser = $sUser;
$db->DBPassword = $sPassword;
return $db;
}

To set the connection of a f.e. a grid-component at runtime I use the follwing function in the BeforeSelect of the grid:

SetComponentConnectionMySQL($sender, $sDatabase, $sHost, $sPort, $sUser, $sPassword);

function SetComponentConnectionMySQL($sender, $sDatabase, $sHost, $sPort, $sUser, $sPassword) {
// Usage: change the connection of a control at runtime, eg. use in grid.BeforeSelect()
$sender->DataSource->DBHost = $sHost;
$sender->DataSource->DBPort = $sPort;
$sender->DataSource->DBDatabase = $sDatabase;
$sender->DataSource->DBUser = $sUser;
$sender->DataSource->DBPassword = $sPassword;
return true;
}

Of course these functions can be extended if you want to set additional database parameters. For reference: have a look at the connection-array generated by CodeCharge in Common.php.

Hope this helps, Rob.
View profile  Send private message
saseow

Posts: 744
Posted: 08/15/2013, 1:29 AM

Thanks so much for this Rob. The hassle has been that the database must be changed globally and I do not want to put code in for each grid etc. as it is a huge site.

I think I have it working now with Ian's suggestion but have to test a bit more. I will post results here when done.

Thanks again Rob!
Trevor
View profile  Send private message
saseow

Posts: 744
Posted: 08/15/2013, 2:09 AM

Finally! All working as expected using Ian's suggestion.

Thank you both very much!
View profile  Send private message

Add new topic Subscribe to topic   


These are Community Forums for users to exchange information.
If you would like to obtain technical product help please visit http://support.yessoftware.com.

Web Database

Join thousands of Web developers who build Web applications with minimal coding.
CodeCharge.com

Home   |    Search   |    Members   |    Register   |    Login


Powered by UltraApps Forum created with CodeCharge Studio
Copyright © 2003-2004 by UltraApps.com  and YesSoftware, Inc.