Datawindow Column Properties

The Dialog box

Where Clause for Update/Delete

Key Columns

This option is commonly used for single-user applications. It’s also used when working with “many-to-many” tables where the primary key consists of all the columns in the table

Key and Updateable Columns

This method provides maximum consistency

Key and Modified Columns

This option provides maximum concurrency

You should check the data model to make sure the update does not violate the business rules of the database. The value you are changing may depend on the value of another column that may have been changed.

SQL Painter

How can I use Retrieval Arguments to make my where clause more flexible?

Here is a solution I came up with to avoid the need to dynamically modify the Where Clause, or creating multiple datawindows with slightly different SQL. The JOB_QUEUE_RULE table has IDs which must be a positive number ( a good place for a check constraint in the table’s DDL). If you want to see all the rules, set the retrieval argument to 0, otherwise set the ret arg to the ID you want to see.

SELECT R.ID,
       R.DESCRPTION
  FROM dbo.JOB_QUEUE_RULE   R
 WHERE ((R.ID = :ra_id) OR (:ra_id = 0 ))

How do I limit the number of tables to those with a certain prefix?

There exists a parameter to limit the tables you view in the database painter. Here’s the language you need.

• Click the DB profile tool bar button
• Select the profile you want and click EDIT (this action displays the DATABASE PROFILE SETUP dialogue)
• Select the SYSTEM tab
• Go to the TABLE CRITERIA section and type ,W11559,”TABLE” (or replace W11559 with whichever account you want)
• Click OK (this action returns you to the DATABASE PROFILES dialogue)
• Click CONNECT
• Help can be found under TABLECRITERIA (one word)

How can I use Dual (Oracle) to populate my initial search screen?

SELECT '' as secretary_code,
'' as LOG_SEQ_NO,
'' as governor_log_no,
'' as SUBJECT_DESC,
' as BUREAU_REGION_CODE,
to_date('' ) as FR_received_on_date,
to_date('' ) as TO_received_on_date,
to_date('' ) as FR_DUE_DATE,
to_date('' ) as TO_DUE_DATE,
to_date('' ) as FR_letter_out_date,
to_date('' ) as TO_letter_out_date,
to_date(to_date(to_char(add_months(sysdate,-12),'mm/dd/yyyy' ), 'mm/dd/yyyy' )) as FR_date,
to_date(to_char(sysdate,'mm/dd/yyyy' ),'mm/dd/yyyy' ) as TO_date,
'' as leg_cust_last_name,
'' as leg_cust_first_name,
to_date('' ) as fr_letter_date,
to_date('' ) as to_letter_date
FROMDUAL

Magic where clause for datawindow receiving a date retrieval argument

WHERE “WM_HUNTERS_CHOICE_APPLICANT”.”DOB” = to_date(:as_dob, ‘mm/dd/yyyy’ )

When I try to switch from Syntax mode to Graphic mode, PowerBuilder gives me a message saying that it can’t.

Tables associated with your SQL can not be accessed from your current database profile. Switch to a database profile with the correct server and system login ID and password.

DataWindow Bands

Header

GroupHeader

Detail

Group Trailer

Footer (end of each page)

Summary

Suppress Repeating Values in detail band

There is a selection under the Rows menu heading for this

Newspaper Columns in detail band

This option can be specified in the Print Specifications tab of the DataWindow Object. The newspaper column effect appears only when the report is printed or in print preview mode

Note, this will also display header information in newspaper column. Since you probably will not want this text repeated, the display can be limited by checking the Suppress Print After First Newspaper Column check box in the column’s general property page.

How come my groups are goofy

The data must be sorted in the same way as the group.

Column Properties

Display Properties (Masks)

! Upper Case
^ Lower Case
a Alphanumeric
x Any character
# Number

Column Display

How do I get my field to appear like a MLE and word wrap?

• In the datawindow painter, select the column
• Properties
• EDIT tab

Check AUTO VERT SCROLL and AUTOSELECTION. Other selected items may also work, however do not select HORZ SCROLL BAR. When I update a column, the change is applied to the database for many rows, not just the current one.

Check the unique Key Column in the DataWindow’s Update properties dialog box. Click the Primary Key button to assure that the columns selected are indeed unique.

Okay, in a datawindow, how do you get rid of displaying the time portion of the date field?

In the DataWindow painter, select the field and change the format property

Graying out a checkbox

If a checkbox has 3 states, and it’s state is set to that 3rd state (other), then a gray checkmark will appear in the checkbox instead of a black one.

So, by changing the state of checkbox to that 3rd state, and protecting it, it will appear to be grayed out.

Extended Attributes

What is the PowerSoft Repository?

The Powersoft repository (AKA Powersoft Catalog) is a series of 5 tables containing extended attributes pertaining to a data table and its columns. These attributes contain information such a s labels, headings, display formats, validation rules, and edit styles.

Do not confuse the database catalog with the repository. The database catalog contains column definitions, initial values, constraints, and referential integrity rules and has no relationship with the PowerSoft repository.

How can I modify a table with out dropping it and clobbering the extended attributes?

1. Use the “Object|Export syntax to log” menu selection to save the data table syntax to the painter log.

2. Select Design|Save Log As to save the log to a file. The log contains both the syntax and INSERT statements that populate the repository with the extended attributes for the table.

3. Make changes to the SQL statements in the saved file, import the file into the Database Administration painter notepad, and execute the statements to recreate the table and populate the repository.

Or you could use a database design tool

How do I synchronize the repository?

When data tables are deleted outside the Database painter, for example using DROP table from SQL Plus, extended attributes associated with the deleted table remain in the repository. This information is now orphaned and takes up space.

To remove orphaned attributes from the repository, select Design|Synchronize PB Attributes in the Database painter. This menu items compares all attribute information to the list of existing tables and purges attributes for tables that no longer exist.

Warning: make sure you have access to all the data tables, otherwise you risk deleting extended attributes for tables that you can’t see.

What is DWEAS Utility?

The DataWindow Extended Attribute Synchronizer (DWEAS) lets you update many of the attributes of an existing DataWindow object with the current extended attribute values from the Powersoft repository.

This is important because when a new DataWindow is built, its extended attributes are gathered from the current values in the repository. When the values change, the object attributes are not automatically updated.