7. Datawindow Operations using Code

Multiple table update on Datawindow

Updates on Data window can be applied using two overloads:

dwcontrol.Update()
dwcontrol.Update ( { boolean accept {, boolean resetflag } } )

the 2nd overload has two parameters:

- Accept will perform acceptext() before updation. if validation fails on acceptext(),
Update fails.this is by default true on 1st overload of upload.

- resetflag will set the flag that the fields are updated & reflected to Db. By default,
  it is set to true on 1st overload.




Code Implementation

// this will update the table1 set to update on datawindow & set resetflag to false.
  dw_datawindow.update(true,false);

 // disable table update to previous columns of table 1
  dw_datawindow.Modify("column1.Update = No")
  dw_datawindow.Modify("column2.Update = No")
  dw_datawindow.Modify("column3.Key = No")

  // update table changed to this new table 2 & columns
   dw_datawindow.Modify("DataWindow.Table.update = ~"db_name..table_2~"")
   dw_datawindow.Modify("column_1fortable2.Update = Yes")
   dw_datawindow.Modify( "column_2fortable2.Update = Yes")
   dw_datawindow.Modify("column_3fortable2.Update = Yes")
   dw_datawindow.Modify("column_4fortable2.Update = Yes")
   dw_datawindow.Modify("column_5fortable2.Update = Yes")
   dw_datawindow.Modify("column_6fortable2.Key = Yes")


dw_datawindow.Update();  



  // setting back the table1 as updatable table
  dw_datawindow.Modify("DataWindow.Table.update = ~"db_name..table_1~"")
  dw_datawindow.Modify("column1.Update = Yes")
  dw_datawindow.Modify("column2.Update = Yes")
  dw_datawindow.Modify("column3.Key = Yes")

  // disable table update to previous columns
   dw_datawindow.Modify("column_1fortable2.Update = no")
   dw_datawindow.Modify( "column_2fortable2.Update = no")
   dw_datawindow.Modify("column_3fortable2.Update = no")
   dw_datawindow.Modify("column_4fortable2.Update = no")
   dw_datawindow.Modify("column_5fortable2.Update = no")
   dw_datawindow.Modify("column_6fortable2.Key = no")

6. Coding

Calling functions in powerbuilder:

Syntax
{ objectname.} { type } { calltype } { when } name ( { argumentlist } )


objectname :   this(default) ; objectname
type :    function(default); event
calltype : Static(default) ; dynamic

     dynamic keyword is used when there is no need to resolve the function name at compilation. On Running, it resolves the function name & gives error if function doesn't exist.

      this is used when child object have parent reference and we want to call a child function not in parent. 

when : trigger(default) ; post

    When you post a function or event, it is added to the object’s queue and executed in its turn. In most cases, it is executed when the current script is finished; however, if other system events have occurred in the meantime, its position in the queue might be after other scripts. Its return value is not available to the calling script.

isnull vs valid 

isnull() in powerbuilder is to check for nullity of expression or variable.
IsValid() is used to check whether reference is null or initialized.

isvalid(obect type) & isnull(expression/variable).

Debugging Application

For debugging, insert breakpoints on code using right click or use keyboard shortcut to assign.

1.

2.




3.

The buttons highlighted in this is used for debugging.  The yellow pointer on code shows the current execution line on which debugger is.

5. Start Coding

 Variable naming standards is :

[scope -prefix][datatype -prefix]_[name]

Visibility scope	Prefix
Argument	a
Global	g
Instance	i
Local	l
Shared	s

Data Type	Prefix
Blob	blb
Boolean	b
Character	c
Date	d
DateTime	dt
Decimal	dec
Double	db
Integer	i
Long	l
Real	r
String	s
Time	tm

and so on..

So for example for local variable of datatype string , naming should be : ls_variable name.


For autocomplete while coding, we need to activate autoscript   & add shortcut key for Activate autoscript :

Decision constructs:

IF Construct

IF Expression THEN
   Statement
 
    [optional]
 elseif
   Statement
 .
      .
[optional]
      else
   Statement

END IF


Choose Construct

CHOOSE CASE /*expression*/
 CASE /*item*/
  /*statementblock*/
 CASE ELSE
  /*statementblock*/
END CHOOSE

Loop Constructs :

for loop

FOR varname = start TO end STEP increment

statement block

NEXT

Do while loop

DO WHILE expression

statement block

LOOP

4. DataWindows : Strength of Powerbuilder

Datawindow is a control used in powerbuilder for displaying data on the form.  Now this data could be updated, deleted  or a new entry can be inserted. Validations can be put while inserting rows.

Types of data sources that can be linked to data window controls:

•         Quick Select – Used when data comes from one or more tables linked by a foreign key.
•         SQL Select – In addition to above grouping & computed columns can be specified
•         External – the data can be imported from a flat file or populated from the code.
•         Query – used when the data source (SQL statement) is saved in a query object defined in the query painter.
•         Stored Procedure

