PowerBuilder Dynamic Application Techniques

1
PowerBuilderDynamic Application Techniques

• Sea Bass Software Limited
• www.seabass.co.uk

2
Seminar Description

• This seminar covers basic techniques for
  developing generic or data driven applications.
• Topics include
• Dynamic datawindow creation and modification
• Dynamic tab controls
• Deployment of COM servers.

3
Section 1

• Dynamically Creating DataWindows

4
Topics

• Look at DataWindow syntax
• ltDataWindowgt.Create function
• LibraryExport function
• lttransactiongt.SyntaxFromSQL function
• Dynamically create DataWindow

5
DataWindow Syntax

• d_tables
• release 10
• DataWindow(units0 timer_interval0
  color1073741824 processing0 HTMLDWno
  print.printername"" print.documentname""
  print.orientation 0 print.margin.left 110
  print.margin.right 110 print.margin.top 96
  print.margin.bottom 96 print.paper.source 0
  print.paper.size 0 print.canusedefaultprinterye
  s print.promptno print.buttonsno
  print.preview.buttonsno print.cliptextno
  print.overrideprintjobno print.collateyes
  hidegraylineno )
• header(height84 color"536870912" )
• summary(height0 color"536870912" )
• footer(height0 color"536870912" )
• detail(height84 color"536870912" )
• table(column(typechar(128) updatewhereclauseyes
  nametable_name dbname"table_name" )
• retrieve"select table_name from systable where
  creator 1 and table_name not like 'pb' order
  by table_name" )
• text(bandheader alignment"2" text"Table Name"
  border"6" color"33554432" x"5" y"4"
  height"64" width"1211" html.valueishtml"0"
  nametable_name_t visible"1" font.face"Tahoma"
  font.height"-10" font.weight"400"
  font.family"2" font.pitch"2" font.charset"0"
  background.mode"2" background.color"12632256" )
• column(banddetail id1 alignment"0"
  tabsequence32766 border"0" color"33554432"
  x"5" y"4" height"76" width"3538"
  format"general" html.valueishtml"0"
  nametable_name visible"1" edit.limit128
  edit.caseany edit.autoselectyes
  edit.autohscrollyes font.face"Tahoma"
  font.height"-10" font.weight"400"
  font.family"2" font.pitch"2" font.charset"0"
  background.mode"1" background.color"536870912"
  )
• htmltable(border"1" )
• htmlgen(clientevents"1" clientvalidation"1"
  clientcomputedfields"1" clientformatting"0"
  clientscriptable"0" generatejavascript"1"
  encodeselflinkargs"1" netscapelayers"0" )
• xhtmlgen() cssgen(sessionspecific"0" )
• xmlgen()
• xsltgen()
• jsgen()
• export.xml(headgroups"1" includewhitespace"0"
  metadatatype0 savemetadata0 )

6
ltDataWindowgt.Create function

• Creates a DataWindow object using DataWindow
  source code and puts that object in the specified
  DataWindow control or DataStore object.
• Does not become a permanent part of the
  application source library.

7
LibraryExport function

• Exports an object from a library. The object is
  exported as syntax.
• LibraryExport ( libraryname, objectname,
  objecttype )
• You can obtain the syntax of any DataWindow as a
  string.
• See LibraryImport for saving dynamically created
  DataWindows

8
lttransactiongt.SyntaxFromSQL

• Generates DataWindow source code based on a SQL
  SELECT statement.
• lttransactiongt.SyntaxFromSQL ( sqlselect,
  presentation, err )
• sqlselect A string whose value is a valid SQL
  SELECT statement.
• presentation A string whose value is the default
  presentation style you want for the DataWindow.
• err A string variable to which PowerBuilder will
  assign any error messages that occur.

9
lttransactiongt.SyntaxFromSQL

• string ls_view,ls_type
• string ls_ERRORS, ls_sql_syntax
• string ls_presentation, ls_dwsyntax
• //Get the presentation style
• choose case true
• case rb_grid.checked
• ls_presentation "style(typegrid)"
• case rb_form.checked
• ls_presentation "style(typeform)"
• case rb_tabular.checked
• ls_presentation "style(typetabular)"
• end choose
• //Assemble SQL string
• ls_sql_syntax 'SELECT FROM '
  this.GetItemString(currentrow,'table_name')
• //Generate DW syntax
• ls_dwsyntax SQLCA.SyntaxFromSQL(ls_sql_syntax,
  ls_presentation, ls_ERRORS)

10
Example 1

• Dynamically creating a DataWindow

11
Step 1 of 2

• Add code to create DataWindow to rowfocuschanged
  event of dw_tables (see next page for code)
• Add code to New, Delete, Save and Cancel buttons

12
Step 2 of 2

• string ls_view,ls_type
• string ls_ERRORS, ls_sql_syntax
• string ls_presentation, ls_dwsyntax
• //Get the presentation style
• choose case true
• case rb_grid.checked
• ls_presentation "style(typegrid)"
• case rb_form.checked
• ls_presentation "style(typeform)"
• case rb_tabular.checked
• ls_presentation "style(typetabular)"
• end choose
• //Assemble SQL string
• ls_sql_syntax 'SELECT FROM '
  this.GetItemString(currentrow,'table_name')
• //Generate DW syntax
• ls_dwsyntax SQLCA.SyntaxFromSQL(ls_sql_syntax,
  ls_presentation, ls_ERRORS)

13
Section 2

• Dynamically Altering WHERE Clause

14
Topics

• Look at DataWindow syntax again
• ltDataWindowgt.Describe function
• ltDataWindowgt.Modify function
• DataWindow Syntax Utility
• Dynamically modify DataWindow where clause

15
DataWindow Syntax Again

• d_tables
• release 10
• DataWindow(units0 timer_interval0
  color1073741824 processing0 HTMLDWno
  print.printername"" print.documentname""
  print.orientation 0 print.margin.left 110
  print.margin.right 110 print.margin.top 96
  print.margin.bottom 96 print.paper.source 0
  print.paper.size 0 print.canusedefaultprinterye
  s print.promptno print.buttonsno
  print.preview.buttonsno print.cliptextno
  print.overrideprintjobno print.collateyes
  hidegraylineno )
• header(height84 color"536870912" )
• summary(height0 color"536870912" )
• footer(height0 color"536870912" )
• detail(height84 color"536870912" )
• table(column(typechar(128) updatewhereclauseyes
  nametable_name dbname"table_name" )
• retrieve"select table_name from systable where
  creator 1 and table_name not like 'pb' order
  by table_name" )
• text(bandheader alignment"2" text"Table Name"
  border"6" color"33554432" x"5" y"4"
  height"64" width"1211" html.valueishtml"0"
  nametable_name_t visible"1" font.face"Tahoma"
  font.height"-10" font.weight"400"
  font.family"2" font.pitch"2" font.charset"0"
  background.mode"2" background.color"12632256" )
• column(banddetail id1 alignment"0"
  tabsequence32766 border"0" color"33554432"
  x"5" y"4" height"76" width"3538"
  format"general" html.valueishtml"0"
  nametable_name visible"1" edit.limit128
  edit.caseany edit.autoselectyes
  edit.autohscrollyes font.face"Tahoma"
  font.height"-10" font.weight"400"
  font.family"2" font.pitch"2" font.charset"0"
  background.mode"1" background.color"536870912"
  )
• htmltable(border"1" )
• htmlgen(clientevents"1" clientvalidation"1"
  clientcomputedfields"1" clientformatting"0"
  clientscriptable"0" generatejavascript"1"
  encodeselflinkargs"1" netscapelayers"0" )
• xhtmlgen() cssgen(sessionspecific"0" )
• xmlgen()
• xsltgen()
• jsgen()
• export.xml(headgroups"1" includewhitespace"0"
  metadatatype0 savemetadata0 )

16
ltDataWindowgt.Describe

• ltDataWindowgt.Describe(ls_request)
• s this.Describe(This.GetColumnName()
  ".ColType")
• s this.Describe("DataWindow.FirstRowOnPage")
• s this.Describe( "Evaluate('LookUpDisplay(state_
  code) ', " rownumber ")")

17
ltDataWindowgt.Modify

• ltDataWindowgt.Modify(ls_request)
• dw_cust.Modify("DataWindow.Color 255")
• dw_cust.Modify("salary.Color'0tIf(salarygt90000,2
  55,65280)'")
• dw_cust.Modify("dept_id.Color'0t "
  "If(dept_id200,65380,If(dept_id100,255,0))'")

18
DataWindow Syntax Utility
19
Dynamically modify DataWindow where clause

• string l_sql
• l_sql dw_data.Describe ('DataWindow.Table.Select
  ')
• l_sqll_sql ' ' sle_where.text
• dw_data.Modify('DataWindow.Table.Select
  "'l_sql'"')
• dw_data.Retrieve()

20
Example 2

• Dynamically altering a DataWindow WHERE clause

21
Step 1 of 2

• Add a single line edit and command button

22
Step 2 of 2

• Add code to buttton
• string l_sql
• l_sql dw_data.Describe ('DataWindow.Table.Select
  ')
• l_sqll_sql ' ' sle_where.text
• dw_data.Modify('DataWindow.Table.Select
  "'l_sql'"')
• dw_data.Retrieve()

23
Section 3

• Tab Controls

24
Topics

• Tab Control Basics
• Tab Page Basics
• Dynamically Opening Tab Pages
• Referencing Tab Pages

25
Tab Control Basics

• Primarily a wrapper for pages
• Tab control has its own events and functions
• Has a control array containing references to its
  pages

26
Tab Page Basics

• Custom Visual User Objects
• Can either be created as part of the Tab Control
• Normally created as distinct Custom Visual User
  Objects
• Have all the normal events and functions of a
  Custom Visual User Object

27
Dynamically Opening Tab Pages

• OpenTab
• Opens a custom visual user object of a known
  datatype as a tab page in a Tab control.
• tabcontrolname.OpenTab ( userobjectvar, index )

28
Example

• userobject luo
• integer li_row
• integer li_rows
• string ls_name
• boolean lb_done
• integer li_tab
• integer li_tabs
• li_rows dw_data.RowCount()
• IF dw_data.of_get_column_type (sle_group.text)
  'string' THEN
• For li_row 1 to li_rows
• ls_name dw_data.GetItemString(li_row,sle_group
  .text)
• li_tabs upperbound(tab_group.control)
• lb_done False
• For li_tab 1 to li_tabs
• IF tab_group.controlli_tab.text ls_name
  THEN
• lb_done True
• END IF

29
Example 3

• Dynamic Tab Pages

30
Step 1 of 3

• Add a single line edit and command button
• Add a tab control and delete the default page
• Send the tab control to back

31
Step 2 of 3

• Code Apply button
• userobject luo
• integer li_row
• integer li_rows
• string ls_name
• boolean lb_done
• integer li_tab
• integer li_tabs
• li_rows dw_data.RowCount()
• IF dw_data.of_get_column_type (sle_group.text)
  'string' THEN
• For li_row 1 to li_rows
• ls_name dw_data.GetItemString(li_row,sle_group
  .text)
• li_tabs upperbound(tab_group.control)
• lb_done False
• For li_tab 1 to li_tabs
• IF tab_group.controlli_tab.text ls_name
  THEN

32
Step 3 of 3

• Code selectionchanged
• string ls_name
• ls_name this.controlnewindex.text
• dw_data.of_where ('where 'sle_group.text'
  ''ls_name''')

33
Section 4

• Creating OLE Servers

34
Topics

• Create a Generic Export NVO
• Deploy NVO as COM object
• Connect to COM Server

35
Create a Generic Export NVO

• COM objects are NVOs
• Any functionality you want to deploy must be in a
  NVO NO VISUAL COMPONENTS!

36
Example

• n_export.of_export
• string ls_view,ls_type
• string ls_ERRORS, ls_sql_syntax
• string ls_presentation, ls_dwsyntax
• integer li_r
• datastore lds_data
• lds_data create datastore
• ls_presentation "style(typetabular)"
• //Assemble SQL string
• ls_sql_syntax 'SELECT FROM ' as_table
• //Generate DW syntax
• ls_dwsyntax SQLCA.SyntaxFromSQL(ls_sql_syntax,
  ls_presentation, ls_ERRORS)
• li_r -1

37
Deploy NVO as COM object

• Create a COM/COM Target
• Copy over NVOs

38
Create the COM DLL
39
Register the COM DLL

• regsvr32.exe c\at\export.dll

40
View in Class Broswer
41
Connect to COM Server

• ConnectToNewObject (ltole_objectgt)
• OLEObject ole_obj
• integer li_r
• ole_obj CREATE OLEObject
• li_r ole_obj.ConnectToNewObject("PB100.n_export.
  1.0")
• li_r ole_obj.of_export(sle_table.text,sle_file.t
  ext)
• li_r ole_obj.DisconnectObject()

42
Example 4

• Creating OLE Servers

43
Step 1 of 4

• Create NVO n_export, add function of_export
• string ls_view,ls_type
• string ls_ERRORS, ls_sql_syntax
• string ls_presentation, ls_dwsyntax
• integer li_r
• datastore lds_data
• lds_data create datastore
• ls_presentation "style(typetabular)"
• //Assemble SQL string
• ls_sql_syntax 'SELECT FROM ' as_table
• //Generate DW syntax
• ls_dwsyntax SQLCA.SyntaxFromSQL(ls_sql_syntax,
  ls_presentation, ls_ERRORS)
• li_r -1

44
Step 2 of 4

• Create test harness window
• Save button code
• n_export ln_export
• ln_export create n_export
• ln_export.of_export(sle_table.text,sle_file.text)

45
Step 3 of 4

• Create Com/COM Component Target
• Create COM DLL
• Register DLL

46
Step 4 of 4

• Create test harness window
• Save button code
• OLEObject ole_obj
• integer li_r
• ole_obj CREATE OLEObject
• li_r ole_obj.ConnectToNewObject("PB100.n_export.
  1.0")
• li_r ole_obj.of_export(sle_table.text,sle_file.t
  ext)
• li_r ole_obj.DisconnectObject()

47
QA

• Simon Boulton
• Sea Bass Software
• www.seabass.co.uk
• simon.boulton_at_seabass.co.uk