Hi *,
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.
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(ReplyPacket.java:56)
at com.sap.dbtech.jdbc.ConnectionSapDB.throwSQLError(ConnectionSapDB.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(CallableStatementSapDB.java:340)
at com.sap.dbtech.jdbc.CallableStatementSapDB.execute(CallableStatementSapDB.java:255)
at com.sap.dbtech.jdbc.CallableStatementSapDB.executeUpdate(CallableStatementSapDB.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
