update call with jdbc

[Thomas Günter]

hi

I'm using jdbc to connect to an existing informix database and have real
troubles with executing an update command. the following is a printout
of my console. as you can see, the select works fine, but the update
produces an exception.

X:\>java dbconnection.SQLClient
psql> open
Database connection could be created!

psql> select AufNr, AufIdVrLinie from T_AufAuftrag where AufNr = 20
Cols: 2
aufnr | aufidvrlinie |
---------------| ---------------|
20 | 0 |

psql> UPDATE T_AufAuftrag SET AufIdVrLinie = 1 WHERE AufNr = 20;
While updating table:
java.sql.SQLException
at
com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:308)
at
com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1227)
at
com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1147)
at
com.informix.jdbc.IfxResultSet.executeQuery(IfxResultSet.java:218)
at
com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:707)
at
com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:117)
at
dbconnection.InformixConnection.updateSQL(InformixConnection.java:76)
at dbconnection.SQLClient.execCmd(SQLClient.java:95)
at dbconnection.SQLClient.<init>(SQLClient.java:42)
at dbconnection.SQLClient.main(SQLClient.java:28)

this is the implementation of the update call:

stmt = con.createStatement();
stmt.executeQuery(update);

where update is the whole update command from above. there can't be many
things wrong and i took the code from the informix examples. so the only
possible error source could be the SQL statement. i just can't finde the
mistake. have you got an idea?

thanks for any help!

tom

[dhag...@my-deja.com]

One issue is that you are calling Statement.executeQuery(...) rather
than Statement.executeUpdate(...) or just Statement.execute(...).

Also, typically with JDBC, you leave off semicolons on the individual
SQL statements, though I'm not sure if this is the case with the
Informix driver.

Finally, it is safest to do SQL UPDATE's through PreparedStatement's
so that string quoting/encoding issues are handled for you by the
driver. E.g.,

int cnt = -1;
PreparedStatement ps = null;
try {
ps = con.prepareStatement("UPDATE T_AufAuftrag SET " +
"AufIdVrLinie = ? WHERE AufNr = ?");
ps.setInt(1,1);
ps.setInt(2,20);
cnt = ps.executeUpdate();
} finally {
if(ps != null) try {
ps.close();
} catch(SQLException ignore) { }
}

This also demonstrates how to cleanly ensure that your statement
is closed, regardless of whether an exception is thrown -- something
that is very important to ensure resource cleanup.

Hope this helps,

-=- D. J.

In article <3A0AE238...@isoe.ch>,

Sent via Deja.com http://www.deja.com/
Before you buy.