39677614 Power Builder Training 704n8

Power Builder Training

All rights reserved by Satyam Computers Services Ltd.

Session 1


Introduction To Powerbuilder

 Powerbuilder is a 4GL client/server enterprise

development tool.  Its strongest strength is • Database connectivity • Object – Orientation Implementation.  In its initial releases, PowerBuilder was just used as a client --talking to the Database Server directly.  Powerbuilder applications can be deployed as client, as a middle tier application or both.  PowerBuilder s SQL and stored procedures using the Data Window control andbyembedded/dynamic All rights reserved Satyam Computers Services Ltd.SQL and

Powerbuilder Features

 The following major features are ed by Powerbuilder . • Object-Orientation Implementation • SQL • Data Window • Database Connectivity • Embedded/ Dynamic SQL • Remote Procedure Calls • ODBC Drivers • Version Control Interfaces


Powerbuilder Family of Products

InfoMaker

Infomaker is a subset of PowerBuilder , used by end for generating reports.

PowerDesign er

PowerDesigner s multi- database deg .

 The following are some important products from Powersoft. • Infomaker • PowerDesigner • Adaptive Server Anywhere

• .

Adaptive Server Anywhere

ASA is full-featured transaction processing SQL Database Management System.


Summary

 What we have learnt from the Session1 ? • Definition Of PowerBuilder • Idea about the PowerBuilder features • Overview of various tools /products that could be used in the Real World All rights reserved by Satyam Computers Services Ltd.

Session 2


PowerBuilder Environment

 In this session we will learn about PowerBuilder environment -- such as toolbars, toolbar customization, opening painters, etc..  After this session you will be able to: • Create an icon for PowerBuilder under Windows NT/95. Start and stop PowerBuilder. • Dock toolbars. Customize toolbars. Invoke a painter automatically after starting PowerBuilder. • How To Use PowerBuilder help system.


Prerequisites

 Should have PowerBuilder (Desktop/ Professional/ Enterprise version) installed on your computer.


Starting PowerBuilder

 Double-click -'PowerBuilder x.x For Intel 32’ icon . All rights reserved by Satyam Computers Services Ltd.

Powerbuilder Interface

 On starting the PowerBuilder opens the last application worked on. All rights reserved by Satyam Computer Services Ltd.

Power

While developing you create/test objects using painters. Each painter has an icon associated with it. Selecting 'File/Power' from the menu for seeing the desctriptive text and shortcut for each icon.


Overview Of The Painters Application Painter Application object is the entry point to any PowerBuilder application

Window Painter In any application, Window is the main interface between a and a application.

Menu Painter A menu is a list of options or commands. Example your browser's menu.

Data Window object allows you to retrieve data from a database or other source for display . A data window object consists of the following:  Data Source Definition : That is the data from where to bring i.e. database ,table, column etc.  Presentation Definition: Presentation style of the retrieved data from the database .

Object allows you to reuse the code .It is nothing but a collection of one or more Powerbuilder/Non PowerBuilder objects. Non PB Object includes objects from VB, Dlls ,OCX and so on


Overview Of The Painters A Query Painter allows you to build SQL SELECT statements which can be saved as query objects in the PowerBuilder library. The function painter allows you to build function objects that define a series of frequently executed commands. The Structure Painter allows you to create structure objects. A structure is nothing but a set of related variables (which may have different data types) grouped under a single name. Objects you create in PowerBuilder painters (application, windows, menus, functions, queries, structures, objects and DataWindow objects) are stored in an operating system file with ".pbl" extension (pronounced as pible). All rights reserved by Satyam Computers Services Ltd.

Overview Of The Painters Database Painter allows you to create database objects--such as table, view, index, stored procedure, trigger, etc. without knowing the actual DDL syntax. Project Painter allows you to create PowerBuilder Dynamic Linked Libraries (PBDs), executables. Need to define what libraries need to be included in the project for the first time. A Run icon allows you to execute the application you are currently working on.

A Debug painter allows you to view the execution of the current application step-by-step and simplifies the discovery of bugs. All rights reserved by Satyam Computers Services Ltd.

Toolbars

 What is a toolbar? Answer: A toolbar is nothing but a collection of icons associated with actions.

 All the icons as shown in the picture below the menu, are part of the toolbar called Powerbar. All rights reserved by Satyam Computers Services Ltd.

Types Of Toolbars  PowerBuilder comes with three toolbars: • Powerbar • Painterbar • Stylebar


Powerbar Powerbar is similar to Power in of functionality and contains all options available in Power.


Painterbar

Unlike Powerbar where the toolbar icons are static, Painterbar toolbar icons are dynamic.


Stylebar  Stylebar allows you to change the text style - such as text's font, font size, justification, etc.


Customizing Toolbar  The power of a PowerBuilder toolbar is in its flexibility. It helps in adding or removing icons.


Script Editor

Script editor allows you to write scripts for object events, functions To start script editor for an object, invoke the right mouse popup menu by clicking the right mouse button on an object and select Script .


SUMMARY  In this session we have learnt about the following • Ovetrview of the following Power

