Serviço SFDatabases.Database

O serviço Database permite aceder a bases de dados incorporadas ou descritas em documentos Base. Este serviço disponibiliza métodos para:

Aceda aos dados nas tabelas da base de dados.
Executar consultas SELECT e aplicar funções de agregação.
Executar instruções SQL como INSERT, UPDATE, DELETE, etc.

Cada instância do serviço Database representa uma única base de dados e permite aceder às suas tabelas, consultas e dados.

Este serviço não permite aceder a formulários ou relatórios no documento Base que contém a base de dados. Para aceder a formulários num documento Base, consulte o método FormDocuments do serviço Base.

Todas as interações entre este serviço e a base de dados são realizadas exclusivamente através de SQL.

As instruções SQL podem ser executadas no modo direto ou indireto. No modo direto, a instrução é enviada para o motor da base de dados sem qualquer verificação ou revisão da sintaxe.

As interfaces disponibilizadas incluem tabelas simples e listas de consultas, bem como acesso aos dados da base de dados.

Para tornar as instruções SQL mais legíveis, pode utilizar parênteses retos «[ ]» para delimitar nomes de tabelas, consultas e campos, em vez de utilizar outros caracteres de delimitação que possam ser exclusivos de determinados Sistemas de Gestão de Bases de Dados Relacionais (RDBMS). No entanto, tenha em atenção que os caracteres de delimitação são obrigatórios neste contexto.

Processamento de transações

Por predefinição, a base de dados processa as transações no modo de confirmação automática, o que significa que é efetuada uma confirmação após cada instrução SQL.

Utilize o método SetTransactionMode para alterar o comportamento predefinido, o que permite efetuar confirmações e reversões manualmente.

Os métodos Commit e Rollback são utilizados para delimitar transações.

No LibreOffice, existem cinco tipos de modos de isolamento de transações, conforme definido no grupo de constantes com.sun.star.sdbc.TransactionIsolation:

Constante	Valor	Interpretação
NONE	0	O tratamento de transações está desativado e a base de dados está configurada no modo de confirmação automática predefinido.
READ_UNCOMMITTED	1	Podem ocorrer leituras incorretas, leituras não repetíveis e leituras fantasmas. Se uma linha for alterada por uma transação, outra transação poderá ler essas alterações, mesmo que estas ainda não tenham sido confirmadas.
READ_COMMITTED	2	Dirty reads are prevented, however non-repeatable reads and phantom reads can occur. This level prevents that rows with uncommitted changes are read.
REPEATABLE_READ	4	Dirty reads and non-repeatable reads are prevented. However, phantom reads can occur. Besides preventing uncommitted data from being read, it also prevents that two read operations in the same transaction return different results.
SERIALIZABLE	8	Dirty reads, non-repeatable reads and phantom reads are prevented. In addition to the constraints of the previous level, it also ensures that the set of records that match a WHERE clause remains unchanged inside the same transaction.

Read the Wikipedia page on Isolation in Database Systems to learn more about transaction integrity.

Service invocation

Before using the Database service the ScriptForge library needs to be loaded or imported:

• As macros básicas requerem o carregamento da biblioteca ScriptForge através da seguinte instrução:
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")

• Os scripts Python requerem a importação do módulo scriptforge:
from scriptforge import CreateScriptService

Sintaxe:

To create a instance of the Database service you can use the CreateScriptService method:

CreateScriptService("SFDatabases.Database", [filename: str], [registrationname], [readonly], [user, [password]]): svc

In the syntax described above you can use either "SFDatabases.Database" or simply "Database" as the first argument of the CreateScriptService method.

Parâmetros:

filename: The name of the Base file. Must be expressed using SF_FileSystem.FileNaming notation.

registrationname: The name of a registered database. If filename is provided, this argument should not be used.

Conversely, if a registrationname is specified, the filename parameter should not be defined.

readonly: Determines if the database will be opened as readonly (Default = True).

user, password: Additional connection parameters to the database server.

Exemplo:

Em Basic


      GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
      Dim myDatabase as Object
      Set myDatabase = CreateScriptService("Database", "/home/user/Documents/myDB.odb")
      ' Run queries, SQL statements, ...
      myDatabase.CloseDatabase()

Em Python


      from scriptforge import CreateScriptService
      myDatabase = CreateScriptService("Database", "/home/user/Documents/myDB.odb")
      # Run queries, SQL statements, ...
      myDatabase.CloseDatabase()

Accessing Databases with the UI Service

It is also possible to access the database associated with a Base document using the ScriptForge.UI service, as shown in the examples below:

Em Basic


      Dim myDoc As Object, myDatabase As Object, ui As Object
      Set ui = CreateScriptService("UI")
      Set myDoc = ui.OpenBaseDocument("/home/user/Documents/myDB.odb")
      ' User and password are supplied below, if needed
      Set myDatabase = myDoc.GetDatabase()
      ' Run queries, SQL statements, ...
      myDatabase.CloseDatabase()
      myDoc.CloseDocument()

