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

Re: Using a TNS Name in Connection String

Postby dynsight » Aug 15th, '11, 19:30

Sorry this took so long... I had a heart attack and was out of work...

I'm Back!!

Here is what I have so far. The lines of code are here:
line4914.png
line4914.png (12.5 KiB) Viewed 2928 times


It seems to be related to column names, so here is my SQL statement:

Code: Select all

$sql="SELECT CD_ADDR_ROLE.ID,
       CD_ADDR_ROLE.CD,
       CD_ADDR_ROLE.SHRT_DESC,
       CD_ADDR_ROLE.LONG_DESC
  FROM CD_ADDR_ROLE";


However, running the datagrid code generates 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


My version of the datagrid.class.php is
PHP DataGrid Pro (AJAX enabled) version 6.1.1

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

Re: Using a TNS Name in Connection String

Postby administrator » Aug 16th, '11, 06:45

First of all please download the latest version of datagrid 6.7.5
Does this error still occurs?

BTW, we're planning to implement PDO instead of PEAR in the next version, so it solve a lot of problems.

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

Re: Using a TNS Name in Connection String

Postby dynsight » Aug 16th, '11, 12:56

I cannot access my update... it is expired.

I will be contacting support to get this enabled

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

Re: Using a TNS Name in Connection String

Postby dynsight » Aug 17th, '11, 13:51

Well, I updated to 6.7.5 this morning and still have the same error:

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


Here is the line of code
line5147.png
line5147.png (23.5 KiB) Viewed 2898 times


Here is my connection string:

Code: Select all

$db_conn = DB::factory('oci8');
//$result_conn = $db_conn->connect(DB::parseDSN('oci8://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
//$result_conn=oci_connect("dbuser", "dbpassword", "dbhost");-- This works fine, but when this line uncommented and the one below commented out, nothing happens...blank page
$result_conn = $db_conn->connect(DB::parseDSN("oci8://user:pw@host/host"));


Please note: Your connection string approach is a bit unusual for me. I have had great success with this:

Code: Select all

$c = oci_connect("user", "pw", "db")


but your suggested connection string is a bit different

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

Re: Using a TNS Name in Connection String

Postby dynsight » Aug 17th, '11, 14:15

I also tried using your suggested method (out of the box, so to speak)

Code: Select all

$DB_USER='user';
$DB_PASS='pw';
$DB_HOST='host';
$DB_NAME = "dbname";

$db_conn = DB::factory('oci8');
$result_conn = $db_conn->connect(DB::parseDSN('oci8://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));


I get the same error

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

Re: Using a TNS Name in Connection String

Postby administrator » Aug 18th, '11, 08:11

Please contact us via PM with access parameters to your site, so we could check this issue online.

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

Re: Using a TNS Name in Connection String

Postby dynsight » Aug 18th, '11, 13:46

    :arrow: Not sure what you mean by "PM" is that a support ticket?
    :arrow: Cannot grant you access to the site. Not only is it behind a very secure firewall, but it contains incredibly sensitive information
Sorry. I am guessing I have to give up trying to connect to Oracle... The problem does not seem to be with the connection, but may be related to how you read the data.

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

Re: Using a TNS Name in Connection String

Postby administrator » Aug 21st, '11, 08:19

Unfortunately we couldn't help you if we couldn't see this issue on your server.
Can you please just upload this page on some shared server, so we cloud test it online?

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

Re: Using a TNS Name in Connection String

Postby dynsight » Jan 3rd, '12, 19:15

I updated to datagrid 7.1.5 and rewrote the connection information as follows:

Code: Select all

  define ("DATAGRID_DIR", "datagrid/");                     /* Ex.: "datagrid/" */
    require_once(DATAGRID_DIR.'datagrid.class.php');

##  *** set needed options and create a new class instance
$debug_mode = true;   
$messaging = true;       
$unique_prefix = "abc_";
$dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix);

##  *** first parameter "PEAR" or "PDO" (experimental)
$DB_USER='user';     
$DB_PASS='password';             
$DB_HOST='ip address OR TNS Names';  //tried both
$DB_NAME='dbname';

 $sql="SELECT CD_ADDR_ROLE.ID,
       CD_ADDR_ROLE.CD,
       CD_ADDR_ROLE.SHRT_DESC,
       CD_ADDR_ROLE.LONG_DESC
  FROM CD_ADDR_ROLE";
 
$default_order = array();   /* Ex.: array("field_1"=>"ASC", "field_2"=>"DESC") */
$dgrid->DataSource("PEAR", "oci8", $DB_HOST, $DB_NAME, $DB_USER, $DB_PASS, $sql, $default_order);        ##  *** put a primary key on the first place
     
     
    ##  *** set needed options
      $debug_mode = true;
      $messaging = true;
      $unique_prefix = "f_"; 
      $dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix);

         
      $dg_caption = 'FACTS2 Users';
      $dgrid->SetCaption($dg_caption);



When visiting the page, I get the following error...
Fatal error: Call to a member function isError() on a non-object in C:\Inetpub\wwwroot\php\datagrid\datagrid.class.php on line 9458

Look, has anyone been successful connecting this to Oracle? If not, that's fine. It works splendidly with MySQL and I love it. Take off the Oracle references from your site, and let's call it a day.

If it does work, please let me know how to make it work. Obvioulsy I am doing something right, since I get to line 9458 of your code, which is in this function:

Code: Select all

private function CheckIsError(&$dset = null)
   {
        if(empty($dset)) $dset = $this->dataSet;
        if($this->dbType == "PEAR"){
            if($this->dbHandler->isError($dset) == 1){
                return true;
            }           
        }
        return false; 

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

Re: Using a TNS Name in Connection String

Postby administrator » Jan 4th, '12, 09:14

Here the working example of Oracle connection:

Code: Select all

  $DB_USER='root';           
  $DB_PASS='12345';           
  $DB_HOST='private-22269fa';       
  $DB_NAME='XE';       

  $debug_mode = true;        /* display SQL statements while processing */   
  $messaging = true;          /* display system messages on a screen */
  $dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix);

  $default_order = array("SUBSCRIBER_ID"=>"ASC");
  $sql = "SELECT SUBSCRIBER_ID, '' as MD, COS_NAME FROM CG3_POTROSNJA_GPRS";

  $dgrid->DataSource("PEAR", "oci8", $DB_HOST, $DB_NAME, $DB_USER, $DB_PASS, $sql, $default_order);             


Also, please check if you need to make any changes in Oracle connection string in datagrid.class.php

Code: Select all

if($db_driver == "oci8"){
     $DB_DESCR = "(DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ".$DB_HOST.")(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = ".$DB_NAME.")))";               
     $result_conn = $db_conn->connect(DB::parseDSN($db_driver.'://'.$DB_USER.':'.$DB_PASS.'@'.$DB_DESCR));
}


Return to “Databases: different types implementation”