o Powerbar o Painterbar o Stylebar.  How to customize the Painterbars ? How to access Script Editor ? All rights reserved by Satyam Computers Services Ltd.

PowerBuilder Environment - Exercises

• Make Powerbar to float. • Display the toolbar text. • Hide the Powerbar and then show it.


Session 3


Target Points of This Session • Previous session familiarized you with the PowerBuilder environment. • This session onwards, you will be learning about various painters ( to create objects ), PowerScript ( to manipulate them ) and finally build a complete project. • After this session you will be able to: Create a PowerBuilder Library. Create a PowerBuilder Application Object. Defining the specifications of 'Product Management System', which you would be developing in the 'PowerBuilder Online Courses'. All rights reserved by Satyam Computers Services Ltd.

Creating Library • To create a library need to invoke the library painter by clicking on icon. • To create a library, select Library/Create from the menu or click on icon.


Creating an Application Object • Application object is the entry point of an application. • You need to invoke an application object painter by clicking on the icon.


Summary • In this session we have learnt the following: • How to create a library ? • How to create a application object


Session 4


Window Painter • This session will help you to understand the following: • Understand what a window is. • Understand about Window Controls and how they work together. • Paint windows. • Preview/Run/Print the window. • Understand different types of windows.


Introduction to Window Painter

• Irrespective of the application ,without the interface its not possible to interact with the software. • The object that PowerBuilder provides is called a "Window .The basic building blocks of the interface are designed, maintained and customized using the Window Painter. All rights reserved by Satyam Computers Services Ltd.

Creating a Window

 Select the New button in the 'Select Window‘ dialog

box.


 Once the window is opened it will look like:


Window Painter


Window Controls  The major window controls are : • SingleLineEdit • MultiLineEdit • Editmask • CommandButton • Picturebutton • ListBox etc.


Properties Of The Controls  Some of the major properties of the controls are : • Visible • Enabled • Focus Rectangle, etc

Visisble

Helps the end s to see the control.

Enabled

This property is not checked by default. If this property is not set, nothing will happen when the clicks on this control.

Focus Rectangle

Visible


Single Line Edit Control

 This control is used to input data from the End –s . All rights reserved by Satyam Computers Services Ltd.

Other Important Controls • • • • • • • • • • • • • • •

MultiLineEdit DropDownPictureListBox Picture EditMask ListView DataWindow Control CommandButton TreeView Object PictureButton CheckBox ListBox RadioButton Vertical Scrollbar DropDownListBox GroupBox Horizontal Scrollbar Tab Control Line Rectangle RoundRectangle Oval Graph etc.. All rights reserved by Satyam Computers Services Ltd.

Session 5


DataWindow controls • DataWindow technology is implemented in two parts: • A DataWindow object The DataWindow object defines the data source and presentation style for the data. • A DataWindow control or component The control or component is a container for the DataWindow object in the application. • You write code that calls methods of the container to manipulate the DataWindow object.


Function of Data Window Controls

• The DataWindow was originally invented for use in PowerBuilder to provide • Powerful data retrieval, manipulation, and update capabilities for client/server applications. • PowerBuilder DataWindow A PowerBuilder control for use in client/server and distributed PowerBuilder applications. All rights reserved by Satyam Computers Services Ltd.

Presentation styles A presentation style defines a typical style of report and handles how rows are grouped on the page. You can customize the way the data is displayed in each presentation style. Important Presentation Styles include: • Tabular Data columns across the page and headers above each column. Several rows are viewable at once. • Freeform Data columns going down the page with labels next to each column. One row displayed at a time. • Grid Row-and-column format like a spreadsheet with grid Label Several labels per page with one row for each label. Used for mailing and other labels. • Composite Several DataWindow objects grouped into a single presentation. Not ed by the Web DataWindow • Graph Graphical presentation of data. Not ed by the Web DataWindow • Crosstab Data summary in a row-and-column format. • RichText Paragraphs of text with embedded data columns. Not ed by the Web DataWindow or the Sybase DataWindow Web control for ActiveX. All rights reserved by Satyam Computers Services Ltd.

Data Sources The data source specifies the following: • DataWindow comes from • What data items are displayed. • Data can come from : – – – –

tables in a database, or You can import data from a file or specify the data in code. For databases, the Data specification is saved in a SQL statement. In all cases, the DataWindow Object saves the names of the data items to display, as well as their data types.


Types Of Data Sources • The following Data Sources are available:

– Quick Select : The data is coming from one or more tables in a SQL database. The tables must be related through a foreign key. – SQL Select :You want more control over the select statement that is generated for the data source. You can specify grouping,computed columns, and so on – Query:The data has already been selected and the SQL statement is saved in a query object that you have defined in the Querypainter. When you define the DataWindow object, the queryobject is incorporated into the DataWindow and does not need to be present when you run the application. – Stored Procedure The data is defined in a database stored procedure. – External The data is not stored in a database, but is imported from a file (such as a tab-separated or dBASE file) or populated from code.


