Solved

Recursive Function returns NULL

Posted on 2004-09-15
16
534 Views
Last Modified: 2008-02-01
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 = Server.CreateObject("ADODB.RecordSet")
      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
Comment
Question by:RickDai
  • 8
  • 5
  • 3
16 Comments
 
LVL 9

Expert Comment

by:cb1393
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 = Server.CreateObject("ADODB.RecordSet")
      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

by:RickDai
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

by:cb1393
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 = Server.CreateObject("ADODB.RecordSet")
     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

by:RickDai
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

by:cb1393
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 = Server.CreateObject("ADODB.RecordSet")
     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

by:RickDai
ID: 12069375
Nope nothing... blank.
0
 
LVL 12

Expert Comment

by:GoofyDawg
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

by:RickDai
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 2

Author Comment

by:RickDai
ID: 12076914
Points increased, this function is critical to further programming.
0
 
LVL 12

Accepted Solution

by:
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

set conn = Server.CreateObject("ADODB.Connection")
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

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

Author Comment

by:RickDai
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

by:GoofyDawg
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

by:RickDai
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 = Server.CreateObject("ADODB.RecordSet")
      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 = Server.CreateObject("ADODB.RecordSet")
      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

by:GoofyDawg
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

by:RickDai
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now