DEV16: ABL Database Objects Updated

1
DEV-16 ABL Database Objects Updated
David Olson
Director, Enterprise Solutions
Mary Szekely
Just Mary
2
Agenda

• ProDataSets in perspective
• Whats new for DB objects in 10.1C
• Getting more out of your DB objects

3
ProDataSets in Perspective
ProDataSets are about data relations between
tables.

• The data relation Order to Order-Lines can be
  represented as composite rows or tuples
• Order 3 01/02/07 Order-line 1 95
• Order 3 01/02/07 Order-line 2 44
• The same data relation can be represented in
  nested fashion as
• Order 3 01/02/07
• Order-line 1 95
• Order-line 2 44

4
ProDataSets in Perspective
The tuple form of data The Result Set
5
The Trouble with Result Sets
Distributing data SQL tuples versus datasets

• Wide SQL tuple has extra copies of higher levels
• Each row is independent of all other data
• Updating can be difficult
• Ideal for streaming large amounts of data where
  processing cannot wait for a completed set

6
ProDataSets in Perspective
Typical XML format of the same data. Foreign
keys deduced by context, still with some
redundancy.
lt?xml version"1.0" ?gt - ltOrderDataSet
xmlnsxsi"http//www.w3.org/2001/XMLSchema-instan
ce"gt - ltttOrdergt   ltCustNumgt66lt/CustNumgt  
ltOrderNumgt3lt/OrderNumgt   ltOrderDategt1997-09-23lt/O
rderDategt   lt/ttOrdergt - ltttOrdergt  
ltCustNumgt83lt/CustNumgt   ltOrderNumgt4lt/OrderNumgt
  ltOrderDategt1998-01-17lt/OrderDategt  
lt/ttOrdergt - ltttCustgt   ltCustNumgt66lt/CustNumgt  
ltNAMEgtFirst Down Footballlt/NAMEgt   lt/ttCustgt -
ltttCustgt   ltCustNumgt83lt/CustNumgt   ltNAMEgtFallen
Arch Runninglt/NAMEgt   lt/ttCustgt -
ltttOrderLinegt   ltOrderNumgt3lt/OrderNumgt  
ltLineNumgt1lt/LineNumgt   ltItemNumgt45lt/ItemNumgt  
ltQtygt95lt/Qtygt   lt/ttOrderLinegt
7
ProDataSets in Perspective
Normalized data The Business View of Data
8
DataSets are Fun
Distributing data SQL tuples versus datasets

• Dataset format has no extra copies
• One row in a table is dependent on rows in other
  tables, so no processing can occur until the
  entire set has been read
• Ideal for caching moderate amounts of data
• Ideal for loosely-coupled operations
• Updating is safe and easy Browsing easy

9
DataSet Principles
ProDataSets, ABL SDOs, Java SDOs, .NET Datasets

• Deliver related data as tables of logical
  records, not as tuples in a matrix
• Guarantee updating can be safe and effective,
  including before images for optimistic locking
• Minimize data transmission
• Can be transformed to and from XML easily
• Do not assume all data comes from one data source
• May be defined dynamically

10
Agenda

• ProDataSets in perspective
• Whats new for DB objects in 10.1C
• Getting more out of your DB objects

11
Under Development

• This talk includes information about potential
  future products and/or product enhancements.
• What we are going to say reflects our current
  thinking, but the information contained herein is
  preliminary and subject to change. Any future
  products we ultimately deliver may be materially
  different from what is described here.

12
Adventures in Buffer Methods
Ability to create dataset Before-Table records on
the Server side for uniform update with
SAVE-ROW-CHANGES

• Buffer handle method MARK-ROW-STATE
• Allows you to create a before-table record for
  any after-table record with a row-state and
  before-table buffer of your choice
• Buffer handle method MARK-NEW
• Allows you to create before-table records and
  mark them as ROW-CREATED for an entire temp-table

Useful for WebSpeed and data coming from XML
13
Recursive Relationships
14
Dataset Recursion
FILL a Dataset through a Recursive Relation
RECURSIVE property for DATA-RELATIONS
manufacturing example

• DEFINE TEMP-TABLE ttpart FIELD part-num AS CHAR
• FIELD mcost AS DECIMAL
• INDEX part-num AS UNIQUE part-num.
• DEFINE TEMP-TABLE ttps FIELD comp-num AS CHAR
• FIELD assy-num AS CHAR
• FIELD qty AS INT
• INDEX ixcomp AS UNIQUE comp-num assy-num.
• INDEX ixassy AS UNIQUE assy-num comp-num.
• DEFINE DATASET mfg FOR ttpart, ttps
• DATA-RELATION FOR ttpart, ttps
• RELATION-FIELDS(part-num, assy-num)
• DATA-RELATION FOR ttps, ttpart
• RELATION-FIELDS(comp-num, part-num) RECURSIVE.

15
Recursive Relationships
Single Table Recursion

• The Trouble with Management

16
Recursive Relationships
Single Table Recursion

• The Trouble with Management
• Managers are employees
• Navigation through self-recursion can be difficult

Relationship tables EMPLOYEE and
EMPLOYEE Relationship fields emp-name and manager
17
Dataset Recursion
FILL a Dataset through a Recursive Relation

• RECURSIVE property for DATA-RELATIONS
• Single Table model

