Application Development

This chapter discuss how Panther passes data from an application screen to a database. The topics are:

• Colon preprocessing—Using the colon preprocessor to put Panther values into SQL statements. Its forms are:variable, :+variable, and :=variable.

• Parameters—Binding values to SQL parameters when executing a named cursor. The form is ::variable.

The DBMS RUN command is used to execute SQL statements if no data is being returned to Panther, for example, for UPDATE, INSERT, and DELETE statements.

For information on sending data in service requests and calling methods in three-tier applications, refer to Chapter 5, "Defining Services in JetNet and Oracle Tuxedo Applications," in JetNet/Oracle Tuxedo Guide.

Colon Preprocessing

Panther supports colon preprocessing as part of its standard JPL syntax.

Refer to "Colon Preprocessing" for a description of standard colon preprocessing. One or more colon variables can appear anywhere in a DBMS statement; however, The first word in the statement cannot be colon-expanded. Therefore, the following two statements are illegal:

:verb SELECT * FROM students

JPL must know the command word to perform syntax checking and compilation before executing a JPL statement.

In addition to the standard forms of colon preprocessing, Panther's database drivers support special forms of colon preprocessing for values sent to a database. The forms are:

These forms of colon preprocessing replace a variable with its value and format it in a style that is appropriate for a column value in an INSERT statement, an UPDATE statement, or a WHERE clause (described below).

Colon-plus Processing

Before colon preprocessing a statement, JPL determines which engine to use. If executing a DBMS statement, the JPL parser examines the statement for a WITH ENGINE clause. If it finds the clause, it uses the specified engine. If it finds a WITH CONNECTION clause, it uses the connection's engine. If neither clause is used, JPL uses the engine of the default connection. Colon-plus processing is not necessary in statements using the WITH CURSOR clause. The only WITH CURSOR statement that uses column values is DBMS EXECUTE and this statement uses binding, not colon-plus processing, to supply column values.

For each :+variable used in a JPL statement, the following steps are performed:

Perform Standard Colon Preprocessing

Panther searches for variable in the following places:

• JPL variables local to the procedure that JPL is executing.

• JPL variables local to the module containing the procedure that JPL is executing.

• Widgets on the current screen.

• LDB variables.

When it finds the variable, it copies its value to an internal work buffer. Any formatting is performed on this copy. The variable's contents remained unchanged.

When a screen entry function is executed, it, by default, searches for variable in the LDB before searching the current screen. For more information on variables and their scope, refer to "Variables."

Determine the Variable's Panther Type

Specific widget properties are examined to determine the variable's Panther type, which in turn, determines how to format the data. Since a variable can have more than one qualifying property specification, Panther uses some precedence rules when assigning a Panther type.

A widget or LDB variable has exactly one Panther type (one of the following):

The following properties—Null Field, C Type, Data Formatting, and Keystroke Filter—are examined to determine the Panther type. If the property specifications do not resolve the variable's (including JPL variables) type, it is assigned FT_CHAR as the Panther type.

Notes: You can use the c_type property to determine a variable's Panther type.

Null Field (null_field)

Panther checks to see if the widget has null_field set to PV_YES. If the value of the variable equals the null value assignment (specified in the Null Text (null_text) property), the processor replaces the variable's value with the database engine's null string. On most engines, it is the string NULL. When a variable's value is null, it is not necessary to determine the Panther type.

If a numeric field is blank or empty, Panther substitutes NULL as the column's value for that field, even if the Null Field property is set to No. If the column is specified as NOT NULL in the database, the engine returns an error.

Consider a widget having the following Format/Display property settings: null_field set to PV_YES, null_text set to * (a single asterisk), and the repeating property set to PV_YES. At runtime, the user enters no data in the field represented by this widget; it is considered null, and Panther displays a repeating string of asterisks (****) as the widget's content. The database driver converts the string **** to NULL (that is, the value of the engine's null string) before passing it to the database engine.

On the other hand, if the user enters a text in the widget, the processor proceeds to determine the variable's Panther type from other widget properties.

C Type (c_type)

This property's value has the highest priority in determining the variable's Panther type; its value is used to assign a Panther type, unless it is set to PV_DEFAULT or PV_OMIT.

A newly created widget, one you create with the screen editor, is automatically assigned PV_DEFAULT as its C type property value. However, widgets that are database-derived (from the repository), are assigned, via the import process, a C type based on the column's data type. You can also explicitly set a C type.

Be aware of C type property settings that conflict with other properties. For example, if a widget has a C Type setting of PV_INT and a Data Formatting setting of PV_DATE_TIME, its Panther type is FT_INT. The date/time format is enforced for data entry, but Panther's database drivers do not convert the date/time format string into a format the engine would recognize.

The Panther type for the C Type property values are as follows:

C type	Panther type
Char String	FT_CHAR
Hex Dec	FT_HEX
Int	FT_INT
Unsigned Int	FT_UNSIGNED
Short Int	FT_SHORT
Long Int	FT_LONG
Float	FT_FLOAT
Double	FT_DOUBLE
Zoned Dec	FT_ZONED
Packed Dec	FT_PACKED

Data Formatting

Checks this property to determine if the widget expects date/time (PV_DATE_TIME) or numeric data (PV_NUMERIC), determining if the Panther type is DT_DATETIME or DT_CURRENCY, respectively.

If the Data Formatting property is set PV_NONE, Panther examines the Keystroke Filter property.

Keystroke Filter

Checks this property if the variable is neither a date/time or numeric field. If the Keystroke Filter property is set to PV_DIGITS_ONLY, the Panther type assignment is FT_UNSIGNED; if it is set to PV_YES_NO (accepts Yes/No values), the Panther type assignment is DT_YESNO; if it is set to PV_NUMERIC (accepts numbers only), the type assignment is FT_DOUBLE.

Once a non-null variable's Panther type is determined, this classification is used to perform any necessary formatting before returning the formatted text to JPL.

DT_DATETIME Variables

The processor calls the support routine to format the text in the engine's default syntax for date/time. Some support routines store a Panther format string (defined in the date_format property) in the style of the engine. When formatting a field value, it can simply pass the format string and value to Panther's date/time routines to reformat the string. Other support routines can call a conversion function from the DBMS library to perform the task.

The actual result is dependent on the engine. For example, if the value in a date/time field is December 31, 2019 3:05 PM and the current engine is using the ORACLE support routine, Panther formats the date as:

TO_DATE('31122019 150500', 'ddmmyyyy hh24miss')

If the engine is using the SYBASE support routine, Panther formats the date as:

'Dec 31,2019 3:5:0:000PM'

Some engines support more than one data type for date-time columns. For more information, refer to the Database Drivers.

FT_CHAR Variables

The processor checks if the engine uses quote and escape characters. By default, an engine uses a single quote for quote_char, and a single quote for escape_char.

The processor first determines the size of the formatted text by adding the length of the unformatted text and the number of embedded quote_chars in the text (and for the enclosing quote characters). If it cannot allocate a buffer large enough for the text, the processor returns the SM_MALLOC error. If the allocation is successful, the processor writes the formatted text to the buffer. It puts a quote_char at the first position in the buffer and, as it copies each character from the source string to the buffer, it compares the character with quote_char. If the character equals quote_char the processor puts an escape_char before the embedded quote_char. A final enclosing quote_char is put at the end of the text.

For example, Panther formats the value: Ms. Penelope O'Brien to

'Ms. Penelope O''Brien'

Panther formats the value: Reported record sales for "The Novice's Guide to PC's" to:

'Reported record sales for "The Novice''s Guide to PC''s"'

A few engines do not support both single and double quotes within a character string. For engine-specific information, refer to the Database Drivers.

