Wednesday, April 1, 2009

Dynamically Creating DataWindow Objects

Objects can be added to your DataWindow programmatically via a Modify statement. In my opinion, the dynamic creation of objects within a DataWindow has been a highly underused feature.
Dynamically creating (or destroying) objects within a DataWindow has many advantages such as:
- Dynamically changing the content
- If a printed DataWindow varies in appearance from its visual presentation



The syntax for creating objects within DataWindows can be daunting; no wonder it's not used that often. Before I go into more detail, it's important to know how objects are contained in a DataWindow in the first place. Once you understand this, you will find that dynamically creating objects is easy.
The Naked DataWindow :


It should come as no surprise that a DataWindow is really just a collection of objects, each with its own properties. When a programmer is creating a DataWindow via the DataWindow painter, he or she is actually just using a graphical IDE to create and set the properties of the objects that make up the DataWindow. Actually, the naked DataWindow is not graphical at all but exists in text format. It's just that most programmers tend to create and edit DataWindows via the DataWindow Painter.
Prior to PowerBuilder 8, if you wanted to take a peek at what a DataWindow looked like in its text format, it would have to be exported via the library painter, then opened up within a text editor. At that point, changes could be made and the text file could be imported back into PowerBuilder. As the file was being imported, PowerBuilder would regenerate it, making sure your hack was syntactically correct.
Since PowerBuilder 8, Sybase has allowed programmers to directly modify objects via a Source Editor, effectively putting an end to the enjoyable, unsupported DataWindow source hacking days. By viewing the source code of an existing DataWindow, you'll appreciate the syntax of creating objects dynamically. A basic understanding of the DataWindow syntax can help a lot. Let's start with the DataWindow illustrated in Figure 1.



Understanding the Syntax

If this simple DataWindow is opened up in the Source Editor, you'll see all the objects that it's comprised of. The complete syntax is extremely lengthy and cannot be listed in its entirety here. At first glance, the syntax looks foreboding, but after further inspection, it becomes more familiar. If you think about it, it looks almost identical to the syntax for the Describe and Modify function. That's because it is the syntax for Describe and Modify.
The source code can be broken down into six categories:
1. Version information
2. DataWindow properties
3. Band properties
4. Source definition
5. Object definitions
6. DataWindow HTML/XML properties

Version Information

