Host variables in Javascript

A host variable is a name-value pair substituted for a :name placeholder used in the SELECT or WHERE clause of a data source, or in a SQL statement. There are two ways to supply host variable values from JavaScript:

• The hostvars option of a $.udb function that has one, for example $.udb.executeSQLStatement() or $.udb(ds).executeQuery().
• $.udb.genericHostVar() and $.udb.clearGenericHostVars().

danger

A third mechanism, setting a plain $.udb.hostvars object directly (e.g. $.udb.hostvars = {ID: 242}), existed in older USoft versions but is obsolete as of USoft 11: nothing in the API reads from it anymore, so assigning to it silently does nothing.

If you still have code that does this, migrate it to the hostvars option of the function actually performing the call, or to $.udb.genericHostVar() if the same value is needed across several calls. The rest of this article covers only the two mechanisms that still work.

The hostvars option

Use the hostvars option directly on the $.udb function being called if it has one. This is the right choice unless the same host variable and value is needed again in a later, separate action or event — see Generic host variables below for that case.

$.udb.executeSQLStatement('InsertStatement', {
    hostvars: { ID: 242, NAME: 'Jones' }
});

The hostvars option is a structure of the form:

{ name: value, name: value, ... }

where name is the name of a host variable used in the SELECT or WHERE clause of a data source, or in a SQL statement, and value is a literal value substituted for it. Value is typed either as a numeric value, in which case it is unquoted, or as a string value, in which case it is quoted.

note

Name is typed as a string. Name must be surrounded by single or double quotes if it contains one or more spaces. If not, the quotes are optional. JSON prescribes double quotes.

The ExecuteSQLStatement action referred to in the example above is defined in Web Designer with its Label property set to 'InsertStatement', and performs this query:

INSERT INTO PERSON( person_id, name )
VALUES ( :ID, :NAME )

If placeholders are used in the Output Expressions or Where Clause of a data source, make sure appropriate host variable values are supplied every time the data source is addressed:

$.udb('ds').executeQuery({ hostvars: { name: 'JONES' } });

Generic host variables

If the same host variable and value is required across multiple actions or events, use $.udb.genericHostVar() instead of repeating the hostvars option everywhere it is needed:

$.udb.genericHostVar('ID', 242);

Generic host variables and their values are valid and automatically included in every Page Engine call, until removed with $.udb.clearGenericHostVars() (or an individual one with $.udb.genericHostVar(name, null)), or until you navigate to another page.

Setting host variables from an event

Instead of (or before) calling a $.udb function, host variables can also be set from within an event, for example beforeexecutequery, or any other event that runs before an action that queries data sources with host variable placeholders. The event handler's options parameter exposes the same options struct that was (or will be) passed to the function being called, through its options.options property:

$.udb('ds').on('beforeexecutequery', (evt, options) => {
    options.options.hostvars = { name: 'JONES' };
});

Date/time host variable values

Host variable values that represent dates and times are typed as string values, and therefore quoted, as the following two snippets show:

$.udb.executeSQLStatement('InsertStatement', {
    hostvars: { ID: 242, NAME: 'Jones', START_DATE: '12-02-2020' }
});

$.udb('ds').executeQuery({ hostvars: { agreement_date: '22-06-2022' } });

You must cast these string values to actual date and time values if you want to store them in the database or execute queries based on them. For example, to execute a query with '22-06-2022' from the example above as a date value, cast the host variable in the Where Clause of the data source:

agreement_date = USFormat.CharToDate( :agreement_date, 'DD-MM-YYYY' )