Apache OpenOffice Community Forum

Villeroy wrote:On my Linux system URLButton.py and its Basic predecessor crash LO5.1 when the form loads. No such problem with LO4.4.7.
Did you upgrade from LO 5.0 or LO 4?

Villeroy wrote:Are you sure that you are using the "After Record Change" event? I may be wrong but I think that the error message you mention can not occur after record change.
I removed LO5 from my machine. It was crashing all the time even after resetting the user profile.

Villeroy wrote:You may try the following variant of the working routine:

Code: Select all

def AfterRecordChange(e):
    frm = e.Source
    if not frm.isBeforeFirst() and not frm.isAfterLast():
        cols = frm.getColumns()
        sButtonNames = getURLButtonNames(frm)
        for s in sButtonNames:
            btn = frm.getByName(s)
            tag = btn.Tag
            field = cols.getByName(tag)
            txt = field.getString()
            if s.startswith(FilePrefix):
                try:
                    url = uno.systemPathToFileUrl(txt)
                except:
                    b = False
                else:
                    b = os.path.exists(txt)
            else:
                url = txt
                b = startswithProtocol(url)

            btn.Enabled = b
            btn.TargetURL = b and url or ''

The 3rd line with the if condition is new. Subsequent lines are one indentation level higher (4 spaces).

Villeroy wrote:Installed LO5 again. My database document does not crash because of the macro. It crashes due to this SQL expression:

Code: Select all

SELECT  CONCAT( 'http://maps.google.com/maps?q=', "STREET", ',', "CITY" )AS "URL" FROM "AddressTable"

My LO5 problem has nothing to do with yours.
The following URL buttons work for me:
URL button with biblio form: download/file.php?id=21572 (enter URLs in the unused "Address" field).
URL button with embedded HSQL and http: file: and mail: download/file.php?id=25438
Same with URLButton.py embedded in odb document: download/file.php?id=26982

Bug report about the crash: https://bugs.documentfoundation.org/sho ... i?id=97853

SELECT CASE WHEN LEFT( "tWWW"."WWW", 5 ) = 'http:' OR LEFT( "tWWW"."WWW", 6 ) = 'https:' THEN '' ELSE 'http://' END || "tWWW"."WWW" "WWW", "FKCompanyID", "WWWID" FROM "tWWW"

"ELSE ''

Villeroy wrote:First of all, I have to thank you for debugging my lousy SQL code which lacked the necessary 2 slashes in [... ELSE 'http://' ...]. This error of mine produced invalid URLs in the case when the www address had no protocol prefix.
I updated my URLButton.odb with your bug fix download/file.php?id=27026 and with the Python code embedded in the example document.

I am sorry to not being able to reproduce your problem. Everything works just fine with LO4.4, LO5.1 and AOO4.2.

dreamquartz wrote:BTW, you gave the embedded version. Can you please provide with an separate version of URLButton.py?

Villeroy wrote:

dreamquartz wrote:BTW, you gave the embedded version. Can you please provide with an separate version of URLButton.py?

It is the exact same code you already have. All others can extract the code module out of the zip container.

Edit: I had to upload another version because I was confused about all those versions of URLButton.odb in various folders. I included the Python code in a form document which can be saved as plain text in your profile folder. At least I could test this one on a Windows7 PC with embedded and stand-alone HSQL. It works

1. 1st record does not have an email address. There is an email address shown and URLButton is active.

SELECT
  CASE WHEN SUBSTR("EMAIL",1,7)='mailto:'
  THEN ''
  ELSE 'mailto:'
  END || EMAIL AS "URL"
FROM "TESTTABLE"

SELECT
  CASE WHEN SUBSTR("EMAIL",1,7)='mailto:'
  THEN  EMAIL
  ELSE 'mailto:' || EMAIL
  END AS "URL"
FROM "TESTTABLE"

Villeroy wrote:

1. 1st record does not have an email address. There is an email address shown and URLButton is active.

When testing the form in design mode you save the latest button URL with the form design. Open the form for editing. Remove the URL from the button properties, save and close the form without any button URL. This is a disadvantage when manipulating the model of a form control during runtime. Unfortunately there is no method to change the URL of the loaded form control.
The button URL is a little bit tricky to remove. Set the action property to "None" and then back to "Open Document or URL" and the URL will be removed.

Finally I found an issue with HSQL2.3:

Code: Select all

SELECT
  CASE WHEN SUBSTR("EMAIL",1,7)='mailto:'
  THEN ''
  ELSE 'mailto:'
  END || EMAIL AS "URL"
FROM "TESTTABLE"

"mailto:test@example.com" --> "_______mailto:test@example.com"
"test@example.com" --> "mailto:test@example.com"
The case where no substitution should take place is left padded with 7 spaces.

Work-around:

Code: Select all

SELECT
  CASE WHEN SUBSTR("EMAIL",1,7)='mailto:'
  THEN  EMAIL
  ELSE 'mailto:' || EMAIL
  END AS "URL"
FROM "TESTTABLE"

concatenates the full result in both conditional cases rather than concatenating the conditional result.
I reported this issue to the HSQL developer.

Yesterday's version download/file.php?id=27032 with the embedded Python module supresses the error message when the cursor is before first or after last and it includes the above work-around in the SQL query.
When I save my form with blank button URLs, remove some protocol prefixes from the URLs and remove some URLs completely, the macro disables and enables its buttons accordingly and the query complements the missing prefixes correctly.

Villeroy wrote:Sorry, as a coder I'm a lousy hobbyist. The only problem I could reproduce occurred when you saved your form design with enabled URL buttons and button URLs. These URLs remained in place when the form loaded with a first record having an invalid URL. I fixed that problem now and tested with an empty first record and some gaps in the other records.
Extract this archive into a trusted directory: https://www.mediafire.com/?mlxa0mrhgn7e8hc

It is a self-installing bundle with slightly modified (hopefully improved) Python code embedded in document plus an additional code copy in a form. The installer will not register the database. The URL form is set up with all 3 buttons enabled and with preset URLs. The macro code will correct this form design flaw as you can see in the blank first row and the other rows having gaps. If you test it with some dummy files in the right directory, you will notice that "enable-file-button-if-file-exists" works fine. The directory for the local files can be set up in the Setup table.
Replace the old URLButton.py in your profile with the one extracted from the Base document or with the code in the form document (File>SaveAs... Plain text with .py suffix in your script directory).

Edit: plus embedded HSQL without active code and the Python code in a form document