Ananda has asked for the wisdom of the Perl Monks concerning the following question:

Hello Monks, Greetings !!

I am facing problem while executing an outer join querry in Oracle 8i DB using Perl.

I am using perl DBI::DBD 

#open connection to the database
my $db = DBI->connect("dbi:Oracle:host=$host;sid=$sid;port=$port", $ui
+d, $pwd) or die "Error while connecting to database!!!\nIncorrect Con
+nection information\n";
 
my $SQL_query;
    
    $SQL_query = qq {SELECT A.DISPLAY_ORDER, A.NAME PARENT_NAME, B.NAM
+E CHILD_NAME FROM CATEGORY A, CATEGORY B WHERE B.ID_PARENT_CATEGORY(+
+) = A.ID_CATEGORY AND A.DISPLAY_ORDER != -1 ORDER BY DISPLAY_ORDER};
                            
    debug($SQL_query);
    
    my $sth = $db->prepare( $SQL_query )  or die "Can't prepare SQL st
+atement: $DBI::errstr\n";
    
 ### Execute the statement in the database
 $sth->execute or die "Can't execute SQL statement: $DBI::errstr\n";
 
my ($pdisplay_order, $parent_name, $child_name);
   
 # Bind perl variables to columns:  
 my $rv = $sth->bind_columns(\$pdisplay_order, \$parent_name, \$child_
+name);
 
### Retrieve the returned rows of data
 while ($sth->fetchrow_array()) {
 chomp($pdisplay_order);
 chomp($parent_name);
 chomp($child_name);
 
 print "$pdisplay_order, $parent_name, $child_name\n";
 }

[download]
However, when I use the following, I can fetch the values from the tab
+le / view
 #$SQL_query = qq {SELECT display_order, name, id_parent_category from
+ CATEGORY ORDER BY DISPLAY_ORDER };
    #$SQL_query = qq {SELECT display_order, name, id_parent_category f
+rom MY_VIEW };

I tried creating a oracle view for the SQL query which has the outer j
+oin and used the following query but still I could not display the va
+lues from the array.
    #$SQL_query = qq {SELECT * from VW_CATEGORIES};

[download]

Any immediate help is appreciated

CHEERS Ananda

Replies are listed 'Best First'.
Re: Reading Database with Outer Joins
by rdfield (Priest) on Jan 13, 2003 at 09:22 UTC
    You're combining two approaches to retieving data. In order to populate values defined in bind_columns use fetch, when using fetchrow_array DBI returns a list, thus ($pdisplay_order, $parent_name, $child_name) = $sth->fetchrow_array();

    Update:Checked the DBI docs, and there is a fully worked example of the use of bind_columns in the section titled bind_columns

    rdfield

[reply]
[d/l]
[select]
      Thanks rdfield. That helped.

      Ananda

[reply]
Re: Reading Database with Outer Joins
by cadfael (Friar) on Jan 13, 2003 at 17:03 UTC 
    my $SQL_query;
   
   $SQL_query = qq {SELECT A.DISPLAY_ORDER, A.NAME 
PARENT_NAME, B.NAME CHILD_NAME
FROM CATEGORY A, CATEGORY B 
WHERE B.ID_PARENT_CATEGORY(+) = A.ID_CATEGORY 
AND A.DISPLAY_ORDER != -1 ORDER BY DISPLAY_ORDER};

    [download]
    Just a suggestion -- Oracle, SQL Server, Sybase, and others tend to use idiosyncratic ways of expressing joins. Using the ANSI join specifications will make your code more portable between RDBMS implementations. For example: 
    SELECT A.DISPLAY_ORDER, A.NAME PARENT_NAME,  B.NAME CHILD_NAME 
FROM CATEGORY A
LEFT OUTER JOIN  CATEGORY B 
ON B.ID_PARENT_CATEGORY = A.ID_CATEGORY

    [download]
    I know that Sybase, SQL Server, Postgres and Oracle all support ANSI joins, and I suspect there are others as well.

    -----
    "Computeri non cogitant, ergo non sunt"

[reply]
[d/l]
[select]
      I agree that ANSI syntax is more portable (and less confusing when you work on multiple platforms), but I believe that this form is not supported by Oracle 8i (it does work in 9i).

      smersh
[reply]

Back to Seekers of Perl Wisdom