Em Python


      ui = CreateScriptService("UI")
      doc = ui.OpenBaseDocument("/home/user/Documents/myDB.odb")
      # User and password are supplied below, if needed
      myDatabase = doc.GetDatabase()
      # Run queries, SQL statements, ...
      myDatabase.CloseDatabase()
      doc.CloseDocument()

The GetDatabase method used in the example above is part of ScriptForge's Base service.

Properties

Name	Readonly	Type	Description
Queries	Yes	Array of strings	The list of stored queries.
Tables	Yes	Array of strings	The list of stored tables.
XConnection	Yes	XConnection	The UNO object representing the current database connection.
XMetaData	Yes	XDatabaseMetaData	The UNO object representing the metadata describing the database system attributes.

List of Methods in the Database Service
CloseDatabase Commit CreateDataset DAvg DCount DMin	DMax DSum DLookup GetRows OpenFormDocument OpenQuery	OpenSql OpenTable Rollback RunSql SetTransactionMode

CloseDatabase

Closes the current database connection.

Sintaxe:

db.CloseDatabase()

Exemplo:


    myDatabase.CloseDatabase() ' Basic


    myDatabase.CloseDatabase() # Python

Commit

Commits all updates done since the previous Commit or Rollback call.

This method is ignored if commits are done automatically after each SQL statement, i.e. the database is set to the default auto-commit mode.

Sintaxe:

db.Commit()

Exemplo:

Em Basic


      ' Set the REPEATABLE_READ transaction level
      myDB.SetTransactionMode(4)
      myDB.RunSql("UPDATE ...")
      myDB.Commit()
      myDB.RunSql("DELETE ...")
      ' Test some condition before committing
      If bSomeCondition Then
          myDB.Commit()
      Else
          myDB.Rollback()
      End If
      ' Restore auto-commit mode
      myDB.SetTransactionMode()

Em Python


      myDB.SetTransactionMode(4)
      myDB.RunSql("UPDATE ...")
      myDB.Commit()
      myDB.RunSql("DELETE ...")
      if some_condition:
          myDB.Commit()
      else:
          myDB.Rollback()
      myDB.SetTransactionMode()

CreateDataset

Creates a Dataset service instance based on a table, query or SQL SELECT statement.

Sintaxe:

db.CreateDataset(sqlcommand: str, opt directsql: bool, opt filter: str, opt orderby: str): svc

Parâmetros:

sqlcommand: A table name, a query name or a valid SQL SELECT statement. Identifiers may be enclosed with square brackets. This argument is case-sensitive.

directsql: Set this argument to True to send the statement directly to the database engine without preprocessing by LibreOffice (Default = False).

filter: Specifies the condition that records must match to be included in the returned dataset. This argument is expressed as a SQL WHERE statement without the "WHERE" keyword.

orderby: Specifies the ordering of the dataset as a SQL ORDER BY statement without the "ORDER BY" keyword.

Exemplo:

The following examples in Basic and Python return a dataset with the records of a table named "Customers".

Em Basic


      oDataset = myDatabase.CreateDataset("Customers", Filter := "[Name] LIKE 'A'")

Em Python


      dataset = myDatabase.CreateDataset("Customers", Filter = "[Name] LIKE 'A'")

DAvg, DCount, DMin, DMax, DSum

Computes the given aggregate function on a field or expression belonging to a table.

Optionally, a SQL WHERE clause can be specified as a filter that will be applied prior to the aggregate function.

Sintaxe:

db.DAvg(expression: str, tablename: str, [criteria: str]): any

db.DCount(expression: str, tablename: str, [criteria: str]): any

db.DMin(expression: str, tablename: str, [criteria: str]): any

db.DMax(expression: str, tablename: str, [criteria: str]): any

db.DSum(expression: str, tablename: str, [criteria: str]): any

Parâmetros:

expression: A SQL expression in which the field names are surrounded with square brackets.

tablename: A table name (without square brackets).

criteria: A WHERE clause without the "WHERE" keyword, in which field names are surrounded with square brackets.

Exemplo:

The example below assumes the file Employees.odb has a table named EmployeeData.

Em Basic


      GlobalScope.BasicLibraries.LoadLibrary("ScriptForge")
      Dim myDB as Variant
      Set myDB = CreateScriptService("Database", "/home/user/Databases/Employees.odb")
      ' Counts the number of employees in the table
      MsgBox myDB.DCount("[ID]", "EmployeeData")
      ' Returns the sum of all salaries in the table
      MsgBox myDB.DSum("[Salary]", "EmployeeData")
      ' Below are some examples of how tables can be filtered
      MsgBox myDB.DCount("[ID]", "EmployeeData", "[Position] = 'Manager'")
      MsgBox myDB.DCount("[ID]", "EmployeeData", "[Position] = 'Sales' AND [City] = 'Chicago'")
      MsgBox myDB.DCount("[ID]", "EmployeeData", "[FirstName] LIKE 'Paul%'")

