How to make a ResultSet Read/Write

I have a DRAW document and BASE document. The BASE document has tables that contain data which relates to different shapes in the DRAW document.

I have written a macro that will display data related to a selected shape and display it in a Dialog.

Clicking the "Cancel button on the Dialog causes it to close, so all good up to that point.

I want to be able to edit the TextBoxes on the Dialog and then click the OK button to save the updated data back to the BASE document but when I try to do this I get an error message that the “Result Set is Read Only”.

I’ve used MRI to examine the Connection to determine that it is Read/Write, but do not see a Read Only property for the Result Set.

I have manually tested the SQL statement in the BASE GUI and I can update the fields that way, so I think my SQL is OK.

The OOo BASIC Guide tells me there is a ResultSetConcurrency Variant that controls whether the Result Set can be modified (but doesn’t go so far as to explain how to modify it). Using MRI I can see that ResultSetConcurrency is a number which is ReadOnly. I’m guessing that I need to set this value to something else, somehow, as part of the ExecuteQuery method. Half a day of looking through various documents and forums has not enlightened me :frowning:

I’ve attached my Macro(s) that relate to the problem I’m stuck at, so perhaps someone can point me in the right direction (please!)
BTW - these macros are still in very early development, so maybe there is some fundamental flaw here somewhere.

Option Explicit

Global FibreDoc As Object
Global Connection As Object
Global DataSource As Object
Global DatabaseContext As Object
Global bPitSaveFlag as Boolean
Global oPitDialog as Object

Sub SetDefaults

	Dim InteractionHandler as Object
FibreDoc = ThisComponent
DatabaseContext = createUnoService("")
DataSource = DatabaseContext.getByName("Fibre-data")
If Not DataSource.IsPasswordRequired Then
  Connection = DataSource.GetConnection("","")
  InteractionHandler = createUnoService("")
  Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If
End Sub

Sub ShowPitDialog
	  Dim oCurrentSelection As Variant
	  Dim oPit As Variant
	  Dim sPitName as String
	  Dim iNameLength as Integer
	  Dim iSepPos as Integer
	  Dim iPitID as Integer
	  Dim sPitSQL as String
	  Dim sTypeSQL as String
  Dim oPitStatement as Object
  Dim oTypeStatement as Object
  Dim oResultSet as Object
  Dim oTypeResult as Object
  Dim oTypeList as Object
  Dim sTitle as String
  Dim iCount as Integer
   Globalscope.BasicLibraries.LoadLibrary( "MRILib" )
  If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then 
  End If	  
  bPitSaveFlag = False 'Indicator to save updated data

  oCurrentSelection = FibreDoc.getCurrentSelection()
  oPit = oCurrentSelection.getByIndex(0)
  sPitName = oPit.Name  'The name of the object that is selected
  iNameLength = len(sPitName)
  iSepPos = InStr(sPitName,":")  'Location of the separating ":" in the name
  iPitID = Right(sPitName,(iNameLength-iSepPos)) 'The number to the right of the separating ":"
  sPitSQL = "SELECT ""ID"", ""Lat"", ""Long"", ""Type"", ""Comments"" FROM ""Pits"" WHERE ""ID"" = " & iPitID & "" 
  oPitStatement = Connection.createStatement()
  oResultSet = oPitStatement.executeQuery(sPitSQL)  ' Get the record relating to the selected Pit
  If Not IsNull(oResultSet) Then
        oPitDialog = CreateUnoDialog(DialogLibraries.Lib_pits_pipes.Pit_info)

        oPitDialog.setTitle("Pit " & iPitID & " Information")
        oPitDialog.getControl("txtLat").Text = oResultSet.getString(2)
        oPitDialog.getControl("txtLong").Text = oResultSet.getString(3)  
        oPitDialog.getControl("txtComments").Text = oResultSet.getString(5) 
        oTypeList = oPitDialog.getControl("cbxPitType")
        oTypeList.Text = oResultSet.getString(4)
        sTypeSQL = "SELECT DISTINCT ""Pits"".""Type"" AS ""PitTypes"" FROM ""Pits"" ""Pits"" ORDER BY ""Type"""
        oTypeStatement = Connection.createStatement()
        oTypeResult = oTypeStatement.executeQuery(sTypeSQL)  ' Get the list of pit types
        If Not IsNull(oTypeResult) Then
            iCount = 1
                 oTypeList.addItem(oTypeResult.getString(1),iCount)  ' add each Pit type to the Combo List
                 iCount = iCount + 1

		 End If
        If bPitSaveFlag Then
REM    **************  The next line generates the error  ****************
            oResultSet.UpdateString(2, (oPitDialog.getControl("txtLat").Text) )
        End If

  End If
 End Sub


When using executeQuery you are reading. With 'executeUpdate` you are writing.

So you have at least two choices. In the first, have two connections for statements. See this post → How to scroll through a table in base

That same post has a link to → firebird equivalent for resultset.last which uses a RowSet to do what you want. Tested with this code:

Dim oRS as Object
oRS = createUnoService("")
oRS.DataSourceName = ThisComponent.Location
oRS.CommandType =
oRS.Command = sPitSQL
oRS.updateString(2, "hello")

There is a lot to cover with this situation. The first link above contains many other links which are most helpful in covering this topic in more detail.

Here is more information on RowSet → Service RowSet

and also for CommandType in RowSet → Constants' Group CommandType

NOTE: This was tested with HSQLDB v1.8 embedded database. You have not specified what DB you are using.

Thanks again @Ratslinger for your great advice. I’ve modified my code to use RowSet and now it works as I want. You’ve saved me much frustration of banging my head on the brick wall at the end of the dead-end alley I’d navigated myself into. Also, thanks for the link to Andrew Pitonyak’s BASE programming guide - I’m sure it will be a useful resource. FYI - I’m just using the built-in BASE database system, not connecting to anything external (for the moment).