FT_HEX Variables

Panther converts the widget's hexadecimal string to a binary format before writing it to the database. The valid hexadecimal string must be an even-length, null-terminated string consisting only of the following letters and numbers: 0-9, A-F, a-f. No character validation on the string is performed on field exit, but if the string cannot be converted, an error occurs when the SQL statement is executed.

For FT_HEX data, colon plus and colon equal processing are not available. However, regular colon expansion can be used.

Single line text widgets containing binary data have a maximum size of 127 bytes. To successfully write data longer than 127 bytes, either declare a variable using DBMS BINARY or in the screen editor, change the Widget Type to Multitext and set the Word Wrap property to Yes.

FT_NUMERIC and DT_CURRENCY Variables

The processor calls the function sm_strip_amt_ptr to strip editing characters from the numerical string. The function strips all non-digit characters (such as dollar signs) except for an optional leading negative sign and a decimal point. The colon preprocessor does not use precision edits when formatting numeric values.

For example, Panther formats the value $500,000.00 as 500000.00. The value (-89.003) as -89.003, and a value of 001-02-0003 as 001020003.

To preserve embedded punctuation in numeric fields, set the widget's C Type property to Char String (PV_CHAR_STRING).

For engine-specific information, refer to Database Drivers.

If a widget, defined to accept numeric or currency data, is empty or blank, Panther substitutes NULL as the column's value for that widget, even if the Null Field property is set to No. If the corresponding column is specified as NOT NULL in the database, the engine returns an error.

Colon-equal Processing

To specify a null value in a search criteria, most engines require the syntax

SELECT select_list FROM table WHERE column IS NULL

To select rows where a column value is either known or unknown (that is, NULL), use the colon-equal processor. For example:

DBMS QUERY SELECT * FROM titles \
	WHERE rating_code :=rating_code

If the widget named rating_code has the following Format/Display property settings: Null Field set to Yes, Null Text set to * (a single asterisk), and the Repeating property set to Yes. Panther formats the widget's data value PG as `PG'and executes the SELECT statement:

SELECT * FROM titles WHERE rating_code = 'PG'

It formats the widget's "null" value, **** (repeating asterisks), as:

IS NULL

and executes the SELECT statement:

SELECT * FROM titles WHERE rating_code IS NULL

Consider a widget named last_name (from the VideoBiz database) on a screen having the following property settings:

c_type = PV_DEFAULT
null_field = PV_NO
data_formatting = PV_NONE
keystroke_filter = PV_UN FILTERED

Therefore, the Panther type is FT_CHAR.

If the widget last_name contains the text D'Angelo when the following statement is executed:

DBMS QUERY SELECT * FROM customers \
	WHERE last_name = :+last_name

Panther passes the query:

SELECT * FROM customers WHERE last_name = 'D''Angelo'

If last_name is empty, Panther passes the empty string, not a null string:

SELECT * FROM employee WHERE last_name = ''

Null conversion is performed only on variables having the null_field property set to PV_YES.

Writing Date/Time or Null Data to the Database

Consider that the widget member_date, from the VideoBiz database, is defined to accept a date (in the form MM/DD/YYYY) and also allow for null data. Given these property settings:

The Date/Time setting has a higher precedence than the Keystroke Filter setting, therefore, the Panther type for the widget is DT_DATETIME. If widget contains the date 12/31/2015, and the following function is executed:

DBMS WITH CONNECTION oracle_conn RUN \
	INSERT INTO customers (cust_id, last_name member_date) \
	VALUES (:+cust_id, :+last_name, :+member_date)

Panther passes the following statement to the engine (in this example, ORACLE is the engine):

