pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedJDBC and insert - stack overflow
5+ messages / 5 participants
[nested] [flat]
* JDBC and insert - stack overflow
@ 1999-05-24 14:09 Crispin Miller <crispin@cs.man.ac.uk>
1999-05-25 10:24 ` Re: [GENERAL] JDBC and insert - stack overflow Herouth Maoz <herouth@oumail.openu.ac.il>
0 siblings, 1 reply; 5+ messages in thread
From: Crispin Miller @ 1999-05-24 14:09 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hi,
I am afraid I am a newbie. Forgive me if this is a silly question-
I'm trying to create a large database using JDBC to load the tables by
calling executeUpdate with the statement "SQL INSERT INTO" + tableName +
" VALUES (" etc...
After 14 and a half thousand rows the java virtual machine crashes with:
Exception in thread "main" java.lang.StackOverflowError
at java.lang.Class.newInstance0(Native Method)
at java.lang.Class.newInstance(Compiled Code)
at sun.io.Converters.newConverter(Compiled Code)
at sun.io.Converters.newDefaultConverter(Compiled Code)
at sun.io.ByteToCharConverter.getDefault(Compiled Code)
at java.lang.String.<init>(Compiled Code)
at postgresql.PG_Stream.ReceiveString(Compiled Code)
at postgresql.Connection.ExecSQL(Compiled Code)
at postgresql.Statement.execute(Compiled Code)
at postgresql.Statement.executeUpdate(Compiled Code)
My first question is:
Am I doing things in a really stupid way?
If not, how do I fix the problem - I have tried increasing the default
stack and memory size for the JVM...
Crispin
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [GENERAL] JDBC and insert - stack overflow
1999-05-24 14:09 JDBC and insert - stack overflow Crispin Miller <crispin@cs.man.ac.uk>
@ 1999-05-25 10:24 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-05-27 17:24 ` Re: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow Peter T Mount <peter@retep.org.uk>
0 siblings, 1 reply; 5+ messages in thread
From: Herouth Maoz @ 1999-05-25 10:24 UTC (permalink / raw)
To: crispin@cs.man.ac.uk; +Cc: pgsql-interfaces
At 17:09 +0300 on 24/05/1999, Crispin Miller wrote:
> Hi,
> I am afraid I am a newbie. Forgive me if this is a silly question-
> I'm trying to create a large database using JDBC to load the tables by
> calling executeUpdate with the statement "SQL INSERT INTO" + tableName +
> " VALUES (" etc...
JDBC questions belong in the Interfaces list, where I have now redirected
this thread.
The proper way to perform inserts is using a PreparedStatement, like this:
PreparedStatement pstmt = con.prepareStatement(
"INSERT INTO my_table (num,str,dt) VALUES (?,?,?)"
);
Then you loop, and in each iteration you do:
pstmt.setInt( 1, myIntVariable);
pstmt.setString( 2, myStringVariable);
pstmt.setDate( 3, mySQLDateVariable);
int rowCount = pstmt.executeUpdate();
Try this method. I'm not sure it will solve your stack problems. Usually,
stack overflow is due to deep recursion. But once you use this, you should
be wasting less memory anyway.
By the way, use con.setAutoCommit( false ) to have all the inserts in one
big transaction. It saves a lot of time.
Herouth
--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow
1999-05-24 14:09 JDBC and insert - stack overflow Crispin Miller <crispin@cs.man.ac.uk>
1999-05-25 10:24 ` Re: [GENERAL] JDBC and insert - stack overflow Herouth Maoz <herouth@oumail.openu.ac.il>
@ 1999-05-27 17:24 ` Peter T Mount <peter@retep.org.uk>
1999-05-27 20:44 ` Re: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Peter T Mount @ 1999-05-27 17:24 UTC (permalink / raw)
To: Herouth Maoz <herouth@oumail.openu.ac.il>; +Cc: crispin@cs.man.ac.uk; pgsql-interfaces
On Tue, 25 May 1999, Herouth Maoz wrote:
> At 17:09 +0300 on 24/05/1999, Crispin Miller wrote:
>
>
> > Hi,
> > I am afraid I am a newbie. Forgive me if this is a silly question-
> > I'm trying to create a large database using JDBC to load the tables by
> > calling executeUpdate with the statement "SQL INSERT INTO" + tableName +
> > " VALUES (" etc...
>
> JDBC questions belong in the Interfaces list, where I have now redirected
> this thread.
>
> The proper way to perform inserts is using a PreparedStatement, like this:
>
> PreparedStatement pstmt = con.prepareStatement(
> "INSERT INTO my_table (num,str,dt) VALUES (?,?,?)"
> );
>
> Then you loop, and in each iteration you do:
>
> pstmt.setInt( 1, myIntVariable);
> pstmt.setString( 2, myStringVariable);
> pstmt.setDate( 3, mySQLDateVariable);
>
> int rowCount = pstmt.executeUpdate();
>
> Try this method. I'm not sure it will solve your stack problems. Usually,
> stack overflow is due to deep recursion. But once you use this, you should
> be wasting less memory anyway.
If at some point we can store the query plan when the prepared statement
is created, the updates will speed up tremendously, as the backend isn't
parsing every line. Obviously this is a future item...
> By the way, use con.setAutoCommit( false ) to have all the inserts in one
> big transaction. It saves a lot of time.
Don't forget to commit() at the end, as you can't guarantee that a
transaction will be committed when the connection closes.
Peter
--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow
1999-05-24 14:09 JDBC and insert - stack overflow Crispin Miller <crispin@cs.man.ac.uk>
1999-05-25 10:24 ` Re: [GENERAL] JDBC and insert - stack overflow Herouth Maoz <herouth@oumail.openu.ac.il>
1999-05-27 17:24 ` Re: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow Peter T Mount <peter@retep.org.uk>
@ 1999-05-27 20:44 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 1999-05-27 20:44 UTC (permalink / raw)
To: Peter T Mount <peter@retep.org.uk>; +Cc: pgsql-interfaces
Peter T Mount <peter@retep.org.uk> writes:
>> By the way, use con.setAutoCommit( false ) to have all the inserts in one
>> big transaction. It saves a lot of time.
> Don't forget to commit() at the end, as you can't guarantee that a
> transaction will be committed when the connection closes.
In fact, I believe that we guarantee the opposite: any open transaction
will be aborted if the client closes the connection without sending a
commit command...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* RE: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow
@ 1999-05-28 09:17 Peter Mount <petermount@it.maidstone.gov.uk>
0 siblings, 0 replies; 5+ messages in thread
From: Peter Mount @ 1999-05-28 09:17 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Peter Mount (Home) <peter@retep.org.uk>; +Cc: pgsql-interfaces
That's what I thought, but at that time in the morning, I wasn't sure
;-)
--
Peter T Mount, Enterprise Support
petermount@it.maidstone.gov.uk
Anything I write here are my own views, and cannot be taken as the
official words of Maidstone Borough Council
-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Thursday, May 27, 1999 9:45 PM
To: Peter T Mount
Cc: pgsql-interfaces@postgreSQL.org
Subject: Re: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow
Peter T Mount <peter@retep.org.uk> writes:
>> By the way, use con.setAutoCommit( false ) to have all the inserts in
one
>> big transaction. It saves a lot of time.
> Don't forget to commit() at the end, as you can't guarantee that a
> transaction will be committed when the connection closes.
In fact, I believe that we guarantee the opposite: any open transaction
will be aborted if the client closes the connection without sending a
commit command...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~1999-05-28 09:17 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
1999-05-24 14:09 JDBC and insert - stack overflow Crispin Miller <crispin@cs.man.ac.uk>
1999-05-25 10:24 ` Herouth Maoz <herouth@oumail.openu.ac.il>
1999-05-27 17:24 ` Peter T Mount <peter@retep.org.uk>
1999-05-27 20:44 ` Tom Lane <tgl@sss.pgh.pa.us>
1999-05-28 09:17 RE: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow Peter Mount <petermount@it.maidstone.gov.uk>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox