Techniques for accessing data

Two techniques

There are two ways to access data values in a DataWindow control:

  • Methods

    SetItem and the group of GetItem methods access single values in specific rows and columns. For example:

    dw_1.SetItem(1, "empname", "Phillips")
    ls_name = dw_1.GetItemString(1, "empname")
  • Expressions

    DataWindow data expressions use dot notation and can refer to single items, columns, blocks of data, selected data, or the whole DataWindow control. For example:

    dw_1.Object.empname[1] = "Phillips"
    dw_1.Object.Data[1,1] = "Phillips"

Both methods allow you to access data in any buffer and to get original or current values.

Which technique to use

The technique you use depends on how much data you are accessing and whether you know the names of the DataWindow columns when the script is compiled:

If you want to access

Use

A single item

Either an expression or a method. Both are equally efficient when referring to single items.

Exception

If you want to use a column's name rather than its number, and the name is not known until runtime, use a method; methods allow you to name the column dynamically.

More than one item, such as:

  • All the data in a column

  • A block of data specified by ranges of rows and columns

  • Data in selected rows

  • All the data in the DataWindow

An expression. Specifying the data you want in a single statement is much more efficient than calling the methods repeatedly in a program loop.


What's in this section

The rest of this section describes how to construct expressions for accessing DataWindow data. The section Syntaxes for DataWindow data expressions provides reference information on the syntaxes for data expressions.

For information on methods

For information about using methods for accessing data, see

SetItem

GetItemDate

GetItemDateTime

GetItemDecimal

GetItemNumber

GetItemString

GetItemTime in Methods for the DataWindow Control

About DataWindow data expressions

The Object property of the DataWindow control lets you specify expressions that refer directly to the data of the DataWindow object in the control. This direct data manipulation allows you to access small and large amounts of data in a single statement, without calling methods.

There are several variations of data expression syntax, divided into three groups. This section summarizes these syntaxes. The syntaxes are described in detail later in this chapter.

Data in columns or computed fields when you know the name

One or all items

(if rownum is absent, include either buffer or datasource)

dwcontrol.Object.columnname {.buffer } {.datasource } { [ rownum ] }

Returns a single value (for a specific row number) or an array of values (when  rownum is omitted) from the column.

See Syntax for one or all data items in a named column.

Selected items

dwcontrol.Object.columnname {.Primary }{.datasource }.Selected

Returns an array of values from the column with an array element for each selected row.

See Syntax for selected data in a named column.

Range of items

dwcontrol.Object.columnname {.buffer } {.datasource } [ startrownum, endrownum ] 

Returns an array of values from the column with an array element for each row in the range.

See Syntax for a range of data in a named column.

Data in numbered columns

Single items

dwcontrol.Object.Data {.buffer } {.datasource } [ rownum, colnum ] 

Returns a single item whose datatype is the datatype of the column.

See Syntax for a single data item in a DataWindow.

Blocks of data

involving a range of rows and columns

dwcontrol.Object.Data {.buffer } {.datasource } [ startrownum, startcolnum, endrownum, endcolnum ] 

Returns an array of structures or user objects. The structure elements match the columns in the range. There is one array element for each row in the range.

See Syntax for data in a block of rows and columns.

Whole rows

Single row or all rows

dwcontrol.Object.Data {.buffer } {.datasource } { [ rownum ] }

Returns one structure or user object (for a single row) or an array of them (for all rows). The structure elements match the columns in the DataWindow object.

See Syntax for data in a single row or all rows.

Selected rows

dwcontrol.Object.Data {.Primary } {.datasource } .Selected

Returns an array of structures or user objects. The structure elements match the columns in the DataWindow object. There is one array element for each selected row.

See Syntax for all data from selected rows.

Summary of syntaxes

This diagram summarizes the variations in data expression syntax:

Figure: Variations in data expression syntax

For information about getting and setting values of DataWindow object properties using a similar syntax, see Accessing DataWindow Object Properties in Code

When a DataWindow data expression is evaluated

Expressions that refer to DataWindow data are not verified until execution of your application.

No compiler checking

When your script is compiled, PowerBuilder does not verify the parameters of the expression that follow the Object property. Your application can select or change the DataWindow object in a DataWindow control at runtime without invalidating the compiled script.

Potential execution errors

If the datatype of the expression is not compatible with how the expression is used, or if the specified rows or columns do not exist, an error will occur at runtime.

You can handle the error by surrounding the expression in a try-catch block and catching any DWRuntimeErrors, or by writing a script for the DataWindow control's Error event.

Getting and storing the data from a DataWindow data expression

A DataWindow data expression can return a large amount of data.

Data structures for data

Single row and column

When your data expression refers to a single row and column, you can assign the data to a variable whose data matches the column's datatype. When the expression refers to a single column but can refer to multiple rows, you must specify an array of the appropriate datatype.

More than one column

When the expression refers to more than one column, you can get or set the data with a structure or user object. When you create the definition, you must assign datatypes to the fields (in a structure) or instance variables (in a user object) that match the datatypes of the columns. When your expression refers to multiple rows, you get an array of the structure or user object.

Likewise, if you want to set data in the DataWindow control, you will set up the data in structures or user objects whose elements match the columns referred to in the expression. An array of those structures or user objects will provide data for multiple rows.

Datatypes

For matching purposes, the datatypes should be appropriate to the data -- for example, any numeric datatype matches any other numeric type.

Examples of data structures

The following table presents some examples of data specified by an expression and the type of data structures you might define for storing the data:

Type of selection

Sample data storage

A single item

A single variable of the appropriate datatype.

A column of values

An array of the appropriate datatype.

A row

A structure whose elements have datatypes that match the DataWindow object's columns.

