Solved

# Recursive Function returns NULL

Posted on 2004-09-15
534 Views
I am having a problem with this recursive function, it is returning NULL, please take a look.

Function GetMainCategory(cat_id)
Dim dbRes, SQL, temp_id
SQL = "SELECT * FROM category_detail WHERE category_id = " & cat_id
Set dbRes = dbConn.Execute(SQL)
If Not dbRes.EOF Then
temp_id = dbRes("category_sub")
If temp_id <> "0" Then
GetMainCategory(temp_id)
Else
GetMainCategory = cat_id
End If
End If
dbRes.Close
Set dbRes = Nothing
End Function

the db is like this:
category_id, category_sub
33, 0
47, 33
51, 47

when the category_sub = 0 then it is a main category/root category.

The Function is returning NULL.
For some reason changing this into a SUB routine with 2 arguments made it possible, but unfortunately I need a FUNCTION to return a result for later use.
0
Question by:RickDai
• 8
• 5
• 3

LVL 9

Expert Comment

ID: 12069086
Some modifications that may help dubugging:

Function GetMainCategory(cat_id)
Dim dbRes, SQL, temp_id
SQL = "SELECT * FROM category_detail WHERE category_id = " & cat_id
Set dbRes = dbConn.Execute(SQL)
If Not dbRes.EOF Then
If Not IsNull(dbRes("category_sub")) Then
temp_id = dbRes("category_sub")
If temp_id <> "0" Then
GetMainCategory(temp_id)
Else
GetMainCategory = cat_id
End If
Else
GetMainCategory = "category_sub from db returned NULL"
End If
Else
GetMainCategory = "the function returned nothing"
End If
dbRes.Close
Set dbRes = Nothing
End Function
0

LVL 2

Author Comment

ID: 12069257
Re; cb1393

I tried your modded function, it seems to return nothing, not even the 'return results'
I am assuming that this is happneing with my previous function as well.

any clues why that is?
0

LVL 9

Expert Comment

ID: 12069286
If this doesn't return something, I'm out of ideers:

Function GetMainCategory(cat_id)
Dim dbRes, SQL, temp_id
SQL = "SELECT * FROM category_detail WHERE category_id = " & cat_id
Set dbRes = dbConn.Execute(SQL)
If Not dbRes.EOF Then
If Not IsNull(dbRes("category_sub")) Then
temp_id = dbRes("category_sub")
If temp_id <> "0" Then
GetMainCategory = "temp_id returned " & temp_id
Else
GetMainCategory = "cat_id returned " & cat_id
End If
Else
GetMainCategory = "category_sub from db returned NULL"
End If
Else
GetMainCategory = "the function returned nothing"
End If
dbRes.Close
Set dbRes = Nothing
End Function
0

LVL 2

Author Comment

ID: 12069328
I used:

Response.Write(GetMainCategory(51))

output:
temp_id returned 47

and for some reason that calling of the function to recurse is screwing up.

but i need it to return

33, 0 <// --- return 33 as the sub is 0
47, 33
51, 47

0

LVL 9

Expert Comment

ID: 12069346
Ahh... try this...

Function GetMainCategory(cat_id)
Dim dbRes, SQL, temp_id
SQL = "SELECT * FROM category_detail WHERE category_id = " & cat_id
Set dbRes = dbConn.Execute(SQL)
If Not dbRes.EOF Then
If Not IsNull(dbRes("category_sub")) Then
temp_id = dbRes("category_sub")
If temp_id > 0 Then
GetMainCategory(temp_id)
Else
GetMainCategory = cat_id
End If
Else
GetMainCategory = "category_sub from db returned NULL"
End If
Else
GetMainCategory = "the function returned nothing"
End If
dbRes.Close
Set dbRes = Nothing
End Function
0

LVL 2

Author Comment

ID: 12069375
Nope nothing... blank.
0

LVL 12

Expert Comment

ID: 12071275
Sorry about jumping into this a bit late. What exactly are you trying to accomplish? Mind you, I mean no disrespect by this question, I just want to understand what the business problem is and then perhaps make a suggestion.

GoofyDawg
0

LVL 2

Author Comment

ID: 12071574
I am trying to return the top category_id or root category_id of the given category_id

such as
category_id, category_sub
33, 0
47, 33
51, 47

GetMainCategory(51)  should return 33
GetMainCategory(46) should return 33
GetMainCategory(33) should return 33
0

LVL 2

Author Comment

ID: 12076914
Points increased, this function is critical to further programming.
0

LVL 12

Accepted Solution

GoofyDawg earned 100 total points
ID: 12077601
Sometimes you have to punt... I modified your code a bit and instead of using GetMainCategory = cat_id, I just set a variable that I defined called main_cat once the function found the right value. It works just fine. Here's the code.

