MvREVEALSTRUCTURE documentation wrong

[wcw]

The only documentation I could find on MvREVEALSTRUCTURE was in the 4.0 reference guide (since there is no 5.0 reference guide). It lists the command as:
<MvREVEALSTRUCTURE
NAME="database alias"
DATABASE="path/filename.dbf""
VIEW= "viewname" (optional for xBase3 )
VARIABLE="varname">

So as a simple test I tried:
<MvREVEALSTRUCTURE
NAME = "Merchant"
DATABASE="{ l.directory $ 'expstru.dbf' }"
VIEW = "Products">

I tried it with and without MvOPENVIEW first.

No joy! The error is:
MvREVEALSTRUCTURE is an Unsupported Operation

What would be the correct command to get the structure of the Products table?

[Nerd Boy Inc]

Dear Bill,

Here is a script I use to look at database files. This is the uncompiled version and should tell you what you need to know.

Just change the .txt to .mv and run it on Empresa.

[wcw]

That won't work with 5.0. I have a working version for 4.0 as the documentation is correct. But it does not work in 5.0, even with the use of NAME = "Merchant" and VIEW = "Products". Do you have any access to the 5.0 documentation that might show how it is supposed to work in 5.0?

[Nerd Boy Inc]

Dear Bill,

Is this for MySQL or MivaSQL?

It seems to me Reveal does not work on MySQL. I also seem to think there was a Thread on this awhile back. I will search and see if I can find it. If I do I will post a link to it.

As far as I know there is no 5.0 Docs for Miva Script.

[wcw]

I found one thread and could not make heads or tails about it. I was looking for a simple how to get the structure of the products table. If I see that, I can figure out the rest of what I need to do.

It needs to be transparent for mivasql and mysql as the entry will be the view name within the Merchant database.

[Nerd Boy Inc]

Dear Bill,

Did you look here:

http://extranet.mivamerchant.com/forums/showthread.php?t=16404

[eMediaSales]

Hi Bill,

Nearly every SQL database on the planet has proprietary structure/metadata SQL commands. The only way to provide real cross-db compatibility at the database structure level is to create a wrapper interface and underlying functions that differ based on underlying database type.

That said, the Miva-MySQL interface DOES implement an underlying command, dbview_revealstructureagg, but NOT dbview_revealstructuretable. The implication of this is subtle - and a few clues are here in the v4.x Miva Script manual snippet shown below.

In a nutshell, MvREVEALSTRUCTURE works for MySQL AND MivaSQL, but only into an "aggregate variable" (ie: array of structures in Miva Script terms), and NOT into a new database/table as you're used to with xbase3:
MvREVEALSTRUCTURE:


NAME="database alias"
DATABASE="path/filename.dbf""
VIEW= "viewname" (optional for xBase3 )
VARIABLE="varname"

If variable is specified, variable is set to array of structures, with array index = field # and member names as above (listed in "Each record contains")

So the "VARIABLE" parameter is required. I haven't done this in a long time, so I don't have a working snippet to share, but I do KNOW it's possible. Now, that said, one other thing I do recall from working on this specific area of dbapi code, is that the REVEALSTRUCTURE for MivaSQL databases will only return *partial information*. It DOES NOT return full information like field types, size, etc., JUST FIELD NAMES. The reasons are curious, but essentially the underlying database doesn't have access to this information in the object that executes the query. Strange but it is what it is.

Finally, if you want to go old-school on this, remember MivaSQL is really a collection of xbase3 databases with a front-end wrapper. There is a MetaData table in mivadata/Merchant5/mivamerchant.dbf that holds everything you need. If you look at the MivaSQL-to-MySQL conversion script I wrote (it's available for download somewhere around here) it will show you how to open the structure metadata, and get the information directly.

Best,
james

[wcw]

Thanks. That was helpful. Apparently the command will not save to a dbf file anymore. However the example claudio used saved to a variable (which was the other option in the 4.0 doc). The example did not save the field names. Rather it showed the value in each field in every record in the databases. That is useful, but not for the exercise I am working on. So here is the revised code to get the field names of a specific table within Merchant database. I tested on MivaSQL and it works. I'll test MySQL tomorrow.


<MvOPENVIEW NAME = "Merchant"
VIEW = "{g.Origin_File}"
QUERY = "{'SELECT * FROM ' $ g.Store_Table_Prefix $ g.Origin_File}">
<MvREVEALSTRUCTURE NAME = "Merchant" VARIABLE = "l.struc">
<MvASSIGN NAME = "l.max" value="{miva_array_max(l.struc)}">
<MvASSIGN NAME = "l.i" VALUE = "1">
<MvWHILE EXPR = "{ l.i LE l.max }">
<MvEVAL EXPR = "{'<BR>' $ l.struc[l.i]:FIELD_NAME}">
<MvASSIGN NAME = "l.i" VALUE = "{l.i + 1}">
</MvWHILE>
<MvCLOSEVIEW NAME = "Merchant" VIEW ="{g.Origin_File}">

[wcw]

Thanks James. Yea, after I ran the example Jeff pointed to, it became apparent there was a shortcoming in the 5.0 implementation. The dbf write would have been nice but I can live with the "variable". I really wanted the type, size and decimals but I see they are giving erroneous results with MivaSQL. I can live with just the name too.

Thanks guys. This will give me a nice jump start.