Dirk Zoettl wrote:
>
> I have a problem concerning an UPDATE statement.
> Though a SELECT COUNT(*) returns 1 I get an exception when
> I try to execute an UPDATE - see test code and output.
>
> SQL> UPDATE xyz SET ver=ver+1,len=?,data=?,bytes=? WHERE
> sid=? AND oid=? AND cur=1
> EXC: [-7012] More than one update row not allowed
>
> When I leave out the binary column named "bytes", the statement
> gets executed without exception.
Thank you for reporting.
This is a bug and will be fixed.
To explain a little bit:
the kernel scans the table, one row after the other. In this case (no index,
no corresponding where-clause) it has to scan the whole table in key-sequence.
The first (and only) row fulfilling the where-clause is found AND THE LAST KEY-COLUMN
set to a higher value than it had before.
The kernel looks for the next row, sees THIS just handled row and before the check
can be done that this is one of the rows handled during this update
(--> this re-found row will not changed again),
before this check can happen, the check is done that no more than 1 row within
one update-command can change its long-value.
And here we are.
Workaround:
If one wants to update a long-column with a real value and can specifiy a
qualification that the kernel finds 0 or 1 row (even internally) --> no -7012, then
a normal
update SET long-column = :param
is fine.
If one wants to update a long-column with a real value and has trouble with -7012, then
select ... for update
update ... where current of
is needed.
If one wants to set the long-column to NULL (which is possible for how
many rows you like in one update-statement), use
update ... set long-col = NULL
do not use a parameter there.
Elke
SAP Labs Berlin
>
> Source code:
>
> import java.sql.*;
>
>
> /**
> * Test database transaction isolation.
> */
> public class UpdateTest {
>
> public static void main(String[] args) throws Exception {
> String driver = "com.sap.dbtech.jdbc.DriverSapDB";
> String url =
> "jdbc:sapdb://localhost/TANGRO?transport=socket";
> String user = "user";
> String password = "password";
>
> //--- load driver ---
> Class.forName(driver);
>
> //--- connect to database ---
> Connection conn = getConnection(url, user, password);
>
> //--- display info ---
> // String info = getDbInfo(conn, true);
> // System.out.println(info);
>
> Statement stmt;
>
> //--- setup ---
> stmt = conn.createStatement();
> try {
> stmt.execute("DROP TABLE xyz");
> conn.commit();
> } catch (SQLException ex) { }
> stmt.execute("CREATE TABLE xyz (sid INTEGER NOT
> NULL, oid INTEGER NOT NULL, ver INTEGER NOT NULL, " +
> "cur INTEGER NOT NULL, len INTEGER NOT
> NULL, data VARCHAR(30), bytes LONG BYTE, PRIMARY KEY (sid,oid,ver))");
> stmt.executeUpdate("INSERT INTO xyz
> (sid,oid,ver,cur,len,data,bytes) values (4711, 1, 1, 0, 10,
> 'a very old', x'112233')");
> stmt.executeUpdate("INSERT INTO xyz
> (sid,oid,ver,cur,len,data,bytes) values (4711, 1, 2, 0, 5,
> 'a old', x'445566')");
> stmt.executeUpdate("INSERT INTO xyz
> (sid,oid,ver,cur,len,data,bytes) values (4711, 1, 3, 1, 9,
> 'a current', x'778899')");
> stmt.executeUpdate("INSERT INTO xyz
> (sid,oid,ver,cur,len,data,bytes) values (4711, 2, 1, 1, 1,
> 'b', x'AABBCC')");
> conn.commit();
>
> PreparedStatement pstmt =
> conn.prepareStatement("SELECT COUNT(*) FROM xyz WHERE sid=?
> AND oid=? AND cur=1");
> pstmt.setInt(1, 4711);
> pstmt.setInt(2, 1);
> ResultSet rset = pstmt.executeQuery();
> rset.next();
> int count = rset.getInt(1);
> rset.close();
> System.out.println("count=" + count);
>
> pstmt = conn.prepareStatement("UPDATE xyz SET
> ver=ver+1,len=?,data=?,bytes=? WHERE sid=? AND oid=? AND cur=1");
> pstmt.setInt(1, -1);
> pstmt.setString(2, "update test");
> pstmt.setBytes(3, null);
> pstmt.setInt(4, 4711);
> pstmt.setInt(5, 1);
> int rc = pstmt.executeUpdate();
> System.out.println("rc=" + rc);
> conn.commit();
> conn.close();
> System.exit(0);
> }
>
> private static Connection getConnection(String url,
> String user, String password) throws SQLException {
> Connection conn = DriverManager.getConnection(url,
> user, password);
> conn.setAutoCommit(false);
> conn.setReadOnly(false);
> return conn;
> }
>
> // ... some helper functions for testing left out
> }
>
> //--- end of file
> --------------------------------------------------------------
> --------------------
>
> Output:
> C:\jdk1.3.1_06\bin\java.exe -classpath
> C:\tangro\classes;D:\sapdb\sapdb-jdbc-bin-7.3.00.29a.jar
> de.tangro.sql.UpdateTest
> isReadOnly : false
> getAutoCommit : false
> getCatalog : null
> getURL : "jdbc:sapdb://localhost/TANGRO"
> getUserName : "DBA"
> getDriverName : "SAP DB"
> getDriverVersion : "package
> com.sap.dbtech.jdbc, SAP DB JDBC Driver, SAP AG, 7.3.0
> Build 029-000-087-559"
> getDriverMajorVersion : 0
> getDriverMinorVersion : 1
> getDatabaseProductName : "SAP DB"
> getDatabaseProductVersion : "Kernel 7.3.0 Build
> 029-000-087-809"
> getIdentifierQuoteString : "
> getMaxColumnsInTable : 254
> getMaxConnections : 50
> getMaxStatements : 2147483647
> getDefaultTransactionIsolation : TRANSACTION_READ_COMMITTED
> getTransactionIsolation : TRANSACTION_READ_COMMITTED
>
> count=1
> com.sap.dbtech.jdbc.exceptions.DatabaseException: SAP DBTech
> SQL: [-7012] More than one update row not allowed
> at
> com.sap.dbtech.jdbc.packet.ReplyPacket.createException(ReplyPa
> cket.java:56)
> at
> com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError(ConnectionSa
> pDB.java:699)
> at
> com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:380)
> at
> com.sap.dbtech.jdbc.ConnectionSapDB.execute(ConnectionSapDB.java:278)
> at
> com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableSta
> tementSapDB.java:340)
> at
> com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableSta
> tementSapDB.java:255)
> at
> com.sap.dbtech.jdbc.CallableStatementSapDB.executeUpdate(Calla
> bleStatementSapDB.java:639)
> at UpdateTest.main(UpdateTest.java:54)
> Exception in thread "main" Process terminated with exit code 1
>
>
> Sincerely,
> Dirk Z�ttl
> --
> _____________________________________________________
> | |
> | tangro software components gmbh |
> | |
> | dirk zoettl phone: +49 6221 13336-0 |
> | czernyring 22/10 fax: +49 6221 13336-21 |
> | d-69115 heidelberg email: [EMAIL PROTECTED] |
> | germany internet: www.tangro.de |
> |_____________________________________________________|
>
> _______________________________________________
> sapdb.general mailing list
> [EMAIL PROTECTED]
> http://listserv.sap.com/mailman/listinfo/sapdb.general
>
_______________________________________________
sapdb.general mailing list
[EMAIL PROTECTED]
http://listserv.sap.com/mailman/listinfo/sapdb.general
