Clever MvOPENVIEW examples please.

[Keith Hunniford]

EVEN DUMB OBVIOUS STUFF USEFUL

Please include MvOPENVIEW samples here that build an example list that demystify this command, with explanations. Perhaps that is the one and only way that we will be able to create a useful pseudo document. I am struggling with how to use this powerfully without escaping lots of single quotes, so hopefully some examples will help.

Please include the equivilent pure SQL statement and use obvious table names and columns to bring clarity.

Starting with MvOPENVIEW 101:

SQL:
Select * from employee where firstname="John";

MIVASCRIPT:
<MvASSIGN NAME="l.searchfor" VALUE="{ 'John' }">
<MvOPENVIEW NAME = "database"
VIEW = "employeeview"
QUERY = "{ 'select * from employees where firstname=?' }"
FIELDS="l.searchfor">

[Keith Hunniford]

Oh.. and here is an eariler example posted by Marcus which shows more than one field substitution:

<MvOPENVIEW
NAME = "{ l.schema }"
VIEW = "v"
QUERY = "SELECT * FROM products WHERE id=? AND price>?"
FIELDS="l.prod_id,l.min_price">

[wmgilligan]

With thanks to Markus for getting me started...
=========================================

<MvASSIGN NAME = "l.record_id" VALUE = "{ callerid }" >

<MvOPENVIEW NAME="Merchant" VIEW="Baskets" QUERY="{ 'SELECT * FROM s01_Baskets WHERE session_id = ?' }" FIELDS="l.record_id">

<MvEval expr="{ Baskets.d.whateverfieldyouneed}">

Finds the record in the baskets database that matches the sessionid (callerid).

[Tongo]

I forget who posted this one a while back. Has come in handy many times since totrec is gone:

<MvOPENVIEW NAME= "Merchant"
VIEW= "Database_Select_Count"
QUERY= "{ 'SELECT COUNT(*) AS count FROM ' $ l.tbl_name }">

<MvIF EXPR = "{ NOT g.MvOPENVIEW_Error }">

<MvIF EXPR = "{ NOT Database_Select_Count.d.EOF }">
<MvASSIGN NAME = "l.count" VALUE = "{ Database_Select_Count.d.count }">
</MvIF>

<MvCLOSEVIEW NAME = "Merchant" VIEW = "Database_Select_Count">



Forgot the explanation; returns the total number of records in the database (l.count)

[Kent Multer]

OK, I've got a good one for you ...

I just started working with MM5, and I'm finding the OPENVIEW business a little cumbersome. Since the view itself has limited functionality, you often end up loading your views into arrays; and you need a separate "Load_xxx" function for each table, because the field names are different. I got tired of that pretty quickly, so I found an alternative. SQL has a SHOW COLUMNS query, which works similarly to the MvREVEALSTRUCTURE tag. it gives you a way to find out, on the fly, what columns are in a table.

Here's a LoadView function that can read ANY table in the Merchant database, and put the contents into an array of structures, where the structure member names are the same as the table columns. I've just done a few tests, but it seems to be working correctly.

<MvFUNCTION NAME="LoadView" PARAMETERS="table, query" STANDARDOUTPUTLEVEL="">
<MvOPENVIEW NAME="Merchant" VIEW="tempcolumns" QUERY="{ 'SHOW COLUMNS FROM ' $ l.table $ ';' }">
<MvIF EXPR="{ g.MvOPENVIEW_Error }">
<MvEVAL EXPR="{ 'ERROR: couldn\'t read field data, ' $ g.MvOPENVIEW_Error $ '<br>' }">
</MvIF>

<MvWHILE EXPR="{ NOT tempcolumns.d.eof }">
<MvASSIGN NAME="l.nf" VALUE="{ l.nf + 1 }">
<MvASSIGN NAME="l.fldnam" INDEX="{ l.nf }" VALUE="{ tempcolumns.d.Field }">

<MvSKIP NAME="Merchant" VIEW="tempcolumns">
</MvWHILE>

<MvCLOSEVIEW NAME="Merchant" VIEW="tempcolumns">

<MvOPENVIEW NAME="Merchant" VIEW="tempdata" QUERY="{ l.query }">
<MvIF EXPR="{ g.MvOPENVIEW_Error }">
<MvEVAL EXPR="{ 'ERROR: couldn\'t read data, ' $ g.MvOPENVIEW_Error $ '<br>' }">
</MvIF>

