Digital Workshop

Welcome to the Digital Workshop Message Boards
It is currently December 23rd, 2024, 10:06 am

All times are UTC [ DST ]




Post new topic Reply to topic  [ 3 posts ] 
Author Message
 Post subject: Reading 'Record Sets' from DB --- open the Aperture a bit
PostPosted: April 11th, 2008, 12:51 am 
Offline
Godlike
Godlike

Joined: November 12th, 2005, 1:56 am
Posts: 1474
Location: SFBay Area
Opus: OpusPro v9.0x, & Evol.
OS: Vista32
System: Core 2 duo 2Ghz, RAM 3GB, Nvidia Go 7700 - laptop
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:
Code:
var db = new Database("DSN=Products;")
var RecSet = db.ExecuteSQL("SELECT Price, Qty FROM Customer_Order WHERE OrderID=1;")
var total = 0
for (var i=1; i<= RecSet.GetNumberOfRecords(); i++)
{
total += RecSet.Price * RecSet.Qty
RecSet.NextRecord()
}

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'
Code:
db = new Database("DSN=Products;")
RecSet = db.ExecuteSQL("SELECT   etc etc etc
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. :?:

_________________
_good things come to those who wait(0)_


For this message Lar_123 has been thanked by : mackavi


Top
 Profile  
 
 Post subject:
PostPosted: April 11th, 2008, 11:13 am 
Offline
Godlike
Godlike
User avatar

Joined: March 21st, 2007, 10:44 am
Posts: 3188
Location: UK
Opus: Evolution
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

_________________
When you have explored all avenues of possibilities, what ever remains, how ever improbable, must be the answer.

Interactive Solutions for Business & Education
Learn Anywhere. Learn Anytime.

www.interaktiv.co.uk
+44 (0) 1395 548057


Top
 Profile Visit website  
 
 Post subject:
PostPosted: April 11th, 2008, 11:40 pm 
Offline
Godlike
Godlike

Joined: November 12th, 2005, 1:56 am
Posts: 1474
Location: SFBay Area
Opus: OpusPro v9.0x, & Evol.
OS: Vista32
System: Core 2 duo 2Ghz, RAM 3GB, Nvidia Go 7700 - laptop
Okay, good info to know.
Quote:
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.
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. :twisted:

_________________
_good things come to those who wait(0)_


For this message Lar_123 has been thanked by : mackavi


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 3 posts ] 

All times are UTC [ DST ]


Who is online

Users browsing this forum: No registered users and 11 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  
Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group