<MvCREATE>

A database must be created before it can be used. When you create a database, you are defining its structure. An empty database file is created on your system. This step needs to be done only once for each database.

If the database file already exists, it will be overwritten with an empty database with the specified structure. When a database is created it is implicitly opened, just as with <MvOPEN>, with the alias in NAME. Alias names can not contain the '.' (period) character. 

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: The physical path and file name that will be created. For xBase3 files (default) the extension is normally .dbf. If a MEMO field is specified a  second file will be automatically created with the extension .dbt.
• FIELDS: Is a comma separated list of field names and field type.
  • field_name CHAR(max_chars), You must specify max_chars up to 254. For larger data use the MEMO field type.
  • field_name NUMBER(digits_before.digits_after), You may specify the digits_before a decimal point and digits_after up to a maximum of 19 characters including the decimal. If omitted, e.g. NUMBER(), defaults to NUMBER(19). If a decimal is omitted, none will be permitted. Numeric values with more than the specified number of decimal places will be truncated. Numeric values with more than the specified number of digits before the decimal place will not be entered in the database.
  • field_name DATE, Date fields have a fixed length of 8 digites in the form YYYYMMDD.
  • field_name BOOL, True or false where 1 is true and 0 is false.
  • field_name MEMO, fields have a dynamic length, allocated as needed.
• TYPE: Optional. xBase3 (default) | MivaSQL ". This attribute can be omitted for xBase3 databases. Support for ODBC has been dropped.

Sample Database:

<MvCREATE NAME="employees"
          DATABASE="{ g.path $ 'workers.dbf' }"
          FIELDS="
              name CHAR(40),
              title CHAR(20),
              salary NUMBER(7.2),
              started DATE,
              fulltime BOOL,
              comments MEMO">

Database Aliases

The NAME attribute is referring to a database alias. An important aspect of working with Miva Script databases is that databases are always referred to in Miva Script tags using an alias, rather than the actual physical filename of the database. An alias is simply a name that you assign to the database when you create or open it. The same database file can be open two or more times simultaneously with different aliases, for example using different indexes.

Database Files

The DATABASE attribute contains the actual filename of the new database to be created, usually specifying a folder path. This location is relative to the data directory for the current user. If you are creating the database in a subdirectory of the data directory, you must create the subdirectory first, using the fmkdir() function or explicitly through your operating system or FTP program. You can use any file extension for the database file, but .dbf is recommended. The database file in the example above is hr/dbs/workers.dbf. MEMO fields allocate space dynamically by using a second file with the same name as the original but having the extension .dbt.

This example creates or opens a database file as needed, creating the sub-directory at the same time.

<MIVA STANDARDOUTPUTLEVEL="html, text, compresswhitespace">

<MvASSIGN NAME="g.path" VALUE="/myfiles/">
<MvIF EXPR="{ NOT fexists(g.path) }">
    <MvASSIGN NAME="g.ok" VALUE="{ fmkdir(g.path) }">
    Folder was created.<br>
</MvIF>

<MvIF EXPR="{ fexists(g.path $ 'workers.dbf') }">
    <MvOPEN NAME="employees" DATABASE="{ g.path $ 'workers.dbf' }">
    File is open.<br>
<MvELSE>
    <MvCREATE NAME="employees"
        DATABASE="{ g.path $ 'workers.dbf' }"
        FIELDS="
        name CHAR(40),
        title CHAR(20),
        salary NUMBER(7.2),
        started DATE,
        fulltime BOOL,
        comments MEMO">

        File was created.<br>
</MvIF>

Database Fields

The FIELDS attribute contains a comma-separated list of field names with their data types and parameters. Database field names can be up to 10 characters in length.

Fields can be assigned one of five different data types: CHAR (character), NUMBER, DATE, BOOL (boolean/logical), and MEMO (dynamic length).

CHAR field definitions must specify the maximum length of the data in that field. For example, 'CHAR(40)' specifies a maximum length of 40 characters. The maximum length of a character field is 254 characters.

NUMBER field definitions can specify the number of digits allowed in the number. This specification can have two formats: 'NUMBER(N)' indicates that the number can have N digits, but cannot contain a decimal point; 'NUMBER(N.M)' indicates that the number can have N digits before the decimal point and M digits after the decimal point. For example, 'NUMBER(7.2)' specifies 7 digits before the decimal point and 2 digits after. If no digit specification is provided, then NUMBER(19) is assumed.

Numeric values with more than the specified number of decimal places will be truncated. Numeric values with more than the specified number of digits before the decimal place will not be entered in the database.

DATE fields have a fixed length. Dates have the form YYYYMMDD.

BOOL fields have a fixed length. These fields have the value '0' (false) or '1' (true).

MEMO fields have a dynamic length. MEMO data is stored in a separate file that has the same name as the database file, but with the .dbt file extension. All of the MEMO fields for all of the records in the database are stored in this file, but they are accessed individually, just like any other type of field. You should not delete this file.

The example above creates five fields: two CHAR fields named employee and title with maximum lengths of 40 and 20 characters, respectively; a NUMBER field called salary whose values can have 7 digits before the decimal and 2 after; a DATE field called started; a BOOL field called fulltime; and a MEMO field called comments.