Dear Perl Monks,

I'm seeking for your wisdom to use the Graphs Module in a meaningful manner per my current needs.

Basically, I have a database that contains 30+ tables. I want to find the quickest path to join 2 or more tables using the Dijkstra Algorithm.

As far as I can tell, the Graphs Module already has a Dijkstra functionality out of the box that can be used for this purpose.

In below code, I am trying to pull the information about my database (the foreign keys and, thus, dependent tables) of my schema.

I want to accomplish two things: 1) Assuming that I have two or more tables given by the user (either through STDIN or any other form), I want my Perl Script to tell me which other tables from my DB schema need to be added to join the original tables together (to create the INNER JOINs, etc), 2) I want to find the quickest path between the originally selected tables

.

However, when I call on the my @nodes = $graph->SPT_Dijkstra($a,$b); method below, I get the following error: "Not an ARRAY reference at C:\Perl\site\lib/Heap/Elem.pm line 31."

1) Does anybody know why this error is happening?

2) Does anybody have a better example as to how this can be accomplished?

Here is my current code:

#!/usr/local/bin/perl
use strict;
use warnings;
use DBI;
use Graph;
use Graph::Undirected;
use Graph::Directed;

my $data_source = q/dbi:ODBC:driver={SQL Server};server=MYCOOLSQLSERVE
+R2008R2;database=MYDEVDATABASE;Regional=No/;
my $user = q/myuser/;
my $password = q/mypassword/;
my $dbh;
my $sql_string;
 
eval {
   unless ($dbh = DBI->connect($data_source, $user, $password))
     {
       if ($@ ne "")    # There was an error    
         {
           print "\nError Description:$@";
           exit;
         }    
     }
};
 
$sql_string = qq/SELECT f.name AS ForeignKey,
                 OBJECT_NAME(f.parent_object_id) AS TableName,
                 COL_NAME(fc.parent_object_id,
                 fc.parent_column_id) AS ColumnName,
                 OBJECT_NAME (f.referenced_object_id) AS ReferenceTabl
+eName,
                 COL_NAME(fc.referenced_object_id,
                 fc.referenced_column_id) AS ReferenceColumnName
                 FROM sys.foreign_keys AS f
                 INNER JOIN sys.foreign_key_columns AS fc
                 ON f.OBJECT_ID = fc.constraint_object_id
                 ORDER BY TableName ASC/;
                 
$dbh->{LongReadLen} = 100000000;
$dbh->{LongTruncOk} = 0;

my $sth = $dbh->prepare($sql_string) or die "Can't prepare statement: 
+$DBI::errstr\n";
$sth->execute ();
my @spresults = ();
my %tableSchema = ();
my $num = 0;

my $graph = Graph->new(refvertexed => 1, undirected=>1);

while(my ($a, $b, $c, $d, $e) = $sth->fetchrow_array) {
        $graph->add_weighted_edge($b, $d, 1);
}

while (@spresults = $sth->fetchrow_array){

    my $key = $spresults[1];
    
    if (exists $tableSchema{$key}){
        my @tmpArray = ();
        my @i = $tableSchema{$key};
        $num += scalar(@i);
        
        for (my $cnt = 0; $cnt < $num; $cnt++){
            if (defined $tableSchema{$key}->[$cnt]){
                push @tmpArray, $tableSchema{$key}->[$cnt];
            }
        }
        push @tmpArray,"$spresults[2],$spresults[3],$spresults[4]";
        $tableSchema{$key} = \@tmpArray;
    }
    else{
        $num = 0;
        my @relationshipArray = ();
        push @relationshipArray, "$spresults[2],$spresults[3],$spresul
+ts[4]";
        $tableSchema{$key} = \@relationshipArray;
    }
    # print $spresults[0]." - ".$spresults[1]." - ".$spresults[2]." - 
+".$spresults[3]." - ".$spresults[4]."\n";
}
print "\n";



#for my $key (keys %tableSchema){
#
#    my $x = scalar(@{$tableSchema{$key}});
#    for (my $g = 0; $g < $x; $g++)
#    {
#        my @elements = split (',',$tableSchema{$key}->[$g]);
#        $graph->add_edge($key, $elements[1]);
#    }    
#}

print "\nThe graph is $graph\n";
print "\n";

foreach my $a ($graph->vertices){
    foreach my $b ($graph->vertices){
        next if $a eq $b;
        my @nodes = $graph->SPT_Dijkstra($a,$b);

    }
}
$graph->SPT_Dijkstra_clear_cache;
print "\n";
[download]

Thank you in advance for your wisdom...