A user object whose instance variables match the DataWindow object's columns.

Selected rows or all rows

An array of the structure or user object defined for a row.

A block of values

An array of structures or user objects whose elements or instance variables match the columns included in the selected range.


Assigning data to arrays

When a data expression is assigned to an array, values are assigned beginning with array element 1 regardless of the starting row number. If the array is larger than the number of rows accessed, elements beyond that number are unchanged. If it is smaller, a variable-size array will grow to hold the new values. However, a fixed-size array that is too small for the number of rows will cause an execution error.

Two ways to instantiate user objects

A user object needs to be instantiated before it is used.

One way is to use the CREATE statement after you declare the user object. If you declare an array of the user object, you must use CREATE for each array element.

The second way is to select the Autoinstantiate box for the user object in the User Object painter. When you declare the user object in a script, the user object will be automatically instantiated, like a structure.

Any datatype and data expressions

The actual datatype of a DataWindow data expression is Any, which allows the compiler to process the expression even though the final datatype is unknown. When data is accessed at runtime, you can assign the result to another Any variable or to a variable, structure, or user object whose datatype matches the real data.

Examples

A single value

This example gets a value from column 2, whose datatype is string:

string ls_name
ls_name = dw_1.Object.Data[1,2]

A structure that matches DataWindow columns

In this example, a DataWindow object has four columns:

An ID (number)
A name (string)
A retired status (boolean)
A birth date (date)

A structure to hold these values has been defined in the Structure painter. It is named str_empdata and has four elements whose datatypes are integer, string, boolean, and date. To store the values of an expression that accesses some or all the rows, you need an array of str_empdata structures to hold the data:

str_empdata lstr_currdata[]
lstr_currdata = dw_1.Object.Data

After this example executes, the upper bound of the array of structures, which is variable-size, is equal to the number of rows in the DataWindow control.

A user object that matches DataWindow columns

If the preceding example involved a user object instead of a structure, then a user object defined in the User Object painter, called uo_empdata, would have four instance variables, defined in the same order as the DataWindow columns:

integer id
string name
boolean retired
date birthdate

Before accessing three rows, three array elements of the user object have been created (you could use a FOR NEXT loop for this). The user object was not defined with Autoinstantiate enabled:

uo_empdata luo_empdata[3]
luo_empdata[1] = CREATE uo_empdata
luo_empdata[2] = CREATE uo_empdata
luo_empdata[3] = CREATE uo_empdata
luo_empdata = dw_1.Object.Data[1,1,3,4]

Setting DataWindow data with a DataWindow data expression

When you set data in a DataWindow control, the datatypes of the source values must match the datatypes of the columns being set.

Single value or an array

When your data expression refers to a single row and column, you can set the value in the DataWindow control with a value that matches the column's datatype. When you are setting values in a single column and specifying an expression that can refer to multiple rows, the values you assign must be in an array of the appropriate datatype.

Multiple columns and whole rows

When the expression refers to more than one column, you can assign the data with a structure or user object to the DataWindow data. When you create the definition, the fields (in a structure) or instance variables (in a user object) must match the columns. There must be the same number of fields or variables, defined in the same order as the columns, with compatible datatypes.

When your expression can refer to multiple rows, you need an array of the structure or user object.

Using arrays to set values

You do not have to know in advance how many rows are involved when you are setting data in the DataWindow control. PowerBuilder uses the number of elements in the source array and the number of rows in the target expression to determine how to make the assignment and whether it is necessary to insert rows.

If the target expression is selected rows or a range of rows, then:

  • When there are more array elements than target rows, the extra array elements are ignored

  • When there are fewer array elements than target rows, the column(s) in the extra target rows are filled with default values

If the target expression is all rows but not all columns, then:

  • When there are more array elements than target rows, the extra array elements are ignored

  • When there are fewer array elements than target rows, only the first rows up to the number of array elements are affected

    If the target expression is all rows and all columns, then the source data replaces all the existing rows, resetting the DataWindow control to the new data.

Inserting new rows

When you are setting data and you specify a range, then if rows do not exist in that range, rows are inserted to fill the range. For example, if the DataWindow control has four rows and your expression says to assign data to rows 8 through 12, then eight more rows are added to the DataWindow control. The new rows use the initial default values set up for each column. After the rows are inserted, the array of source data is applied to the rows as described above.

Examples

These examples refer to a DataWindow object that has three columns: emp_id, emp_lname, and salary. The window declares these arrays as instance variables and the window's Open event assigns four elements to each array:

integer ii_id[]
string is_name[]
double id_salary[]
uo_empdata iuo_data[]
uo_empid_name iuo_id[]

The uo_empdata user object has three instance variables: id, name, and salary. The uo_empid_name user object has two instance variables: id and name.

This example sets emp_lname in the selected rows to the values of is_name, an array with four elements. If two rows are selected, only the first two values of the array are used. If six rows are selected, the last two rows of the selection are set to an empty string:

dw_1.Object.emp_lname.Selected = is_name

This example sets salary in rows 8 to 12 to the values in the array id_salary. The id_salary array has only four elements, so the extra row in the range is set to 0 or a default value:

dw_1.Object.salary[8,12] = id_salary

This statement resets the DataWindow control and inserts four rows to match the array elements of iuo_data:

dw_1.Object.Data.Primary = iuo_data

This example sets columns 1 and 2 in rows 5 to 8 to the values in the array iuo_id:

dw_1.Object.Data.Primary[5,1, 8,2] = iuo_id

This example sets emp_id in the first four rows to the values in the ii_id array. Rows 5 through 12 are not affected:

dw_1.Object.emp_id.Primary = ii_id