There is a good calc demo at https://github.com/rafaelhlima/LibOCon_2021_SFCalc but I’m not aware of anything for Base. I rolled the help examples into the following module as a start to launching demos off a form.
Edit: Updated to include @Ratslinger fixes from database with control from form buttons in ScriptForge Database Demo - #4 by Ratslinger then reformatted with Black. Creating/opening database and closing form/database still to be resolved. Any testing, feedback and suggestions are welcome.
SF_Base_Demo.py
"""
Module to demonstrate SourceForge Methods in the Database Service
CloseDatabase close_database
DAvg refer DCount
DCount see table_stats
DMin refer DCount
DMax refer DCount
DSum see table_stats
DLookup see get_first_match
GetRows see get_employee_records
RunSql see create_table_with_values
"""
# ScriptForge_Database_service_Demo
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_database.html?DbPAR=BASIC
from scriptforge import CreateScriptService
svcUI = CreateScriptService("UI")
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_basic.html?&DbPAR=BASIC
# Create a basic service and use MsgBox
def service_from_database(args=None):
# Creates the service
bas = CreateScriptService("Basic")
bas.MsgBox("Hello!")
# Database Service method - Service invocation
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_base.html?&DbPAR=BASIC #Service invocation
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_database.html?DbPAR=BASIC#hd_id91587913266988
# Create a database and close it
def create_database(args=None):
myDatabase = CreateScriptService("Database", "E:\demo\myDB.odb")
bas = CreateScriptService("Basic")
bas.MsgBox("Hello!")
# myDatabase.CloseDatabase()
doc.CloseDocument(True)
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_database.html?DbPAR=BASIC#hd_id771615147491563
# Open a database - from Database service Accessing Databases with the UI Service
def open_database(args=None):
ui = CreateScriptService("UI")
doc = ui.OpenBaseDocument("E:\demo\myDB.odb")
# User and password are supplied below, if needed
myDatabase = doc.GetDatabase()
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_database.html?&DbPAR=BASIC&System=WIN#CloseDatabase
# Close a database *** CloseDatabase Not working
def close_database(args=None):
bas = CreateScriptService("Basic")
# Creates DB service using current document
doc = CreateScriptService("SFDocuments.Document", bas.ThisDatabaseDocument)
# myDatabase.CloseDatabase()
doc.CloseDocument(True)
# Database Service method - RunSql
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_database.html?&DbPAR=BASIC#RunSql
# SQL Demo - Create Table with test data
def create_table_with_values(args=None):
# Prep SQL - Drop; Create; Add data
mysql = """
Drop Table "EmployeeData" IF EXISTS;
CREATE TABLE "EmployeeData" (
"ID" INT IDENTITY,
"FirstName" VARCHAR(50),
"LastName" VARCHAR(50),
"Position" VARCHAR(50),
"Salary" DECIMAL(10, 2),
"City" VARCHAR (50)
);
-- If no Field_name is specified, all fields must be completed in right order
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Kate', 'Rosmith', 'Nurse', 75000, 'Lexingtown');
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Paul', 'Mejia', 'Family Therapist', 50000, 'Hogworth');
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Kaitlin', 'Palmer', 'Manager', 83250, 'Chicago');
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Paul', 'Alexander', 'Mechanic', 74600, 'Queensby City');
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Conrad', 'Palmer', 'Manager', 115000, 'North Dodgestead');
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Jordin', 'Wise', 'Sales', 83954, 'Springstead');
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Dylan', 'Thornton', 'Cashier', 28620, 'Kettlewich');
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Paul', 'Smith', 'Analyst', 63525, 'Goldview');
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Janelle', 'Kaye', 'Sales', 93640, 'Chicago');
INSERT INTO "EmployeeData"
VALUES
(NULL, 'Ryker', 'Khan', 'Anthropologist', 79425, 'Richcaster');
"""
# Creates DB service using registered DB name
myDatabase = CreateScriptService("Database", "", "Employees", False)
# Runs the SQL
myDatabase.RunSql(mysql, directsql=True)
# Display message
bas = CreateScriptService("Basic")
bas.MsgBox("Table Created")
# Database Service method - DCount; DSum
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_database.html?&DbPAR=BASIC#DFunctions
# Calculate employee stats
def table_stats(args=None):
# Create DB service - File needs path
# myDB = CreateScriptService("Database", "E:\demo\EmployeeData.odb")
# Creates DB service using registered DB name
myDB = CreateScriptService("Database", "", "Employees")
# Create service for MsgBox
svc = CreateScriptService("Basic")
# For alternative string display
item_a = "Employee Count"
# Method DCount
item_b = str(myDB.DCount("[ID]", "EmployeeData"))
svc.MsgBox("Employee Count" + "\n\n" + item_b.center(len(item_a), "_"))
# Method DSum
svc.MsgBox("Total Salary - " + str(myDB.DSum("[Salary]", "EmployeeData")))
# Method DCount - Selective
svc.MsgBox(
"Manager Count - "
+ str(myDB.DCount("[ID]", "EmployeeData", "[Position] = 'Manager'"))
)
# Method DCount - Selective
svc.MsgBox(
"Chicago Sales Count - "
+ str(
myDB.DCount(
"[ID]", "EmployeeData", "[Position] = 'Sales' AND [City] = 'Chicago'"
)
)
)
# Method DCount - Selective
svc.MsgBox(
"Emp First name Paul Count - "
+ str(myDB.DCount("[ID]", "EmployeeData", "[FirstName] LIKE 'Paul%'"))
)
# Database Service method - DLookup
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_database.html?&DbPAR=BASIC#DLookup
# Get first match of criteria
def get_first_match(args=None):
# Creates DB service using registered DB name
myDB = CreateScriptService("Database", "", "Employees")
# Create service for MsgBox
svc = CreateScriptService("Basic")
# Method DLookup
svc.MsgBox(
"Select First Occurance FirstName with"
+ "\n"
+ "Descending Seq of FirstName"
+ "\n"
+ "and LastName like Smith"
+ "\n\n"
+ (
myDB.DLookup(
"[FirstName]",
"EmployeeData",
criteria="[LastName] LIKE 'Smith'",
# orderclause="[FirstName] DESC",
)
)
)
# Method DLookup
svc.MsgBox(
"Salary of Record ID #3 - "
+ str(myDB.DLookup("[Salary]", "EmployeeData", criteria="[ID] = '3'"))
)
# Method DLookup
svc.MsgBox(
"Fuel Cost * Quantity for Record ID # 5 - "
+ str(
myDB.DLookup(
"[FuelQuantity] * [FuelCost]", "Fuel", criteria="[FuelID] = '5'"
)
)
)
# Database Service method - GetRows
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_database.html?&DbPAR=BASIC#GetRows
# Get employee data records
def get_employee_records(args=None):
# Creates DB service using registered DB name
myDB = CreateScriptService("Database", "", "Employees")
# Create service for MsgBox
svc = CreateScriptService("Basic")
# queryResults is a returned Array
# header is column titles
queryResults = myDB.GetRows("EmployeeData", header=True)
svc.MsgBox(
"Retrieve table data; Show:"
+ "\n"
+ "FirstName of first record"
+ "\n\n"
+ (queryResults[1][1])
)
# queryResults is a returned Array
queryResults = myDB.GetRows(
"SELECT * FROM EmployeeData ORDER BY [FirstName]", maxrows=50
)
svc.MsgBox(
"Retrieve table data; Show:"
+ "\n"
+ "FirstName of first record"
+ "\n"
+ "Select statement ordered by FirstName"
+ "\n\n"
+ (queryResults[0][1])
)
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_form.html?&DbPAR=BASIC
# and
# https://help.libreoffice.org/7.3/en-US/text/sbasic/shared/03/sf_formcontrol.html?&DbPAR=BASIC
# get_form and sub form - display data from each
def get_form(args=None):
bas = CreateScriptService("Basic")
# Creates DB service using current document
doc = CreateScriptService("SFDocuments.Document", bas.ThisDatabaseDocument)
# The statement below is necessary only if the form hasn't been opened yet
form_doc = doc.OpenFormDocument("Fuel")
# Forms - In document `Fuel` access `Mainform`
form = doc.Forms("Fuel", "MainForm")
# get control on `MainForm`
form_control = form.Controls("fmtOdometer")
# Display value of field
bas.MsgBox("Odometer - " + str(form_control.Value))
# Get sub form using main form
subform = form.Subforms("SubForm")
# Get table grid control
subform_control = subform.Controls("SubForm_Grid")
# Get column in grid control
grid_control = subform_control.Controls("FuelCost")
# Display value of field in selected record
bas.MsgBox("First FuelCost in Grid - " + str(grid_control.Value))
# Close form
form.CloseFormDocument()
g_exportedScripts = (
service_from_database,
create_database,
open_database,
close_database,
create_table_with_values,
table_stats,
get_first_match,
get_employee_records,
get_form,
)