Hello,
I am also new to H2 & JDBC. I modified a the code to call
Statement.getResultSet() in a loop. This shows how the position
increments with each pass of the loop.
.
.
.
Class.forName("org.h2.Driver");
Connection conn = DriverManager.getConnection("jdbc:h2:~/
test");
Statement statement = conn.createStatement();
statement.execute("CREATE TABLE sample_table (sample_table_id
INTEGER, x_cord INTEGER, y_cord INTEGER);\n");
System.out.println("Table update count:" +
statement.getUpdateCount());
statement.execute("INSERT INTO sample_table (sample_table_id,
x_cord, y_cord) VALUES (0, 0, 0);\n");
statement.execute("INSERT INTO sample_table (sample_table_id,
x_cord, y_cord) VALUES (1, 1, 1);\n");
System.out.println("Insert update count:" +
statement.getUpdateCount());
ResultSet rs = statement.executeQuery("SELECT * from sample_table;
\n");
System.out.println("Select update count:" +
statement.getUpdateCount());
while (rs.next()) {
System.out.println(rs.getString("sample_table_id"));
System.out.println(rs.getString("x_cord"));
System.out.println(rs.getString("y_cord"));
System.out.println("Select more results:" +
statement.getResultSet());
}
.
.
.
Table update count:0
Insert update count:1
Select update count:-1
0
0
0
Select more results:rs3: columns: 3 rows: 2 pos: 0
1
1
1
Select more results:rs3: columns: 3 rows: 2 pos: 1
Does this help?
Micheal
On Mar 23, 2:31 pm, Fawzib Rojas <[email protected]> wrote:
> I made a generic wrapper to simplify JDBC use and I use something
> similar. You could do something like this (haven't tested it):
>
> for(
> hasResultSet=statement.execute(); // Comment(1)
> hasResultSet==true || (updateCount=statement.getUpdateCount())!=-1;
> // Comment(2)
> hasResultSet=statement.getMoreResults() // Comment(3)
> ){
> if (hasResult) {
> writer.write(statement.getResultSet()) ;
> }else{
> writer.write(updateCount);
> }
>
> }
>
> Comments:
> 1. Execute the statement, returns true if first result is a resultset,
> false if update count or no results
> 2. will enter loop if we have a result or we have an updatecount
> 3. we processed a result, try to get another one
>
> FYI, in H2 *all* stored procedures return a ResultSet which is a problem
> if you want to have a generic way to process statements. If a stored
> procedure return only update counts, H2 returns a dummy rowset with 1
> field named <procedure_name> and a value of (null). Was pulling my hair
> for a while wondering why my framework worked everywhere except H2. Had
> to change to Apache Derby for that reason.
>
> On 3/23/2011 11:56 AM, dodtsair wrote:
>
> > I was expecting the wrong behavior from getMoreResults. I am writing
> > some code that will likely expect more then one result from some sql.
> > So I wanted to iterate over it.
>
> > something like while(iterator.hasNext()) {iterator.next}
>
> > So I wrote the following code:
> > while (!(statement.getMoreResults() == false&&
> > (updateCount = statement.getUpdateCount()) == -1)) {
> > ResultSet result = statement.getResultSet();
> > if (result != null) {
> > writer.write(result);
> > }
> > writer.write(updateCount);
> > }
>
> > The assumption being that I could have 0 - many results and I could
> > use getMoreResults to cover the 0 case. When the body of the loop
> > never executed I wrote the unit test to simplify the use case in an
> > attempt to find my error.
>
> > Thanks for the help.
>
> > On Mar 22, 10:33 pm, Thomas Mueller<[email protected]>
> > wrote:
> >> Hi,
>
> >>> Right, but it doesn't explain why he gets "Select results:null".
> >> It does. Statement.getMoreResults() is simply the wrong method.
>
> >>> The culprit seems to be the call to "statement.getMoreResults()"
> >> Exactly. It's wrong to call Statement.execute() and then
> >> Statement.getMoreResults(). All databases I have tested return
> >> "false". The correct way is Statement.execute() and then
> >> Statement.getResultSet().
>
> >> Regards,
> >> Thomas
--
You received this message because you are subscribed to the Google Groups "H2
Database" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/h2-database?hl=en.