INSERT INTO customers (cust_id, member_date) VALUES \
	(43, 'D''Angelo', \
	 TO_DATE('31122015 000000', 'ddmmyyyy hh24miss') 

If no date is entered in the member_date field, its content is 00/00/0000 and Panther passes the following statement to the engine:

INSERT INTO customers (cust_id, last_name, member_date) \
	VALUES (43, 'D''Angelo', NULL)

If a widget accepts only numeric values (keystroke_filter = PV_DIGITS_ONLY), such as a telephone number, the colon-plus processor formats the widget's value as an unsigned integer, removing embedded punctuation and leading zeros. However, if the C Type property is set to PV_CHAR_STRING, the colon-plus processor formats the widget's contents as a character string, preserving embedded punctuation and leading zeros. The C Type property takes precedence over other property specifications.

For example, if the number 00912 is entered in the postal_code widget whose C Type is Char String, and the following statement is executed:

DBMS QUERY SELECT * FROM customers \
	WHERE postal_code = :+postal_code

Panther passes the following query, submitting the data as a character string, to the engine:

SELECT * FROM customers WHERE postal_code = '00912'

On the other hand, if the Keystroke Filter property is set to Digits Only, and the C Type is not set to Char String, the following query, using numeric data, is passed to the engine:

SELECT * FROM customers WHERE postal_code = 912

Setting a widget's C Type property to Hex Dec is one method used to fetch binary values to screens. With this setting, when binary data are fetched in a SQL SELECT statement, Panther converts the binary value to a hexadecimal string. If any subsequent database updates use the data, it is converted back to a binary format before being passed to the database engine.

Using Parameters in a Cursor Declaration

Some engines permit parameters in the SQL statement of a cursor declaration statement. Therefore, they permit one or more values to be supplied when the cursor is executed. On those engines that do not support binding (for example, SYBASE), Panther internally supports cursors with parameters.

When Panther executes a DECLARE CURSOR statement, it scans the statement for parameters. For all engines, Panther recognizes the following syntax to be a parameter:

::parameter

Many vendors use a single colon to begin a parameter name. Since this form conflicts with the colon preprocessor, two colons must be used in JPL. The second colon prevents the colon processor from performing variable substitution. Some vendors, such as Informix, use a single question mark to represent a parameter. Panther also recognizes these engine-specific forms.

If Panther finds a parameter, it sets up a data structure for it. It attempts to find a value for the parameter when the cursor is executed. Parameters can be used to supply column values for any SELECT, INSERT, UPDATE, or DELETE statement. For example,

DBMS DECLARE a_cursor CURSOR FOR \
	SELECT * FROM customers WHERE last_name = ::xyz

The binding data structures are stored with an individual cursor. Therefore, the application should give a unique name to each parameter belonging to a single cursor. A cursor cannot have two parameters with the same name.

A value for a parameter is supplied in the USING clause of an EXECUTE statement,

DBMS WITH CURSOR cursor EXECUTE USING arg [ , arg ... ]

Panther looks for the keyword USING before passing the cursor's query to the DBMS. If it finds the keyword, it assumes the arguments which follow are parameter values. If an arg is not quoted, Panther assumes it is a variable and performs variable substitution and formatting. Values and parameters can be bound by position. For example,

DBMS DECLARE b_cursor CURSOR FOR \
INSERT INTO roles VALUES (::p1, ::p2, ::p3)
....
DBMS WITH CURSOR b_cursor EXECUTE \
	USING title_id, actor_id, role

Values and parameters can be bound explicitly by name:

DBMS DECLARE b_cursor CURSOR FOR \
	INSERT INTO roles VALUES (::p1, ::p2, ::p3)
....
DBMS WITH CURSOR b_cursor EXECUTE \
	USING p3=role, p1=title_id, p2=actor_id

The preceding declaration, p3, p1, and p2 are not Panther variables but role, title_id, and actor_id are. Panther uses the values of role, title_id, and actor_id to execute the INSERT. To supply a literal value to the INSERT, enclose the value in quotes:

DBMS WITH CURSOR b_cursor EXECUTE \
	USING p3=role, p1="89", p2=actor_id

Panther formats binding values in a method similar to the colon-plus processor. This is discussed in detail in the next section.

On those engines that support parameters, using them can improve the efficiency of the application, especially when a cursor is executed several times. On engines where Panther simulates support, such as SYBASE, the use of parameters is less efficient. However, the convenience and the greater ease of portability can compensate for the additional processing.

Parameter Substitution and Formatting

An arg in a USING clause can be:

• A quoted string

• A Panther variable

Colon-plus processing is not necessary because Panther automatically formats the value of parameter variables. If the variable is an array, an occurrence number can be given. If no occurrence is given, Panther concatenates all the non-empty occurrences in the array, separating the occurrences with a single space. Substrings are not permitted.

For each cursor, Panther maintains binding information. When a cursor's statement uses parameters, Panther stores the names of the parameters. When a cursor is executed, Panther compares the values in the DBMS EXECUTE statement with the binding information from the cursor's declaration. This permits both positional and explicit binding.

Panther uses a data structure to store the formatted text and Panther type of arg. If arg is not quoted, Panther assumes it is a variable and determines the variable's data type. Like the colon-plus processor, the binding routine distinguishes between empty and null variables; a variable is null if it the Null Field (null_field) property is set to PV_YES and the variable contains the null string.

If the Panther type is DT_DATETIME, Panther calls the support routine to convert the value to a binary date/time value. For more information, refer to "Writing Date/Time or Null Data to the Database."

No processing is done on the values of FT_CHAR variables or quoted strings.

For all other types, Panther strips characters other than digits, the decimal point, and a leading negative sign from the value.

The following examples show the different formats for arg in a USING clause:

DBMS DECLARE x CURSOR FOR \
	SELECT * FROM titles \
	WHERE title_id=::p1 OR genre_code=::p2

# newid and newtype are LDB variables
DBMS WITH CURSOR x EXECUTE \
	USING p1=newid, p2=newtype

# For p1, a literal value is supplied.
# For p2, code is a JPL variable with the initial text 
# "film_type." film_type is also a widget on the current 
# screen and this widget supplies the parameter's value.
DBMS WITH CURSOR x EXECUTE USING p1='92', p2=:code

# id and vid_type are field arrays. i is a JPL variable
DBMS WITH CURSOR x EXECUTE \
	USING p1=id[i], p2=vid_type[i]

Consider a widget, rent_amount from the videobiz database, with the following property settings: c_type = PV_DEFAULT, keystroke_filter = PV_NUMERIC, data_formatting = PV_NUMERIC, and numeric_type = PV_DEFAULT_0 (Format Type is set to Local Currency). Its Panther type is DT_CURRENCY.

If the data entered in the widget is $1,000.99, and the following statements are executed:

DBMS DECLARE sales_cursor CURSOR FOR \
	SELECT * FROM customers WHERE rent_amount > ::x
...
DBMS WITH CURSOR sales_cursor EXECUTE USING x=rent_amount

the engine executes:

SELECT * FROM customers WHERE rent_amount > 1000.99

Consider the widget named notes, a multiline text widget, with its Word Wrap property set to Yes and with the following property settings:

This widget is a Panther type FT_CHAR. If you execute the following statements:

DBMS DECLARE ins_cursor CURSOR FOR \
	INSERT INTO customers (cust_id, notes) \
	VALUES (::p1, ::p2)...
DBMS WITH CURSOR ins_cursor EXECUTE USING cust_id, notes

when the widget is empty, the DBMS executes:

INSERT INTO customers (cust_id, notes) VALUES (123, '')

If, however, the widget contains text, Panther concatenates the non-empty occurrences into one long string which the DBMS inserts into the column notes.

INSERT INTO customers (cust_id, notes) \
	VALUES (123, 'This customer wants to be notified \
when A River Runs Through It is available for rental.')

Notes: For multiline text widgets, the Max Occurrences property should also be set to avoid memory allocation errors.