Page 1 of 1
How do I open a second odbc connection?
Posted: Wed May 02, 2007 1:41 pm
by 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', 'pear
extend: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.
Re: How do I open a second odbc connection?
Posted: Wed May 02, 2007 2:15 pm
by 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
Re: How do I open a second odbc connection?
Posted: Mon Jun 18, 2007 1:22 pm
by 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>";
Re: How do I open a second odbc connection?
Posted: Mon Jun 18, 2007 2:39 pm
by renke
if some must convert Umlaute like me here is the solution
Code: Select all
echo "<td>" . htmlentities($row['F_Txt']) . "</td>";
Re: How do I open a second odbc connection?
Posted: Tue Jul 01, 2008 2:26 am
by 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.
Re: How do I open a second odbc connection?
Posted: Tue Jul 01, 2008 2:51 am
by SMooTH
Found Dee's code to work, however, had to change his
Connect to
NConnect.
Hope this helps.
Dee wrote:
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
Re: How do I open a second odbc connection?
Posted: Mon Jul 07, 2008 4:22 pm
by 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.
Re: How do I open a second odbc connection?
Posted: Tue Jul 08, 2008 5:06 am
by cyberman
Thanks for providing this document ...
Re: How do I open a second odbc connection?
Posted: Tue Jun 09, 2009 6:22 pm
by Connie
irish wrote:
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 ...
Re: How do I open a second odbc connection?
Posted: Wed Jun 15, 2011 6:37 pm
by 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
extend:transaction');