<MvREVEALSTRUCTURE>

Different syntax is used for dBase3 and MySQL databases.

Attributes

For xBase3

Creates a database that contains information about the structure of the source database. The newly created database contains one record for each field in the source database.

There will be one record in this database for each field definition corresponding to the field definitions used in the <MvCREATE> tag that created the source database. This new database is not opened. To access information in the new database, you must open it.

• NAME: Optional database alias. The new database specified in DATABASE will be created that will contain the structure if this alias database. If  omitted, primary database is assumed. The database must be opened with <MvOPEN>
• DATABASE: Required. Specifies the the full path of the xBase3 database file name being created to hold the structure records.

For MySQL

Returns a structured array that contains information about the structure of the source database table. The array contains one element for each field in the source database.

• NAME: Optional database name. Specifies the database to use. If  omitted, primary database is assumed. The database must be opened with <MvOPENVIEW>
• VIEW: Required. Specifies the view name.
• VARIABLE: Specifies the variable where the structure of the database table is returned.

Data Format

Each record or array element will contain the following fields:

• field_name: The name of a field in the source database.
• field_type - A letter that specifies the type of field:
  • C - Character data (CHAR)
  • N - Numeric data (NUMBER)
  • D - Date (DATE)
  • L - Boolean data (BOOL)
  • M - Memo data (MEMO)
• field_len: The number of characters allowed in the field;
  • for CHAR fields, this is the number of characters specified in the field definition;
  • for NUMBER fields, this is the sum of the number of characters before and after the decimal, plus 1 (for the decimal);
  • the other types of fields
  • DATE  has a  fixed values of 8
  •  BOOL has a  fixed values of 1
  • MEMO has a  fixed values of 10
• field_dec - For numeric data only, the number of decimal places allowed to the right of the decimal point.

Examples

This example illustrates the basic syntax for xBase3 databases.

<MvREVEALSTRUCTURE NAME = "alias" DATABASE = "struct.dbf">

This script will display the structure of the employees database.

<MvOPEN NAME = "employees" DATABASE = "{ g.filename }">
<MvREVEALSTRUCTURE NAME = "employees" DATABASE = "struct.dbf">

<MvOPEN NAME = "struct" DATABASE = "struct.dbf">
<MvWHILE EXPR = "{ NOT struct.d.EOF }">
    <MvEVAL EXPR = "{ struct.d.field_name }">, 
    <MvEVAL EXPR = "{ struct.d.field_type }">, 
    <MvEVAL EXPR = "{ struct.d.field_len}">, 
    <MvEVAL EXPR = "{ struct.d.field_dec }"><br>
    <MvSKIP NAME = "struct" ROWS = 1>
</MvWHILE>

<MvCLOSE NAME = "struct">
<MvCLOSE NAME = "employees">

For MySQL

This example illustrates the basic syntax for MySQL databases.

<MvREVEALSTRUCTURE NAME="{ l.database_name }" VIEW = "{ l.table }" VARIABLE="l.struc">

This script displays the structure of the s01_Products database in Miva Merchant.

<MvASSIGN NAME="l.database" VALUE="Merchant">
<MvASSIGN NAME="l.table" VALUE="s01_Products">

<MvOPENVIEW 
    NAME="{ l.database }" VIEW="{ l.table }" 
    QUERY="{ 'SELECT * FROM ' $ l.table $ ' LIMIT 0,1' }">
<MvREVEALSTRUCTURE NAME="{ l.database }" VIEW = "{ l.table }" VARIABLE="l.struc">

table = <MvEVAL EXPR="{ l.table }"><br>
<b><u>field_dec, field_len, field_name, field_type</u></b><br>

<MvASSIGN NAME="l.posn" VALUE="{ 1 }">
<MvREFERENCE NAME = "l.item" VARIABLE = "{ 'l.struc[' $ l.posn $ ']' }">

<MvWHILE EXPR="{ l.item }">
    <MvEVAL EXPR="{ l.item:field_dec }">,
    <MvEVAL EXPR="{ l.item:field_len }">,
    <MvEVAL EXPR="{ l.item:field_name }">,
    <MvEVAL EXPR="{ l.item:field_type }"><br>

    <MvASSIGN NAME="l.posn" VALUE="{ l.posn + 1 }">
    <MvREFERENCE NAME = "l.item" VARIABLE = "{ 'l.struc[' $ l.posn $ ']' }">
</MvWHILE>

<MvASSIGN NAME="g.columns" VALUE="{ l.struc }">

<MvCLOSEVIEW Name="{ l.database }" VIEW ="{ l.table }">

This example displays all the tables in the Miva Merchant database.

<MvOPENVIEW NAME = "Merchant" VIEW = "Tables" QUERY = "SHOW TABLES">

<MvIF EXPR = "{ NOT Tables.d.EOF }">
    <MvREVEALSTRUCTURE NAME = "Merchant" VIEW = "Tables" VARIABLE = "l.columns">
    <MvASSIGN NAME="g.columns" VALUE="{ l.columns }">

    <MvWHILE EXPR = "{ NOT Tables.d.EOF }">
        <MvEVAL EXPR="{ miva_variable_value( 'Tables.d.' $ l.columns[ 1 ]:field_name ) $ ' <br>' }">
        <MvSKIP NAME = "Merchant" VIEW = "Tables" ROWS = 1>
    </MvWHILE>
</MvIF>

<MvCLOSEVIEW Name="Merchant" VIEW ="Tables">