Using a TNS Name in Connection String

Talk about working with different database types

Moderator: alexandrleonenko

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Using a TNS Name in Connection String

Postby dynsight » Jun 6th, '11, 20:39

I am trying to connect to oracle 10g running on a remote Windows 2003 server. I am hosting the website on a separate application server, running Windows 2003 and PHP 5.3.6

Code: Select all

 $db_conn = DB::factory('oci8');  /* don't forget to change on appropriate db type */
     // $result_conn = $db_conn->connect(DB::parseDSN('oci8://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
//out of the box
    //  $result_conn = oci_connect('user','pw', '//machineIP/schema');
//using simple connect
    $result_conn=oci_connect('user', 'password', 'tnsname');
//using TNS Name


I suspect that the format of my $result_conn is not correct. I would think that the format of the TNS name (last one) is the right method, BUT I suspec that I need to use $db_conn. I can successfully connect to the Database using SQL Plus using TNSNames. I just need to know the proper syntax for the $result_conn using this method.

Suggestions?

administrator
Site Admin
Site Admin
Posts: 6001
Joined: Jan 7th, '09, 23:18
Contact:

Re: Using a TNS Name in Connection String

Postby administrator » Jun 7th, '11, 08:04

## *** creating variables that we need for database connection
$DB_USER='name';
$DB_PASS='';
$DB_HOST='localhost';
$DB_NAME = "localhost:1521/dbName";

$db_conn = DB::factory('oci8');
$db_conn->connect(DB::parseDSN('oci8://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
if(DB::isError($result_conn)){ die($result_conn->getDebugInfo()); }

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Using a TNS Name in Connection String

Postby dynsight » Jun 7th, '11, 17:50

I know, but it doesn't seem to work...

Code: Select all

 ##  *** creating variables that we need for database connection

    // includes database connection parameters
    //include_once('file:///Macintosh HD/Library/WebServer/Documents/datagrid/examples/install/config.inc.php');
   
    $DB_USER='user';           
    $DB_PASS='pw';           
    $DB_HOST='dbhost';       
    $DB_NAME='dbhost:1521/dbname';
   
    //ip address is 170.115.111.84
      ob_start();
    ##  *** (example of ODBC connection string)
    ##  *** $result_conn = $db_conn->connect(DB::parseDSN('odbc://root:12345@test_db'));
    ##  *** (example of Oracle connection string)
    ##  *** $result_conn = $db_conn->connect(DB::parseDSN('oci8://root:12345@localhost:1521/mydatabase));
    ##  *** (example of PostgreSQL connection string)
    ##  *** $result_conn = $db_conn->connect(DB::parseDSN('pgsql://root:12345@localhost/mydatabase));
    ##  === (Examples of connections to other db types see in "docs/pear/" folder)
   
      $db_conn = DB::factory('oci8');  /* don't forget to change on appropriate db type */
      $result_conn = $db_conn->connect(DB::parseDSN('oci8://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
   


Using this I get the following error:
[nativecode=ORA-12154: TNS:could not resolve the connect identifier specified]

However, there is absolutely no problem with the TNS name... I am able to connect with SQL Plus using TNS names...

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Using a TNS Name in Connection String

Postby dynsight » Jun 7th, '11, 20:10

I am used to using the following connection string (TNS Based) and it works fine:

Code: Select all

<?php
$conn=oci_connect('user', 'password', 'tnsname');

if (!$conn) {
trigger_error("Could not connect to database", E_USER_ERROR);
}


?>


So I tried to incorporate the TNS Style connection string using your DB Object:

Code: Select all

$result_conn=oci_connect('user', 'password', 'tnsname');


And there is no error message.... but the resulting page is completely blank. I do have

Code: Select all

$debug_mode = true;
$messaging = true;


but the page is blank... Basically, I need to use the TNS connection string in your datagrid.

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Using a TNS Name in Connection String

Postby dynsight » Jun 7th, '11, 20:20

Just testing, I also tried

$db_conn=oci_connect('username', 'password', 'tnsname');

but get the following error

Fatal error: Call to a member function setFetchMode() on a non-object in C:\Inetpub\wwwroot\php\datagrid\datagrid.class.php on line 596

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Using a TNS Name in Connection String

Postby dynsight » Jun 7th, '11, 20:39

Last Attempt for today... I tried

$result_conn = $db_conn->connect(DB::parseDSN("oci_connect($DB_USER, $DB_PASS, $DB_HOST)"));

And now got the following error

[nativecode=ORA-12560: TNS:protocol adapter error]

I feel like I am so close... and if we can get this working, this tool can become a city wide standard...

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Using a TNS Name in Connection String

Postby dynsight » Jun 7th, '11, 20:54

Actually, your suggested code listed above would not work

Code: Select all

## *** creating variables that we need for database connection
$DB_USER='name';
$DB_PASS='';
$DB_HOST='localhost';
$DB_NAME = "localhost:1521/dbName";

$db_conn = DB::factory('oci8');
$db_conn->connect(DB::parseDSN('oci8://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
if(DB::isError($result_conn)){ die($result_conn->getDebugInfo()); }


since in your last line you make a reference to the variable $result_conn which does not exist

Posts: 2225
Joined: Wed Jan 07, 2009 6:18 pm
Private messageWebsite


Had to change it to:



Code: Select all

// start suggested coce
$DB_USER='username';
$DB_PASS='password';
$DB_HOST='hostname;
$DB_NAME = "hostname:1521/DBname";

$db_conn = DB::factory('oci8');
$db_conn->connect(DB::parseDSN('oci8://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
if(DB::isError($db_conn)){ die($db_conn->getDebugInfo()); }

// end suggested code

administrator
Site Admin
Site Admin
Posts: 6001
Joined: Jan 7th, '09, 23:18
Contact:

Re: Using a TNS Name in Connection String

Postby administrator » Jun 8th, '11, 10:09

You're right, it must be:

## *** creating variables that we need for database connection
$DB_USER='name';
$DB_PASS='';
$DB_HOST='localhost';
$DB_NAME = "localhost:1521/dbName";

$db_conn = DB::factory('oci8');
$result_conn = $db_conn->connect(DB::parseDSN('oci8://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
if(DB::isError($result_conn)){ die($result_conn->getDebugInfo()); }

dynsight
Expert
Expert
Posts: 99
Joined: Mar 3rd, '11, 03:05

Re: Using a TNS Name in Connection String

Postby dynsight » Jun 8th, '11, 12:15

Oddly,

It worked once, but then stopped and is giving the following error:

Fatal error: Cannot use object of type DB_Error as array in C:\Inetpub\wwwroot\php\datagrid\datagrid.class.php on line 4914

administrator
Site Admin
Site Admin
Posts: 6001
Joined: Jan 7th, '09, 23:18
Contact:

Re: Using a TNS Name in Connection String

Postby administrator » Jun 9th, '11, 08:11

What exactly this line of code is?


Return to “Databases: different types implementation”