Let's take a closer look at our Naked DataWindow:
release 9;
The first line of the syntax comprises only one statement indicating the PowerBuilder release with which this DataWindow object was constructed. This line will contain only major release numbers (you won't see 9.01). The release number is important as it tells the DataWindow Engine how to handle the rest of the syntax. Obviously, more recent DataWindow versions contain added features. If you're in PowerBuilder 8 and try to open a DataWindow that was built in PowerBuilder 9, PowerBuilder gives the error message "DataWindow Syntax has incorrect release number." On the other hand, a more recent version of PowerBuilder will happily import a DataWindow created in an earlier release. When an earlier version of a DataWindow is saved or regenerated, it's migrated to the current version.
If you're resourceful, the DataWindow may be migrated backward by changing its release number. It may take a bit of trial and error to remove any of the source code that may not be understood by previous DataWindow Engines.

DataWindow Properties
datawindow(units=0 timer_interval=0color=12632256 processing=0 HTMLDW=no print.printername=""print.documentname="" print.orientation = 0 print.margin.left =110 print.margin.right = 110 print.margin.top = 96 print.margin.bottom = 96print.paper.source = 0 print.paper.size = 0 print.canusedefaultprinter=yesprint.prompt=no print.buttons=no print.preview.buttons=no print.cliptext=noprint.overrideprintjob=no print.collate=yes hidegrayline=no )

After the PowerBuilder release number comes the DataWindow properties, such as the color and print information. Note the new DataWindow features for 9.0, such as hidegrayline, in the above source code. Another DataWindow property that's worth pointing out is the processing property. This specifies the DataWindow's Presentation Style:
0 - (Default) Form, Group, Query, Tabular, N-UP, Label
1 - Grid
2 - Label
3 - Graph
4 - Crosstab
5 - Composite
7 - RichText

The next time you want to change the style of a DataWindow, there's no need to re-create the entire DataWindow. Just change the processing property in the source code. Also at runtime, using dot notation or Describe, the "processing" attribute can be used to determine the DataWindow style. You're not allowed to change the DataWindow Presentation Style at runtime.
Band Properties
header(height=256 color="536870912" )summary(height=92 color="536870912" )footer(height=0 color="536870912" )detail(height=68 color="536870912"height.autosize=yes)

The Band Properties section consists of one statement for each band in the DataWindow. It describes the properties of each band; for example, its height, color, and any expressions it may have. Actually this section is not mandatory, as PowerBuilder will create these four bands even if you don't specify that it do so. If the band properties are not supplied, they'll be created with a height of zero. If your DataWindow contains groups, they won't be specified here. Group "bands" in PowerBuilder are specified elsewhere in the source code.
Source Definition:

Listing 1

table(column=(type=char(20) updatewhereclause=yes name=emp_lname dbname="employee.emp_lname" )column=(type=char(20) updatewhereclause=yes name=emp_fname dbname="employee.emp_fname")column=(type=decimal(3) updatewhereclause=yes name=salary dbname="employee.salary")retrieve="PBSELECT( VERSION(400) TABLE(NAME=~"employee~" )COLUMN(NAME=~"employee.emp_lname~") COLUMN(NAME=~"employee.emp_fname~") COLUMN(NAME=~"employee.salary~")WHERE( EXP1 =~"~~~"employee~~~".~~~"state~~~"~" OP =~"=~" EXP2 =~"'TX'~" ) )ORDER(NAME=~"employee.emp_lname~" ASC=yes ) ")

The source code in Listing 1 has been cosmetically aligned for readability purposes. It's divided into two sections. The first section describes the result set, specifically:

- Data types
- Update characteristics
- Database column names
- Default values

The type property defines the PowerBuilder data type for the column. This property can be changed whenever PowerBuilder fails to correctly determine the data type of a database column. This often happens when PowerBuilder is working with less common data types and time stamps.
The second portion of Listing 1 specifies the SQL source, including any PowerBuilder-defined retrieval argument. This section also describes the SQL that will generate the result set.
The SQL source is actually stored internally in a generic PowerBuilder dialect called PBSELECT. This is how the SQL gets generated when the SQL statement is "Painted". If you choose the "Convert to Syntax" option and type in the SQL statement, PowerBuilder stores the statement and standard SQL:

retrieve=" SELECT ~"employee~".~"emp_lname~",~"employee~".~"emp_fname~",~"employee~".~"salary~"FROM ~"employee~"WHERE ~"employee~".~"state~" = 'TX'ORDER BY ~"employee~".~"emp_lname~" ASC" )

Object Definitions

This section contains all the other objects in the DataWindow. It contains important information as to which band each object belongs to. Objects such as columns, text objects, computed fields, and drawing objects are found here.

Listing 2

text(band=header alignment="0" text="Emp Fname" border="0" color="33554432"x="654" y="172" height="56" width="576" html.valueishtml="0"name=emp_fname_t visible="1" font.face="Arial" font.height="-8" font.weight="700"font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )column(band=detail id=1 alignment="0" tabsequence=32766 border="0" color="33554432"x="73" y="4" height="60" width="576" format="[general]" html.valueishtml="0"name=emp_lname visible="1" edit.limit=20 edit.case=any edit.autoselect=yesedit.autohscroll=yes edit.imemode=0 font.face="Arial" font.height="-8" font.weight="400"font.family="2" font.pitch="2" font.charset="0" background.mode="1" background.color="536870912" )line(band=header x1="73" y1="248" x2="1559" y2="248" name=l_1 visible="1" pen.style="0"pen.width="5" pen.color="33554432" background.mode="2" background.color="1073741824" )

Listing 2 provides the object definitions for a column, text, and line object.
Notice that the code in the listing is literally a help file to see which properties belong to which objects.

DataWindow HTML/XML Properties

This final section contains all the HTML/XML properties that are associated with the DataWindow. Many of these are new to PowerBuilder 9.0.


htmltable(border="1" )htmlgen(clientevents="1" clientvalidation="1" clientcomputedfields="1"clientformatting="0" clientscriptable="0"generatejavascript="1" encodeselflinkargs="1" netscapelayers="0" )export.xml(headgroups="1" includewhitespace="0"metadatatype=0 savemetadata=0 )import.xml()export.pdf(method=0 distill.custompostscript="0" xslfop.print="0" )

Creating Dynamic Objects