Use Of Data Window Object • Using a DataWindow involves two main steps:  Use the DataWindow painter to create or edit a DataWindow object. In the painter, you define the data source, presentation style, and all other properties of the object, such as display formats, validation rules, sorting and filtering criteria, and graphs.  2 In your development environment, put a DataWindow control in a window, visual object, or form or a DataWindow container in a Webpage and associate a DataWindow object with the control or container. All rights reserved by Satyam Computers Services Ltd.

Data Window Object Architecture The basic architectures are: • Client/server A program running on a client workstation accesses a database running on a server. The interface and business logic reside together on the client computer. • Distributed application The interface on the client computer calls components on a middle-tier server, which execute business logic and access the database server. • Web application A client Web browser sends requests for HTML or JSP documents to a Web server. The Web server es control to a page or application server, where server-side scripts can access components on a transaction server that can connect to databases on a database server.


PowerBuilder DataWindow control • The PowerBuilder DataWindow control – Is a container for DataWindow objects in a PowerBuilder application. – You can use it in a window to present an interactive display of data. The can view and change data and send changes to the database


Data Store Objects • In addition to the DataWindow control, the DataStore object has the following functionalities. – It provides a nonvisual container for server applications and other situations where on-screen viewing is not necessary.


Other Functionalities Of Data-Window Control

• The DataWindow s the following: • It s data retrieval with retrieval arguments and data update. • You can use edit styles, display formats, and validation rules for consistent data entry and display. • The DataWindow provides many methods • Used for manipulating the DataWindow, including Modify for changing DataWindow object properties.


Programming Usage • You write scripts in the Window or Object painter to connect to the database, retrieve data, process input, and update data. • In PowerBuilder, you can take advantage of object inheritance by defining a object inherited from a DataWindow control and adding your own custom functionality. You can reuse the customized DataWindow control throughout your applications.


Libraries and applications • You store DataWindow objects in PowerBuilder libraries (PBLs) during development. • When you build your application, you can include the DataWindow objects in the application executable or in PowerBuilder dynamic libraries (PBDs).


Putting a DataWindow object into a DataWindow control

To use the DataWindow object in an application, you add a DataWindow control to a window or form, then associate that control with the DataWindow object, as illustrated in Figure 2-1: All rights reserved by Satyam Computers Services Ltd.

Naming Convention • The DataWindow object naming standards should follow the following guidelines: – To avoid confusion, you should use different prefixes for DataWindow objects and DataWindow controls. – The prefix d_ is commonly used for DataWindow objects. For example, if the name of the DataWindow control is dw_customer, you might want to name the corresponding DataWindow object d_customer.


Dynamical Changes Of Data Window • In addition to specifying the DataWindow object in the Window painter, you can switch the object that displays in the control during execution by changing the value of the DataObject property in code. For example: to display the DataWindow object d_emp_hist from the library emp.pbl in the DataWindow control dw_emp, you can follow the following Programming Guidelines: dw_emp.DataObject = "d_emp_hist“ : The DataWindow object d_emp_hist was created in the DataWindow painter and stored in a library on the application search path. The control dw_emp is contained in the window and is saved as part of the window definition. All rights reserved by Satyam Computers Services Ltd.

Accessing the database • Before you can display data in a DataWindow control, you must get the data stored in the data source into that control. The most common way to get the data is to access a database. • An application goes through several steps in accessing a database: – Set the appropriate values for the transaction object. – Connect to the database. – Set the transaction object for the DataWindow control. – Retrieve and update data. – Disconnect from the database. All rights reserved by Satyam Computers Services Ltd.

Database Connectivity Mechanism • Setting the transaction object for the DataWindow control There are two ways to handle database connections and transactions for the DataWindow control. You can use: • Internal transaction management • A separate transaction object

• The above two methods provide different levels of control over database transactions.


Mechanism Of Internal Transaction Object • When the DataWindow control uses internal transaction management, it handles connecting, disconnecting, commits, and rollbacks. It automatically performs connects and disconnects as needed; any errors that occur cause an automatic rollback. • Whenever the DataWindow needs to access the database (such as when a Retrieve or Update method is executed), the DataWindow issues an internal CONNECT statement, does the appropriate data access, then issues an internal DISCONNECT. All rights reserved by Satyam Computers Services Ltd.

Limitations of Internal Transaction Object

• When not to use it?  Do not use internal transaction management when: Your application requires the best possible performance  Internal transaction management is slow and uses considerable system resources because it must connect and disconnect for every database access. 

 You want control over when a transaction is committed or rolled back. Because internal transaction management must disconnect after a database access, any changes are always committed immediately.


How To Use Internal Transaction Object

• To use internal transaction management, you specify connection values for a transaction object, which could be the automatically instantiated SQLCA. • Call the SetTrans method, which copies the values from a specified transaction object to the DataWindow control's internal transaction object. SQLCA.DBMS = ProfileString("myapp.ini", & "database", "DBMS", " ") ... // Set more connection parameters dw_employee.SetTrans(SQLCA) dw_employee.Retrieve( )


SetTransObject

