9. Fundamentals2

Tilde sign (~)  : this is used to escape special characters.  Used mostly  in modifying datawindow.

     Ex.dw_1.Modify("DataWindow.Table.update = ~"table1~"");

groupcalc() :Use GroupCalc to force the DataWindow object to recalculate the breaks in the grouping levels after you have added or modified rows in a DataWindow. I came across it when i had to reevaluate the grouped values of the datawindow on particular operation of filtering when datawindow is in hiddden state. 

ex. dw_1.groupcalc( );

SqlPreview : This event is triggered just before the datawindow query is executed on db.

  arguements passed to this event:

    -sqlsyntax :Query used in datawindow

   - SQLType: A number identifying the type of SQL statement being sent to the database.

    and so on (request, Buffer, row)

 Regeneration : when you modify an ancestor object, you can regenerate descendants so they pick up the code changes to the ancestor.

To Create Dynamic Datawindow:

SQLCA.SyntaxFromSQL(select string, presentation string, Error string)

dw_1.Create(syntax, Error string)

dw_1.SetTransObject(SQLCA)

dw_1.Rerieve()

8. PowerBuilder Fundamentals1

Scope of Variables used in PB:
Local:
 Local variable is a temporary variable only declared in the script itself and they are available only in the script where they were declared, when the script is finished execution the local variable is destroyed.
Instance:
 Instance variable that belongs to an object and associated with an instance of object. It allows values to be shared between scripts in the same object.
Shared:
A shared variable is also called a class variable.  There is one copy of it
that is shared by all instances of the class. More of like static variable.
Global:
 Global variable can access anywhere in the application. Can access any object in the application. It has public access level.


PBR File:
When a resource is referenced at runtime, if the resource has not been included in the executable file or in a dynamic library, PowerBuilder looks for it in the search path i.e. (current directory, windows directory & directories listed in path variable)
While adding external resource like image (such as BMP, CUR, ICO), sounds, etc.,  we need to specify this in PBR file.
The path in PBR files and PB object source must be an exact match.
After this, include PBR file in the PBR file section in your project as the Resource file name.


Datastore:
- DataStore is a non-visual control of the data window, which can store data for applications without any graphics resources consumption. In addition to a number of visual characteristics, its functions and conduct the same control and data window.
- Used when  user doesn't need to see the data itself

// declare the variable
   DataStore  lds_datastore    
  // create the control
   lds_datastore = Create DataStore
  // assign the datawindow object to datastore
   lds_datastore.DataObject = "d_datawindow"
   // bind the data
   lds_datastore.SetTransObject(SQLCA)
   lds_datastore.Retrieve()  
  // destroy datastore
  DESTROY myDataStore



Structures:
A structure is a collection of one or more related variable of the same or different data types grouped under a single name.
Useful with OpenSheetwithParm () function to pass more than one parameters to other pb object.
Global - Not associated with any objects.
Object Level - Associated with types of windows, menu or user object.

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.