MySQL question

[Kent Multer]

Hi folks --

I'm converting an old module to Merchant 5, and I have a question about SQL queries. In Merchant 4, I could write something like this:

MvFIND a particular record.
MvIF it doesn't exist
MvADD it to the database
/MvIF

-- and now I can reference the new record fields as myDB.d.fieldname. Whether the record was just created, or was there all along, either way, it looks the same at this point.


To achieve this in SQL, I seem to need the following steps:

MvOPENVIEW to look for a particular record (SELECT * WHERE somefield = somevalue)
MvIF it doesn't exist
MvQUERY to add it (INSERT ...)
/MvIF

-- but here's the problem: how can I reference the fields of this newly created record? It wasn't in the table when I did the OPENVIEW; does it get automatically added? Do I have to do another OPENVIEW in order to read the record I just wrote? Or is there a better solution?

Thanks --

[Kent Multer]

Here's a second question that I encountered while trying to find an answer to the first one: how do you navigate in a view?

The Miva Script manual says you can use MvGO and MvSKIP, but I got error messages when I tried to use MvGO to access the first record in a view. MvSKIP does seem to work ... are there any other ways to navigate around in a view?

[MichaelBrock]

To access the fields of the newly added record you need to close the view of table you just added the record to and re-open it (or at least that's been my experience). Note also that you do not need to MvOPENVIEW the table before adding a record to it.

You can use MvSKIP to navigate through the view:

<MvSKIP NAME="Merchant" VIEW="ViewName" ROWS="1">

I don't use MvGO (and either does Miva judging from the developer kit source files) in 5.x.

[mvmarkus]

Here's a second question that I encountered while trying to find an answer to the first one: how do you navigate in a view?

The Miva Script manual says you can use MvGO and MvSKIP, but I got error messages when I tried to use MvGO to access the first record in a view. MvSKIP does seem to work ... are there any other ways to navigate around in a view?

MvGO and MvSKIP with the Miva MySQL drivers only work "downward", meaning you can only go down to a record, but not up (forward-only cursor).

Also, MvGO is -at least that is my experience- notoriously unreliable, so sometimes it seems to work, sometimes it throws an error. Frankly, I'd stay away from it.

Instead, you may want to write your queries so that they give you the records already in the right order (if possible), or you run a new query to access another record (if your MySQL server is on localhost, this is very fast), or you assign the records of your resultset/view to a structure/array and then use the structure to "walk" up and down as you like.

Markus

[Kent Multer]

Thanks for the info; that's a big help, even if it just reassures me that views are as limited as I thought ... In case I didn't mention, I'm using MySQL at the moment. MvGO doesn't work at all in that environment; if I recall correctly, it gets a runtime error that specifically says not to use MvGO with views.

Here's a thought: Can MvQUERY be used to SELECT a bunch of data, and place the data into variables using the FIELDS attribute? Seems like that'd be more convenient than the whole OPENVIEW --CLOSEVIEW protocol for some tasks. But I'm not fluent in SQL; anybody? Thanks again --

[mvmarkus]

Can MvQUERY be used to SELECT a bunch of data, and place the data into variables using the FIELDS attribute? Seems like that'd be more convenient than the whole OPENVIEW --CLOSEVIEW protocol for some tasks.

Hi Kent,

You're welcome. The Fields-attribute is actually used for the variables that you use for your query and that are replaced with a ? (I posted an example earlier for Keith here: http://extranet.miva.com/forums/showpost.php?p=8755&postcount=11 )

What I usually do is to create a generic function that wraps in all the plumbing of the OPENVIEW/CLOSEVIEW/OPENVIEW_ERROR etc. and that returns a array of structures with the results of a query that I send to it. It is not the fastest way at runtime, but it keeps the code tidy and readable. If you are interested, I can probably post an example here.

Markus

[Kent Multer]

Hi Markus -- thanks for the tip. I know that the FIELDS attribute is used to pass data "in" to the query. I was hoping there was a way to use it to pass results "out" into Miva variables, but I guess not.

Yes, loading a whole table into an array of structures is apparently the only way to get full navigation, unless you do a whole lot of OPENVIEW/CLOSEVIEW cycles. Thanks for the confirmation --

[Kent Multer]

Another question:

Is there any difference between this:

<MvOPENVIEW NAME="XXX" VIEW="YYY"
QUERY="SELECT * FROM SomeTable WHERE id=?"
FIELDS="l.someVar">

-- and this:

<MvOPENVIEW NAME="XXX" VIEW="YYY"
QUERY="{ 'SELECT * FROM SomeTable WHERE id=' $ l.someVar }">

--? In other words, is the FIELDS attribute needed, or can you get the same result by including those values directly in a query string?

Thanks again --

[mvmarkus]

Another question:

Is there any difference between this:

<MvOPENVIEW NAME="XXX" VIEW="YYY"
QUERY="SELECT * FROM SomeTable WHERE id=?"
FIELDS="l.someVar">

-- and this:

<MvOPENVIEW NAME="XXX" VIEW="YYY"
QUERY="{ 'SELECT * FROM SomeTable WHERE id=' $ l.someVar }">

--? In other words, is the FIELDS attribute needed, or can you get the same result by including those values directly in a query string?

Thanks again --

The FIELDS-attribute is not needed, it just makes it much more convenient to read and write, and in some cases I can imagine that a database server can optimize the query better by compiling it as a prepared statement, in which only the values of the parameters are exchanged, but no a repeated parsing/interpreting is necessary.


BTW: Your example is actually NOT the same - since $l.someVAR is not enclosed in quotes. You'd either get a query error from the database or MySQL would consider this as some Null value or whatver.

<MvOPENVIEW NAME="XXX" VIEW="YYY"
QUERY="{ 'SELECT * FROM SomeTable WHERE id=\'' $ l.someVar$'\' }">

Markus