Digital Workshop

Just posting this to share a bit of what I've learned with getting records from a Database. This applies to using OpusScript.

I wanted to run a Query and then look at different records (Rows) and different Fields (Columns).

Thanks to Mackavi for his contribution here: viewtopic.php?t=2648&highlight=database+script
I tried what Mack suggested, eliminating the 'var' and it worked. I am able to trigger subsequent 'reads' from the same record set.

The common approach is to use the 'var'. Example, from the help files:Using this method, the 'RecSet' goes away when the code has run even if remaining on the same page in the Pub.

By using w/o 'var'I can navigate thru the records and display various data elements, without reissuing the query.

I think there is a danger doing this as the DB connection may stay open, and record set likewise stays alive. Another post alluded to not being able to "close a Query".

I will likely try to "close out" by setting those global variable to 'null' before leaving the Page.

Morning Lar,

The purpose of the loop in this example is not really meant as a navigational tool. It is to loop through all records in the set to perform a calculation on using the values from price field.

From DB design perspective, if I needed to review these records with / without the need for updating, the loop would feed an array containing an object with the fields as the prototypes. The inclusion of a primary key would then allow an additional function to match the data held in the array to the DB when the next SQL statement was executed.

Also to be fair, my example is a little lame, as basically, I'm pulling back all that data to perform a simple calculation, that should be handled by the original SQL without the need to return all the data - just the answer

Mack

Okay, good info to know.
In my playing, I loop through to load a Listbox. The ReplaceSelection code has the "\t " and "\n" to separate the Fields and the Records respectively.

From that point, I was testing different ways to get at other Fields (of same table) not displayed in the Listbox, or Fields (from other tables e.g., when the Query had a JOIN option etc.) I was starting to 'cascade' listboxes. That's how I stumbled on 'var' or 'no var'.

Anyway, just getting a handle on the consequences of not using 'var'/local variable. My next 'lesson' is writing to the DB, so thanks for more tips. Maybe I will venture into DisArray.