Skip to content

xBase3 Databases#

Miva Script supports three kinds of databases: xBase3 (dBaseIII compatible) databases, MySQL databases, and MivaSQL as a front end to xBase3. Previous versions of Miva Script supported ODBC data sources; that support has been dropped.

Miva Script provides tags to perform database operations such as creating, opening, and closing databases; adding, deleting, and updating records; searching for records; and creating and using database indexes. Any database files or indexes you use must be located in your data directory (typically /mivadata or /data). Some commands only apply to xBase3; for MySQL, <MvOPEN>, <MvSKIP>, <MvGO>, <MvQUERY>, and <MvOPENVIEW> are usually used.

Note

When possible, use Miva Merchant Mia to perform time-consuming database operations locally—such as converting flat files to databases or creating indexes—to avoid heavy web server resource use and timeouts. Mia’s timeout value can be set arbitrarily high.

Introduction to xBase3 Databases#

Miva xBase3 databases organize data into tables—simple tabular formats consisting of records (rows), each with one or more fields (columns). Miva Script enables you to manipulate, order, and search data stored in these tables.

This example is from an employee database. Each record has fields for name, title, salary, start date, status, and comments:

Name Title Salary Start Date Status Comments
Benson President 250000 19930630 1 Former V P sales
Rochester V P Finance 120000 19921015 1 Weird ties
Jeeves V P Technology 150000 19880710 1 Funny hats
Hudson V P Sales 130000 19971201

Creating a Database Table#

A database must be created before it can be used. Creating a database defines its structure but does not populate it with data. This step is done only once. Miva Script uses the <MvCREATE> tag to create a database. If an existing database is created, it is overwritten and its contents are lost. Use the fexists() function to check for an existing file.

Database Aliases#

Aliases allow you to refer to databases by a logical name instead of the physical filename. An alias is the value you assign to the NAME attribute when you create or open a database. Other tags reference the table by this alias. You can open the same database multiple times with different aliases (for example, using different indexes). Alias names may contain letters (a–z, case-insensitive), digits (0–9), or underscores (_), but cannot contain periods (.).

In this example, employees is the alias for the physical file workers.dbf: 

<MvOPEN NAME="employees" DATABASE="/myfiles/workers.dbf" INDEXES="/myfiles/emp_names.mvx">
<MvFIND NAME="employees" VALUE="{ 'Jeeves' }" EXACT="EXACT">

Opening the Database#

A database must be open before use. When you create a database, it opens automatically. Scripts that manipulate existing databases must explicitly open them using <MvOPEN>. A database remains open until the script ends or you close it with <MvCLOSE>. You can open it with a different alias if desired.

The Primary Database#

You can have multiple database aliases open simultaneously. One alias at a time is designated the primary alias and is implied by database tags that omit the NAME attribute. An alias becomes primary when it is created or opened, but you can also set it explicitly with <MvPRIMARY>. You can likewise change the primary index of an open table with <MvPRIMARY>.

Adding and Updating Records#

To add or update records, assign values to the database field variables and then use <MvADD> to insert a record or <MvUPDATE> to modify the current record. Database variables are automatically right-trimmed to the field length—excess characters are truncated, but shorter values remain unchanged in length.

Reading Database Records#

To read or display a record, navigate to it; Miva Script then populates special variables for each field. These same variables are used for updates.

Displays: Benson
<MvGO NAME="employees" ROW="top">
<MvEVAL EXPR="{ employees.d.name }">
Changed to: Ray
<MvASSIGN NAME="employees.d.name" VALUE="{ 'Ray' }">
<MvUPDATE NAME="employees">

Miva Script maintains a record pointer to the current record. A record becomes current when created with <MvADD> or navigated to with <MvFIND>, <MvGO>, or <MvSKIP>. For each CHAR, NUMBER, DATE, BOOL, or MEMO field, a variable alias.d.fieldname is created. You can omit the alias for the primary database (d.fieldname), and if no scope prefix exists (fieldname), it resolves to d.fieldname if not shadowed by any s. or l. variable. Avoid ambiguity by always using the alias and scope prefixes explicitly.

Special Fields#

Miva Script provides additional special fields for each open database:

  • alias.d.totrec – total number of records
  • alias.d.recno – current physical record number
  • alias.d.eof – 1 if end of file reached, else 0
  • alias.d.deleted – 1 if record is marked deleted, else 0

Do not use recno as a unique identifier since it can change when a database is packed. Define a dedicated ID field instead.

Looping Until EOF
<MvWHILE EXPR="{ NOT employees.d.EOF }">
    <MvEVAL EXPR="{ employees.d.name }">, <MvEVAL EXPR="{ employees.d.salary ROUND 2 }"><br>
    <MvSKIP NAME="employees">
</MvWHILE>

Indexing the Database#

When you add records, they append logically. To order or group records (e.g., alphabetically by name or by job title), create an index with <MvMAKEINDEX>. You can open multiple indexes when you open a database or add them later with <MvSETINDEX>. When an index is open, <MvSKIP> and <MvFIND> operate on the indexed order; <MvFIND> requires an index. Using indexes dramatically improves search speed.

Indexes are separate files defining a logical ordering based on a key expression (which can combine fields or apply other manipulation). A table can have multiple indexes open, but only one primary index controls the current sort order; switch the primary with <MvPRIMARY>. When records are added or updated, all open indexes update automatically; use <MvREINDEX> to update an index opened after changes. If an indexed field changes, its position in the index may change without moving the record pointer—this affects <MvFIND> and <MvSKIP> behavior.

Database Navigation#

Move the record pointer using:

  • <MvFIND> – jump to first record matching a value in the current index
  • <MvGO> – go to a specific physical record (ROW="top", "bottom", or a number)
  • <MvSKIP> – advance by N records in physical or indexed order

Filtering the Database#

Use <MvFILTER> to make visible only records that satisfy a criterion (e.g., hide records marked deleted).

Deleting Records#

Deleting is two-step: navigate to the record, then <MvDELETE> to mark it for deletion. <MvPACK> physically removes marked records. This two-step process allows recovery with <MvUNDELETE> before packing.

Database Structure#

Use <MvREVEALSTRUCTURE> to inspect the field definitions of a database record for a specific alias.