Skip site navigation (1) Skip section navigation (2)

Re: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow

From: Peter T Mount <peter(at)retep(dot)org(dot)uk>
To: Herouth Maoz <herouth(at)oumail(dot)openu(dot)ac(dot)il>
Cc: crispin(at)cs(dot)man(dot)ac(dot)uk, pgsql-interfaces(at)postgresql(dot)org
Subject: Re: [INTERFACES] Re: [GENERAL] JDBC and insert - stack overflow
Date: 1999-05-27 17:24:50
Message-ID: Pine.LNX.4.04.9905271822440.2157-100000@maidast.retep.org.uk (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-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(at)retep(dot)org(dot)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


In response to

pgsql-interfaces by date

Next:From: Alex TurnerDate: 1999-05-27 17:49:06
Subject: Wierd error when creating a table...
Previous:From: Swen KabisDate: 1999-05-27 17:24:08
Subject: MySQL -> PostGreSQL conversion

pgsql-general by date

Next:From: Paul RamseyDate: 1999-05-27 17:27:28
Subject: Support for Geometric Types
Previous:From: Ji LiDate: 1999-05-27 17:08:54
Subject: DBI connection problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group