Reading from database with 2 Stmts/Resultsets - Resultset gets deleted midway through

Question

I'm currently trying to read data from a database using two Statements and two ResultSets, one of both for "mainresults" and the other two for temporary data.

mainresult = mainconn.executeQuery("SELECT problem_name, dir, sparse from problem");
while(mainresult.next()){
     ....
//I know, not the finest query, but it should do the job, shouldn't it?
tempresult = tempconn
            .executeQuery("SELECT upper_name, complexity_name FROM problem_reduction "
                    + "INNER JOIN problem ON problem_reduction.lower_name ="
                    + "problem.problem_name WHERE problem_reduction.lower_name= \""
                    + mainresult.getString("problem_name") + "\"");
            //This is where the mainresults are gone (only if tempresult has an entry)
            while (tempresult.next()) {

            // set attributes from reductionWrapper
            from = tempresult.getString("upper_name");
            Complexity c = Complexity.getComplexity(tempresult
                    .getString("complexity_alias"));

            // add attributes to reductionsTodo
            reductionsTodo.add(new ReductionWrapper(curProblem, from, c));
            }

eg. problem holds 20 entrys. For the 4th entry there are actually results for the tempresultset. So after parsing through those tempresults the reamining 16 results in mainresults are gone. The type of mainconn is Statement by the way, bad choice of name there sorry ;)

I should add that the methods in the second while loop have nothing to do with databaseconnections OR the resultsets. So, is there something I'm missing?

I know it's hard to analyse it without the full code but the error should actually be here..

Not sure you can have 2 simultaneously open connections. Maybe we can find a way to do it in one? — Relevant, Jul 09 '14 at 16:51
@Relevant what on earth do you mean? Can't have two open connections? That would render a database entirely useless would it not? — Boris the Spider, Jul 09 '14 at 16:53
@OP unrelated, but you **must** use `PreparedStatement` and parameterised queries. Doing anything else is irresponsible. — Boris the Spider, Jul 09 '14 at 16:54
As a side note, I would suggest replacing `java.sql.Statement` with `java.sql.PreparedStatement` in order to avoid SQL injection.Not that anyone would care.. — Daniel, Jul 09 '14 at 16:54
From your problem description it is difficult to tell what your are seeing. Are you seeing an error? If so, what error? What is the type of mainconn? Is it Connection? Then you should probably do Statement stmt = mainconn.createStatement(); and then mainresult = stmt.executeQuery("select problem_name... "); etc. — skarist, Jul 09 '14 at 16:55
Maybe this is related? I know it's not mysql, but maybe something similar. http://stackoverflow.com/questions/6054384/sql-nested-queries-in-c-sharp-and-connection-open-problem — Relevant, Jul 09 '14 at 16:56
@skarist No, I don't get an error. the problem table actually holds 20 entrys. For the 4th entry there are actually results for the tempresultset. So after parsing through those tempresults the reamining 16 results in mainresults are gone. The type of mainconn is Statement by the way, bad choice of name there sorry ;) — user3821361, Jul 09 '14 at 16:58
@BoristheSpider Of course I didn't mean that a database can't handle multiple connections. Sorry if that's the way it came across. — Relevant, Jul 09 '14 at 17:01
If you temporarily comment out the `Complexity c` assignment and the `reductionsTodo.add` does the outer loop continue with the remaining 16 items that are currently "disappearing"? — Gord Thompson, Jul 09 '14 at 17:23
Since I'm a new user I have to post the answer here: OK, I actually found the error and I'm quite ashamed to admit: .executeQuery("SELECT upper_name, complexity_name FROM... Complexity c = Complexity.getComplexity(tempresult .getString("complexity_alias")); Won't actually get me any results, since I've never selected the "complexity_alias". Maybe changing values with replace all is sometimes a good option;) — user3821361, Jul 09 '14 at 17:45

Reading from database with 2 Stmts/Resultsets - Resultset gets deleted midway through

0 Answers0