[Solved] Problems with URLButton.py macro after updating...

[dreamquartz]

Hi All,

After installing LO 5.10 on Windows 7 PRO, URLButton.py gives the following error:
com.sun.star.uno.ExceptionThe cursor points to before the first or after the last row. (Error during invoking function AfterRecordChange in module file:///C:/Users/****/Application%20Data/LibreOffice/4/user/Scripts/python/pyDBA/URLButton.py (<class 'ooo_script_framework.com.sun.star.sdbc.SQLException'>: The cursor points to before the first or after the last row.
C:\Users\****\Application Data\LibreOffice\4\user\Scripts\python\pyDBA\URLButton.py:51 in function AfterRecordChange() [txt = field.getString()]
C:\Program Files (x86)\LibreOffice 5\program\pythonscript.py:870 in function invoke() [ret = self.func( *args )]
))

Any ideas what could be the cause, and how to fix it?

Dream

[Villeroy]

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]

https://forum.openoffice.org/en/forum/d ... p?id=21572
Surprisingly, this stand-alone form document works fine with LO5.1 and URLButton.py. Just hit F4 and add arbitrary http: URLs to the "Address" field of the built-in "Bibliography" database.

[dreamquartz]

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?

Both Windows and Ubuntu, LO 4.7 upgrade.
I upgraded to LO 5.1 on Windows 7 PRO, located on desktop, because the internal HSQLDB 1.8 gets corrupted almost continuously.
It is a real pain to see that happen.
I am now on LO 4.5 to determine what the problem there is.

I use both computers for portability reasons.
Take my db with me on a stick.

I did not update to 5.1 yet on Ubuntu 14.04 LTS, located on laptop. Will let you know what that does.

Dream

[Villeroy]

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.

[dreamquartz]

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.

Everything works great concerning URLButton.py under LO 4.5 and 4.7 on both Windows and Ubuntu. That is not the problem.
The problem concerning the need for downgrading under Windows is, that it corrupt the internal db on a very regular basis, while that hardly does happen under Ubuntu. I use Windows, because of the Desktop configuration, including good office chair, coffee maker , and printer close by, but also it is at the office. I do not get bothered that often over there.

I like LO 5.1 though. It is a nice platform, but not in relation to databases. Did not use it for split-db just yet.

Dream

[Villeroy]

You may try the following variant of the working routine:

Code: Select all   Expand viewCollapse view

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

[dreamquartz]

just to let you know.
User profile LO 4 and 5 are in the same place.
https://wiki.documentfoundation.org/UserProfile

Dream

[dreamquartz]

You may try the following variant of the working routine:

Code: Select all   Expand viewCollapse view

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

Will let you know what it does.

Update:
Used
1. Ubuntu 14.04 LTS and LO 5.1.0.3; internal hsqldb 1.8
2. Ubuntu 14.04 LTS and LO 5.1.03; split hsqldb 2.3.3
3. Ubuntu 14.04 LTS and LO 4.4.7.1; internal hsqldb 1.8
4. Ubuntu 14.04 LTS and LO 4.4.7.1; split hsqldb 2.3.3
5. Ubuntu 14.04 LTS and LO 5.0.4.2; internal hsqldb 1.8
6. Ubuntu 14.04 LTS and LO 5.0.4.2; split hsqldb 2.3.3

Did not work. Links remain, when moving between sites as such that if there is no link on the new, and there is one from the previous, the link is still in place.

Dream

[Villeroy]

But the error message is gone. This indicates that no matter what you do on your form, the form always reports to be before first or after last record. In this case the new version of my macro skips everything below the 3rd row.
I don't understand why you get an error where I get a crash and why some forms still work and others don't.

[Villeroy]

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

Code: Select all   Expand viewCollapse view

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: https://forum.openoffice.org/en/forum/d ... p?id=21572 (enter URLs in the unused "Address" field).
URL button with embedded HSQL and http: file: and mail: https://forum.openoffice.org/en/forum/d ... p?id=27032

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

[dreamquartz]

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

Code: Select all   Expand viewCollapse view

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: https://forum.openoffice.org/en/forum/d ... p?id=21572 (enter URLs in the unused "Address" field).
URL button with embedded HSQL and http: file: and mail: https://forum.openoffice.org/en/forum/d ... p?id=25438
Same with URLButton.py embedded in odb document: https://forum.openoffice.org/en/forum/d ... p?id=26982

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

I have been fighting this now for a couple of days, and it turns out that in split 1.8 or 2.3.3 the following does not work.

Code: Select all   Expand viewCollapse view

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"

The reason for the use of this Query (qWWW) is to let the User enter anything that would be a WebSite, and qWWW makes it into something a browser can actually read.

If you look at the table tWWW, I have changed record 2 and 3. These do pass on to a browser. However, anything else does not.
So it appears to be the

Code: Select all   Expand viewCollapse view

"ELSE ''

part of the qWWW.
I have not found any solution for this one.

Attached a simple db with a couple of modifications from your URLDatabase.odb.

Hope you can help.

Dream

[Villeroy]

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 https://forum.openoffice.org/en/forum/d ... p?id=27032 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]

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 https://forum.openoffice.org/en/forum/d ... p?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.

Glad to help you out.

It appears that my problems lay with the split db, and how I use the URLButton.py.
I have split up everything into different sections, and one of them is the Website example I gave you.
When I run the macro in all the same versions you mention, there is no problem at all. The moment I go split, it is problematic.

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

Dream

[Villeroy]

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

[dreamquartz]

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

Still not there yet.
When in LO 4.4.7 or 5.1, embedded, no problems found so far.
When in LO 4.4.7 and split 2.3.3, the websites do not show properly, due to the SELECT CASE WHEN THEN ELSE, as mentioned above. There are spaces added before the URLs.

When in LO 5.1 and split 2.3.3 same problem as with the above mentioned split, but also:
1. 1st record does not have an email address. There is an email address shown and URLButton is active.
2. When moving through the records for the 1st time, the above mentioned error is still there.
3. When moving back to the 1st record, the latest shown email address is shown and the URLButton is active. It should not show anything and the URLButton should be greyed out.
4. When moving to records, it appears, where the information was present, but has been replaced by new, no error. The information for: Web Address, Google maps location, Facebook and Twitter Address.

I have modified the URLButton Data table, where there are now separate tables for the File Folders, Email Addresses, Web Addresses, Google maps locations, Facebook and Twitter Addresses. All with there own Query to prepare data to be properly presented and used.

I have not enough knowledge of Python to figure it out.

Dream

[Villeroy]

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   Expand viewCollapse view

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   Expand viewCollapse view

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 https://forum.openoffice.org/en/forum/d ... p?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.

[dreamquartz]

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   Expand viewCollapse view

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   Expand viewCollapse view

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 https://forum.openoffice.org/en/forum/d ... p?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.

Have been working on the transfer form HSQLDB 1.8 embedded to LO 5.1 and split 2.3.3, and getting close to conclusion.
The only thing that is left, is to finish this part.
It is almost correct by the looks of it, but what turns out to be is that fields are not 'nulled' the moment that there is no webpage. The field will hold the last shown field, what is not correct.

More importantly is that the button is not greyed out and still working. Therefore the webpage will be shown with the information from the last shown field.

Is that fixable?

Dream

[Villeroy]

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

[dreamquartz]

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

Used it for a while, and so far so good. I've marked it as SOLVED.

Thank you so much.

Dream

[Villeroy]

Thank you for testing.