Re: Memory exeception - Mailing list pgsql-jdbc
From | Peter Wasem |
---|---|
Subject | Re: Memory exeception |
Date | |
Msg-id | 3BEB8AA7.E4F6C3D@itag.ch Whole thread Raw |
In response to | Memory exeception (Peter Wasem <peter.wasem@itag.ch>) |
List | pgsql-jdbc |
Hi Barry Looks good! I thought cursors are only available in embedded SQL. So this approach seems to work for me. Thanks for your help. Peter Barry Lind wrote: > Peter, > > You certainly can do this with java. I do in my code in many places. > Consider the following example that processes all rows in the table > 'bar' by fetching 10 rows at a time. > > thanks, > --Barry > > PreparedStatement l_cursorstmt = connection.PrepareStatement("declare > foo_cursor cursor for select foo from bar"); > PreparedStatement l_fetchstmt = connection.PrepareStatement("fetch > forward 10 from foo_cursor"); > > //open the cursor > l_cursorstmt.execute(); > > ResultSet l_rset; > int l_count; > do { > //continue looping until you get no rows returned in a fetch > l_count = 0; > //fetch rows from the cursor using the fetch statement > l_rset = l_fetchstmt.executeQuery(); > while (l_rset.next) { > l_count++; > //process the rows here > } > } while (l_count > 0); > > //don't forget to close the cursor > PreparedStatement l_closestmt = connection.PrepareStatement("close > foo_cursor"); > l_closestmt.execute(); > > Peter Wasem wrote: > > > Hi Barry > > > > This is exactly what I observed. The physical memory dropped down to almost > > zero (but no swap was used) before the exception was thrown. I'm not sure if I > > can use cursors (other than ResultSet) since my application is Java-based. > > > > Thanks > > > > Barry Lind wrote: > > > > > >>Peter, > >> > >>Postgres will return the entire result into memory first before > >>processing the first row of data. If you don't want this behavior you > >>should use explicit cursors and fetch statements to get data a few rows > >>at a time. See the postgres documentation on the 'cursor' and 'fetch' > >>sql statements. > >> > >>thanks, > >>--Barry > >> > >>Peter Wasem wrote: > >> > >> > >>>Hi > >>> > >>>I have a problem in processing large numbers of rows in ResultSets. > >>>Here's the code fragment where the problem occurs: > >>> > >>>... > >>> ResultSet rset = stmt.executeQuery( "select ... from ... where ... > >>>order by ..." ) ; > >>> while(rset.next() ) > >>> { // Process the row } > >>>... > >>> > >>>The query addresses some 100'000 rows. When stmt.executeQuery() is > >>>executed suddenly an exception occurs. > >>>The same program works fine with other JDBC drivers. > >>> > >>>Any ideas ? > >>> > >>>Greetings > >>>Peter > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 2: you can get off all lists at once with the unregister command > >>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >>> > >>> > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html
Attachment
pgsql-jdbc by date: