ScriptForge Database Demo

English
,
#1

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,
)
1 Like
Using ScriptForge Form Service
Macro Examples in LibreOffice Help Don't Work
#2

Hello,

While I personally believe it is preferrable to learn and use the API, and since Script Forge is in the release and it appears you are determined this is easier, I offer some observations.

I have mentioned in other postings that the documentation and samples are poor and have errors. Unfortunately that is what you example contains also. Here are my observations.

def create_database(args=None):

This does not create any database. It accesses an existing Base file to create a service to use MsgBox.

def open_database(args=None):

Works (must edit Base location - should be a comment on this).

Comment - # User and password are supplied below, if needed. There is nothing below supporting this.

Seems a bit off target since you state:
...as a start to launching demos off a form.

The database is already open at this point.

def close_database(args=None):

This errors - myDatabase is not defined.

def create_table_with_values(args=None):

This errors - myDatabase is not defined. It is worth noting, when providing the necessary access, the opening of a database has a default of readonly = True.

Also the SQL should provide for dropping the table if it exists before you try to create it.

def database_stats(args=None):

Proper location of the Base file is needed or a registered name in another parameter.

The table name used is not consistent with the table name used earlier.

def get_first_match(args=None):

myDB is not defined.

The table name used is not consistent with the table name used earlier.

def get_employee_records(args=None):

myDB is not defined.

The table name used is not consistent with the table name used earlier.

def get_form(args=None):

Getting a form by index is not a good direction as newly crated forms can replace this. However, this code (tried many variations) appears to be buggy. Still has error: 'bool' object has no attribute 'Forms'

def activate_form(args=None):

Do not understand what this is here for as it appears to be for Calc.

def close_form(args=None):

Do not understand what this is here for as it appears to be for Calc. Also seems this will error as I see no definition for form.

There seems to be little more here than what is posted in the Help files - no explanations. This is what I would imagine you are attempting to remedy. Also testing seems missing as much of this did not work for me without additional code.

Finally, future items such as this may be better posted as a question with you then presenting this as an answer.

#3

@flywire

In going one step further with the get_form code, the bug seems to be in trying to access SubForms. Can get a field & access the data:

# get_form
def get_form(args=None):
# This routine uses tables and forms from your "Automobile.odb" Base file
# noted in linked post
    db = CreateScriptService('SFDocuments.Document', XSCRIPTCONTEXT.getDocument())
    #  The next statement is necessary only if the form hasn't been opened yet
    form_doc = db.OpenFormDocument('Fuel')
    form = db.Forms('Fuel', 'MainForm')
    form_control = form.Controls('fmtOdometer')
    bas = CreateScriptService("Basic")
    bas.MsgBox(form_control.Value)

Edit:

Appears can get to sub form with:

    subform = form.Subforms('SubForm')
    subform_control = subform.Controls('SubForm_Grid')

but still have not gotten to data in table grid.

Edit 2:

Can now access item in grid:

    grid_control = subform_control.Controls('FuelCost')
    bas.MsgBox(grid_control.Value)

Seems, as I stated (ScriptForge resources) documentation is poor at best!

1 Like
Using ScriptForge Form Service
#4

It has been a couple of days since posting my observations and see no changes to the original code presented. Do not see benefit to posted code which isn’t working. It is mainly a copy of the code in the Help file.

I will post here a sample of what I did (based somewhat upon the original post). Have eliminated a couple of items, embedded the Python code in the document, added some comments and a bit more reference, some alternate ways to do the same thing, and created a form (TryCode) with buttons to see the item(s) at work.

If the table EmployeeData is deleted and re-created using this form you must refresh the tables or close then open again the Base file in order to use most of the other routines.
Employees.odb (25.7 KB)

This needs more work such as error routines but it does provide working code.

Edit:

Sorry - BIG OOOPS

Since some of the routines use a different method to access the service, register the Base file as Employees

1 Like
#5

Your comments are really appreciated because, paraphrasing your comments, the help is a struggle. My time has been limited over the last few days.

#6

@Ratslinger That’s a nice job. It works well as MsgBox displays with the first MsgBox being a demo of Basic Services and probably enough detail.
.
The form could do with a few instructions and deactivating invalid buttons but I think error routines are outside the scope of a minimal demo. I see the module as a user module rather than embedded in the document mainly so it is visible without loading the database. Do you have any concerns if I load it to GitHub? I’ll contact the ScriptForge devs to see if it can be included as a tutorial.
.
It would be nice to run it without needing to register the Base file. I’m still struggling with creating/opening/closing services. Consistency across the ScriptForge help examples would be useful too.

#9

