Best Coding Practice: Creating Array From DB Fields 2

[hererxnl]

I'm looking for opinions/facts on these topics.

I've been querying an Access DB and creating arrays in a RecordSet based on ", " delimiters in my jet sql statements. Here's an example:

sql = "SELECT [UserID] & ', ' & [Job#] & ', ' & [Priority] & ', ' & [EndTime] AS [strJobDetails] FROM [Job] WHERE [Account#]=1 AND [Dept#]=0 ORDER BY [UserID], [Job#];"

Which brings up my first two questions:

When creating an array of fields like this, is any one character a "better" candidate as a delimiter in terms of processing efficiency and/or consistent results? For example a ";" instead of a ",".

Also, when defining the delimiter in the sql statement, the example that I took this concept from adds a space after the delimiter, is this the best practice?

Still with me? Great!

Once the recordset has been defined, I've been looping through the results (obviously including EOF and BOF conditions).

At the beginning of each loop I split the array like this:

strJobArray = Split(rs("strJobDetails"), ", ")

At this point should I define each array element or should I use the the array element by itself? Some of these strings are being called 5-10 times in a loop. I'm assuming that answer lies in my previous statment and there's some "magic" number of times a string/element is called that tells you define it earyl in the loop, or does is only pertain to Server variables like Request("Whatever").?

For example:

strJobArray(0) = strUID

One more (potentially stupid) question. When using "=" to define an Object, String, Variable, etc... do most of you include a space before and after the "=" like I did in the last example? Does it make a difference, even in theory?

I know there's a fine line between "Best Coding Practice" and crazy, anal retentive coder, but since this app will be used 24/7 I want to start out with the best blend of coding pratices and efficienty/consistency.

Think you have a better way to accomlish these tasks? Let me know. All comments/opinions/thrashings welcomed and appreciated (not too many thrashings!).

Thanks.

 

[GaryC123]

In one simple step

arrayName=rs.getrows

Puts all the records into an array

http://www.eatdrinkandsleep.com

 

[Tarwn]

Ok, to hit your quesitons first and my recomendations second:
1) Delimiters: Since VBScript allows pslitting on multiple characters delimiters I tend to prefer non-standard delimiters because there is less chance of someone including them in the string (if it is user input). I have yet to see someone type #DELIM# as part of their first name, last name, job description, etc

2) Commas-with-spaces as delimiters: Well, personally I don't see the difference except that:
a) It makes the delimited string easier to read
b) It makes the later split statement more difficult to read
So I guess it comes down to preference

3) Multiple Calls to generate value vs variable: I would say if you use it more than once, drop it into a variable. Either way the first call is going to have to have some memory asigned to store the value. The second use (or assignment to a variable) will cause another memory assignment. The third call causesa third (but at this point it is in your variable, so no cost there), etc. So after the second call I would think your saving resources by having it in a variable instead of generating it each time.

4) Using "=": I generally space it out on each side, that way the code is easier to read (at least to me). Since VBScript doens't have the slew of brackets and other characters that enclose or seperate chunks of code, I generally prefer to have it spaced out so I can read it faster.


Recomendations:
Rather than output a comma-delimited string as one field value you could have it output the list of fields then us the GetString method of the RecordSet object to pull the data out as delimited strings. Another option would be to use the GetRows method to generate a two-dimensional array straight from the recordset. My general rule of thumb is the less I handle recordsets the more efficienct my code gets.

Here's a reference on the Recordset object that has the two functions listed above:

http://www.w3schools.com/ado/ado_ref_recordset.asp

Hope that helps,
-T

[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.

 

[hererxnl]

Thanks guys. Some of this is semantics (i.e. spaces before and after "="). However,0 here we have two well respected programmers iterating the same point, creating an array based on the getrows method of the recrodset object. I'm listening, and learning.

Tarwn, I checked out the w3schools link you sent (BTW that site has been a huge help in my learning) but, as is often the case, I'm having trouble seeing the practical application of this method in the example I gave by reading about the method and examining the example they provide.

Is the benefit of the GetRows method less in building the array but rather the dissection when looping through the data?

The example they give:

set rs = Server.CreateObject("ADODB.recordset")
rs.Open "Select * from Customers", conn

'The first number indicates how many records to copy
'The second number indicates what recordnumber to start on
p=rs.GetRows(2,0)

... collects ALL fields which (I think) is counter productive in my case, given the amount of records already in the database. So I would make a more selective SQL statement.

The real question I have from the example is in the p=rs.GetRows(2,0) statement. If I've read this correctly, I'm supposed to know how many records to copy and where to start, neither of which is information I can provide as it's dynamic data generated by the amount of information returned by the database based on the criteria established in my SQL statement.

Thanks again.

 

[GaryC123]

Using GetRows is far better than looping thru a recordset, much less strain on the server. And its just one line
the syntax is
arrayName=rs.getrows ' nothing else

Then you can loop through the array elements e.g.

response.write arrayName(0,0)
The first element is each field in a record, the second element is each row returned from the db i.e. increment the second element is liking moving through the recordset.

http://www.eatdrinkandsleep.com

 

[hererxnl]

Gary, thanks for the response. I was reading too much into the example at w3schools and I think the fog is lifting. I going to play with this method and rethink my approach.

 

[hererxnl]

You both get a nod.

 

[Tarwn]

Yep, the GetRows arguments are all optional. So basically you can say:
"Gimme Everything": objRS.GetRows()
"Gimme Just x Records": objRS.GetRows(x)
"Gimme Everything Starting wherever I happen to be (instead of at the beginning)": objRS.GetRows(-1,0)
"Gimme just fields 'f1', 'f2', and 'f3' from the top": objRS.GetRows(-1,1,"f1,f2,f3")

So basically the arguments for GetRows let you hit it with all defaults (give you everything the recordset has) or you can limit by filling in some of the arguments (but it isn't requires).

As far as limiting what fields you pull back in your SQL statement, this is always a good idea. Retrieving fields you will never use is just wasteful in communications time and work resources for the db.
Using a Select * is even worse because not only are you asking for all the fields back, the database has to first build a query to determine which fields those are (basically using the FROM portion of your SQL query to put together the field list). So it in effect has to execute two queries to return the results from the one query you asked for.

Glad to be of assistance, always good to see people trying to push their skills even further,
-T


[sub]01000111 01101111 01110100 00100000 01000011 01101111 01100110 01100110 01100101 01100101 00111111[/sub]
Need an expensive ASP developer in the North Carolina area? Feel free to let me know.