Hey everyone, New list member. I've been using sqlite for some time now and I'm very happy with the results. Very nice. Lately, I've been toying with the idea of creating user defined functions. I've successfully created simple functions that work as the functions provided in func.c work. However, I would REALLY like the ability to query the database from inside the function callback for each row. For example, right now I'm storing hierarchical data in the database. There is a parent child relationship so I've got to do some recursive processing to get it out. Like if you have the following: (think directory tree) TABLE(node_id,parent_id,nodename)
So I wanted all the parents of node_id=10 and there are 20 parents… I would have to query 20 times. I'm really lost on how to handle this parent->child relationship using standard SQL. So first thing that came to mind was subselects, but I don't know exactly how that would work…. Then, I tried to create a user function and query the database from inside the user function using the same database connection that I was called from. However, it doesn't seem to return any results (and to my surprise NO errors either). Everything seems to succeed but no rows are returned. (maybe because I am doing the query inside another query on the same connection and my parent query is wiping out the error messages?) Anyways, I was wondering what you all would suggest. What is the best way to pull out that parent->child relationship (moving up and down)? Can I create a query to return the directory path of node_id=10 when it has many parents using standard SQL? Should I continue down the procedure road? (I actually think it is a good idea) I was thinking of making multiple connections inside each function call (yuck) But I think it would work?! I might try this later tonight. Will SQLite someday support nested queries from inside the userdef functions? Any input would be greatly appreciated! Thanks, Dave Horner --------------------------------- Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
