Hello, your update command is not a primary key update. So it's possible that your update affects more than one row. But this is not allowed for updates which contains a long column.
Regards, Marco ---------------------------------------------- Marco PASKAMP SAP DB, SAP Labs Berlin > -----Original Message----- > From: Dirk Zoettl [mailto:[EMAIL PROTECTED]] > Sent: Mittwoch, 22. Januar 2003 20:08 > To: [EMAIL PROTECTED] > Subject: Java UPDATE problem > > > 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(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
