MvREVEALSTRUCTURE#

Returns information on the structure of database tables.

Syntax

<MvREVEALSTRUCTURE NAME = "string: {  expression } | literal"
                   DATABASE = "string: {  expression } | literal"
                   VIEW = "string: {  expression } | literal"
                   VARIABLE = "{ expression } | variable name">

Different syntax is used for dBase3 and MySQL databases.

Attributes#

For xBase3#

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

For MySQL#

Attribute	Description
`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).
- DATE has a fixed value of 8.
- BOOL has a fixed value of 1.
- MEMO has a fixed value of 10.
field_dec: For numeric data only, the number of decimal places allowed to the right of the decimal point.

Examples#

xBase3#

Basic syntax

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

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

MySQL#

Basic syntax

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

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

Display 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">