• The SetTransObject method associates • A transaction object with the DataWindow control. • PowerBuilder has a default transaction object called SQLCA that is automatically instantiated. • You can set its connection properties, connect, and assign it to the DataWindow control. // Set connection parameters in the transaction object SQLCA.DBMS = ... SQLCA.database = ... CONNECT USING SQLCA; dw_emp.SetTransObject(SQLCA) dw_emp.Retrieve( )


Transaction Object • Instead of or in addition to using the predefined SQLCA transaction object, you can define your own transaction object in a script. • This is necessary if your application needs to connect to more than one database at the same time. • The following statement uses SetTransObject to associate dw_customer with a programmer-created transaction object (trans_customer): transaction trans_customer trans_customer = CREATE transaction // Set connection parameters in the transaction object trans_customer.DBMS = ... trans_customer.database = ... CONNECT USING trans_customer; dw_customer.SetTransObject(trans_customer) dw_customer.Retrieve( ) All rights reserved by Satyam Computers Services Ltd.

Retrieving and updating data

• You call the following two methods to access a database through a DataWindow control: – Retrieve – Update

• After you have set the transaction object for your DataWindow control, you can use the Retrieve method to retrieve data from the database into that control: dw_emp.Retrieve( ) • Omitting retrieval arguments If your DataWindow object takes retrieval arguments but you do not them in the Retrieve method, the DataWindow control prompts the for them when Retrieve is called. All rights reserved by Satyam Computers Services Ltd.

Retrieval Arguments • Retrieval arguments qualify the SELECT statement associated with the DataWindow object, reducing the rows retrieved according to some criteria. • For example, in the following SELECT statement, Salary is a retrieval argument defined in the DataWindow painter: SELECT Name, emp.sal FROM Employee WHERE emp.sal > :Salary • When you call the Retrieve method, you supply a value for Salary. In PowerBuilder, the code looks like this: dw_emp.Retrieve( 50000 ) All rights reserved by Satyam Computers Services Ltd.

Updating data • After s have made changes to data in a DataWindow control, you can use the Update method to save those changes in the database. • In PowerBuilder, the code looks like this: dw_emp.Update() • Update sends to the database all inserts, changes, and deletions made in the DataWindow control since the last Update method.


Example The following example gives an idea about Updates: // Connect to the database specified in the

// transaction object EmpSQL CONNECT USING EmpSQL; // Set EmpSQL as the transaction object for dw_emp dw_emp.SetTransObject(EmpSQL) // Retrieve data from the database specified in // EmpSQL into dw_emp dw_emp.Retrieve() // Make changes to the data... ... // Update the database IF dw_emp.Update() > 0 THEN COMMIT USING EmpSQL; ELSE ROLLBACK USING EmpSQL; END IF // Disconnect from the database DISCONNECT USING EmpSQL;


Datawindow buffers • The Data Window Controls maintains the following Buffers. • Primary: Data that has not been deleted or filtered out (that is, the rows that are viewable) • Filter : Data that was filtered out • Delete : Data that was deleted by the or through code All rights reserved by Satyam Computers Services Ltd.

How Edit Control Is Working In This Scenario? • As the moves around the DataWindow control, the DataWindow places an edit control over the current cell (row and column):


What Is Text ? • The contents of the edit control are called text. Text is data that has not yet been accepted by the DataWindow control. • Data entered in the edit control is not in a DataWindow buffer yet; it is simply text in the edit control.


What Is Item? • When the changes the contents of the edit control and presses ENTER or leaves the cell (by tabbing, using the mouse, or pres ARROW or DOWNARROW), the DataWindow processes the data and either accepts or rejects it • It depends on whether it meets the requirements specified for the column. • If the data is accepted, the text is moved to the current row and column in the DataWindow Primary buffer. The data in the Primary buffer for a particular column is referred to as an item.


How text is processed in the edit control

• When the data in a column in a DataWindow has been changed and the column loses focus (for example, because the tabs to the next column), the following sequence of events occurs: • The DataWindow control converts the text into the correct datatype for the column. For example, if the is in a numeric column, the DataWindow control converts the string that was entered into a number. If the data cannot be converted, the ItemError event is triggered. • If the data converts successfully to the correct type, the DataWindow control applies any validation rule used by the column. If the data fails validation, the ItemError event is triggered. • If the data es validation, then the ItemChanged event is triggered. If you set an action/return code of 1 in the ItemChanged event, the DataWindow control rejects the data and does not allow the focus to change. In this case, the ItemError event is triggered.

• If the ItemChanged event accepts the data, the ItemFocusChanged event is triggered next and the data is stored as an item in a buffer.


How Text Is Processed In Edit Controls?


Action/Return Codes • You can affect the outcome of events by specifying numeric values in the event’s program code. • For example, step 3 above describes how you can force data to be rejected with a code of 1 in the ItemChanged event. • To specify action/return codes: • PowerBuilder DataWindow Use a RETURN statement All rights reserved by Satyam Computers Services Ltd.

Accessing the text in the edit control • The following methods allow you to access the text in the edit control: •

GetText—Obtains the text in the edit control • SetText—Sets the text in the edit control

• In addition to these methods, the following events provide access to the text in The edit control: • EditChanged • ItemChanged • ItemError All rights reserved by Satyam Computers Services Ltd.

