Recursive Looping through an Array. 2

[travisbrown]

I'm trying to nest some lists using a recursive function. My usual method of getting data from a db is using GetRows() and looping through using FOR/NEXT.

Using this method with recursive function, I think I'd be overwriting the iteration variable values (x) as soon as I hit the second loop, so I'm wondering what the best alternative would be.

There's a top level table, then one that holds all the sub-record relationships for records with sub-records.

I cannot change the table design, so I'm stuck with working with it as is.

	sSQL = "SELECT pa_subj.subj_id,pa_subj_relation.* FROM pa_subj LEFT OUTER JOIN pa_subj_relation ON pa_subj.subj_id = pa_subj_relation.subj_id_child WHERE SUBJ_ID_PARENT IS NULL"

arrSubjects = GetRows(sSQL)

response.write "<ul>"

FOR i = 0 TO UBOUND(arrSubjects,2)
	response.write "<li>"
	response.write arrSubjects(0,i)
	CALL FindSubCategory(arrSubjects(0,i))
	response.write "</li>"
NEXT
response.write "</ul>"


FUNCTION FindSubCategory(id)
	sSQL = "SELECT SUBJ_ID, SUBJ_ID_SUB FROM PA_SUBJ_SUBSUBJ WHERE SUBJ_ID = '" & id & "'"
	arrSubSubj = GetRows(sSQL)
	IF isArray(arrSubSubj) THEN
		FOR x = 0 TO UBOUND(arrSubSubj,2)
		response.write "<ul>"
			response.write "<li>"
			response.write arrSubSubj(1,x)
			CALL FindSubCategory(arrSubSubj(1,x))
			response.write "</li>"
		response.write "</ul>"
		NEXT
	END IF

END FUNCTION

FUNCTION GetRows(input)
	Set rs = Server.CreateObject("ADODB.Recordset")
	rs.ActiveConnection = dbconn
	rs.Source = input
	rs.Open()
		IF NOT rs.EOF AND NOT rs.BOF THEN
			GetRows = rs.GetRows()
		END IF
	rs.Close()
	Set rs = Nothing
END FUNCTION

 

[trollacious]

All parameters passed are local variables, not global. If you DIM arrSubSubj in the Sub (shouldn't be a Function, doesn't return anything), it should work fine.

Also, in GetRows, you should use the following:

If Not rs.EOF And Not rs.BOF Then
  GetRows = rs.GetRows()
[b][red]Else
  GetRows = Nothing[/red][/b]
End If

Lee

 

[tsuji]

[1] The single most important scope control is to dim arrSubSubj and x. If you dim sSQL too, it is good but not critical.
[tt]
FUNCTION FindSubCategory(id)
dim [red]arrSubSubj, x[/red][blue], sSQL[/blue]
'etc etc
END FUNCTION
[/tt]
[2] In function getrows, you can dim rs, but not critical. It is better.

[3] It is perhaps more effective if you establish rs (set rs=...) in the main (ie outside the function getrows). Like that, you have one instance of rs and save you from creating one each time. You can leave rs.activeconnection, rs.source, rs.open and rs.close in the function.
[4]
>GetRows = Nothing
No, I don't think so.

 

[tsuji]

[3.1] In case you do as in [3], sure take the set rs=nothing line to the main as well, not inside the function.

 

[travisbrown]

Thanks guys. Good tips.

Sub (shouldn't be a Function, doesn't return anything), it should work fine.

What do you mean it doesn't return anything? It's returning an array.

GetRows = Nothing

I don't understand what this is for. Every time I call that function, it's going to set GetRows as an array or nothing anyways. Explicitly emptying it seems redundant.

 

[trollacious]

CALL FindSubCategory(XXXXXXXXXX) does NOT return an array. It returns nothing.

IF NOT rs.EOF AND NOT rs.BOF THEN
  GetRows = rs.GetRows()
END IF

What does the function return if it IS the end of the file or the beginning of the file? It's not good programming practice to not set a default value or have an Else to set the value the function returns.

Lee

 

[travisbrown]

Ah. Well, that's why I wasn't using CALL, rather, implicitly calling the function by setting a variable.

I didn't see any benefit to an ELSE value, especially setting it to "Nothing" as you suggest. Seems that if GetRows() returns nothing, then the function value is just nothing, which is what I want it to be. Any other value would be unused and unneeded.

Seems like a concise way to do it to me, but I know very little about standard programming practice. I just do what I find the least amount of fault with. I haven't run into any issues for the years I've been doing it this way.

What do you see as the compromise with doing it this way?

 

[tsuji]

If the basic is wrong, it is illusive to talk about good programming practice. The point is:
>GetRows = Nothing
Syntactically wrong and is a runtime error. It is no pleasure to put it so explicit.