Em Python


      myDB = CreateScriptService("Database", "/home/user/Databases/Employees.odb")
      bas = CreateScriptService("Basic")
      bas.MsgBox(myDB.DCount("[ID]", "EmployeeData"))
      bas.MsgBox(myDB.DSum("[Salary]", "EmployeeData"))
      bas.MsgBox(myDB.DCount("[ID]", "EmployeeData", "[Position] = 'Manager'"))
      bas.MsgBox(myDB.DCount("[ID]", "EmployeeData", "[Position] = 'Sales' AND [City] = 'Chicago'"))
      bas.MsgBox(myDB.DCount("[ID]", "EmployeeData", "[FirstName] LIKE 'Paul%'"))

DLookup

Computes a SQL expression on a single record returned by a WHERE clause defined by the Criteria parameter.

If the query returns multiple records, only the first one is considered. Use the OrderClause parameter to determine how query results are sorted.

Sintaxe:

db.DLookup(expression: str, tablename: str, [criteria:str], [orderclause: str]): any

Parâmetros:

expression: A SQL expression in which the field names are surrounded with square brackets.

tablename: A table name (without square brackets).

criteria: A WHERE clause without the "WHERE" keyword, in which field names are surrounded with square brackets.

orderclause: An ORDER BY clause without the "ORDER BY" keywords. Field names should be surrounded with square brackets.

Exemplo:

Em Basic


      MsgBox myDB.DLookup("[FirstName]", "EmployeeData", Criteria := "[LastName] LIKE 'Smith'", OrderClause := "[FirstName] DESC")
      MsgBox myDB.DLookup("[Salary]", "EmployeeData", Criteria := "[ID] = '3'")
      MsgBox myDB.DLookup("[Quantity] * [Value]", "Sales", Criteria := "[SaleID] = '5014'")

Em Python


      bas = CreateScriptService("Basic")
      bas.MsgBox(myDB.DLookup("[FirstName]", "EmployeeData", criteria = "[LastName] LIKE 'Smith'", orderclause = "[FirstName] DESC"))
      bas.MsgBox(myDB.DLookup("[Salary]", "EmployeeData", criteria = "[ID] = '3'"))
      bas.MsgBox(myDB.DLookup("[Quantity] * [Value]", "Sales", criteria = "[SaleID] = '5014'"))

GetRows

Stores the contents of a table or the results of a SELECT query or of an SQL statement in a two-dimensional array. The first index in the array corresponds to the rows and the second index refers to the columns.

An upper limit can be specified to the number of returned rows. Optionally column names may be inserted in the first row of the array.

The returned array will be empty if no rows are returned and the column headers are not required.

Sintaxe:

db.GetRows(sqlcommand: str, directsql: bool = False, header: bool = False, maxrows: int = 0): any

Parâmetros:

sqlcommand: A table or query name (without square brackets) or a SELECT SQL statement.

directsql: When True, the SQL command is sent to the database engine without pre-analysis. Default is False. This argument is ignored for tables. For queries, the applied option is the one set when the query was defined.

header: When True, the first row of the returned array contains the column headers.

maxrows: The maximum number of rows to return. The default is zero, meaning there is no limit to the number of returned rows.

Exemplo:

Below are a few examples of how the GetRows method can be used:

Em Basic


      Dim queryResults as Variant
      ' Returns all rows in the table with column headers
      queryResults = myDB.GetRows("EmployeeData", Header := True)
      ' Returns the first 50 employee records ordered by the 'FirstName' field
      queryResults = myDB.GetRows("SELECT * FROM EmployeeData ORDER BY [FirstName]", MaxRows := 50)

Em Python


      queryResults = myDB.GetRows("EmployeeData", header = True)
      queryResults = myDB.GetRows("SELECT * FROM EmployeeData ORDER BY [FirstName]", maxrows = 50)

OpenFormDocument

Opens the specified form document in normal mode. This method returns a FormDocument service instance corresponding to the specified form document.

If the form document is already open, the form document window is activated.

If the specified form document does not exist, then Nothing is returned.

Sintaxe:

svc.OpenFormDocument(formdocument: str): svc

Parâmetros:

formdocument: The name of the FormDocument to be opened, as a case-sensitive string.

Exemplo:

Em Basic

Most form documents are stored in the root of the Base document and they can be opened simply using their names, as in the example below:


    Dim oFormDoc As Object
    oFormDoc = myDB.OpenFormDocument("myFormDocument")