Sample Code In Item Changed Event

• This is the PowerBuilder version of the code for The Itemchanged Event: int a, age age = Integer(sle_age.text) a = Integer(data) // Set the return code to 1 in the ItemChanged // event to tell PowerBuilder to reject the data // and not change the focus. IF a < age THEN RETURN 1


ItemError event • The ItemError event is triggered if there is a problem with the data. By default, it rejects the data value and displays a message box. You can write code for the – ItemError event to do some other processing. – For example, you can set a code to accept the data value, or reject the data value but allow focus to change.


Accessing the Items in a DataWindow • You can access data values in a DataWindow by using methods or DataWindow data expressions. Both methods allow you to access data in any buffer and to get original or current values. • There are several methods for manipulating data in a DataWindow control. • These methods obtain the data in a specified row and column in a specified buffer Like for example :      

GetItemDate, GetItemDateTime, GetItemDecimal, GetItemNumber, GetItemString, GetItemTime All rights reserved by Satyam Computers Services Ltd.

Examples Of Get Functions • For example, the following statement, using PowerBuilder syntax, picks the value from the empname column of the first row to the variable ls_Name: • ls_Name = dw_1.GetItemString (1,"empname")


SETITEM • This PowerBuilder statement sets the value of the empname column in the first row to the string Waters: • dw_1.SetItem(1, "empname", "Waters")


Expressions In PowerBuilder • Expressions in PowerBuilder The Object property of the DataWindow control lets you specify expressions that refer directly to the data of the DataWindow object in the control. • Example Of Code: • dw_1.Object.jobtitle[3] = "Programmer“

• The next statement sets the value of the first column in the first row in the DataWindow to Smith: • dw_1.Object.Data[1,1] = "Smith"


Other Data-Window Methods • There are many more methods you can use to perform activities in DataWindow controls. – AcceptText: Applies the contents of the edit control to the current item in the DataWindow control. – DeleteRow: Removes the specified row from the DataWindow control, placing it in the Delete buffer.It does not delete the delete the row from the database. – Filter: Displays rows in the DataWindow control based on the current filter. – GetRow: Returns the current row number – InsertRow: Inserts a new row – Reset :Clears all rows in the DataWindow control – Retrieve: Retrieves rows from the database – RowsCopy, RowsMove :Copies or moves rows from one DataWindow – control to another ScrollToRow Scrolls to the specified row – SelectRow :Highlights a specified row – ShareData : Shares data among different DataWindow controls. – Update: Sends to the database all inserts, changes, and deletions that have been made in the DataWindowcontrol All rights reserved by Satyam Computers Services Ltd.

Properties Of Data Window Object • DataWindow object properties store the information that controls the behavior of a DataWindow object. • You can access the properties of a DataWindow object by using the Describe and Modify methods or DataWindow property expressions. • Describe—Reports the values of properties of a DataWindow object and controls within the DataWindow object • Modify—Modifies a DataWindow object by specifying a list of instructions that change the DataWindow object's definition All rights reserved by Satyam Computers Services Ltd.

Examples of Describe and Modified • Describe-Examples: The following statements assign the value of the Border property for the empname column to a string variable: string ls_border ls_border= Describe("empname.Border")

• Modify-Examplesdw_1.: The following statement changes the value of the Border property for the empname column to 1: dw_emp.Modify("empname.Border=1")


Object Property use • Use the Object property and dot notation. For example: integer li_border li_border =Integer(dw_1.Object.empname.Border)

• dw_1.Object.empname.Border = 1


Retrieve And Update Error • Return codes for the Retrieve and Update methods • Retrieve – Retrieve>=1 Retrieval succeeded; returns the number of rows retrieved. – Retrieve =-1 Retrieval failed; DBError event triggered – Retrieve = 0 No data retrieved

• Update – Update =1 Update succeeded. – Update = -1 Update failed; DBError event triggered. – Egs: IF dw_emp.Update() > 0 THEN – COMMIT USING EmpSQL; – ELSE – ROLLBACK USING EmpSQL; – END IF All rights reserved by Satyam Computers Services Ltd.

DBError Event • The DataWindow control triggers its DBError event whenever there is an error following a retrieval or update; that is, if the Retrieve or Update methods return –1. • For example: if you try to insert a row that does not have values for allcolumns that have been defined as not allowing NULL, the DBMS rejects the row and the DBError event is triggered. All rights reserved by Satyam Computers Services Ltd.

DBERROR MESSAGE • By default, the DataWindow control displays a message box describing the error message from the DBMS, as shown here:


How DWControl Updates The Database

• There are four DataWindow item statuses, two of which apply only to rows: – New! – NewModified! – NotModified! – DataModified!


