Transcript PowerScript - Database Operations

PowerScript - DB Operations
 Transaction Object
 Application Object Events
 DataWindow Functions
 DataWindow Query Mode
 Events & Functions
 Error Handling
PowerBuilder Online Courses - by Prasad Bodepudi
PowerScript - DB Operations
 Transaction Object
Stores information required to connect to
the database.
Stores the status of the last executed SQL
statement.
SQLCA is a global transaction object and is
available by default in all PB Applications.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Transaction Object (Cont…)
CONNECT statement connects to the
database.
DISCONNECT statement disconnects from
the database.
An application can have any number of
transaction objects.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Transaction Object (Cont…)
You can create your own transaction objects
other than SQLCA.
One application can connect to any number
of databases.
DbHandle() tells you whether the given
transaction object is still connected to the
database or not.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Transaction Object
 Application Object Events
 DataWindow Functions
 DataWindow Query Mode
 Events & Functions
 Error Handling
PowerBuilder Online Courses - by Prasad Bodepudi
PowerScript - DB Operations
 Application Object Events
Open - Executes when app. opens.
Idle - Fires when the app. is idle for more
than the time specified using Idle().
SystemError - Fires whenever an error
occurs.
Close - Fires before shutting down the app.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Application Object Events (Cont…)
ConnectionBegin - Fires when a PB client
connects.
ConnectionEnd - Fires when a PB client
disconnects.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Transaction Object
 Application Object Events
 DataWindow Functions
 DataWindow Query Mode
 Events & Functions
 Error Handling
PowerBuilder Online Courses - by Prasad Bodepudi
PowerScript - DB Operations
 DataWindow Object Events
SetTrans()
Sort()
SetTransObject() SetFilter()
Filter()
Retrieve()
AcceptText()
Describe()
InsertRow()
Modify()
DeleteRow()
Print()
Update()
SetSort()
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 SetTrans()
Sets the specified transaction object to the
DataWindow.
Connects and disconnects automatically
everytime Retrieve()/Update() is called.
Gives NO control on transaction management
to developers.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 SetTrans() (Cont…)
Connecting & Disconnecting takes more
physical resources.
Good for single user apps, not good for multiuser apps.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 SetTransObject()
Similar functionality like SetTrans()
Does NOT connect & disconnect to the
database automatically. Developer should
take care of it.
Gives more control to the developer on
transaction management.
Takes less resources & good for multi-user
environment.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Retrieve()
Retrieves data into the DataWindow control.
Extra arguments are ignored in v5.0 onwards.
Array variables can be specified as
arguments to use in IN clauses in the
DataWindow.
By default, resets the DataWindow before
populating the DataWindow.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Retrieve() (Cont…)
Doesn’t make sense for external data source,
eventhough no compilation errors are
generated.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 InsertRow()
Inserts a row before the specified row in the
DataWindow.
Passing zero argument appends a row to the
bottom of the DataWindow.
Returns the row number of the inserted row.
This function does NOT automatically insert
the row in the connected database.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 DeleteRow()
Deletes the specified row in the DataWindow.
This function does NOT automatically delete
the row in the connected database.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Update()
Saves changes (insert, delete, update) from a
DataWindow into the connected database.
Has no affect for external data source
DataWindows.
Has no affect for OLE presentation, GROUP
style DataWindows.
Upon success, resets the DataWindow
row/column flags to ‘Not Changed’.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 SetSort()
Sets the sorting criteria to a DataWindow.
Typically called before calling Sort().
To prompt the user for the DataWindow sort
criteria, set the sort criteria to NULL value.
 Sort()
Sorts the DataWindow on the criteria
specified using the SetSort() function.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 SetFilter()
Sets the filter criteria to a DataWindow.
Typically called before calling Filter().
To prompt the user for the DataWindow sort
criteria, set the filter criteria to NULL value.
 Filter()
