<MvOPEN>

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

• 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 the an xBase3 database.
• TYPE: Optional keywords. 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 performed 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 as set a global variable. (in practice a singe 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 opended.
</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 records 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 examples shows the syntax for opening MySQL database. A MySQL database usually consist of multiple tables. Accessing records in a MySQL tables 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 examples Opens a MySQL database, Opens a view that selects a particular table and record based on a product_code.

Notice: 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"... ).

<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>' }">