If form documents are organized in folders, it becomes necessary to include the folder name to specify the form document to be opened, as illustrated in the following example:


    oFormDoc = myDB.OpenFormDocument("myFolder/myFormDocument")

Em Python


    formDoc = myDB.OpenFormDocument("myFormDocument")


    formDoc = myDB.OpenFormDocument("myFolder/myFormDocument")

OpenQuery

Opens the Data View window of the specified query and returns an instance of the Datasheet service.

If the query could not be opened, then Nothing is returned.

Sintaxe:

db.OpenQuery(queryname: str): obj

Parâmetros:

queryname: The name of an existing query as a case-sensitive String.

Exemplo:

Em Basic


      myDatabase.OpenQuery("MyQuery")

Em Python


      myDatabase.OpenQuery("MyQuery")

OpenSql

Runs a SQL SELECT command, opens a Data View window with the results and returns an instance of the Datasheet service.

Sintaxe:

db.OpenSql(sql: str, directsql: bool): obj

Parâmetros:

sql: A string containing a valid SQL SELECT statement. Identifiers may be enclosed by square brackets.

directsql: When True, the SQL command is sent to the database engine without pre-analysis (Default = False).

Exemplo:

Em Basic


      myDatabase.OpenSql("SELECT * FROM [Customers] ORDER BY [CITY]")

Em Python


      myDatabase.OpenSql("SELECT * FROM [Customers] ORDER BY [CITY]")

OpenTable

Opens the Data View window of the specified table and returns an instance of the Datasheet service.

Sintaxe:

db.OpenTable(tablename: str): obj

Parâmetros:

tablename: The name of an existing table as a case-sensitive String.

Exemplo:

Em Basic


      myDatabase.OpenTable("MyTable")

Em Python


      myDatabase.OpenTable("MyTable")

Rollback

Cancels all changes made to the database since the last Commit or Rollback call.

Sintaxe:

db.Rollback()

Exemplo:

Em Basic


      myDB.SetTransactionMode(1)
      myDB.RunSql("UPDATE ...")
      ' ...
      If bSomeCondition Then
          myDB.Rollback()
      End If

Em Python


      myDB.SetTransactionMode(1)
      myDB.RunSql("UPDATE ...")
      # ...
      if bSomeCondition:
          myDB.Rollback()

RunSql

Executes an action query of an SQL statement such as creating a table, as well as inserting, updating and deleting records.

The method returns True when successful.

The RunSql method is rejected with an error message in case the database was previously opened in read-only mode.

Sintaxe:

db.RunSql(sqlcommand: str, directsql: bool = False): bool

Parâmetros:

sqlcommand: A query name (without square brackets) or a SQL statement.

directsql: When True, the SQL command is sent to the database engine without pre-analysis. (Default = False). For queries, the applied option is the one set when the query was defined.

Exemplo:

Em Basic


      myDatabase.RunSql("INSERT INTO [EmployeeData] VALUES(25, 'Smith', 'John')", DirectSQL := True)

Em Python


      myDatabase.RunSql("INSERT INTO [EmployeeData] VALUES(25, 'Smith', 'John')", directsql = True)

SetTransactionMode

Defines the level of isolation in database transactions.

By default databases manage transactions in auto-commit mode, which means that a Commit is automatically performed after every SQL statement.

Use this method to manually determine the isolation level of transactions. When a transaction mode other than NONE is set, the script has to explicitly call the Commit method to apply the changes to the database.

This method returns True when successful.

Changing the transaction mode closes all Dataset instances created from the current database.

Sintaxe:

db.SetTransactionMode(transactionmode: int = 0): bool

Parâmetros:

transactionmode: Specifies the transaction mode. This argument must be one of the constants defined in com.sun.star.sdbc.TransactionIsolation (Default = NONE)

Read the section Transaction handling above to learn more about the transaction isolation levels used in LibreOffice.

Exemplo:

Em Basic


      myDB.SetTransactionMode(com.sun.star.sdbc.TransactionIsolation.REPEATABLE_READ)
      oDataset = myDB.CreateDataset("SELECT ...")
      ' ...
      ' Reset the transaction mode to default
      myDB.SetTransactionMode()

Em Python


      from com.sun.star.sdbc import TransactionIsolation
      myDB.SetTransactionMode(TransactionIsolation.REPEATABLE_READ)
      dataset = myDB.CreateDataset("SELECT ...")
      # ...
      myDB.SetTransactionMode()

Todas as rotinas ou identificadores do ScriptForge Basic que tenham o caractere de sublinhado «_» como prefixo estão reservados para uso interno. Não se destinam a ser utilizados em macros do Basic ou em scripts Python.

SFDatabases.Datasheet service

SFDatabases.Dataset service

SFDocuments.Document service

SFDocuments.Form service

ScriptForge.UI service