<MvWHILE EXPR="{ NOT tempdata.d.eof }">
<MvASSIGN NAME="l.n" VALUE="{ l.n + 1 }">

<MvASSIGN NAME="l.f" VALUE="0">
<MvWHILE EXPR="{ l.f LT l.nf }">
<MvASSIGN NAME="l.f" VALUE="{ l.f + 1 }">

<MvASSIGN NAME="l.data" INDEX="{ l.n }" MEMBER="{ l.fldnam[l.f] }"
VALUE="{ miva_variable_value('tempdata.d.' $ l.fldnam[l.f]) }">

</MvWHILE>

<MvSKIP NAME="Merchant" VIEW="tempdata">
</MvWHILE>

<MvCLOSEVIEW NAME="Merchant" VIEW="tempdata">

<MvFUNCRETURN VALUE="{ l.data }">
</MvFUNCTION>

This should save me some typing :^) . Enjoy! --

[RayYates]

Kent,

I was trying to modify this routine as shown below to make it a bit more generic.

<MvFUNCTION NAME="LoadView" PARAMETERS="table, query, qfields" STANDARDOUTPUTLEVEL="">

<MvOPENVIEW NAME="Merchant" VIEW="tempdata" QUERY="{ l.query }" FIELDS="{ l.qfields }">

I call it like this:
<MvASSIGN NAME="l.data" VALUE="{ LoadView(l.prodfile, l.SQL, 'l.cat_id') }">

but when I test eof it returns 1

if I call it like this
<MvOPENVIEW NAME="Merchant" VIEW="temptable" QUERY="{ l.SQL }" FIELDS="l.cat_id">

eof it returns 0

What am I doing wrong?

[wcw]

Your parameter is table. But in the openview you use tempdata. Try "{l.table}"

[RayYates]

Thanks Bill but the table paramiter is used earlier in Kents function. I was trying to add "fields".

I ended up hard coding the field values into the SQL string, but it would be nice to know why this does not work.

[mvmarkus]

Hi al,

Like kent, I use a generic function to handle SQL queries and load the results into arrays of structures. It uses MvREVEALSTRUCTURE, since unlike the SHOW TABLE command it gives the fields of the view (not of the underlying table(s). The query values (indicated as l.par[x]) are parameterized and can have as many variables as needed.


<MvASSIGN NAME = "l.q" VALUE = "SELECT * FROM table WHERE field=? AND field2<?" >
<MvASSIGN NAME = "l.par[1]" VALUE = "20" >
<MvASSIGN NAME = "l.par[2]" VALUE = "70" >

<MvASSIGN NAME = "l.c" VALUE = "{ eo_query(l.q,l.par,l.r) }" >





<MvFUNCTION NAME = "eo_query" PARAMETERS="query VAR,parameters VAR,result VAR" STANDARDOUTPUTLEVEL = "html,text,compresswhitespace">
<Miva MvOPENVIEW_ERROR="nonfatal,display">
<Miva MvCLOSEVIEW_ERROR="nonfatal,nodisplay">
<Miva MvPRIMARY_ERROR="nonfatal,nodisplay">
<Miva MvREVEALSTRUCTURE_ERROR="nonfatal,nodisplay">

<MvASSIGN NAME = "l.schema" VALUE = "{ g.system_variables:default_schema }" >


<MvASSIGN NAME="l.task_start_time2" VALUE="{s.dyn_time_t}">
<MvASSIGN NAME="l.task_start_usec2" VALUE="{s.dyn_tm_usec}">

<MvIF EXPR = "{ l.parameters }" >
<MvASSIGN NAME = "l.a" VALUE = "1" >
<MvWHILE EXPR = "{ l.parameters[l.a] }">
<MvASSIGN NAME = "l.list" INDEX="{l.a }" VALUE = "{ 'l.parameters['$l.a$']' }" >
<MvASSIGN NAME = "l.a" VALUE = "{ l.a+1 }" >
</MvWHILE>
<MvOPENVIEW NAME = "{ l.schema }" VIEW = "v" QUERY = "{l.query}" Fields="{l.list}">
<MvELSE>
<MvOPENVIEW NAME = "{ l.schema }" VIEW = "v" QUERY = "{l.query}">
</MvIF>

<MvASSIGN NAME = "l.result" MEMBER="TIME" VALUE = "{ gettoken( showtime( l.task_start_time2,l.task_start_usec2) ,'|',1) }" >
<MvASSIGN NAME = "l.result" MEMBER="query" VALUE = "{ l.query }" >

<MvIF EXPR = "{ MvOPENVIEW_ERROR }" >
<MvFUNCRETURN VALUE= "{ '<br><strong>Error:</strong><br>'$
MvOPENVIEW_ERROR $
'<br><strong>Query:</strong><br>
<blockquote>'$
l.query$
'</blockquote><br><strong>Parameters:</strong><br>'$
miva_array_serialize( l.parameters) }" >
<MvELSE>
<MvIF EXPR = "{ NOT v.d.eof }" >
<MvREVEALSTRUCTURE NAME = "{ l.schema }" VIEW="v" VARIABLE="l.struc">

