Recursive calls to database in perl

I know there's an easy way of doing this, but my recursion abilities are out of practice. Given a database table that has three fields:

id label child_id

I should be able to put together a recursive function that will give output like this:

child (input of program) parent1 parent2 grandparent1 great-grandparent1 grandparent2 grandparent3 parent3 grandparent4 grandparent5

I know it should be easy, but I can't get my mind to go through the mental gymnastics to make it work. Also, is this a good thing to do? Seems like I might end up leaving open quite a few database connections.

I think this is the part making it difficult for me. I'm starting with a child_id, and working my way up. And a child can have many parents. So, the output would be the child id at the 'root' of the tree and then it's parents and grandparents for each branch. The more I think about it, it's just the traditional 'one parent, many grandparents' formula, except for semantics. I could just be over thinking it.

The table would look something like this:

table parents id child_id label 1 NULL child 2 1 parent1 3 1 parent2 4 1 parent3 5 3 grandparent1 6 3 grandparent2 7 3 grandparent3 8 5 great-grandparent1 9 4 grandparent4 10 4 grandparent5


You could try this way

sub getChildren {
my $id = shift;
my $depth = shift;
my $sql = qq/SELECT id,label,child_id FROM table WHERE id=?/;
my $sth = $db->prepare($sql);
my $sth->execute($id);
while(my ($id,$label,$child_id)=$sth->fetchrow_array) {
print " "x$depth,$label;

I actually explained a quite similar problem in my blog, Implementing a depth first search in a PostgreSQL stored procedure, and my way of solving this using perl.

If your database doesn't support stored procedures you can do the same thing client-side, but you need to fetch the entire table first and do it in memory.

You could of course do it recursively and fetch each entry as you go along, but it will not scale because of the SQL statement overhead (except maybe on SQLite). If performance is not a problem this must be by far the easiest solution.

Category:database Time:2010-06-25 Views:0

Related post

  • How do I connect to a MSSQL database using Perl's DBI module in Windows? 2008-10-14

    How do I connect to a MSSQL database using Perl's DBI module in Windows? --------------Solutions------------- Use DBD::ODBC. If you just create a data source with the Control Panel -> System Management -> ODBC Data Sources -> System Data Sou

  • What is the best way to validate XML against XML Schema, parsing it and storing data back to MySQL Database using Perl? 2009-10-16

    Currently I am working with XML and I want to know what would be best approach to validate XML against XML Schema, parse it and store it back to MySQL database using Perl ? I think of XML::Xerces for parsing purpose but not sure if it's the right app

  • How do I insert values from parallel arrays into a database using Perl's DBI module? 2009-11-18

    I need to insert values in database using Perl's DBI module. I have parsed a file to obtain these values and hence these values are present in an arrays, say @array1, @array2, @array3. I know how to insert one value at a time but not from an arrays.

  • How can I manipulate a local database with Perl? 2010-09-26

    I'm a Perl programmer with some nice scripts that go fetch HTTP pages (from a text file-list of URLs) with cURL and save them to a folder. However, the number of pages to get is in the tens of millions. Sometimes the script fails on number 170,000 an

  • How do you list the table names of a Jet database in Perl with DBI? 2011-03-07

    Is there a portable way to list the table names in *.mdb files (i.e. Microsoft Jet databases, also known as "Access databases") in Perl? Usually I run my Perl scripts on Windows with Cygwin. I can run SQL queries on *.mdb databases just fine with the

  • Getting 504 Gateway error while updating MySQL database using Perl 2011-08-02

    All, I am trying to update mysql database using perl script. This script allows user to upload a csv file, and records from csv will be uploaded in the database. I am running in to 504 Gateway error all the time. Any thoughts ? #!/usr/bin/perl -- # d

  • What's the faster way to compare around 700,000 rows from 2 different databases using Perl? 2012-02-22

    I'm using Perl to connect to 2 different databases (MySQL and Sybase) using DBI, there's around 700,000 records on each and I need them to be the same (most likely there will be a few different records every week or so), first time doing this would b

  • How can I copy a directory recursively and filter filenames in Perl? 2008-10-22

    How do I copy a directory including sub directories excluding files or directories that match a certain regex on a Windows system? --------------Solutions------------- I'd do something like this: use File::Copy; sub copy_recursively { my ($from_dir,

  • Recursively printing data structures in Perl 2009-06-24

    I am currently learning Perl. I have Perl hash that contains references to hashes and arrays. The hashes and arrays may in turn contain references to other hashes/arrays. I wrote a subroutine to parse the hash recursively and print them with proper i

  • How do I connect to an MS Access database using Perl? 2009-10-30

    I have a .accdb file on my local machine and I am trying to connect to it and read some data from 3 tables within the DB. How do I establish the connection using Perl? So far I have scraped together this much for MS Access, but I am getting errors sa

  • How can I parse XML data and insert it into a MySQL database using Perl? 2009-11-17

    Here is the thing that I am trying to accomplish: In broader sense, parse the XML data using a SAX parser and insert it into the appropriate database column in a MySQL table. Here is sample Books.xml <?xml version="1.0" encoding="UTF-8"?> <!

  • recursive function category database 2010-07-21

    i hoping to create a recursive function which i don't have an idea yet this is my code to fetch category from database <?php $sql = mysql_query("SELECT * FROM categories WHERE category_parent = '1' ORDER BY lft ASC"); while($row = mysql_fetch_arra

  • Purge postgres database using perl 2010-11-01

    I have a Postgres purging script in bash where I used to run multiple commands on different tables i.e. copy data to temp table. i.e. table1 -> temp_table1 delete data from original table based on the temp tables. for both the above steps, for eac

  • What is a friendly module that allows querying a MySQL database from Perl? 2010-11-07

    Searching CPAN yielded so many results I don't know where to start. I need a simple and friendly module for some basic querying. --------------Solutions------------- For raw SQL queries, you'll need DBI and DBD::mysql. But I highly recommend that you

  • Can we generate mysql database in perl without connecting to server? 2011-03-10

    Recently i came across a tool written in tcl which generates a mysql database without connecting to a server. It uses some c libraries, can this be done in perl? Sorry if this is too basic question. This is completely new to me, couldnt find much inf

  • Recursive search and replace usind Perl in cmd (Windows) 2011-03-30

    I am using this command to search and replace a string with another in the command prompt: perl -pi -i.bak -e "s/Mohan/Sitaram/g" ab.txt This replaces Mohan with Sitaram in the file ab.txt in the current directory. However I want to replace all occur

  • Accessing latin1 mysql database from perl script using utf8 2011-04-30

    I have a perl script using the utf8 pragma, and for various reasons it is most practical that it does most of its operations in utf8. However, I need to access a mysql database, where all tables are in latin1. How should I do this? A bit of 'pseudoco

  • Upload into database using Perl 2011-07-07

    I have the folling Perl script that I am using to import data directly into a database form an html file. The file is called 'demo.htm'. The script works without the databse connection part in that it strips off what I do not need. However now I am t

  • Copy Directories Recursively From Ftp Server Using Perl 2011-10-20

    I need to write a perl script which has to log in to an FTP server and download all the sub-directories and contents on sub-directories to local machine. The version of Perl on the FTP server is 5.8.8, i can't upgrade it. One method is to create dire

Copyright (C), All Rights Reserved.

processed in 0.502 (s). 13 q(s)