diff --git a/source/text/sbasic/shared/03/sf_database.xhp b/source/text/sbasic/shared/03/sf_database.xhp new file mode 100644 index 0000000000..6c137ef477 --- /dev/null +++ b/source/text/sbasic/shared/03/sf_database.xhp @@ -0,0 +1,324 @@ + + + + + + + SFDatabases.Database service + /text/sbasic/shared/03/sf_database.xhp + + + +

+ + Database service + +

+ +

SFDatabases.Database service

+ The Database service provides access to databases either embedded or described in Base documents. This service provides methods to: + + + Get access to data in database tables. + + + Run SELECT queries and perform aggregate functions. + + + Run SQL action statements such as INSERT, UPDATE, DELETE, etc. + + +

+ TODO: Add reference to the future help page SFDocuments.SF_Form service in the paragraph below + Each instance of the Database service represents a single database and gives access to its tables, queries and data. This service does not provide access to forms or reports in the Base document. + All exchanges between this service and the database are done using SQL only. + SQL statements may be run in direct or indirect mode. In direct mode the statement is transferred to the database engine without any syntax checking or review. + The provided interfaces include simple tables and queries lists, as well as access to database data. + To make SQL statements more readable, you can use optional square brackets to enclose tables, queries and fields instead of using other enclosing characters that may be exclusive to certain Relational Database Management Systems (RDBMS). + +

Service invocation

+ The code snippet below shows how to access any database with the Database service. + + Dim myDatabase As Object + Set myDatabase = CreateScriptService("SFDatabases.Database", [FileName], [RegistrationName], [ReadOnly], [User, [Password]]) + ' ... Run queries, SQL statements, ... + myDatabase.CloseDatabase() + +

+ FileName: The name of the Base file. Must be expressed using SF_FileSystem.FileNaming notation. + RegistrationName: The name of a registered database. If a file name 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. + +

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 example below: + + Dim myDoc As Object, myDatabase As Object, ui As Object + Set ui = CreateScriptService("UI") + Set myDoc = ui.OpenBaseDocument("myDb.odb") + ' User and password are supplied below, if needed + Set myDatabase = myDoc.GetDatabase() + ' ... Run queries, SQL statements, ... + myDoc.CloseDocument() + + + + Database Service;Queries + Database Service;Tables + +

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
+ DAvg
+ DCount + + + + + DMin
+ DMax
+ DSum + + + + + DLookup
+ GetRows
+ RunSql + + + +

+ +

+ CloseDatabase -------------------------------------------------------------------------------------------------------------------------- + + Database Service;CloseDatabase + +

CloseDatabase

+ Closes the current database connection. +

+ + myDatabase.CloseDatabase() + +

+ +

+ DFunctions -------------------------------------------------------------------------------------------------------------------------- + + Database Service;DAvg + + + Database Service;DCount + + + Database Service;DMax + + + Database Service;DMin + + + Database Service;DSum + +

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. +

+ + myDatabase.DAvg(Expression As String, TableName As String, [Criteria As String]) As Variant + +

+ The example below assumes the file Employees.odb has a table named EmployeeData. + + GlobalScope.BasicLibraries.LoadLibrary("ScriptForge") + Dim myDB as Variant + Set myDB = CreateScriptService("Database", "~/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%'") + +

+ +

+ DLookup -------------------------------------------------------------------------------------------------------------------------- + + Database Service;DLookup + +

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. +

+ + myDatabase.DLookup(Expression As String, TableName As String, [Criteria As String], [OrderClause As String]) As Variant + +

+ 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. +

+ + 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'") + +

+ +

+ GetRows -------------------------------------------------------------------------------------------------------------------------- + + Database Service;GetRows + +

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. +

+ + myDatabase.GetRows(SQLCommand As String, [DirectSQL As Boolean], [Header As Boolean], [MaxRows As Long]) As Variant + +

+ 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. +

+ Below are a few examples of how the GetRows can be used: + + 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) + +

+ +

+ RunSql -------------------------------------------------------------------------------------------------------------------------- + + Database Service;RunSql + +

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. +

+ + myDatabase.RunSql(SQLCommand As String, [DirectSQL As Boolean]) As Boolean + +

+ 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. +

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

+ +

+ + -- cgit