How do I open a second odbc connection?

[AndyHolyer]

I need to set up some CMS pages which fetch and update data from a remote database (but which for now is running on the same database server (which for political reasons is MS SQL 2000 - don't throw tomatoes, I know, I know).

For now, I'm just putting a {php}{/php} tag into my page, since I'm just going for a quick proof-of-concept. I'm doing:

$sar =&  ADONewConnection('mssql', 'pearextend:transaction');

$connect_result = $sar->Connect('(server)', 'SAR_user', 'goodbye', 'SAR');

if (FALSE == $connect_result)
{
die('Database Connection to SAR failed');
}

The SAR_user is distinct from the cms_user; however I'm finding that $config->GetDb(); is being changed to this new ADODB connection, so all the content links further down the page now fail. Has anyone seen this before? Is there a way to turn it off?

Many thanks in advance for any help you can provide.

[Dee]

Hmm, I tried the same in a UDT with a second MySQL connection (no mssql at my disposal), and that worked as expected:

Code: Select all

global $gCms;
$sar =&  ADONewConnection('mysql', 'pear:date:extend:transaction');

$connect_result = $sar->Connect('localhost', 'someuser', 'somepass', 'somedb'); // connected with valid info different from config.php
if (FALSE == $connect_result)
   {
      echo ('Database Connection to SAR failed');
   }
echo '<pre>';
print_r($gCms->GetDb());
echo '</pre>';

It printed out a pear_adoconnection Object with the username and password info from config.php (not someuser and somepass so to say).

Regards,
D

[renke]

i provide a telefonlist from another MSSQL-Database. i did this by folling user definited tag:

{telefonlist}

maybe it is helful for someone. i did not use the adodb.

the funktion convertUmlaute() does not work in the tag. if i use an iframe everythink works normal.
any solution to make it work as user definited tag?

Code: Select all

function convertUmlaute($text){
   //DIE UMLAUTE WERDEN KONVERTIERT  /////
   $pattern1="/ä/";
   $replace1="ä";
   $text=preg_replace($pattern1,$replace1, $text);
   $pattern2="/ö/";
   $replace2="ö";
   $text=preg_replace($pattern2,$replace2, $text);
   $pattern3="/ü/";
   $replace3="ü";
   $text=preg_replace($pattern3,$replace3, $text);
   $pattern1a="/Ä/";
   $replace1a="Ä";
   $text=preg_replace($pattern1a,$replace1a, $text);
   $pattern2a="/Ö/";
   $replace2a="Ö";
   $text=preg_replace($pattern2a,$replace2a, $text);
   $pattern3a="/Ü/";
   $replace3a="Ü";
   $text=preg_replace($pattern3a,$replace3a, $text);
   $pattern4="/ß/";
   $replace4="ß";
   $text=preg_replace($pattern4,$replace4, $text);
   return $text;
}

$myServer = "10.11.30.3";
$myUser = "sa";
$myPass = "sa";
$myDB = "Verwaltung";

$s = @mssql_connect($myServer, $myUser, $myPass)
or die("Couldn't connect to SQL Server on $myServer");

$d = @mssql_select_db($myDB, $s)
or die("Couldn't open database $myDB");

$query = "SELECT N_Name, N_Vorname, F_Txt, An_Hausruf, R_txt, G_txt, B_Txt, B_Langtext, D_Txt, An_Fernruf ";
$query .= "FROM AI_A_Orgplan ";
$query .= "WHERE B_Txt LIKE 'K%' ";
$query .= "ORDER BY D_Sort, D_Txt, B_Sort, B_Txt, F_Sort, F_Txt, N_Name, N_Vorname";

$result = mssql_query($query);
$numRows = mssql_num_rows($result);

echo "<table>";
echo "<tr>";

$strName=" ";
while($row = mssql_fetch_array($result))
{
	if ($strName==$row["B_Txt"]) 
	{
		echo "<tr>";
		echo "<td>" . convertUmlaute($row["F_Txt"]) . "</td>";
		echo "<td>" . convertUmlaute($row["N_Name"]) .', '. $row["N_Vorname"] . "</td>";
		echo "<td>" . convertUmlaute($row["An_Hausruf"]) . "</td>";
		echo "<td>" . convertUmlaute($row["R_txt"]) . "</td>";
		echo "<td>" . convertUmlaute($row["An_Fernruf"]) . "</td>";
		echo "</tr>";
	}
	else 
	{
		echo "<tr>";
		echo "<td> </td>";
		echo "<td> </td>";
		echo "<td> </td>";
		echo "<td> </td>";
		echo "<td> </td>";
		echo "</tr>";
		echo "<tr>";
		echo "<td> </td>";
		echo "<td> </td>";
		echo "<td> </td>";
		echo "<td> </td>";
		echo "<td> </td>";
		echo "</tr>";
		echo "<tr>";
		$strName=convertUmlaute($row["B_Txt"]);
		echo "<td><h3>" . convertUmlaute($row["B_Txt"]) . "</h3></td>";
		echo "<td><h3>" . convertUmlaute($row["B_Langtext"]) . "</h3></td>";
		echo "<td></td>";
		echo "<td></td>";
		echo "<td></td>";
		echo "</tr>";
		echo "<tr>";
		echo "<td><u>Funktion</u></td>";
		echo "<td><u>Name</u></td>";
		echo "<td><u>Hausruf</u></td>";
		echo "<td><u>Raum</u></td>";
		echo "<td><u>Fernruf</u></td>";
		echo "</tr>";
		echo "<tr>";
		echo "<td>" . convertUmlaute($row["F_Txt"]) . "</td>";
		echo "<td>" . convertUmlaute($row["N_Name"]) .', '. $row["N_Vorname"] . "</td>";
		echo "<td>" . convertUmlaute($row["An_Hausruf"]) . "</td>";
		echo "<td>" . convertUmlaute($row["R_txt"]) . "</td>";
		echo "<td>" . convertUmlaute($row["An_Fernruf"]) . "</td>";
		echo "</tr>";
	}
}

echo "</table>";

[renke]

if some must convert Umlaute like me here is the solution

Code: Select all

echo "<td>" . htmlentities($row['F_Txt']) . "</td>";

http://de.php.net/manual/en/function.htmlentities.php

[SMooTH]

I am also experiencing this issue.

I have attempted what Dee has posted, but it still does not work.
I will not argue that the connection to both databases does not work, however, when smarty is trying to display a global content, it does not work.

Can anyone assist with the code THEO was suggesting?

I have tried storing the config values in a varaibles, but not sure how to reset them after the db2 connection is finished.

Cheers.

[SMooTH]

Found Dee's code to work, however, had to change his Connect to NConnect.

Hope this helps.

Hmm, I tried the same in a UDT with a second MySQL connection (no mssql at my disposal), and that worked as expected:

Code: Select all

global $gCms;
$sar =&  ADONewConnection('mysql', 'pear:date:extend:transaction');

$connect_result = $sar->Connect('localhost', 'someuser', 'somepass', 'somedb'); // connected with valid info different from config.php
if (FALSE == $connect_result)
   {
      echo ('Database Connection to SAR failed');
   }
echo '<pre>';
print_r($gCms->GetDb());
echo '</pre>';

It printed out a pear_adoconnection Object with the username and password info from config.php (not someuser and somepass so to say).

Regards,
D

[irish]

I had the same issue, I hacked a few files in order to easily select any database. I have attached a doc. that explains it. It's not very pretty but it works well for me.

[cyberman]

Thanks for providing this document ...

[Connie]

I had the same issue, I hacked a few files in order to easily select any database. I have attached a doc. that explains it. It's not very pretty but it works well for me.

this looks interesting and I was pondering to use it, even edited the files already, but then I thought that this is not the best solution as you have to do this with every upgrade

I think this is a risk if you work for clients...

so I decided for myself that it is better to move the database tables from Database I to Database II ...

[RHF]

Connie,

The doc file you mentioned is apparently no longer available, and I wondered if you could explain what you meant by "move the database tables from Database I to Database II ..." I am guessing that you are suggesting to simply copy the second database tables into the CMSMS database and use it there. Is this what you meant?

I guess this is reasonable, but it sure would be nice to be able to connect directly to a different database.

Thanks