where

This tag executes an SQL query. The SQL element sql_string is a string but the {@filename} pseudo-variable can be used to insert a file containing the string. In either case, the string may contain embedded variables.

The DATABASE element db_string refers to the file, database, or directory name (see the table below) or to the name of the ODBC data source.

The CONNECT element connect_string must be the ODBC connect string (see the documentation provided with the ODBC driver).

The Microsoft ODBC Desktop Driver pack, also called the Data Access Pack, will replace the "short forms" below by the correct connect string.

Legend.

D - database element requires directory name

F - database element requires file name

E - database element requires name of existing file (ex. TEST.MDB for MS Access)

L - limited driver, data definition language not supported

If a Paradox or SQL database is queried, a password may be required. The database password is supplied in the CONNECT element, using the PWD identifier. For example, the following CONNECT string includes a password:

The MODE element mode_string refers to whether an application needs to have write access to records within the database. If the query is a SELECT, it should be opened in READONLY mode and a set of records is retrieved (see section 4.5 on the RECORD tag). Higher performance is possible when opening the table in EXCLUSIVE mode, but at the expense of other users. Action queries, including INSERT INTO, UPDATE, and DELETE, require SHARED or EXCLUSIVE mode. DDL (Data Definition Language) queries, including CREATE TABLE, DROP TABLE, and ALTER TABLE, require EXCLUSIVE mode. Action and DDL queries are not compatible with RECORD tags or with database related variables such as {TotalRows}, and database field name variables. Use a subsequent query in READONLY mode, such as a COUNT(*) query for accurate counts of records affected.

The ERROR element filename_string refers to the NBT file to be inserted after the line that causes a run-time error if it is encountered within the DATA tags, typically while executing the query. All form variables passed to the original NBT file are accessible to this error file. If the ERROR element is absent, it is presumed that error processing is carried out within the DATA tags, or that the default error processing defined in the NETBASE.INI file is used.

The ALIAS tag is used if two or more queries are to be open at once and the value of a field from the first query is required within the record tags of the second query. This is not required within the elements of the data tag for the second query, for instance in the SQL element sql_string, where the first query is still the currently active one.

Notes:

1. Additional DATA tags can be nested within DATA and RECORD tags. The fields of the most recently opened table are presumed, unless an alias is used inside the DATA tag. It is possible to use a field value within the elements of the next data tag (for example, in the SQL string, to open a new table).
2. EXCLUSIVE mode should not be used unless absolutely required. If connection pooling is enabled, an EXCLUSIVE query will disable all access to the database long after it has finished because the connection will remain in the pool until it times out. Similarly, a SHARED connection still residing in the pool will block an EXCLUSIVE operation until it times out.

Examples:

Example 1: Deleting records based on the values of a form checkbox variable.

      <IF VARIABLE="{PROCESS}" OPERATOR="EQ" COMPARISON="DEL_ORDER">

        <RECORD NAME="POToDelete">

           <DATA SQL="DELETE FROM PurchaseOrder WHERE UserId='{[User.UserId]}' AND PONumber={POToDelete};", DATABASE="{DB}/order.mdb", CONNECT="", MODE="SHARED">

           </DATA>

        </RECORD>

      </IF>

Example 2: Updating the contents of a database record.

      <DATA SQL="UPDATE [PaymentType] Set Description='Payment by Cheque' WHERE Code='CHEQUE';", DATABASE="{DB}/ORDER.MDB", CONNECT="", MODE="SHARED">

      </DATA>

Example 3: Changing the definition of a database table.

      <DATA SQL="ALTER TABLE CartItem ADD COLUMN PONumber Long;", DATABASE="{DB}/ORDER.MDB", CONNECT="", MODE="EXCLUSIVE">

      </DATA>

Example 4: Updating the values of the new column added in Example 3 based on the contents of other tables.

      <DATA SQL="SELECT * FROM PurchaseOrder;", DATABASE="{DB}/ORDER.MDB", CONNECT="", MODE="READONLY", ALIAS="SelPO">

      <RECORD>

        <DATA SQL="SELECT * FROM CartItem WHERE OrderNumber={[SelPO.OrderNumber]} AND Vendor_Code='{[SelPO.Vendor_Code]}' AND CartNumber={[SelPO.CartNumber]};", DATABASE="{DB}/ORDER.MDB", CONNECT="", MODE="READONLY", ALIAS="SelItem">

        <RECORD>

          <DATA SQL="UPDATE [CartItem] Set PONumber={[SelPO.PONumber]} WHERE ItemNumber={[SelItem.ItemNumber]};", DATABASE="{DB}/ORDER.MDB", CONNECT="", MODE="SHARED">

            <IF VARIABLE="{TotalRows}" OPERATOR="EQ" COMPARISON="0">

              Error! Failed to update CartItem for PO, PONumber is {[SelPO.PONumber]}, ItemNumber is {[SelItem.ItemNumber]}.

            </IF>

          </DATA>

        </RECORD>

        <IF VARIABLE="{TotalRows}" OPERATOR="EQ" COMPARISON="0">

          Error! Failed to find CartItems for PO, PONumber is {[SelPO.PONumber]}.

        </IF>

        </DATA>

      </RECORD>

      </DATA>