Filters the DataWindow on the criteria
specified using the SetFilter() function.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 AcceptText()
Takes the value of the current field, validates
the data and puts into the Primary buffer.
Triggers ItemChanged event.
Can be called internally by Update() when
TRUE argument is passed to Update().
Calling AcceptText() in ItemChanged event
will put your program in a loop.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Describe()
Describes the DataWindow object and other
object within the DataWindow object
properties.
Till v5.0, this was the only way to find
DataWindow object attributes at run-time.
.Object syntax can be used from v5.0
onwards.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Modify()
Modifies the DataWindow object and other
object within the DataWindow object attribute
values.
Till v5.0, this was the only way to change
DataWindow object attribute values at runtime.
.Object syntax can be used from v5.0
onwards.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Print()
Prints the DataWindow.
Passing TRUE argument will display ‘Cancel’
dialog box while printing.
Make sure to retrieve all rows if you set the
DataWindow to ‘Retrieve Only As Needed’,
otherwise, only retrieved rows are printed.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Transaction Object
 Application Object Events
 DataWindow Functions
 DataWindow Query Mode
 Events & Functions
 Error Handling
PowerBuilder Online Courses - by Prasad Bodepudi
PowerScript - DB Operations
 DataWindow Query Mode
Automatically inserts few blank rows and
allows the user to input query criteria.
Generates WHERE clause dynamically.
Removes the existing WHERE clause
temporarily & sets it with the dynamically
generated WHERE clause.
Uses the same format you painted in the
DataWindow painter.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Transaction Object
 Application Object Events
 DataWindow Functions
 DataWindow Query Mode
 Events & Functions
 Error Handling
PowerBuilder Online Courses - by Prasad Bodepudi
PowerScript - DB Operations
 Events
All most all MS-Windows events are mapped
to PowerBuilder events using Event Ids.
Events are triggered automatically.
User-Defined events will not be fired
automatically unless mapped to an event id
other than pbm_customXX event ids.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Events (Cont…)
Can be defined without mapping to any event
id.
Have the ability to take parameters.
Existing parameters to the existing events
can’t be added/deleted/modified.
Can be triggered using TriggerEvent().
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 TriggerEvent()
Old syntax coming since version 1.
Used to trigger events.
Doesn’t allow to send parameters.
Return value of the event script can’t be
captured.
Existing events need to be suffixed with !.
Cont…
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 TriggerEvent() (Cont…)
User-Defined events need to be placed in
quotations.
Triggering events dynamically is not allowed.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Firing an Event Using New Syntax
New syntax, introduced with v5.0.
Allows you to send parameters to the event.
Return value of the event script can be
captured.
Allows to fire an event dynamically.
Allows firing nested events/functions.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Firing an Event Using New Syntax (Cont…)
Same syntax can be used to post an event.
New syntax allows to trigger/post a function
like an event.
Dynamic posting of an event/function in
nested calls is not allowed.
Triggering a non-existing event returns NULL
return value.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Firing an Event Using New Syntax (Cont…)
Triggering an event that doesn’t return a
return value results in zero return value.
Triggering/Posting a non-existing function
dynamically will result in run-time error.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Transaction Object
 Application Object Events
 DataWindow Functions
 DataWindow Query Mode
 Events & Functions
 Error Handling
PowerBuilder Online Courses - by Prasad Bodepudi
PowerScript - DB Operations
 PowerScript - Error Handling
General errors trigger application object’s
SystemError event.
Writing no script to the SystemError event will
stop the application.
By writing script to the SystemError event,
you can resume from the next line of error
line or stop the application.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 PowerScript - Error Handling (Cont…)
Error information is available in ‘Error’ object.
You can use SignalError() to signal an error
while testing.
SignalError() triggers SystemError event.
If you specify error number & text in the
SignalError(), PowerBuilder automatically
populates other information.
Cont...
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 PowerScript - Error Handling (Cont…)
If you do not specify arguments to the
SignalError(), then you need to take care of
populating ‘Error’ object.
New function PopulateError() in v6.0.
Error no & text are mandatory arguments to
this function.
Doesn’t trigger SystemError event. Just
populates ‘Error’ object.
PowerBuilder Online Courses
Http://www.applied-software.com
© Prasad Bodepudi. All rights reserved.
PowerScript - DB Operations
 Transaction Object
 Application Object Events
 DataWindow Functions
 DataWindow Query Mode
 Events & Functions
 Error Handling
Questions? Drop an
e-mail at
[email protected]
The End
PowerBuilder Online Courses - by Prasad Bodepudi