PostgreSQL JDBC - Should this code work??

Author

Should this code work??

Joost Kraaijeveld

2005-10-31, 9:24 am

Hi,

Should this code work (it obviously doesn't but I want to know why):

Class.forName("org.postgresql.Driver");
Connection connection =
DriverManager.getConnection("jdbc:postgresql://muntserver:5432/muntdev","postgres", "");

Statement selectStatement = connection.createStatement();
ResultSet resultSet = selectStatement.executeQuery(
"select objectid, orderobjectid from prototype.orderlines");

connection. setAutoCommit(false)
;

PreparedStatement updateStatement =
connection.prepareStatement("update prototype.orderlines set
ordernumber = (select ordernumber from prototype.orders where objectid
= ?) where objectid = ?");

while( resultSet.next() )
{
if( (++record % 1000) == 0){
System.err.println( "handling record: " + record);
}

updateStatement. setString(1,resultSe
t.getString("orderobjectid"));
updateStatement. setString(2,resultSe
t.getString("objectid"));

updateStatement.addBatch();
}

updateStatement.execute();
connection.commit();
selectStatement.close();
updateStatement.close();
connection.close();

Sorry about the HTML but for some reason my email editor suddenly
insists on wrapping otherwise.

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

Joost Kraaijeveld

2005-10-31, 9:24 am

To answer my own question: because you did not call executeBatch() but
execute() ?

--
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Rob Kirkbride

2005-10-31, 9:24 am

Joost Kraaijeveld wrote:

> Hi,
>
> Should this code work (it obviously doesn't but I want to know why):
>
> Class.forName("org.postgresql.Driver");
> Connection connection =
> DriverManager.getConnection("jdbc:postgresql://muntserver:5432/muntdev","postgres",
> "");
>
> Statement selectStatement = connection.createStatement();
> ResultSet resultSet = selectStatement.executeQuery(
> "select objectid, orderobjectid from prototype.orderlines");
>
> connection. setAutoCommit(false)
;
>
> PreparedStatement updateStatement =
> connection.prepareStatement("update prototype.orderlines set
> ordernumber = (select ordernumber from prototype.orders where objectid
> = ?) where objectid = ?");
>
> while( resultSet.next() )
> {
> if( (++record % 1000) == 0){
> System.err.println( "handling record: " + record);
> }
>
> updateStatement. setString(1,resultSe
t.getString("orderobjectid"));
> updateStatement. setString(2,resultSe
t.getString("objectid"));
>
> updateStatement.addBatch();
> }
>
> updateStatement.execute();
> connection.commit();
> selectStatement.close();
> updateStatement.close();
> connection.close();

It would help if you gave us the error that you're getting?

Rob

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Jan de Visser

2005-10-31, 11:23 am

On Monday 31 October 2005 09:54, Joost Kraaijeveld wrote:
> Â_Â_Â_Â_Â_Â_Â_Â_Â_Â_
Â_Â_Â_Â_Â_Â_Â_Â_Â_Â_
Â_Â_Â_Â_Â_Â_Â_Â_Â_Â_
Â_Â_updateStatement. addBatch();Â_Â_Â_Â_Â
_Â_Â_Â_Â_Â_Â_Â_Â_Â_Â
_Â_
> Â_Â_Â_Â_Â_Â_Â_Â_Â_Â_
Â_Â_Â_Â_Â_Â_Â_Â_Â_Â_
Â_ }
>
> Â_Â_Â_Â_Â_Â_Â_Â_Â_Â_
Â_Â_Â_Â_Â_Â_Â_Â_Â_Â_
Â_Â_Â_Â_updateStatem
ent. execute();Â_Â_Â_Â_Â_
Â_Â_Â_Â_Â_Â_Â_Â_Â_Â_
Â_Â_Â_Â_Â_Â_Â_

You need to call updateStatement.executeBatch()

HTH,

JdV!!

--
--------------------------------------------------------------
Jan de Visser Â_ Â_ Â_ Â_ Â_ Â_ Â_ Â_ Â_ Â_ jdevisser@digitalfai
rway.com

Â_ Â_ Â_ Â_ Â_ Â_ Â_ Â_ Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Sponsored Links