diff options
Diffstat (limited to 'wizards')
| -rw-r--r-- | wizards/source/scriptforge/python/scriptforge.py | 9 | ||||
| -rw-r--r-- | wizards/source/sfdatabases/SF_Database.xba | 332 | ||||
| -rw-r--r-- | wizards/source/sfdatabases/SF_Dataset.xba | 11 | ||||
| -rw-r--r-- | wizards/source/sfdatabases/SF_Register.xba | 2 |
4 files changed, 337 insertions, 17 deletions
diff --git a/wizards/source/scriptforge/python/scriptforge.py b/wizards/source/scriptforge/python/scriptforge.py index 77e0119809da..998d57d066a8 100644 --- a/wizards/source/scriptforge/python/scriptforge.py +++ b/wizards/source/scriptforge/python/scriptforge.py @@ -1794,6 +1794,9 @@ class SFDatabases: def CloseDatabase(self): return self.ExecMethod(self.vbMethod, 'CloseDatabase') + def Commit(self): + return self.ExecMethod(self.vbMethod, 'Commit') + def CreateDataset(self, sqlcommand, directsql = False, filter = '', orderby = ''): return self.ExecMethod(self.vbMethod, 'CreateDataset', sqlcommand, directsql, filter, orderby) @@ -1831,9 +1834,15 @@ class SFDatabases: def OpenTable(self, tablename): return self.ExecMethod(self.vbMethod, 'OpenTable', tablename) + def Rollback(self): + return self.ExecMethod(self.vbMethod, 'Rollback') + def RunSql(self, sqlcommand, directsql = False): return self.ExecMethod(self.vbMethod, 'RunSql', sqlcommand, directsql) + def SetTransactionMode(self, transactionmode = 0): + return self.ExecMethod(self.vbMethod, 'SetTransactionMode', transactionmode) + # ######################################################################### # SF_Dataset CLASS # ######################################################################### diff --git a/wizards/source/sfdatabases/SF_Database.xba b/wizards/source/sfdatabases/SF_Database.xba index cf970ea980fc..141a5bade393 100644 --- a/wizards/source/sfdatabases/SF_Database.xba +++ b/wizards/source/sfdatabases/SF_Database.xba @@ -25,6 +25,12 @@ Option Explicit ''' ''' The provided interfaces include simple tables, queries and fields lists, and access to database metadata. ''' +''' Tranaction handling +''' Changes to data remain reversible until the moment the running script instructs the database to commit them. +''' The implicit (default) behaviour is that commit takes place after the execution of every single SQL statement. +''' The choice can be made (SetTranactionMode()) to take commitments manually. +''' The Commit() and Rollback() statements delimit transactions. +''' ''' Service invocation and usage: ''' 1) To access any database at anytime ''' Dim myDatabase As Object @@ -67,6 +73,9 @@ Private _URL As String ' Text on status bar Private _Location As String ' File name Private _ReadOnly As Boolean Private _MetaData As Object ' com.sun.star.sdbc.XDatabaseMetaData +Private _User As String ' Connection parameters to enable a reconnection +Private _Password As String +Private _Datasets As Variant ' Array of open datasets REM ============================================================ MODULE CONSTANTS @@ -86,6 +95,9 @@ Private Sub Class_Initialize() _Location = "" _ReadOnly = True Set _MetaData = Nothing + _User = "" + _Password = "" + _Datasets = Array() End Sub ' SFDatabases.SF_Database Constructor REM ----------------------------------------------------------------------------- @@ -140,19 +152,56 @@ Check: ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Try: + _CloseConnection() + Dispose() + +Finally: + ScriptForge.SF_Utils._ExitFunction(cstThisSub) + Exit Sub +End Sub ' SFDatabases.SF_Database.CloseDatabase + +REM ----------------------------------------------------------------------------- +Public Sub Commit() +''' Commit all updates done since the previous Commit or Rollback +''' The statement is ignored if the commits are done automatically after each SQL statement. +''' Args: +''' Returns: +''' Exceptions: +''' DBREADONLYERROR The method is not applicable on a read-only database +''' Example: +''' db.SetTransactionMode(4) ' Highest transaction level +''' db.RunSql("UPDATE ...") +''' db.Commit() +''' db.RunSql(DELETE ...") +''' If ...something happened... Then db.Rollback() Else db.Commit() +''' db.SetTransactionMode() ' Back to the automatic mode + +Const cstThisSub = "SFDatabases.Database.Commit" +Const cstSubArgs = "" + + On Local Error GoTo Finally + +Check: + ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) + If _ReadOnly Then GoTo Catch_ReadOnly + +Try: With _Connection - If Not IsNull(_Connection) Then - If ScriptForge.SF_Session.HasUnoMethod(_Connection, "flush") Then .flush() - .close() - .dispose() + If Not .AutoCommit Then + .commit() + ' To make updates potentially visible in the user interface ... + _FlushConnection() End If - Dispose() End With Finally: + On Local Error GoTo 0 ScriptForge.SF_Utils._ExitFunction(cstThisSub) Exit Sub -End Sub +Catch_ReadOnly: + ScriptForge.SF_Exception.RaiseFatal(DBREADONLYERROR) + GoTo Finally +End Sub ' SFDatabases.SF_Database.Commit REM ----------------------------------------------------------------------------- Public Function CreateDataset(Optional ByVal SQLCommand As Variant _ @@ -497,6 +546,7 @@ Public Function Methods() As Variant Methods = Array( _ "CloseDatabase" _ + , "Commit" _ , "CreateDataset" _ , "DAvg" _ , "DCount" _ @@ -509,7 +559,9 @@ Public Function Methods() As Variant , "OpenQuery" _ , "OpenSql" _ , "OpenTable" _ + , "Rollback" _ , "RunSql" _ + , "SetTransactionMode" _ ) End Function ' SFDatabases.SF_Database.Methods @@ -602,7 +654,7 @@ Finally: Exit Function Catch: GoTo Finally -End Function ' SFDocuments.SF_Base.OpenQuery +End Function 'SFDatabases.SF_Database.OpenQuery REM ----------------------------------------------------------------------------- Public Function OpenSql(Optional ByRef Sql As Variant _ @@ -642,7 +694,7 @@ Finally: Exit Function Catch: GoTo Finally -End Function ' SFDocuments.SF_Base.OpenSql +End Function ' SFDatabases.SF_Database.OpenSql REM ----------------------------------------------------------------------------- Public Function OpenTable(Optional ByVal TableName As Variant) As Object @@ -678,7 +730,7 @@ Finally: Exit Function Catch: GoTo Finally -End Function ' SFDocuments.SF_Base.OpenTable +End Function ' SFDatabases.SF_Database.OpenTable REM ----------------------------------------------------------------------------- Public Function Properties() As Variant @@ -694,6 +746,45 @@ Public Function Properties() As Variant End Function ' SFDatabases.SF_Database.Properties REM ----------------------------------------------------------------------------- +Public Sub Rollback() +''' Cancel all updates done since the previous Commit or Rollback +''' The statement is ignored if the commits are done automatically after each SQL statement. +''' Args: +''' Returns: +''' Exceptions: +''' DBREADONLYERROR The method is not applicable on a read-only database +''' Example: +''' db.SetTransactionMode(4) ' Highest transaction level +''' db.RunSql("UPDATE ...") +''' db.Commit() +''' db.RunSql(DELETE ...") +''' If ...something happened... Then db.Rollback() Else db.Commit() +''' db.SetTransactionMode() ' Back to the automatic mode + +Const cstThisSub = "SFDatabases.Database.Rollback" +Const cstSubArgs = "" + + On Local Error GoTo Finally + +Check: + ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) + If _ReadOnly Then GoTo Catch_ReadOnly + +Try: + With _Connection + If Not .AutoCommit Then .rollback() + End With + +Finally: + On Local Error GoTo 0 + ScriptForge.SF_Utils._ExitFunction(cstThisSub) + Exit Sub +Catch_ReadOnly: + ScriptForge.SF_Exception.RaiseFatal(DBREADONLYERROR) + GoTo Finally +End Sub ' SFDatabases.SF_Database.Rollback + +REM ----------------------------------------------------------------------------- Public Function RunSql(Optional ByVal SQLCommand As Variant _ , Optional ByVal DirectSQL As Variant _ ) As Boolean @@ -788,9 +879,188 @@ Catch: GoTo Finally End Function ' SFDatabases.SF_Database.SetProperty +REM ----------------------------------------------------------------------------- +Public Function SetTransactionMode(Optional ByVal TransactionMode As Variant) As Boolean +''' Configure the handling of transactions. +''' Usually all transactions are in auto-commit mode, that means, a commit takes place +''' after each single SQL command. Therefore to control a transaction manually, implies to switch auto-commit off. +''' The first SQL command starts a transaction that is active until the corresponding +''' methods have been committed or rolled back. +''' +''' The transaction mode remains valid until the next call of the method with a different value, +''' or until the closure of the actual Database instance, +''' or until a call to SetTransactionMode() without argument, which cancels the manual transaction mode. +''' +''' The method may close and replace the actual connection. This means that all open datasets +''' are first closed. Open datasheets might see their content changed or vanish. +''' The easiest is to set the transaction mode immediately after the creation of the Database instance. +''' +''' Args: +''' TransactionMode: one of the com.sun.star.sdbc.TransactionIsolation constants: +''' (0) NONE Indicates that transactions are not supported. Default: cancel the transaction mode. +''' (1) READ_UNCOMMITTED Dirty reads, non-repeatable reads and phantom reads can occur. +''' This level allows a row changed by one transaction to be read by another transaction +''' before any changes in that row have been committed (a "dirty read"). +''' If any of the changes are rolled back, the second transaction will have retrieved an invalid row. +''' (2) READ_COMMITTED Dirty reads are prevented; non-repeatable reads and phantom reads can occur. +''' This level only prohibits a transaction from reading a row with uncommitted changes in it. +''' (4) REPEATABLE_READ Dirty reads and non-repeatable reads are prevented; phantom reads can occur. +''' This level prohibits a transaction from reading a row with uncommitted changes in it, +''' and it also prohibits the situation where one transaction reads a row, +''' a second transaction alters the row, and the first transaction rereads the row, +''' getting different values the second time (a "non-repeatable read"). +''' (8) SERIALIZABLE Dirty reads, non-repeatable reads and phantom reads are prevented. +''' This level includes the prohibitions in REPEATABLE_READ and further prohibits +''' the situation where one transaction reads all rows that satisfy a WHERE condition, +''' a second transaction inserts a row that satisfies that WHERE condition, +''' and the first transaction rereads for the same condition, retrieving +''' the additional "phantom" row in the second read. +''' Returns: +''' True when successful. +''' Exceptions: +''' DBREADONLYERROR The method is not applicable on a read-only database +''' Example: +''' oDb.SetTransactionMode(com.sun.star.sdbc.TransactionIsolation.SERIALIZABLE) ' 8 + +Dim bSet As Boolean ' Return value +Dim bCommit As Boolean ' To compare with AutoCommit +Const cstThisSub = "SFDatabases.Database.SetTransactionMode" +Const cstSubArgs = "TransactionMode=0|1|2|4|8" + + If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo Catch + bSet = False + +Check: + If IsMissing(TransactionMode) Or IsEmpty(TransactionMode) Then TransactionMode = 0 + If ScriptForge.SF_Utils._EnterFunction(cstThisSub, cstSubArgs) Then + If Not ScriptForge.SF_Utils._Validate(TransactionMode, "TransactionMode", ScriptForge.V_NUMERIC, Array(0, 1, 2, 4, 8)) Then GoTo Finally + End If + If _ReadOnly Then GoTo Catch_ReadOnly + +Try: + With _Connection + bCommit = ( TransactionMode > com.sun.star.sdbc.TransactionIsolation.NONE ) + ' Replace the existing connection + If Not IsNull(_Connection) Then + If .AutoCommit And bCommit Then + _CloseConnection() + Set _Connection = _DataSource.getIsolatedConnection(_User, _Password) + ElseIf Not .AutoCommit And Not bCommit Then + _CloseConnection() + Set _Connection = _DataSource.getConnection(_User, _Password) + End If + End If + + ' Set the transaction mode + If bCommit Then + .SetTransactionIsolation(CLng(TransactionMode)) + .setAutoCommit(Not bCommit) + End If + End With + + bSet = True + +Finally: + SetTransactionMode = bSet + ScriptForge.SF_Utils._ExitFunction(cstThisSub) + Exit Function +Catch: + GoTo Finally +Catch_ReadOnly: + ScriptForge.SF_Exception.RaiseFatal(DBREADONLYERROR) + GoTo Finally +End Function ' SFDatabases.SF_Database.SetTransactionMode + REM =========================================================== PRIVATE FUNCTIONS REM ----------------------------------------------------------------------------- +Public Function _AddToDatasets(ByRef poDataset As Object) As Long +''' Insert a newly created Dataset instance in the open datasets array +''' and return the index of the used entry. +''' Empty space is reused. +''' Args: +''' poDataset: the dataset instance to insert + +Dim lIndex As Long ' Return value +Dim lSize As Long ' UBound of the _datasets array +Dim i As Long + +Check: + lIndex = -1 + If IsNull(poDataset) Then Exit Function + On Local Error GoTo Finally + lSize = UBound(_Datasets) + +Try: + ' Can an empty entry be reused ? + For i = 0 To lSize + If IsNull(_Datasets(i)) Then + lIndex = i + Exit For + End If + Next i + + ' Resize _Datasets if no empty space + If lIndex < 0 Then + lSize = lSize + 1 + If lSize > 0 Then + ReDim Preserve _Datasets(0 To lSize) + Else + ReDim _Datasets (0 To 0) + End If + lIndex = lSize + End If + + ' Insert new object + Set _Datasets(lIndex) = poDataset + +Finally: + _AddToDatasets = lIndex + Exit Function +End Function ' SFDatabases.SF_Database._AddToDatasets + +REM ----------------------------------------------------------------------------- +Private Sub _CloseConnection() +''' Close the actual connection +''' To enable transaction modes, it is necessary to reinitialize the connection to the datasource. +''' The reinit includes +''' - the closure of all open datasets +''' - flushing the buffered and committed updates +''' - the effective closure +''' Otherwise the experience showed undesired side-effects. + +Dim oParent As Object ' Parent of actual connection +Dim oDataset As Object ' Single dataset in the _Datasets array +Dim oSession As Object : Set oSession = ScriptForge.SF_Session +Dim i As Long + + On Local Error GoTo Finally ' Never abort + +Check: + If IsNull(_Connection) Then Exit Sub + +Try: + ' Close datasets + For i = 0 To UBound(_Datasets) + Set oDataset = _Datasets(i) + If Not IsNull(oDataset) Then oDataset.CloseDataset() + Set _Datasets(i) = Nothing + Next i + _Datasets = Array() + + ' Flush buffers + _FlushConnection() + + ' Close the connection + _Connection.close() + _Connection.dispose() + +Finally: + On Local Error GoTo 0 + Exit Sub +End Sub ' SFDatabases.SF_Database._CloseConnection + +REM ----------------------------------------------------------------------------- Private Function _CollectFormDocuments(ByRef poContainer As Object) As String ''' Returns a token-separated string of all hierarchical formdocument names ''' depending on the formdocuments container in argument @@ -826,7 +1096,7 @@ Finally: _CollectFormDocuments = "" End If Exit Function -End Function ' SFDocuments.SF_Base._CollectFormDocuments +End Function ' SFDatabases.SF_Database._CollectFormDocuments REM ----------------------------------------------------------------------------------------------------------------------- Private Function _DFunction(ByVal psFunction As String _ @@ -968,6 +1238,34 @@ Catch: End Function ' SFDatabases.SF_Database._ExecuteSql REM ----------------------------------------------------------------------------- +Private Sub _FlushConnection() +''' Empties the buffers of the actual connection +''' Sub called after each commit and at connection closure.. + +Dim oParent As Object ' Parent of actual connection +Dim oSession As Object : Set oSession = ScriptForge.SF_Session + + On Local Error GoTo Finally ' Never abort + +Check: + If IsNull(_Connection) Then Exit Sub + +Try: + ' Flush buffers + With oSession + If .HasUnoMethod(_Connection, "getParent") Then + Set oParent = _Connection.getParent() + If .HasUnoMethod(oParent, "flush") Then oParent.flush() + ElseIf .HasUnoMethod(_Connection, "flush") Then + _Connection.flush() + End If + End With + +Finally: + Exit Sub +End Sub ' SFDatabases.SF_Database._FlushConnection + +REM ----------------------------------------------------------------------------- Private Function _GetColumnValue(ByRef poResultSet As Object _ , ByVal plColIndex As Long _ ) As Variant @@ -1003,13 +1301,17 @@ Const cstMaxBinlength = 2 * 65535 Case .ARRAY : vValue = poResultSet.getArray(plColIndex) Case .BINARY, .VARBINARY, .LONGVARBINARY, .BLOB Set oStream = poResultSet.getBinaryStream(plColIndex) - If bNullable Then - If Not poResultSet.wasNull() Then lSize = CLng(oStream.getLength()) Else lSize = 0 + If IsNull(oStream) Then + lSize = 0 Else - lSize = CLng(oStream.getLength()) + If bNullable Then + If Not poResultSet.wasNull() Then lSize = CLng(oStream.getLength()) Else lSize = 0 + Else + lSize = CLng(oStream.getLength()) + End If + oStream.closeInput() End If vValue = lSize ' Return length of field, not content - If Not IsNull(oStream) Then oStream.closeInput() Case .BIT, .BOOLEAN : vValue = poResultSet.getBoolean(plColIndex) Case .DATE vDateTime = poResultSet.getDate(plColIndex) @@ -1170,4 +1472,4 @@ Private Function _Repr() As String End Function ' SFDatabases.SF_Database._Repr REM ============================================ END OF SFDATABASES.SF_DATABASE -</script:module> +</script:module>
\ No newline at end of file diff --git a/wizards/source/sfdatabases/SF_Dataset.xba b/wizards/source/sfdatabases/SF_Dataset.xba index 28091d9368b8..e9eb050d93f0 100644 --- a/wizards/source/sfdatabases/SF_Dataset.xba +++ b/wizards/source/sfdatabases/SF_Dataset.xba @@ -106,6 +106,8 @@ Private _UpdatableFields As Variant ' Array of updatable field names Private _DefaultValues As Variant ' Array of field default values // _Fields Private _AutoValue As Long ' Index of AutoValue field. None = -1 +Private _DatasetIndex As Long ' Index of the dataset in the _Datasets array of the parent database + REM ============================================================ MODULE CONSTANTS REM ====================================================== CONSTRUCTOR/DESTRUCTOR @@ -127,6 +129,7 @@ Private Sub Class_Initialize() _UpdatableFields = Array() _DefaultValues = Array() _AutoValue = -1 + _DatasetIndex = -1 End Sub ' SFDatabases.SF_Dataset Constructor REM ----------------------------------------------------------------------------- @@ -280,6 +283,7 @@ Try: .close() .dispose() End With + If _DatasetIndex >= 0 Then Set _ParentDatabase._Datasets(_DatasetIndex) = Nothing Dispose() bClose = True End If @@ -1350,8 +1354,11 @@ Try: If Len(sUpdatableFields) <= 1 Then _UpdatableFields = Array() Else _UpdatableFields = Split(Mid(sUpdatableFields, 2), ",") End With End With + + ' Insert the instance in the _Datasets array of the parent database + _DatasetIndex = _ParentDatabase._AddToDatasets([Me]) - bDataset = True + bDataset = ( _DatasetIndex >= 0 ) Finally: _Initialize = bDataset @@ -1661,4 +1668,4 @@ CatchError: End Function ' SFDatabases.SF_Dataset._SetColumnValue REM ============================================ END OF SFDATABASES.SF_DATASET -</script:module> +</script:module>
\ No newline at end of file diff --git a/wizards/source/sfdatabases/SF_Register.xba b/wizards/source/sfdatabases/SF_Register.xba index cee09f94f3f3..e1b752f7f107 100644 --- a/wizards/source/sfdatabases/SF_Register.xba +++ b/wizards/source/sfdatabases/SF_Register.xba @@ -121,6 +121,8 @@ Try: If ScriptForge.SF_Utils._ErrorHandling() Then On Local Error GoTo CatchConnect Set ._Connection = ._DataSource.getConnection(vUser, vPassword) If IsNull(._Connection) Then GoTo CatchConnect + ._User = vUser + ._Password = vPassword ._ReadOnly = vReadOnly Set ._MetaData = ._Connection.MetaData ._URL = ._MetaData.URL |