Apache OpenOffice (AOO) Bugzilla – Issue 77865
ResultsetType and ResultSetConcurrency do not change
Last modified: 2013-08-07 15:45:09 UTC
I used a calc document to connect a base database (function getVerbindung) function getVerbindung dim databasecontext, quelle as object databasecontext = createUnoService("com.sun.star.sdb.DatabaseContext") quelle = databasecontext.getByName( “/.../..“) getVerbindung = quelle.getConnection("","") end function When i try to update content of the database via the resultset it did (and will) not work. Code Example and Error Message: sub test dim verb, set11 as object verb = getVerbindung set11 = verb.createStatement set11.ResultsetType = 1005 ' ResultsetType is changing from 1003 to 1004 but never to 1005 set11.ResultSetConcurrency = 1008 'Just 1007 is possible set11 = set11.executeQuery("Select ""Name"" from ""Personendaten""") set11.next msgbox set11.getString(1) ' you can read the first name stored into the database set11.updateString(1, "Test") ' Error Message: "...Resultset is Read Only", Basic crashs set11.updateRow verb.close End Sub I tried that with OpenOffice 2.2 (german version) Open Office 2.1 (german version) Open Office 2.1 portable (german version) everytime on a XP system. After posting the problem in the OO Forum (german) some people meant it could be a bug in OO. One user reported the same problem. http://de.openoffice.info/viewtopic.php?p=49499#49499 and my own post and responses: http://de.openoffice.info/viewtopic.php?p=49195#49195
I have a similar behaviour with Oo2.0.4 (Debian package *.2.0.4.dfsg.2-7etch1) running on linux 2.6.18-3-486. Sample code below generates the problem that a ResultSet is not created to be updateable when the Statement ResultSetConcurrency is set to UPDATABLE. SCROLL_INSENSITIVE seems to work ok, however. I have checked that the ResultSet is read correctly but cannot be updated, but this is not shown below. The data source file has write permission for all users. REM ***** BASIC ***** Sub Main Dim DatabaseContext As Object Dim Names Dim DataSource As Object Dim Connection As Object Dim Stmt As Object Dim Rs As Object DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext") Names = DatabaseContext.getElementNames() DataSource = DatabaseContext.getByName(Names(2)) MsgBox DataSource.Name 'data source is found ok MsgBox DataSource.IsPasswordRequired 'Result=false Connection = DataSource.getConnection("","") Stmt=Connection.createStatement() MsgBox Stmt.ResultSetConcurrency 'Result=1007 MsgBox Stmt.ResultSetType 'Result=1003 Stmt.ResultSetConcurrency=com.sun.star.sdbc.ResultSetConcurrency.UPDATABLE Stmt.ResultSetType=com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE MsgBox Stmt.ResultSetConcurrency 'Result=1008 MsgBox Stmt.ResultSetType 'Result=1004 Rs=Stmt.executeQuery("SELECT ""Description"" FROM ""Purchases""") 'Data are read ok into Rs MsgBox Rs.ResultSetConcurrency 'Result=1007 (Should be 1008) MsgBox Rs.ResultSetType 'Result=1004 MsgBox Stmt.ResultSetConcurrency 'Result=1007 (Should be 1008) MsgBox Stmt.ResultSetType 'Result=1004 End Sub
does the problem still occure in a current version?
I can confirm that the code as used for this Issue will not return an result set that you can update. One problem is that by default the EscapeProcessing property of the satatement is FALSE. However, setting this to TRUE before the call to executeQuery does not fix the problem.
change owner
set to 'new' and set target
I suppose all of you use the HSQLDB engine, don't you? The problem is that HSQL in version 1.8.* does not support updatable result sets (IIRC, 1.9.* will support them). The result set type and concurrency are silently downgraded, which is allowed by both the JDBC and the SDBC API (though we in fact encountered drivers in the past which did no silent downgrading, but instead raised an exception). So, what you encounter here is the fact that your backend does not support the feature you try to use. Other solutions for updates include - use UPDATE statements, instead of updatable result sets - use a css.sdb.RowSet instance
closing