How The Row Statuses Are Set • When data is retrieved : When data is retrieved into a DataWindow, all row and columns initially have a status of NotModified!. • After data has changed in a column in a particular row, either because the changed the data or the data was changed programmatically, such as through the SetItem method, the column status for that column changes to DataModified!. • Once the status for any column in a retrieved row changes to DataModified!, the row status also changes to DataModified!. • When rows are inserted :When a row is inserted into a DataWindow, it initially has a row status of New!, and all columns in that row initially have a column status of NotModified!. After data has changed in a column in the row,either because the changed the data or the data was changed programmatically, such as through the SetItem method, the column status changes to DataModified!. Once the status for any column in the inserted row changes to DataModified!, the row status changes to NewModified!. When a DataWindow column has a default value, the column’s status does not change to DataModified! until the makes at least one actual change to a column in that row. All rights reserved by Satyam Computers Services Ltd.

Row Status After Insert And Update Row Stataus

Sql Statement

• NewModified! • DataModified!

• INSERT • UPDATE

A column is included in an UPDATE statement only if the following two conditions are met: • The column is on the updatable column list maintained by the DataWindow object. For rows in the Delete buffer The DataWindow control generates SQL DELETE statements for any rows that were moved into the Delete buffer using the DeleteRow method. (But if a row has a row status of New! or NewModified! before DeleteRow is called, no DELETE statement is issued for that row.) All rights reserved by Satyam Computers Services Ltd.

Programatically changing the row or column status

• You might need to change the status of a row or column programmatically. • Typically, you do this to prevent the default behavior from taking place. • You use the SetItemStatus method to programmatically change a DataWindow's row or column status information. All rights reserved by Satyam Computers Services Ltd.

Effects of changing from one row status to another

Original Status • New! – New! – NewModified ! Yes – DataModified ! Yes – NotModified! No • NewModified! – New! No – NewModified! – – DataModified! Yes! – NotModified! New! • DataModified! – New! NewModified! – NewModified! Yes – DataModified! – – NotModified! Yes • NotModified! – New! Yes – NewModified! Yes – DataModified! Yes – NotModified! All rights reserved by Satyam Computers Services Ltd.

Dynamically Changing DataWindow Objects

• DataWindow objects and all entities in them (such as columns, text,graphs, and pictures) each have a set of properties. • You can look at and change the values of these properties during execution using DataWindowmethods or property expressions. • You can also create DataWindow objects during execution. All rights reserved by Satyam Computers Services Ltd.

Modifying a DataWindow object • With some DataWindow properties, you can assign a value through an expression that the DataWindow evaluates during execution, instead of havingto assign a value directly. • For example, the following statement displays a salary in red if it is less than $12,000, and in black otherwise: • dw_1.Modify("salary.Color &= '0 ~t if(salary <12000,255,0)' ") All rights reserved by Satyam Computers Services Ltd.

Specifying the DataWindow object syntax • There are several ways to specify or generate the syntax required for the Create method. Not all the techniques are available in all environments. – Use the SyntaxFromSQL method of the transaction object

• SyntaxFromSQL: You are likely to use SyntaxFromSQL to create the syntax for most dynamic DataWindow objects. If you use SyntaxFromSQL, all you have to do is provide the SELECT statement and the presentation style.In PowerBuilder, SyntaxFromSQL is a method of the transaction object. The transaction object must be connected when you call the method. – sqlca.autocommit=TRUE – sqlca.syntaxfromsql (sqlstmt, presentation, err) – sqlca.autocommit=FALSE All rights reserved by Satyam Computers Services Ltd.

Using a custom DataStore object • To use a DataStore, you first need to create an instance of the DataStore object in a script and assign the DataWindow object to the DataStore. Then, if the DataStore is intended to retrieve data, you need to set the transaction object for the DataStore. – – – – – –

datastore lds_datastore lds_datastore = CREATE datastore lds_datastore.DataObject = "d_cust_list" lds_datastore.SetTransObject (SQLCA) lds_datastore.Retrieve() /* Perform some processing on the data... */ All rights reserved by Satyam Computers Services Ltd.

Common methods in DataStore objects • • • • • • • • • • • • • • •

DeleteRow : Deletes the specified row from the DataStore. Filter : Filters rows in the DataStore based on the current filter criteria. InsertRow : Inserts a new row. Print : Sends the contents of the DataStore to the current printer. Reset :Clears all rows in the DataStore. Retrieve :Retrieves rows from the database. RowsCopy : Copies rows from one DataStore to another DataStore or DataWindow control. RowsMove :Moves rows from one DataStore to another DataStore or DataWindow control. ShareData : Shares data among different DataStores or DataWindow controls. See “Sharing information” on page 86. Sort : Sorts the rows of the DataStore based on the current sort criteria. Update :Sends to the database all inserts, changes, and deletions that have been made since the last Update.


SESSION 6


Window Painter • There are six types of windows available in PowerBuilder. They are all created in the same way, but each has a specific task for which it is used. You don't have to specify the window type before you create the window. The default window type is "Main".