This could be used to extract ,insert, update Date in Table very efficiently.  We create Data-window specifying the :

• Type of Data-window need : Grid, Tabular, Freeform, etc

           

• Table/s & Columns and own columns evaluated on columns or having arbitrary values for all rows.

• 
  
• 
  

• Specify where clause if needed . We can create it using SQL query or design using UI. For specifying dynamic filter to this datawindow, we need to Add Retrieval Argument. 

     

                   

We can specify display formats, presentation styles, and other data properties to make the data meaningful to users.


Adding Datawindow Control to window.

Code for operations on Datawindow control dw_1 

To set datawindow d1 to datawindow control:
dw_1.dataobject='d1';

To retrieve Datawindow control rows:
dw_1.retrieve( );

Retrieve giving parameters need to retrieve:
dw_1.retrieve( arg1,arg2,arg3, arg4, . . .);

To delete current row:
dw_1.deleterow(dw_1.getrow( ));

To insert row:
dw_1.insertrow( row);
where row is before which you want to insert a row.To insert a row at end,specify 0.

To update all changes  made in datawindow (insertion,deletion,updation)  to  Db:
dw_1.update()
before this update operation

For updating rows at db, Powerbuilder need update clause ;
 for this we need to configure update on datawindow for the table to be updated,

Datawindow dynamic capabilities is beyond the limits of mentioning it here . Datawindow  It would benefit to explore to use best of datawindow using properties of columns

•  Visibility & color ( Background / foreground)
•  Alignment, Size of columns & gap b/w rows.
•  property setting using datawindow expression.
•  Changing column to be of drop-down capability using other datawindow column 
•  Inserting computed columns on the available columns .

3. Setting Database Profile & Db connection

Before Working on any data window object while coding, PowerBuilder should have database connection Profiles -Connection for using dB objects in code or create data window.

For Setup of Db profile, we use below option: 

Once, This is setup , now we could create datawindows . To clear this out, This is just for validating db objects and designing datawindow at development. 

For using the Database while running the application, you need to configure connection using SQLCA object.

SQLCA- Transaction object, used to communicate with your database. 

I am using Sql server Db example here: 

We can use this code to initialize SQLCA object parameters 

SQLCA.DBMS = "SNC SQL Native Client(OLE DB)"

SQLCA.ServerName = "localhost"

SQLCA.LogId = "sa"

SQLCA.DBParm = "Database='trydb',TrustedConnection=1"

Connecting/ Disconnecting to SQLCA  :

connect Using SQLCA;

Code b/w the block to access the db objects.

disconnect using SQLCA;

It is a good practice to use connect statement at open() & disconnect statement at close() event of window or application.

Using connection Service for db connection

We could Create connection service using connection wizard and use in our code to handle db connection. 

There are 3 functions  in Connection Service object.

of_getconnectioninfo() is the one having SQLCA parameter initialized .the code is auto-generated while creating this connection object ,selecting the db parameters .

To use this connection , we can use the following Code described in screenshot or else in our way how we want it to be implemented.

2.Getting Started

To Get Started, We need a Workspace. The workspace is where you build, edit, debug, and run PowerBuilder targets. Workspace File (.pbw) lists all the target files and contents of your project. For one project, We need one workspace. 
This is similar to Solution(.sln) file in c#. 

After this, We need to create target (.pbt)  which is the application to be created. With this  a default library(.pbl) is created.  Libraries(.pbl) in powerbuilder is similar to .proj files in C#.  We can create multiple libraries within a single application .Having different libraries for intended purpose help in reusability and modularity .

The Application has the open() Event fired when the application starts to Run. Here we need to specify the window to open when the application starts. Also , Database connection can be configured here using  SQLCA and closed  in close() event.

1.Introduction

Power-builder is a Integrated Development Environment developed by Sysbase SAP.

The applications written in it use its power-script language.  Power-builder is known for its fluent & easy database connectivity . Most applications on power-builder made are the Desktop applications which needs easy to maintain Database connectivity & DML operations on Table,

 It has a very efficient data handling object DataWindow , for which powerbuilder can be boasted on.

Because of its DataWindow capabilities, it can be used to generate reports, transform  data & can provide validations on data inserted.

Win - Forms executables can be made consisting of  data-windows & inbuilt UI controls .

Powerbuilder  Features:

• Object oriented Capabilites like Inheritance & Polymorphism . 
• Event- driven , functional, Variables with their visiblity scope.
• Supports Breakpoints & debugging capability.
• Data window : Strength of Powerbuilder.  this can be Grid, Tabular, Group & FreeForm.
• Embedded/Dynamic SQL: PowerBuilder supports both embedded and dynamic SQL, including scrollable cursors and Stored Procedure execution.