Thread: Should this code work??

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,resultSet.getString("orderobjectid"));
updateStatement.setString(2,resultSet.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

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

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,resultSet.getString("orderobjectid"));
> updateStatement.setString(2,resultSet.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

On Monday 31 October 2005 09:54, Joost Kraaijeveld wrote:
>                                 updateStatement.addBatch();                
>                      }
>
>                         updateStatement.execute();                      


You need to call updateStatement.executeBatch()

HTH,

JdV!!

--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------