Hi,

I am trying to run a SELECT Statement which should return 250'000
records. I want to iterate trhough this recordset and insert a record
to another table after some calculations... but I ran into a problem I
dont understand. I included some code below together with the table
definition.

My system has 1.5 GB RAM, I am runnning Windows XP, Firebird 1.5 RC3
and Jaybird1 and Jdk 1.4.2.

If I run the code below I get the following output (First lines ommited):

..................................................350
.................................................Error during
Transform.Contacts...
GDS Exception. Dynamic SQL Error
SQL error code = -504
Cursor unknown
Error Code: 335544569
SQL State: null

If I comment the autocommit and commit statements I get an out of
memory message. If I add a first statement I can open the SELECT for
the first 70'000 records, if I put a higher number I get the following
output:

TestContacts...
java.lang.OutOfMemoryError
Exception in thread "main"

Task Manager tells me that I have more than 750 MB available memory.

Maybe some of the more experienced developers in this form have some
advice on how to solve this.

Thanks in advance
regards, Hans

---------------------------------------------------------------------
package ch.smp.transform;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import ch.utils.HiHelper;

public class TestContacts {

private Connection conn = null;
private PreparedStatement insStmt = null;
private Statement selStmt = null;
private ResultSet rs = null;

private long rc = 0;

//----------------------------------------------------------------------
public TestContacts(Connection c) {

System.out.println("TestContacts... ");
conn = c;

try {

selStmt = conn.createStatement();
selStmt.execute("DELETE FROM Contacts");

conn.setAutoCommit(false);

rs = selStmt.executeQuery
("SELECT kaknr, kalndc, kavnam, kaname, kanamz, " +
" kastr, kaplz, kaort, kadist, kapofc, kapofa, kaplzp, " +
" kaortp, katelp, katelg, katfax, kasprc, kamkk, kaanrc, " +
" katitc, kagdat, kawspc, kalspc, kaljmt, kaaspc, kaajmt, " +
" kaovgr, kaklac, kagknr, kalrgc, kaprgc, kawspc, kalspc, " +
" kaljmt, kaaspc, kaajmt, kaovgr, kaklac, kagknr, kalrgc, " +
" kaprgc, kknaz2, kkvna2, kkanr2 " +
"FROM mka0p LEFT JOIN mkk0p ON kaknr=kkknr " +
"ORDER BY CAST(kaknr AS INTEGER)");

while (rs.next()) {
System.out.print(".");
rc++;
if (rc % 50 == 0) {
System.out.println(rc);
conn.commit();
}
}
rs.close();

conn.setAutoCommit(true);

}
catch (SQLException e) {
System.out.println ("Error during Transform.Contacts...");
HiHelper.showSQLException (e);
}
}

}

-------------------------------------------------------------------
DDL:

CREATE TABLE MKA0P (
KAKNR VARCHAR(10) NOT NULL,
KALNDC VARCHAR(2),
KAPMCP VARCHAR(5),
KAPMCN VARCHAR(4),
KAPMCS VARCHAR(4),
KAPMCH VARCHAR(4),
KAPMCV VARCHAR(1),
KAPMCZ VARCHAR(1),
KASSTR VARCHAR(5),
KASNAM VARCHAR(5),
KAVNAM VARCHAR(14),
KANAME VARCHAR(30),
KANAMZ VARCHAR(30),
KASTR VARCHAR(30),
KAPLZ VARCHAR(6),
KAZPA VARCHAR(4),
KAORT VARCHAR(24),
KADIST VARCHAR(30),
KAPOFC VARCHAR(1),
KAPOFA VARCHAR(10),
KAPLZP VARCHAR(6),
KAORTP VARCHAR(24),
KATELP VARCHAR(16),
KATELG VARCHAR(16),
KATFAX VARCHAR(16),
KASPRC VARCHAR(1),
KAMKK VARCHAR(1),
KAANRC VARCHAR(1),
KATITC VARCHAR(1),
KAGDAT VARCHAR(10),
KAZIVC VARCHAR(1),
KAKIND VARCHAR(2),
KALEGC VARCHAR(1),
KALEGJ VARCHAR(6),
KAABSP VARCHAR(1),
KAWSPC VARCHAR(1),
KALSPC VARCHAR(1),
KALJMT VARCHAR(10),
KAASPC VARCHAR(1),
KAAJMT VARCHAR(10),
KAOVGR VARCHAR(4),
KAKLAC VARCHAR(1),
KAGKNR VARCHAR(10),
KALRGC VARCHAR(3),
KAPRGC VARCHAR(3),
KAWRGC VARCHAR(3),
KAMOS1 VARCHAR(2),
KAMOS2 VARCHAR(2),
KAK01C VARCHAR(3),
KAK02C VARCHAR(3),
KAK03C VARCHAR(3),
KAK04C VARCHAR(3),
KAK05C VARCHAR(3),
KAK06C VARCHAR(3),
KAK07C VARCHAR(3),
KAK08C VARCHAR(3),
KAKAUF VARCHAR(4),
KAFKNR VARCHAR(4),
KAASLD VARCHAR(11),
KADSLD VARCHAR(11),
KAKRED VARCHAR(8),
KAKRIC VARCHAR(2),
KAKREC VARCHAR(2),
KAKJMT VARCHAR(10),
KAMSPC VARCHAR(1),
KAUSTI VARCHAR(17),
KAMWSP VARCHAR(1),
KAZHLC VARCHAR(1),
KAPFR VARCHAR(1),
KAETLC VARCHAR(1),
KARABS VARCHAR(5),
KAEPAS VARCHAR(5),
KAVSAC VARCHAR(1),
KAPREL VARCHAR(1),
KAHJMT VARCHAR(10),
KAHWTR VARCHAR(3),
KAHZLG VARCHAR(3),
KAHBID VARCHAR(1),
KAHKNR VARCHAR(10),
KABWRA VARCHAR(10),
KABAUA VARCHAR(10),
KAJWO1 VARCHAR(8),
KAWTR1 VARCHAR(3),
KAZLG1 VARCHAR(3),
KABID1 VARCHAR(1),
KAUMS1 VARCHAR(6),
KAAWR1 VARCHAR(4),
KAUWR1 VARCHAR(7),
KAJWO2 VARCHAR(8),
KAWTR2 VARCHAR(3),
KAZLG2 VARCHAR(3),
KABID2 VARCHAR(1),
KAUMS2 VARCHAR(6),
KAAWR2 VARCHAR(4),
KAUWR2 VARCHAR(7),
KAJWO3 VARCHAR(8),
KAWTR3 VARCHAR(3),
KAZLG3 VARCHAR(3),
KABID3 VARCHAR(1),
KAUMS3 VARCHAR(6),
KAAWR3 VARCHAR(4),
KAUWR3 VARCHAR(7),
KAJWO4 VARCHAR(8),
KAJWO5 VARCHAR(8),
KAZHLP VARCHAR(6),
KADEBE VARCHAR(8),
KARFMR VARCHAR(6),
KADBWK VARCHAR(6),
KAAKTI VARCHAR(50),
KAWGRA VARCHAR(100),
KAAADK VARCHAR(6),
KAABSK VARCHAR(6),
KAATBK VARCHAR(6),
KAAFBK VARCHAR(6),
KAAEBK VARCHAR(6),
KAALDK VARCHAR(6),
KAALFK VARCHAR(6),
KAAALK VARCHAR(8),
KAAARK VARCHAR(8),
KAAABO VARCHAR(4),
KAIABO VARCHAR(4),
KAAAAK VARCHAR(6),
KAANNK VARCHAR(2),
KAAKNK VARCHAR(2),
KAUADK VARCHAR(8),
KAUBSK VARCHAR(10),
KAUTBK VARCHAR(10),
KAUFBK VARCHAR(10),
KAUEBK VARCHAR(10),
KAULFK VARCHAR(10),
KAURTK VARCHAR(10),
KAURPK VARCHAR(4),
KAULDK VARCHAR(10),
KAUAAK VARCHAR(10),
KAMJMT VARCHAR(10),
KAMVIS VARCHAR(10),
KAJMTA VARCHAR(10),
KAVISA VARCHAR(10)
);

ALTER TABLE MKA0P ADD CONSTRAINT PK_MKA0P PRIMARY KEY (KAKNR);

CREATE TABLE MKK0P (
KKKNR VARCHAR(10) NOT NULL,
KKNAZ2 VARCHAR(30),
KKVNA2 VARCHAR(14),
KKANR2 VARCHAR(1),
KKTITC VARCHAR(1),
KKGDA2 VARCHAR(10),
KKHNZ1 VARCHAR(4),
KKHNZ2 VARCHAR(4),
KKHNZ3 VARCHAR(4),
KKKO1C VARCHAR(2),
KKKO1N VARCHAR(20),
KKKO2C VARCHAR(2),
KKKO2N VARCHAR(20),
KKKO3C VARCHAR(2),
KKKO3N VARCHAR(20),
KKMJMT VARCHAR(10),
KKMVIS VARCHAR(10)
);

ALTER TABLE MKK0P ADD CONSTRAINT PK_MKK0P PRIMARY KEY (KKKNR);