The Definitive Datawindow

The Definitive DataWindow explores the depths of the DataWindow and shows you how to take full advantage of this popular, but underutilized, technology. Drawing on his extensive knowledge and experience, Richard Brooks leads you from DataWindow basics to its most sophisticated and rarely mined capabilities, providing the insights and advanced skills needed to master this important database development technology.

This book focuses on an in-depth understanding of the DataWindow object, a comprehension that contributes to improved DataWindow control skills, database programming, and user interface design. Other topics include:

Database Painter techniques for creating customized edit masks and display formats Graphing, from basic functions to techniques for animating graphs in response to user actions The DataWindow mini-language that creates sophisticated results with virtually no programming Creating a library of specialized DataWindows DataWindow's role in distributed application development The DataWindow Version 7.0 interface DataWindow with PowerJ and PowerSite Sample code taken directly from existing programs that are running in real-life applications

For those new to the topic, this book presents background on relational database technology, SQL, and DataWindow fundamentals. An entire chapter is devoted to pragmatic tips and techniques for overcoming real-world database development problems. The accompanying CD-ROM features a shareware copy of SQL Wizard and an evaluation copy of SQL Expert.

Part tutorial, part reference, and part practical guide, this indispensable resource will greatly enhance your use of DataWindow and your overall database programming skills.

Table of contents

Chapter 1: DataWindow to Database: Structured Query Language

Characteristics of SQL

Advantages of SQL

Types of SQL

Data Control Language

Data Definition Language

Tables

Create Table

Alter Table

Drop Table

Data Manipulation Language

Select

Insert

Update

Delete

Nulls

Advanced SQL Topics

Indexes

Data Manipulation

Retrieval of Computed Values

Retrieval Using a Wildcard

Qualified Retrieval

Retrieval with Ordering

SELECT Using BETWEEN

SELECT Using IN

Retrieval Using LIKE

ESCAPE Clause

Operations Using NULLS

UPDATE Statements

Aggregate Functions

The Existential Quantifier

Subqueries

Joins

Joins versus Subqueries

Unions

Outer Joins

Views

Data Manipulation Operations with Views

Updatable and Nonupdatable Views

Advantages of Views

SQL Tips, Tricks, and Tools

Efficient Data Retrieval Methods

Efficient Data Modification Methods

Summary

Chapter 2: Understanding Open Database Connectivity

Flat File Databases

Indexed Sequential Access Method Databases

Networked ISAM Databases

Interprocess Communications

Relational Databases

The Acceptance of Structured Programming Language

The Need for Conformity and the Birth of ODBC

How ODBC Works

Summary

Chapter 3: Data Modeling and Database Design

The Architecture of Database Systems

Physical Level

Conceptual Level

External Level

Database Design

Requirements Analysis

Information Modeling

Data Entities

Data Attributes

Data Associations

Entity List

Data Schema Map

Design Constraints

Functional Dependencies

Data Normalization

A Practical Approach to Database Design

Understanding the Application Tasks

The Task Document

The Data Document

The Business Model

Data Entry

Query and Reporting

Summary

Chapter 4: Behind the DataWindow: Highlights of Database Design

Primary Keys

Foreign Keys

Normalization

The Normal Forms

A Real-World Example

The ITEM Table

ACTION Table

MONEY Table

ACCOUNT Table

PERSON Table

Summary

Chapter 5: Working with the Database Painter

Primary Keys

The Catalog Tables

PBCATTBL

PBCATFMT

PBCATVLD

PBCATCOL

PBCATEDT

Maintaining the Catalog Tables

The Edit Style Dialogs

Maintaining Code Tables

Display Format Maintenance

Maintaining Data Validations

A Version 7 Enhancement

Summary

Chapter 6: The Very Basics

The DataWindow Control and the DataWindow Object

The Primary DataWindow Buffer

Proactive and Reactive Programming

DataWindow Buffers

Summary

Chapter 7: The Simplest DataWindows

Need Primary Level Head Here

Selected DataWindow

DataWindows in Current PBL

PBLs in Current Application

Load DataWindow in a Different Application PBL

Open DataWindow

Dismiss DataWindow

New DataWindow

Data Sources

Quick Select

SQL Select

Query

External

Stored Procedure

Presentation Styles

Composite

Crosstab

Freeform

Graph

Grid

Group

Label

N-Up

Ole 2.0

Rich Text

Tabular

Summary

Chapter 8: The DataWindow Painter

The Bands

The Header Band

The Detail Band

The Summary Band

The Footer Band

Group Headers and Footers

Expanding and Contracting Bands

The DataWindow Workspace

The Toolbar

Menu Items

File>New/Open/Close

Edit>Select

Design>Options

Rows

Summary

Chapter 9: DataWindow Objects and Their Properties

A Brief Overview of DataWindow Objects

Select Object

Text

Picture

Line

Oval

Rectangle

Round Rectangle

Column

Computed Field

Graph

Report

OLE Object

OLE Database Blob

Group Box

Button

Average

Count

Page N of N

Sum

Today

DataWindow Object Properties

Common Properties

Color Properties

DataWindow Properties

DataWindow Table Properties

Rich Text Format Properties

Miscellaneous Properties

Bitmap Properties

Button Properties

RadioButtons Properties

Summary

Chapter 10: DataWindow Controls

The Basic Idea

Retrieving Data

The Buffers

Validation

Standard Events

ButtonClicking

ButtonClicked

Clicked

Constructor

DBError