DEFINE TEMP-TABLE ttemp FIELD emp-name AS CHAR
FIELD manager AS CHAR
FIELD age as int INDEX
emp-name AS UNIQUE emp-name. DEFINE DATASET
myorg FOR ttemp DATA-RELATION r1 FOR
ttemp,ttemp RELATION-FIELDS(emp-name,
manager)RECURSIVE.
18
Working with Groups in Queries
BREAK BY, LAST-OF FIRST-OF for a QUERY
DEFINE QUERY q FOR Customer,Order, Order-line,
Item SCROLLING. OPEN QUERY q FOR EACH Customer
WHERE Customer.Cust-num lt 10,
EACH Order OF Customer, EACH
Order-line OF Order, EACH Item
OF Order-line BREAK BY Customer.Sales-rep BY
Order.Order-num. REPEAT GET NEXT q. IF
QUERY qLAST-OF(2) THEN . Done with this order
IF QUERY qLAST-OF(1) THEN Done with this
sales-rep etc etc. END.

• Works for both dynamic and static QUERY OPEN

19
Working with Groups in Queries
Accumulating Totals

• REPEAT
• GET NEXT q.
• IF QUERY-OFF-END(q) THEN LEAVE.
• tot-by-order tot-by-order qty price.
• tot-by-rep tot-by-rep qty price.
• IF QUERY qLAST-OF(2) THEN /Done with this
  order/
• DO
• DISPLAY cust.sales-rep order.order-num
  tot-by-order.
• tot-by-order 0.
• IF QUERY qLAST-OF(1) THEN /Done with
  this sales-rep/
• DO
• DISPLAY tot-by-rep.
• tot-by-rep 0.
• END.
• END.
• END.

20
Handy features in DB objects
ProDataSet and Temp-table READ and WRITE XML
Schema

• TEMP-TABLE and DATASET WRITE-XML,
  WRITE-XMLSCHEMA
• TEMP-TABLE and DATASET READ-XML, READ-XMLSCHEMA
• If receiving table or dataset is dynamic, we can
• infer the schema!
• Around since 10.1A but not talked about enough!

21
DB Object Enhancements
Other new features

• ProDataSets for consuming Web Services
• ProDataSets can be a parameter for the web
  service incoming call
• Already available for Temp-Tables
• Dataset parameters used to be done by serializing
  to XML

22
DB Object Enhancements
Other new features

• NOT-ACTIVE keyword for DATA-RELATION

def temp-table ttcust like customer. def
temp-table ttord like order. def dataset dset
for ttcust,ttord data-relation r1 for
ttcust,ttord relation-fields(cust-num,cust-num)
data-relation r2 for ttord,ttcust
relation-fields(cust-num,cust-num)
not-active. dataset dsetget-relation("r1")acti
ve false. dataset dsetget-relation("r2")active
true.

• Note that ttcust, ttorder is suitable for FILL
• ttord, ttcust, is suitable for NAVIGATION or
  sending to .NET

23
DB Object Enhancements
Other new features

• TOP-NAV-QUERY for datasets
• READ/WRITE attribute for dataset members that
  are not children of any relation

dataset dtop-nav-queryset-callback-procedure ("
off-end ,"fetchcusts"). browse bquery
dataset dtop-nav-query.
24
DB Object Enhancements
Other new features

• DEFAULT-VALUE for BUFFER-FIELDS
• Similar to INITIAL and DEFAULTSTRING, but is in
  the NATIVE format, not CHARACTER.
• For assigning values to a newly created record,
  but not for use as MetaData since TODAY and NOW
  are captured as the current internal native
  datatype date and time

25
Agenda

• ProDataSets in perspective
• Whats new for DB objects in 10.1C
• Getting more out of your DB objects

26
Handy features in db objects
Filtering a ProDataset FILL query with
FILL-WHERE-STRING

• FILL-WHERE-STRING is BOTH read/write and easier
  to use than creating your own query
• Or
• The query is automatically handled by the FILL
  and you do not have to worry about
  creating/deleting it yourself

DATA-SOURCE dord-lineFILL-WHERE-STRING
DATA-SOURCE dord-lineFILL-WHERE-STRING
AND Line-Num lt 3.
DATA-SOURCE dsDeptFILL-WHERE-STRING where
deptcode 400.
27
Handy features in db objects
Loose-copy-mode dataset and temp-table copy

• If loose-copy-mode is TRUE
• The target and source member tables do not have
  to have columns matching by position.
• They are matched by name, or by any previous
  ATTACH-DATA-SOURCE mapping between the target and
  source.

target-dataset-handleCOPY-DATASET(
src-dataset-handle , append-mode , replace-mode
, loose-copy-mode , pairs-list , current-only
)
28
Handy features in DB objects
ProDataSet and Temp-table READ and WRITE XML
Schema

• TEMP-TABLE and DATASET WRITE-XML,
  WRITE-XMLSCHEMA
• TEMP-TABLE and DATASET READ-XML, READ-XMLSCHEMA
• If receiving table or dataset is dynamic, we can
• infer the schema!
• Around since 10.1A but not talked about enough!

29
In Summary

• ProDataSets model REAL business data
• Complex relationships and recursion are easily
  managed
• New features make ProDataSets more flexible
• FILL-WHERE and loosemode are examples
• ProDataSets keep getting better
• We can do things other datasets cannot

30
For More Information, go to

• PSDN Library - http//www.psdn.com/library/index.j
  spa
• 10.1B ProDataSets by John Sadd
• 10.1B ABL Handbook by John Sadd
• Education Courses
• Using ProDataSets
• Documentation
• 10.1B ABL Reference

31
Relevant Exchange Sessions

• DEV-5 Using ProDataSets in OpenEdge 10
• Monday, June 12, 200pm
• DEV-14 Using ProDataSets and WebClient for
  Robust B2B Applications
• Tuesday, June 13, 800am
• DEV-13 Development Tools and ABL Roadmap Info
  Exchange
• Tuesday, June 13, 800am

32
Questions?
33
Thank you foryour time
34
(No Transcript)