If anyone finds this script useful or has ideas for improvement
please forward them to me Eric Hansen at [EMAIL PROTECTED]
I am no expert with ODBC, or GUI for that matter, but I have found
this script useful and wish to make it more useful/sound. Please
give it a try and let me know your ideas. Thanks. If you don't see
# END SCRIPT at the end, then my message was truncated. If this
happens, email me and I will send you the script file.
Regards,
Eric Hansen
Dallas, Texas U.S.A
# Name: dbscrollgrid.pl
# Purpose: Database scrollcursor grid (Perl Win32::ODBC,GUI)
# Author: Eric Hansen, Copyright 1999, Information
# Technology Services, Inc. Dallas, TX
# Contact: [EMAIL PROTECTED]
# Description: Display an ODBC SQL result set in a GUI database grid
# allowing the user to navigate back and forth through
# the result set. This script uses a scroll cursor on
# the database. Large SQL result sets may be navigated
# in this way. 100 rows maximum are loaded to the grid
# from the result set at any one time. By clicking the
# Next or Prev buttons, the user can load the next or
# previous 100 rows from the result set.
# Requirements:Pass DataSourceName(DSN) on command line. Create an
# SQL statement in a file called dbscrollgrid.sql in the
# current directory for input to dbscrollgrid.pl.
# Your ODBC driver must support scrolling cursors.
# You must configure your DSN to enable scrolling cursors.
# i.e. Informix Driver has a checkbox you must check to
# enable scrolling cursors.
use Win32::GUI;
use Win32::ODBC;
# Hide the Dos Window
($DOShwnd, $DOShinstance) = GUI::GetPerlWindow();
GUI::Hide($DOShwnd);
$DSN=$ARGV[0];
if (! $DSN) {
GUI::MessageBox($dummy,"You did not pass the DataSourceName",
"Error",16,);
exit;
}
open(SQL,"dbscrollgrid.sql") || do {
GUI::MessageBox($dummy,"Where's file dbscrollgrid.sql?",
"Error",16,);
exit;
};
$sqltxt='';
while ($rec=<SQL>) {
chomp $rec;
$sqltxt=$sqltxt . " " . $rec;
}
close(SQL);
#-------------------------#
# Window Controls
#-------------------------#
$W = new GUI::DialogBox(
-name => "Window",
-text => "dbscrollgrid.pl - Database Scroll Grid (Win32::ODBC,Win32::GUI)",
-width => 700,
-height => 510,
-left => 10,
-top => 10,
);
$Font = new GUI::Font(
-name => "Tahoma",
-size => 8,
-weight => 700,
-height => -11,
);
$Status = new GUI::StatusBar($W,
-text => " ",
-font => $Font,
-width => $W->ScaleWidth,
);
$LV = new GUI::ListView($W,
-name => "ListView",
-font => $Font,
-left => 10,
-top => 10,
-width => $W->ScaleWidth-20,
-height => $W->ScaleHeight-100,
);
$LV->TextColor(hex("0000FF")); # red
$LV->View(1); # detailed listing
$Prev = $W->AddButton(-name => "Prev",
-text => "Prev",
-width => 45,
-height => 18,
-left => 15,
-top => 410,
);
$Next = $W->AddButton(-name => "Next",
-text => "Next",
-width => 45,
-height => 18,
-left => 15,
-top => 435,
);
$SqlLabel = $W->AddLabel(-name => "SqlLabel",
-width => 610,
-height => 60,
-background => [255,255,255],
-left => 70,
-top => 402,
);
#----------------------------#
# Window Dialog
#----------------------------#
$W->Show;
Connect_To_Database();
Execute_SQL();
Load_Table_Column_Headings();
Next_Click();
GUI::Dialog;
#----------------------------#
# Non-Event Subroutines
#----------------------------#
END {
if ($db) {$db->Close();}
GUI::Show($DOShwnd);
}
sub Connect_To_Database {
$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;
}
$db->SetStmtCloseType(SQL_DONT_CLOSE);
$db->SetStmtOption($db->SQL_CURSOR_TYPE,$db->SQL_CURSOR_DYNAMIC);
$db->SetStmtOption($db->SQL_CURSOR_TYPE,$db->SQL_CURSOR_STATIC);
}
sub Execute_SQL {
$SqlLabel->Text($sqltxt);
$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;
}
}
sub Load_Table_Column_Headings {
$LV->InsertColumn(-index => 0,-width => 50, -text => "Row#");
@Cols = $db->FieldNames();
$lastcol=$#Cols;
for($i=0;$i<$lastcol;$i++) {
$columname=$Cols[$i];
$LV->InsertColumn(-index => ($i+1),-width => 100, -text => $columname);
}
}
sub Load_Table_Data {
$Status->Text("Loading Database Grid...");
$Status->Update;
$itemcnt=0;
$rowcnt=$db->GetStmtOption($db->SQL_ROW_NUMBER());
$LV->InsertItem(-item => $itemcnt, -text => $rowcnt);
@Data=(); # clear the data array
@Data = $db->Data();
$lastfld=$#Data;
$subitemcnt=1;
for($i=0;$i<$lastfld;$i++) {
$fieldvalue=$Data[$i];
$LV->SetItem(-item => $itemcnt,
-subitem => $subitemcnt,
-text => $fieldvalue,
);
$subitemcnt++;
}
$itemcnt++;
while ($db->FetchRow()) {
$rowcnt=$db->GetStmtOption($db->SQL_ROW_NUMBER());
$LV->InsertItem(-item => $itemcnt, -text => $rowcnt);
@Data=(); # clear the data array
@Data = $db->Data();
$lastfld=$#Data;
$subitemcnt=1;
for($i=0;$i<$lastfld;$i++) {
$fieldvalue=$Data[$i];
$LV->SetItem(-item => $itemcnt,
-subitem => $subitemcnt,
-text => $fieldvalue,
);
$subitemcnt++;
}
$itemcnt++;
if ($itemcnt == 100) {last;}
}
if ($itemcnt < 100) {
$db->FetchRow(0,SQL_FETCH_LAST);
$rowcnt=$db->GetStmtOption($db->SQL_ROW_NUMBER());
}
$Status->Text("DataSourceName=$DSN");
$Status->Update;
}
#----------------------------#
# Window Events
#----------------------------#
sub Window_Terminate {
if ($db) {$db->Close();}
GUI::Show($DOShwnd);
exit;
}
sub Prev_Click {
$LV->Clear();
if ($rowcnt > 200) {
$pos=($rowcnt % 100);
if ($pos == 0) {$pos=199;}
else {$pos+=99;}
$db->FetchRow(($pos * -1),SQL_FETCH_RELATIVE);
}
else {
$db->FetchRow(0,SQL_FETCH_FIRST);
}
Load_Table_Data();
}
sub Next_Click {
$LV->Clear();
if ($db->FetchRow()) {
Load_Table_Data();
}
else {
$db->FetchRow(0,SQL_FETCH_LAST);
$rowcnt=$db->GetStmtOption($db->SQL_ROW_NUMBER());
Prev_Click();
}
}
# END SCRIPT