• Main Window A Main Window is a stand-alone window, which acts independent of all others. This type of window can be minimized, maximized and resized; they can also overlap and be overlapped by other windows. They are usually used: – In single window applications, – As sheets in MDI (Multiple Document Interface) applications. (We will explain about MDI concepts & programming in the coming sessions.

• Popup Window A Popup Window is typically opened from another window, which is then said to be the popup window's parent window. The popup window is dependent on its parent window, but it can also be displayed without opening it from the parent window. If you do not open the popup window from another window, it will behave like a main window. Parent window will not overlap a popup window, but if a parent is minimized popup window will be hidden. All rights reserved by Satyam Computers Services Ltd.

Window Types • Response WindowA Response Window is the PowerBuilder's equivalent of a Windows dialog box; it is used to accept response from the , to display warnings and so on. A response window demands action from the and once active remains active until the answers. • Child WindowAs in Popup window, a Child Window can only be opened from another window, its parent. Even though you specify the window type as "child", it will behave like a main window, when opened directly, i.e. not opened from another window. "Directly" means from an application object, from a menu option and so on. • MDI Frame Window A MDI Frame Window allows you to open multiple windows as sheets inside it. • MDI Frame with MicroHelpThis window is exactly like an MDI Frame, except that it has an additional feature of a status bar at the bottom of the window. All rights reserved by Satyam Computers Services Ltd.

Menu Painter • PowerBuilder offers the ability to add your custom menus to the application. In this session you will learn about creating menus, while retaining control over what the can and can't do. You will be learning various techniques to create menus, which you will be using in the 'Product Management System' and you will also be looking at some of the other options available in the Menu Painter. All rights reserved by Satyam Computers Services Ltd.

Types of Menus • You can create three different types of menus in PowerBuilder: • DropDown menus • Cascading menus • Popup menus


DropDown Menus • DropDown menus are displayed when you select an option from a menu bar that is on the topside of a window. • The available options are grouped into logical collections and the group names are displayed horizontally on the menu bar. • The following picture illustrates a typical DropDown menu.


Cascading Menus • A Cascading menu can display more menu options than a DropDown menu. • In simple , if a menu option has a sub menu, it is called a Cascading Menu. • The following pictures gives an idea about the Cascading Menu.


Popup Menus • A Popup menu is displayed in response to a specific event. • Unlike DropDown menus a Popup menu is not displayed at a fixed place, but it pops up at the current mouse position. • The following picture gives an idea about Popup menu.


Creating A Menu • Invoke the New dialog box by selecting File > New menu option and double-click on the Menu icon located in Object tab page . • The following picture will provide a guideline to you:


Properties Of menu • Some of the properties of the menu control are: – Name – Microhelp – Accelerator Key etc… • Name : PowerBuilder automatically assigns the name. • Microhelp: When using MDI windows, you can specify the help text that is to be displayed on the status bar by typing in the MicroHelp property. • Accelefrator Key : An accelerator key is the underlined letter in the text of a menu item or a window object. can access the menu items or objects using the keyboard instead of the mouse by pressing the specified key along with the Alt key. All rights reserved by Satyam Computers Services Ltd.

Database Painter • PowerBuilder can talk to different data sources, i.e. – Relational Databases, such as Sybase SQL Server, Oracle, Informix, Microsoft SQL Server, DB2, Adaptive Server Anywhere (Watcom), etc. – Databases such as FoxPro tables. – Other sources such as Text Files, Excel Spreadsheets, etc. All rights reserved by Satyam Computers Services Ltd.

•

Invoking the Database Painter To invoke a database painter, click the database

icon on the Powerbar. • If the database is not running, PowerBuilder automatically starts the database engine and connects to it.


Creating units Table • To create a new table, select Tables located under ODBC > Product folder and select New Table from the popup menu or click on the icon. This invokes the Create Table dialog box:


Column Attributes • Each column in a table has a range of attributes, such as column name, datatype, width, decimals, etc. • The following properties can be illustrated as below: – Name: This is the name that you use to refer to a column and it can contain any alphanumeric characters, spaces and the underscore character. – Datatypes: This attribute defines the type of data that will be stored in the column. For a database based upon the Adaptive Server Anywhere engine, there are 14+ possible data types. – Width This defines the width, or the maximum number of characters a column can contain. It is a good idea to limit the width of each column, so as to limit the amount of memory assigned to data storage. This use of memory can be wasteful if large amounts are allocated to store small amounts of data. – Null Option This is a Yes/No option, which is used to determine whether or not a column should accept a NULL value. A NULL is an empty undefined value that shouldn't be confused with a zero-length string. The meaning of NULL is "unknown value". All rights reserved by Satyam Computers Services Ltd.

Extended Attributes • What are extended attributes? They define: – How data should be displayed. – How to accept data from the . – What validations are to be done while accepting the data. – The headings and labels for the column, etc… – The display and edit styles of the data stored in the tables. All rights reserved by Satyam Computers Services Ltd.

• The following points illustrates the extended attributes with a description of their usage: – Format: Used to alter the format of the data when it is displayed. – Edit: Used to specify an edit mask for a column. An edit mask allows you to define a template to format the data in a pre-defined manner. – Valid This can be used to specify a validation rule for the data; something like, all numbers must be less than 31. – Justify :You can specify left, right or center justified text. – Height Used to specify the height of the box that displays the data. – Width Used to specify the width of the box that displays the data. – Header Specifies a header for the column that is different from the field name. It is used in Tabular and similar presentation style DataWindows. – Initial You can specify a default value for the column. An example would be to insert today's date if the left the field blank. – Label You can specify a label for the column that is different from the column name. It is used in Freeform and similar presentation style windows. – Comment In this attribute, you can add comments for the column. All rights reserved by Satyam Computers Services Ltd.

Defining Primary Keys • A Primary Key is used to uniquely identify each row in a table. When you apply a primary key to a column in a table, you are forcing each entry in that column to be unique. • It is not mandatory to define a primary key for every table, but if you don't, you won't be allowed to enter data into the table from the data manipulation screen in the Database Painter.


• •

Defining Foreign Keys

A Foreign Key defines the relationship between two tables. We just defined a primary key unit for the units table. To define a foreign key, either (You can do the following only after you create product_master table which is explained at the end of this session in the exercises section): – Click with the right-mouse button on the product_master table in the Objects Layout sheet and select New > Foreign Key popup menu option. – Expand Tables > product_master > Foreign Key and select 'New Foreign Key' popup menu option.


Creating Indexes • Indexes are used to speed up data selection from a table by restricting the number of rows that have to be searched. • For example, if you had a table of s with a gender field and assuming you had equal number of male and female s, then indexing this field would half the number of records that need to be searched by a query.


Data Manipulation •

•

•

• •

• •

• •

To view the data that is stored in a table or to even alter/manipulate it, invoke popup menu on the table title and select Edit Data > Appropriate Format option or click on one of the preview icons from the Painterbar. Importing Data At the moment PowerBuilder s importing data from two file formats, namely tab delimited text and the .DBF format. However, it is likely that in future releases more file formats will be ed. Exporting Data We've seen how to import data from a tab delimited text file. It may occur to you that it would be useful to export data to some other format. To allow this PowerBuilder provides you with tools to several popular file formats. Sorting Data When you are in the data manipulation pane, you can display the data in any sorted order by selecting Rows > Sort... menu option. Filtering DataIf you have a large table with a million rows and want to see only rows that belong to a specific country, what you do? You put a WHERE clause in the SELECT statement to select only those rows that you want to see. Displaying Rows Description While sorting, filtering, deleting and modifying data in the "Output (product) – Units", you may find it useful to see the summary of the things done to the rows. You can see it by selecting 'Rows > Described...' menu option. Print Previewing, Zooming & Printing the Data You can print or preview the current display using the standard File menu options of 'Print...' and 'Print Preview'. In Print Preview mode, you can turn on the Print Preview Rulers to let you adjust the margins and using the 'Print Preview Zoom...' feature you can alter the magnification.


PowerBuilder Attributes and System Tables

• The following table lists all the PowerBuilder system tables that store the PowerBuilder extended attributes information. – PBCATTBL Contains the font information for the text, labels, headers and so on. – PBCATCOL Contains details of the extended attributes for each column in each table in the database. – PBCATFMT Contains definitions of the available formats. – PBCATVLD Contains definitions of the validation rules. – PBCATEDT Contains definitions of the edit styles. All rights reserved by Satyam Computers Services Ltd.

• The following picture illustrates the PowerBuilder system table columns and primary keys.


Commenting Code • You can comment the SQL code in any one of the following styles. • Double Slash Method: '//' without quotations make everything after that in that line is considered as a comment. If you place it in the middle of a line, everything after that will be treated as comments. • //The following select everything from units table. – Select * from units; All rights reserved by Satyam Computers Services Ltd.

Viewing Stored Procedure Syntax

• To view a stored procedure syntax: – Expand the 'Procedures &Functions' folder – Click on the procedure name you want to see the syntax – Invoke popup menu – Select Properties menu option.


Executing a Stored Procedure

• To view a stored procedure syntax: – Expand the 'Procedures & Functions' folder – Click on the procedure name you want to see the syntax – Invoke popup menu – Select Edit > OneOfThePresentationStyle menu option.


Database Profiles • Everything that was created is stored in the product database. While creating a database, there are few things that happen behind the scenes. – PowerBuilder creates the database 'product' using the appropriate database tool. – Creates an ODBC entry for product – Creates a database profile 'product' for the product database. – Connects to the product database using the database profile 'product'. All rights reserved by Satyam Computers Services Ltd.

Connection Options • Profile Name is the name given to a profile and is used whenever you want to connect to the database. • PowerBuilder creates a section with this name in the pb.ini file.


39677614 Power Builder Training 704n8

Overview 3e4r5l

More details w3441

Related Documents 3m3m1z

39677614 Power Builder Training 704n8

Power Builder Tutorial 5n63z

Power Builder Manual.pdf 111l1t

Power Builder 1d2 665052

Power Builder Tutorial 12.5 344e72

Manual Power Builder 12.5 u6fl

More Documents from "SANTOSH" 3j2v1m

The Four Main Categories Of Sql Statements Are As Follows 6p213y

Machine Maintenance Log Book z252d

Autosar Diagnostic 3s496x

39677614 Power Builder Training 704n8

Arcsight Esm Image Viewer Tutorial 595j24

Iec 56- Hvac Circuit Breaker 1 Of 5 165l4l