Destructor

DoubleClicked

DragDrop

DragEnter

DragLeave

DragWithin

EditChanged

Error

ItemChanged

ItemError

ItemFocusChanged

LoseFocus

Other

PrintEnd

PrintPage

PrintStart

RbuttonDown

Resize

RetrieveEnd

RetrieveRow

RetrieveStart

RowFocusChanged

RowFocusChanging

ScrollHorizontal

ScrollVertical

SQLPreview

UpdateEnd

The UpdateStart Event

Beyond the Standard Events

How to Map Other Events to the DataWindow Control

Custom Events

Some Commonly Used or Just Interesting Functions

AcceptText

DBCancel

DeleteRow

Filter

Find

GenerateHTMLForm

GetChild

GetColumn

GetColumnName

GetItemXXX

GetItemStatus

GetNextModified

GetObjectAtPointer

GetRow

GetSelectedRow

GetText

ImportFile

InsertRow

ResetUpdate

Retrieve

RowCount

RowsCopy

RowsDiscard

SaveAs

ScrollToRow

SetColumn

SetFilter

SetFocus

SetItem

SetItemStatus

SetRedraw

SetRowFocusIndicator

SetSort

SetText

SetTransObject

ShareData

ShareDataOff

Sort

Update

Sequencing

Summary

Chapter 11: Graphing

A First Example--A Line Graph

Using Our Simple Graph 462 A Second Example--A Line Graph with a Series

A Third Example--Selectable Series

A Fourth Example--Final Polishing of the Line Graph

A Final Example--A Pie Chart

Moving Beyond the Pie

Exploding a Slice

The Requirements for the Window

Setting Up Your Example

The Run Button

The Clicked Event

The DoubleClicked Event

Summary

Chapter 12: Using Edit and Display Formats

Sections

Colors

Numbers

Strings

Dates

Times

Combinations

Summary

Chapter 13: An Ancestor DataWindow Control

Some Basics First

Events

Firing Sequence

Using Event Return Values

Functions

Variables

A Start--Create Your DataWindow Object

Replace ScrollToRow

Add an Error Service Object

Create the Object

Understanding the Error Object

Error Event Arguments

Write the Error Function

Write the Error Events

Create Your Error Window

Use the Service Object in the DataWindow Object 511 A Solution to an Unusual Problem

Where to Go from Here

Summary

Chapter 14: Tips and Techniques

DataWindow Controls--Coding Your DataWindow

Canceling a Long Retrieve

Reusing the PowerBuilder DataWindow Dialogs

Adding Your Own Help Button to the Dialogs

Using Dot Notation instead of SetSQLSelect

Some Notes on Dot Notation

Nulls Are Values, Too

The Incredibly Flexible Select

Advanced Use of the Script Editor

Using Apostrophes and Quotes

Zooming

Understanding Row/Column Statuses

Creating Optional Arguments (Polymorphism Exemplified)

Beyond Simple Variable Declarations

Some Safe Uses for the Tag Attribute

Implementing Pop-Up Menus

Implementing Drag and Drop

Deceptive Row

Forcing Keystrokes

Finding the Last Editable Column

Looping

Selecting Multiple Columns

SetRedraw

Returning Incorrect Data from a DDDW

Prohibit Column Selection in a Grid

Referencing a Nested Report

Background Printing a Report

Last line on One Page Repeating as First Line on Next

Using Datastores

The DataWindow Object

Aggregates of Aggregates

A Fake Column

Edit Masks and Formats

The If Function

Deleting Columns from the DataWindow Painter

Dirty Reads and Writes

The Color Box

Crosstab DataWindows

Using an Edit Style that's Different from the Format

Fake Columns

Formatting Your Output

Green Bar DataWindows

A Header that Prints only on the First Page

Protecting Saved Rows

Switch Hitting

Using Watermarks

Computed Columns in Column Count

Understanding the Drop-Down DataWindow

Autosizing Height of a Nested Report

Missing Data in Print Preview

Object-Oriented Techniques

A Type-Ahead Search Object

Implementing Master/Detail DataWindows

A Persistent Rich Text Edit Service Object

A VCR Toolbar

A Specialized Drop-Down Listbox 646 Miscellaneous

Changing from a Grid Presentation Style to Tabular and Back

Adding Bookmarks to Your Help System

A Better Metaphor for DataWindow Command Buttons

Using the DataWindow and Datastore in Distributed Applications

Setting Default Values for the DataWindow Painter

Using Custom Colors

A String Collector

A Cute Function for Formatting Names

Data Truncated Error

Handling Employees Efficiently

Optimizing Your DataWindow

A Good Use for Queries

Split Screens

Storing Static Data

Summary

Chapter 15: The Web DataWindow

The Basic Idea

Traditional Two-Tier Environment

Pseudo Three-Tier Environment

Security

Conventions

Database Independence

The Danger of the Pseudo Three-Tier

Three Tier

N-Tier (Distributed) System

Networked (Internet, Intranet, and Extranet)

A Basic System

A More Robust System

The Web DataWindow

Settings

Other Considerations

Moving to the Future

Chapter 16: Differences Between PowerBuilder 6 and 7

The Basic Idea

Menus

Panels

Database Painter

Tab Pages

The DataWindow Painter

Chapter 17: DataWindow Basics for PowerJ and PowerSite Programmers

Libraries

The Painter Concept

The Library Painter

What You Can Do in a Library Painter

Chapter 18 The Future

Glossary

Index