Getting the Syntax : Now that you've seen a naked DataWindow, it should be easy to create and destroy DataWindow objects. Why? Because you've already seen the syntax. For example, let's say that when you print a DataWindow, you want to add a computed field containing the page number. To do this, create the computed file on the DataWindow, then open it up in the source editor. The source code for our new computed field looks like

compute(band=header alignment="1" expression="'Page ' + page() + ' of ' + pageCount()"border="0"color="33554432" x="1157" y="24" height="88" width="466" format="[general]"html.valueishtml="0" name=page_1 visible="1" font.face="Arial" font.height="-10"font.weight="400" font.family="2" font.pitch="2" font.charset="0" background.mode="2" background.color="1073741824" )

The DataWindow did us a favor and built the syntax that we're now going to use to build this object dynamically. At this point, copy and paste the source code to a safe place, then delete the object in the DataWindow painter.
Adding the CodeA logical place to put the code is the DataWindow PrintStart Event. In PrintStart we can place the code to create our computed column. When the DataWindow is finished printing, we can destroy the object in PrintEnd.

Listing 4:

dw_1.Modify("create compute(band=header alignment=~"1~" expression=~"'Page ' + page() + ' of '+ pageCount()~"border=~"0~" color=~"33554432~" x=~"1157~" y=~"24~" height=~"88~"width=~"466~" format=~"[general]~" html.valueishtml=~"0~" name=page_1 visible=~"1~"font.face=~"Arial~" font.height=~"-10~" font.weight=~"400~" font.family=~"2~" font.pitch=~"2~"font.charset=~"0~" background.mode=~"2~" background.color=~"1073741824~" )")

Listing 5:

dw_1.Modify("destroy compute(band=header alignment=~"1~" expression=~"'Page ' + page() + ' of' + pageCount()~"border=~"0~" color=~"33554432~" x=~"1157~" y=~"24~" height=~"88~"width=~"466~" format=~"[general]~" html.valueishtml=~"0~" name=page_1 visible=~"1~"font.face=~"Arial~" font.height=~"-10~" font.weight=~"400~" font.family=~"2~" font.pitch=~"2~"font.charset=~"0~" background.mode=~"2~" background.color=~"1073741824~" )")

Listing 4 provides code for the PrintStart Event, and Listing 5 provides code for the PrintEnd Event.
By using the create function within Modify, when printed the DataWindow will contain a computed column containing the page number. The destroy function cleans it up. As you can see, what looked like a very cumbersome create syntax basically becomes a copy-and-paste job. The secret is to create the object in the DataWindow painter, copy the source code it generated, delete the object on the DataWindow, and paste the code into a Modify statement in the event of your choice.
Summary :

Creating a dynamic DataWindow looks like a daunting task. But as you can see, if you know what a naked DataWindow looks like, the job becomes much easier.

2 comments:

Unknown said...

Hi, probably our entry may be off topic but anyways, I have been surfing around your blog and it looks

very professional. It’s obvious you know your topic and you appear fervent about it. I’m developing a

fresh blog plus I’m struggling to make it look good, as well as offer the best quality content. I have

learned much at your web site and also I anticipate alot more articles and will be coming back soon.Thanks you.




ASC Coding

Sanjiv Singh said...

can you please help me converting below PBSELECT statement into SELECT format.


PBSELECT( VERSION(400)
TABLE(NAME="table_barcode" )
TABLE(NAME="table_barcode_attrib_map" )
TABLE(NAME="table_barcode_attribute" )
COLUMN(NAME="table_barcode.label_name")
COMPUTE(NAME="IsNull(table_barcode_attrib_map.value,0) pacmed_valid")
JOIN (LEFT="table_barcode.barcode_id" OP ="=" RIGHT="table_barcode_attrib_map.barcode_id" OUTER1 ="table_barcode.barcode_id" )
JOIN (LEFT="table_barcode_attrib_map.attribute_id" OP ="=" RIGHT="table_barcode_attribute.attribute_id" OUTER1 ="table_barcode_attrib_map.attribute_id" )
WHERE( EXP1 ="( ~~"table_barcode~~".~~"barcode_id~~"" OP ="=" EXP2 =":as_barcode_id )" LOGIC ="and" )
WHERE( EXP1 ="table_barcode_attribute.attribute_name" OP ="=" EXP2 ="'PACMED VALID FLAG'" ) )
ARG(NAME = "as_barcode_id" TYPE = string)

Custom Search