{update} Directive

Delimits the update section

Syntax

Details

Example

Syntax

{update datasrc="htmSQL-ds" server="host:port" userid="id" password="pw" <sapw="sapw">}

...{/update}

Required Arguments

datasrc="htmSQL-ds"

htmSQL-ds is a name or a variable reference that identifies an htmSQL data source.

Examples:

{update datasrc="employee_data"}
{update datasrc="{&dsrc}"}

A data source specifies a SAS/SHARE server and the libraries that are available through the server. Data sources are defined in data source definition files. To define a data source, use the dsdef program that is provided with htmSQL.

server="host:port"

host:port specifies the SAS/SHARE server to connect to. You can use the server= parameter instead of the datasrc= parameter to specify the SAS/SHARE server. When used alone, the server= parameter must specify both the host name and the port for the SAS/SHARE server.

You can also use server= together with datasrc= to override the host and port that are specified in a data source definition. When used together with the datasrc= parameter, the server= parameter can specify the host name, the port, or both for the SAS/SHARE server. If you specify only one of these items, you must include a colon (:) to indicate which one you are specifying.

If any libraries are defined in the data source definition that is specified by the datasrc= parameter, then htmSQL attempts to define those same libraries to the server that is specified by the server= parameter.

The host name can be specified as a fully qualified domain name or it can be specified in any shortened form that is sufficient to enable network services to identify it.

The port can be specified as a number or as a service name that is defined in the TCP/IP SERVICES file.

Users who are familiar with the SAS syntax for specifying a server name can use a period (.) instead of a colon (:) to separate the host name and port. All of the other syntax rules for the server= option still apply.

The following are examples of valid syntax:

{update server="klondike.acme.com:5228"}

{update server="penn.sylvania:6500"}

{update server="yukon.sasshr1"}

{update datasrc="finance" server="testsrv:"}

{update datasrc="sales & Marketing" server=":5010"}

{update datasrc="alaska" server="yukon:sasshr1"}

If you are just getting started with htmSQL and do not want to define a data source definition file, you can use the server= parameter instead of defining a data source. And, if you specify a port number instead of a service name for this parameter, you also do not need to configure a TCP/IP SERVICES file for htmSQL.

userid="id" (conditionally optional): id is a user ID for the system that the SAS/SHARE server runs on. If your server is running in secure mode, you must specify a user ID. This can be done by specifying a value for this parameter or by specifying a user ID in your data source definition. You do not have to specify user IDs in both places. If the data source definition contains a user ID, then the user ID that you specify for this parameter overrides the user ID that is stored in the data source definition.

password="pw" (conditionally optional): pw is the password for the user ID that is specified in the userid= parameter. If your server is running in secure mode, you must specify a password. This can be done by specifying a value for this parameter or by specifying a password in your data source definition. You do not have to specify passwords in both places. If the data source definition contains a password, then the password that you specify for this parameter overrides the password that is stored in the data source definition.

Optional Argument

sapw="sapw"

sapw is the SAS/SHARE server access password for users. This must be the same password that is specified in

the UAPW= option of the SERVER procedure that was used to define the SAS/SHARE server. You must specify a password if user access to the server is password-protected and if this password is not already specified in your data source definition.
the SAPW= option of the LIBNAME statement and the SQL procedure's CONNECT TO statement.

The password that you specify for this parameter overrides the password that is stored in the data source definition.

Details

The {update} and {/update} directive pair delimits the update section. An input file can contain multiple update sections. Update sections can be nested within the success, norows, and error sections of an update section and within the eachrow and norows sections of a query section. Each update section must contain at least one SQL section and can contain multiple sections. The update section can also contain a success section and an error section.

The SQL section is delimited by the {sql} and {/sql} directive pair and contains the SQL statement to be sent to the SAS/SHARE server. The allowed SQL statements are ALTER, CREATE, DELETE, DROP, INSERT, and UPDATE.

Note: The Webmaster can disable these SQL statements by specifying the READONLY option in the htmSQL configuration file.
The success section is delimited by the {success} and {/success} directive pair and contains instructions on what to do if the SQL statement returns with a return code of zero. The success section can contain a norows section for instances where no rows are updated.
The error section is delimited by the {error} and {/error} directive pair and contains instructions on what to do if the SQL statement returns with a return code that is not equal to zero.

An update section can also contain a {library} directive and other text, including HTML and variable references. The text can appear before sections, between sections, and after sections.

Example

The following example illustrates a typical update section:

{update datasrc="data_source_name"}

{sql}
   [SQL statement here]
{/sql}

{success}
   [Things to do if the return code is 0]
   {norows}
      [Things to do if no rows are returned]
   {/norows}
{/success}

{error}
   [Things to do if the return code is not 0]
{/error}

{/update}