MvOPEN#

Opens the DATABASE filename as alias NAME makes it the primary database. The record pointer is positioned at the first record. The same database file can be open more than once simultaneously with different alias NAMEs.

Syntax

<MvOPEN NAME = "string: {  expression } | literal"
        DATABASE = "string: {  expression } | literal"
        USERNAME = "string: {  expression } | literal"
        PASSWORD = "string: {  expression } | literal"
        INDEXES = "{ expression } | filename list"
        TYPE = "xbase3 | MySQL">

A database must be created before it can be used. When you create a database, you are just defining its structure: a database file is created on your system, but it doesn’t contain any data yet. This step needs to be done only once for each database. Miva Script uses the <MvCREATE> tag to create a database. If an existing database is created, it will be overwritten and the contents lost. You can use the fexists() function to determine if the file already exists. The syntax for MySQL database is different and detailed below.

Optionally, previously created indexes can be loaded when the database is opened.

Attributes#

Attribute	Description
`NAME`	Required, database alias. When a database is open, it is referenced in all other database commands by the alias. This lets you open the same database file with multiple aliases, often with different indexes.
`DATABASE`	Required. The physical path and file name that will be created. For xBase3 files (default) the extension is normally .dbf.
`USERNAME`	(Optional) Used when opening databases that require a username.
`PASSWORD`	(Optional) Used when opening databases that require a password.
`INDEXES`	(Optional) Contains a comma-separated list of index files that will be opened and associated with an xBase3 database.
`TYPE`	(Optional) `xBase3` (default) / `MivaSQL` / `MySQL`. This attribute can be omitted for `xBase3` databases but is required for the others.

Examples#

A database must be open before you can use it. When a database is created, it is automatically opened. Usually, however, the script that you used to create the database (see <MvCREATE>) will be run only once so if you use other scripts to manipulate the database, you have to explicitly open the database, using the <MvOPEN> tag.

<MvOPEN NAME="employees" DATABASE="{ g.path $ 'workers.dbf' }" INDEXES="emp_name.mvx,emp_salary.mvx">

A database stays open until the script terminates, or you close it explicitly with <MvCLOSE>. Once closed, you have to open it again before using. If left open, the current script can make a call to another script or function and perform operations on it, however an attempt to re-open the database will generate a runtime error.

In this example an application uses a user defined function to open a database file and set a global variable. (in practice a single function could open multiple database files) Then a second function processes the file and finally it is closed.

<MvASSIGN NAME="g.employeesIsOpen" VALUE="{ Open_Database('employees', 'workers.dbf') }">

<MvIF EXPR="{ g.employeesIsOpen }">
     <MvEVAL EXPR="{ Process_Records('emplyees') }">
     <MvCLOSE NAME="employees">
<MvELSE>
     workers.dbf could not be opened.
</MvIF>

When you open a database it becomes the primary database for the program regardless of how many other databases were previously opened or the order in which they were opened. A database becomes the primary database by default if it is the one most recently opened. However, you can specify a new primary database or indexes with <MvPRIMARY>.

NAME and DATABASE are required. The alias specified by NAME doesn’t have to be the same one used when the database was created. You can also have the database open multiple times simultaneously under different aliases, and update it under any of these aliases.

The optional INDEXES attribute contains a comma-separated list of index files that will be associated with the database being opened. All indexes are updated when records are added or updated, or when the database is packed. The first index file in the list will become the primary index; it governs the way that the database will be sorted for navigation. You can change the primary index with <MvPRIMARY>.

If the program will modify the open database, all relevant index files should be loaded so that they will be properly updated (you can also update the indexes at a later time using <MvREINDEX>). If a database is being opened only for reading information, an index is not necessary unless a specific sort order is needed or you are using <MvFIND>.

Special Fields#

Miva Script creates additional special fields for each database. - db_alias.d.totrec: total number of records in the database - db_alias.d.recno: current physical record number in the database - db_alias.d.eof: Read as true if the end of the database has been reached. - db_alias.d.deleted: Reads as true if the record is marked for deletion.

The physical record number of a record may change if the database is packed (i.e. records are physically deleted). For this reason, you should not code in a way that uses the physical record number as a unique identifier by which a record can be referred to. You may instead wish to define a database field in which you store a unique identifier (e.g. alias.d.id) for each record.

In the example we use EOF to read to the end of the file.

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

MySQL#

This example shows the syntax for opening a MySQL database. A MySQL database usually consists of multiple tables. Accessing records in a MySQL table is achieved using <MVOPENVIEW> to execute a query on the opened database.

<MvOPEN NAME = "mydatabase" DATABASE = "{ l.database }" TYPE = "MySQL"
        USERNAME = "{ l.username }" PASSWORD = "{ l.password }">

This example opens a MySQL database, opens a view that selects a particular table and record based on a product_code.

<MvOPEN NAME = "Merchant" DATABASE = "mydatabasefile@localhost" TYPE = "mysql"
        USERNAME = "{ l.username }" PASSWORD = "{ l.password }">

<MvASSIGN NAME = "l.query"
          VALUE = "{ 'SELECT * FROM Products WHERE code = ?' }">

<MvOPENVIEW NAME = "Merchant" VIEW = "Products" QUERY = "{ l.query }" FIELDS = "{ l.product_code }">

<MvEVAL EXPR="{ 'Product Code: ' $ Products.d.code $ ' <br>' }">
<MvEVAL EXPR="{ 'Product Name: ' $ Products.d.name $ ' <br>' }">

Note

The records are accessed using the VIEW name (e.g. <MvOPENVIEW VIEW="Products"...) instead of the name used in the MvOpen Tag. (e.g. <MvOPEN NAME = "Merchant"...).