<MvPRIMARY NAME = "{ l.schema }" VIEW="v">
<MvWHILE EXPR = "{ NOT v.d.eof }">
<MvASSIGN NAME = "l.c" VALUE = "{ l.c+1 }" >
<MvASSIGN NAME = "l.a" VALUE = "1" >
<MvWHILE EXPR = "{ l.struc[l.a]:FIELD_NAME }">
<MvASSIGN NAME = "l.res" INDEX="{ l.c }"
MEMBER="{ l.struc[l.a]:FIELD_NAME }"
VALUE = "{ miva_variable_value('v.d.'$ l.struc[l.a]:FIELD_NAME) }" >
<MvASSIGN NAME = "l.a" VALUE = "{ l.a+1 }" >
</MvWHILE>
<MvSKIP>
</MvWHILE>

<MvASSIGN NAME = "l.a" VALUE = "1" >
<MvWHILE EXPR = "{ l.struc[l.a]:FIELD_NAME }">
<MvASSIGN NAME = "l.struc" INDEX="{l.a}" MEMBER="FIELD_NAME" VALUE = "{ l.struc[l.a]:FIELD_NAME }" >
<MvASSIGN NAME = "l.a" VALUE = "{ l.a+1 }" >
</MvWHILE>

<MvASSIGN NAME = "l.result" MEMBER="RESULTSET" VALUE = "{ l.res }" >
<MvASSIGN NAME = "l.result" MEMBER="METADATA" VALUE = "{ l.struc }" >
<MvASSIGN NAME = "l.result" MEMBER="COUNT" VALUE = "{ l.c }" >
</MvIF>
</MvIF>
<MvCLOSEVIEW NAME="{ l.schema }" VIEW="v">
<MvIF EXPR = "{ MvOPENVIEW_ERROR }" >
<MvFUNCRETURN VALUE="{ MvOPENVIEW_ERROR }">
<MvELSE>
<MvFUNCRETURN VALUE="{l.result:count}">
</MvIF>
</MvFUNCTION>




<MvFUNCTION NAME = "showtime" PARAMETERS="start_sec VAR,start_usec VAR" STANDARDOUTPUTLEVEL = "html,text,compresswhitespace">
<MvFUNCRETURN VALUE="{ rnd( ( s.dyn_time_t + s.dyn_tm_usec / 1000000) - ( l.start_sec + l.start_usec/1000000 ),4) $'sec ' $'| Total: '$ rnd( ( s.dyn_time_t + s.dyn_tm_usec / 1000000) - g.system_variables:time_usec,4) $'sec' }">
</MvFUNCTION>


The function returns either the error message, or the number of results (l.c) and l.result, which is an array of structures like this:

l.result:count = Number of results
l.result:query = The SQL statement
l.result:time = Time to execute the query (calculated by Empresa, not MySQL)
l.result:metadata = The structure of the resultset, in other words, the names of the fields in the resultset.
l.result:resultset[x]:fieldname = For every row in the resulting view the corresponding values, for example: l.result:resultset[1]:id, l.result:resultset[33]:description etc etc. etc.

For simplicity reason, I usually MvReference the structure to make it easier to deal with it:

<MvREFERENCE NAME="l.r" VARIABLE="l.result:resultset">

then I can simply use l.r[1]:id, l.r[33]:description instead of this cumbersome and long l.result:resultset

Obviously, for a simple, fast query that just returns a few simple values, this is a bit of an overkill, but it's nice for the lazy coder who doesn't want to rewrite all that stuff over and over again...

I hope this helps,

Markus