[perl-win32-gui] DBGrid.pl Update

[Eric Hansen]

A new font was added to make the data easier to see. 

Eric Hansen

# Name:        dbgrid.pl
# Purpose:     Database grid application (Perl Win32::ODBC,GUI)
# Author:      Eric Hansen,   Copyright 1999, Information
#              Technology Services, Inc. Dallas, TX
# Contact:     [EMAIL PROTECTED]
# Description: 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.
 
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,
);

$Font = new GUI::Font(
    -name => "Tahoma",
    -size => 8,
    -weight => 700,
    -height => -11,
);

$LV = new GUI::ListView($W,
         -name => "ListView",
         -font => $Font,
         -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",
         -font => $Font,
         -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 => " ",
        -font => $Font,
        -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(); 
    } 
  }