dim main_cat, conn, sConn

sConn = "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("/data/test.mdb")
conn.Open(sConn)

Function GetMainCategory(cat_id)
Dim dbRes, SQL, temp_id
SQL = "SELECT * FROM cats WHERE category = " & cat_id
Set dbRes = conn.Execute(SQL)
If Not (dbRes.bof and dbRes.EOF) Then
temp_id = dbRes("category_sub")
If temp_id <> "0" Then
GetMainCategory(temp_id)
Else
main_cat = cat_id
End If
End If
dbRes.Close
Set dbRes = Nothing
End Function

GetMainCategory(47)

Response.Write main_cat

You can see it in action at: http://www.iarchitec.com/test2.asp

GoofyDawg
0

LVL 12

Expert Comment

ID: 12077614
I also tried it with GetMainCategory(51), and it produces the same result.
0

LVL 2

Author Comment

ID: 12077715
Thanks GoofyDawg,

one question, since the function will be residing in an include file and it is called from outside the include file, will i be able to do that, assign the value to an outside variable of the function?
0

LVL 12

Expert Comment

ID: 12078028
It should work just fine. Just make sure you define the variable from within the include file, not outside of it. Include files get executed first, then inline code, then HTML, so you have to make the variable available, and that means it must be defined in the include. BTW, nice job with the recursion, but be real careful about using recursive functions because everytime to make a recursive call, it adds to the memory stack of the server. A recursion creates a call chain that must be maintained for backwards referencing, so you want to only use this technique sparingly. Just something to consider.

GoofyDawg
0

LVL 2

Author Comment

ID: 12078210
GoofyDawg,

Thank you very much, I have taken your advise in memory management. Because GetMainCategory will be mainly used through out most of the site, i have abandoned it.  It still boggles my mind, why my recursive function wasnt returning any value at all.

Before I close this, i would like your comment on this. I have changed the recursive to 3 seperate functions that pretty much checks for the same algorithm.
<%
Function GetMainCategory(cat_id)
While Not IsRoot(cat_id)
cat_id = FindSubCategory(cat_id)
If IsRoot(cat_id) Then
GetMainCategory = cat_id
End If
WEnd
End Function

Function FindSubCategory(cat_id)
Dim SQL, dbRes
SQL = "SELECT * FROM category_detail WHERE category_id = " & cat_id
Set dbRes = dbConn.Execute(SQL)
If Not dbRes.EOF Then
FindSubCategory = dbRes("category_sub")
End If
dbRes.Close
Set dbRes = Nothing
End Function

Function IsRoot(cat_id)
Dim SQL, dbRes
SQL = "SELECT * FROM category_detail WHERE category_id = " & cat_id
Set dbRes = dbConn.Execute(SQL)
If Not dbRes.EOF Then
If dbRes("category_sub") = 0 Then
IsRoot = True
Else
IsRoot = False
End If
End If
dbRes.Close
Set dbRes = Nothing
End Function
%>

My question is, would this be more manageable, memory wise compared to the recursive?
Please note that we may have atleast 20 calls to GetMainCategory per page...
0

LVL 12

Expert Comment

ID: 12078849
In a word, yes. However, you really can't know until you actually test the page and see how it performs. What you might want to do is a load test and monitor memory usage under load. If you're not losing more than a couple to few percent of your RAM, you should be okay. There are some free tools out there that'll let you simulate 1000 users at a time. This would be instructive to see just how much memory your function consumes under load, and how that will affect your site as a whole.

On the other hand, "safe" may not be elegant, but it's pretty straight-forward and easily maintainable. With respect to why your recursive function was not returning anything, perhaps I can shed some light on that. You have to remember that each time you recurse, you add a link to the call stack. All these calls are referenced with pointers in memory. When you want to return a value, it's quite possible that the interpreter just doesn't know which value to return in the call stack. Therefore, it punts and returns nothing, then just terminates the function altogether. When I first ran your logic, I got the same result, which led me to believe that the function was possibly causing the interpreter to fail, but since it just didn't know what to do with it, it didn't even generate an error message. I wonder if running a debug session on that piece of code would reveal anything...

Anyway, good luck, and before you completely trash your function, run a test to see if it consumes more resources than acceptable.

GoofyDawg
0

LVL 2

Author Comment

ID: 12078901
Thank you all,

I will further look into it. But as of right now, I am happy that my project may continue.
0

## Featured Post

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than oneâ€¦
Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is tâ€¦
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files â€” any plâ€¦
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods youÂ´d like to investigate in more detail.  The methods are covered in more detail in oâ€¦