Roth Consulting Perl Script: DBGrid.pl

use Win32::GUI;
use Win32::ODBC;
 
# Hide the Dos Window 
($DOShwnd, $DOShinstance) = GUI::GetPerlWindow();
GUI::Hide($DOShwnd);

#-------------------------#
#    Window Controls 
#-------------------------#

$W = new GUI::Window(
    -name => "Window",
    -text => "dbgrid.pl - Database Grid Application (Win32::ODBC,Win32::GUI)",
    -width => 510,
    -height => 310,
    -left => 100,
    -top => 100,
);

$LV = new GUI::ListView($W,
         -name => "ListView",
         -left => 10,
         -top => 10,
         -width => 490,
         -height => 290,
);
$LV->TextColor(hex("0000FF"));  # red
$LV->View(1);  # detailed listing
$LV->Hide;

$LB = new GUI::Listbox($W,
         -name => "ListBox",
         -style => WS_CHILD | WS_VISIBLE | WS_VSCROLL | 1, 
         -left => 10,
         -top  => 10,
         -foreground => 0x0000FF, # red
         -height => 490,
         -width =>  290, 
); 

$Status = new GUI::StatusBar($W,
    -text => " ",
    -width => $W->ScaleWidth,
);

$Progress = new GUI::ProgressBar($Status,
    -smooth => 0,     # 0=broken bar, 1=smooth bar
    -width  => $Status->Width/2, 
    -height => $Status->Height-3,
    -left   => $Status->Width/2,
    -top    => 2,
);
$Progress->Hide;

#----------------------------#
#      Window Dialog 
#----------------------------#
$W->Show;
Load_DSNs();
GUI::Dialog;

#----------------------------#
#   Non-Event Subroutines
#----------------------------#

END {
    if ($db) {$db->Close();}
    GUI::Show($DOShwnd);
}

sub Load_DSNs {
    $DSNmode=1;
    if (%DSNList=ODBC::DataSources()) { 
       foreach $Name (keys(%DSNList)) {
          $LB->AddString($Name);
       }
       $Status->Text("Double Click a DataSourceName from the List above");
       $Status->Update;
    }
    else {
       GUI::MessageBox($W,"Can't Determine Available DSN's","Error",16,);
       GUI::MessageBox($W,"Shutting Down the Application","Status",64,);
       exit; 
    }
}  

sub Load_TBLs {
   $LB->Reset();
   $db = new ODBC($DSN);   
   if (! $db) {
      GUI::MessageBox($W,"Can't Establish Database Connection to DSN '$DSN'",
        "Error",16,);
      GUI::MessageBox($W,"Shutting Down the Application","Status",64,);
      exit; 
   }
   @Tables=$db->TableList();
   $lasttbl=$#Tables;
   for($i=0;$i<=$lasttbl;$i++) {
      $LB->AddString($Tables[$i]);
   }
   $Status->Text("Double Click a TableName from the List above");
   $Status->Update;
}

sub Load_ListView {
   $Status->Text("Loading Database Grid...");
   $Status->Update;
   #-------------------------------------------#
   #  Get a row count for progress bar display
   #-------------------------------------------# 
   $ret = $db->Sql("SELECT COUNT(*) AS cnt_of_rows FROM $Table");
   if ($ret) {
      $error=$db->Error();  # database error msg
      GUI::MessageBox($W,$error,"Error",16,);
      GUI::MessageBox($W,"Shutting Down the Application","Status",64,);
      exit; 
   } 
   $db->FetchRow();
   $dbcnt = $db->Data("cnt_of_rows");     
   #-----------------------------------#
   # Load Listview from the Database 
   #-----------------------------------#
   $sqltxt = "SELECT ' ', * FROM $Table";
   $ret = $db->Sql($sqltxt);
   if ($ret) {
      $error=$db->Error();  # database error msg
      GUI::MessageBox($W,$error,"Error",16,);
      GUI::MessageBox($W,"Shutting Down the Application","Status",64,);
      exit; 
   } 
   $LV->InsertColumn(-index => 0,-width => 50, -text => "Row#"); 
   @Cols = $db->FieldNames(); 
   $lastcol=$#Cols;
   for($i=1;$i<=$lastcol;$i++) {
      $columname=$Cols[$i];
      $LV->InsertColumn(-index => $i,-width => 100, -text => $columname); 
   }
   $itemcnt=0;
   $rowcnt=1;
   $Progress->Show;
   while ($db->FetchRow()) {
      $pos=($rowcnt*100)/$dbcnt;
      $Progress->SetPos($pos);
      $Progress->Update;
      $LV->InsertItem(-item => $itemcnt, -text => $rowcnt);
      @Data=();  # clear the data array 
      @Data = $db->Data(); 
      $lastfld=$#Data;
      $subitemcnt=1;
      for($i=1;$i<=$lastfld;$i++) {
         $fieldvalue=$Data[$i];
         $LV->SetItem(-item => $itemcnt,-subitem => $subitemcnt,-text => $fieldvalue);
         $subitemcnt++;
      }
      $rowcnt++;
      $itemcnt++;
   }
   $db->Close();
   $Progress->Hide;
   $LV->Show;
   $Status->Text("DataSourceName=$DSN, TableName=$Table");
   $Status->Update;
}

#----------------------------#
#      Window Events
#----------------------------#

sub Window_Terminate {
    if ($db) {$db->Close();}
    GUI::Show($DOShwnd);
    exit;
}

sub Window_Resize {
  $LV->Resize($W->ScaleWidth-20,($W->ScaleHeight-20)-$Status->Height);
  $LB->Resize($W->ScaleWidth-20,($W->ScaleHeight-15)-$Status->Height);
  $Status->Resize($W->ScaleWidth,$W->ScaleHeight);
  $Progress->Resize($Status->Width/2,$Status->Height-3);
  $Progress->Move($Status->Width/2,2);
}  

sub ListBox_DblClick {
  $sel = $LB->SelectedItem(); # sel is zero based index
  if($sel >= 0) {
     $item = $LB->GetString($sel);
     if ($DSNmode) {
        undef $DSNmode;
        $DSN=$item;
        Load_TBLs();
     }
     else {
        $Table=$item; 
        $LB->Hide;
        Load_ListView();
     }
  }
}

	Are you searching for a children's bike trailer?
	Would you like to try growing an avocado plant?
	Easily lose weight by increasing your metabolism.
	What, exactly, is a Calorie?
	Want to know about schooling your child using the Montessori method.

`Name:`	`DBGrid.pl`
`Version:`	`1.0`
`Last Updated:`	`1999.06.22`
`Author:`	`Eric Hansen`
`Description:`	`A database grid script using Win32::ODBC`
`Explanation:`	`The user is displayed a list of available ODBC DataSourceNames. The user double clicks a DSN and then is displayed a list of available tables. The user then double clicks a tablename, afterwhich a listview is loaded with all records from that table. The user can then browse the rows and columns from that database table. No rows are updated. This is a display only database interface. Future releases may provide selective data export features (i.e. selected rows, selected columns), and sorting.`

Perl Script: DBGrid.pl

The DBGrid.pl Script: