Successfully Emulated Oracle's connect by prior to postgresql using Java

Friends,

Today, I was successfully able to simulate Oracle's "connect by prior" equivalent SQL to postgresql with a little bit of help from Java. I was fed up with finding a decent solution in the net for using a similar query to connect by prior. I found many options. Top of them being Evgen's connect by patch for Postgresql 8.2/8.3. But that patch comes with a tarball which i need to setup/install which i dont want to do. Another one is using some crawl_tree method given some by guy. That never even came close to working. I was really fed up and finally switched over to my home ground : JAVA. Yeah, connect by prior method simply follows the Depth first search algorithm. You can see the example picture given in the side representing some hierarchical arrangement of data. The Depth first search has to follow the order : D L J I H I A E A I J L K G K F K L D C B. Just trace this path and you will know how this works. After Eliminating the redundant values you will get : DLJIHAEKGFCB which is what connect by does finally.

So implementing this algorithm is a piece of cake and i did too. What i did is, selected all the records from the table "select * from (sometable)". Stored all the values in an ArrayList. Then using two main columns (parent/child Node) , i applied this algorithm. It perfectly worked for me. It gave the exact result what oracle would have given. I even cross verified them side by side using Aquastudio connected to both Oracle and PostgresDB. I guess this idea should be enough in implementing the logic. If you even want the full code, i can do that for ya.

Bye for now from,

Braga