I was not looking to create a full blown demo. I just thought it important to those looking at this post to find something that works. I already stated in my original comment that I personally believe it is preferable to learn and use the API. You could take it and modify it how you want but it needs some work - just do not post something that is just slapped together as in you original post. I do NOT want this as it is to be a tutorial - for that it needs considerable work including error handling! Currently it is only some sample code executed by buttons on a form.

That is for the user to decide. It is easy enough to extract and do so. For posting here it is best to embed in the document to avoid multiple items and more instructions.

Again, did this to offer multiple options within the code. You can change as there are other methods as shown in the code.

#10

Wondering just what you are doing. You have stated your time is limited over the last days but enough time to ask for others to resolve issues which may not even be issues! Have stated in the FIRST comment that open_database works. So why does that need to be resolved?

Also creating a DB is a one line replacement in that same routine. See → CreateBaseDocument

Have you even tried either of these?

#11

None of the macros (including the Form macro) close documents, possibly the only thing not working but database access needs to run macros where appropriate. The original code was from the Database service but it needed to be modified to allow something to happen.

#12

That has nothing to do with opening or creating a database but is avoiding the question(s) - just a diversion. Roll up you sleeves and do some testing as I have done. Have presented you with many working routines already - a good basis to go forward. As for me, the API is preferable!

Edit:

Just to test, only took me a few minutes (couple of dumb mistakes with naming) to get form closing to work. See → CloseFormDocument

Just combined that with code from get_form.

1 Like
#13

How can the form be closed without explicitly naming either the BaseForm or Form, similar to how ThisDatabaseDocument is used? (Assume macro is run from within the open TryCode form.) Thinking Document service should be able to use ThisComponent to identify the BaseForm and Form.

def close_form(args=None):
    bas = CreateScriptService("Basic")
    # Creates DB service using current document
    doc = CreateScriptService("SFDocuments.Document", bas.ThisDatabaseDocument)
    # Get current form
    form = doc.Forms("TryCode", "Form")
    # Close form
    form.CloseFormDocument()
#14

Create a push button.
Action: Open Hyperlink or URL
URL: .uno.CloseDoc

#15

Last time I looked you needed a name here also.

#16

No but the basic example in the docs above it is better. My code runs. Using ThisComponent from within the TryCode form returns:

com.sun.star.document.OfficeDocument com.sun.star.text.GenericTextDocument com.sun.star.text.TextDocument

which I suppose is the database document, baseform and form but it’s not the names I was after.

@Villeroy suggestion of a uno call could probably work from within SF code too. Does that use the Dispatcher?

#17

I would like to see code to close a form using ‘ThisDatabaseDocument’ and no form name.

#18

ThisDatabaseDocument is the database so no explicit database name is needed. It’s an example of the functionality sought for BaseForm and Form.

#19

Don’t expect there is anything in ScriptForge for that. From my experience it is very limited. Seems you are already looking to the outside with Dispatch commands (which is contrary to this being about a ScriptForge Database Demo). Besides, I could be wrong but I believe you still need the form name at some point if not using the actual button.
.
API is the way to go.

I was using this in the context of closing a form.

Have you seen → https://ask.libreoffice.org/t/thisdatabasedocument-vs-thiscomponent/25373

#20

It calls an UNO command by its URL just like a dispatcher would do. This is how I implement close buttons without stupid macro.

Alternative document closer to be called by push button from any type of document, embedded or not with code embedded in odb or not.
It queries the parent of the calling form control until it reaches an office document and closes that document.

sub closeFormDoc(ev)
doc = getParentObject(ev.Source.Model, "com.sun.star.document.OfficeDocument")
doc.close(False)
End Sub

Function getParentObject(byval m, srvName$)
do until m.supportsService(srvName)
	m = m.getParent()
loop
getParentObject = m
End Function

While we are in this rubbish, can someone explain that weird argument to doc.close( [in] boolean DeliverOwnership ) ? Since 20 years or so I wanted to ask this question but never dared.

#21

The bug will be fixed in the next release, it’ll close any form it is run from (except for the workaround):

# close form
def close_form(evt): # assign form button execute action event to macro
    control = CreateScriptService("FormEvent", evt)  # the button
    form = control.Parent
    fdoc_name = form.BaseForm if CreateScriptService("Platform" \
               ).OfficeVersion.split()[1] >= "7.3.2" else "sf_demo"
    doc = CreateScriptService("Document", bas.ThisDatabaseDocument)
    fdoc = doc.Forms(fdoc_name, form.Name)
    # Close form
    fdoc.CloseFormDocument()

form is a specific instance so I don’t understand why fdoc (instead of form.BaseForm) can’t be returned directly then just run last line.

#22

Although a rhetorical comment of mine to begin with, the code you present still requires the name of the form.
.
And so much code just to close a form? One line in basic. Still see very little benefit in learning and using ScriptForge. One item thus far.
.
I still think one is better off learning the API.

1 Like

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.