How to disable automatic download of record in Base form?

[impocta]

I have a table which contains several picture collumns. When I open the the form to upload pictures, Base automatically starts to download the pictures of the first 41 records. How can I disable the automated (down)load of the (by default) selected records after opening the form? In the current situation I have to wait several minutes before Base has downloaded all the pictures of the first 41 records. This problem will probably become worse as more records have their images uploaded.

I need that Base only downloads the record (including several multi-MB image files) when I search for a specific record, for instance by a ‘form based filter’ or another filter method.
How can I make this work?

I am using LO Base 6.0.3.2 Build ID: 1:6.0.3-0ubuntu1 connected to an online MySQL database through driver: mysql-connector-java-5.1.45 on Xubuntu 18.04

[Ratslinger]

Hello,

Unfortunately this answer is not much better than the last.

Records for a form are retrieved based upon a default internal setting called FetchSize. This default is set to 40.

The good news it that it can be changed. The bad news (only because of your comment in another question) is that it needs to be changed via a macro. For the sake of completion, here is the macro:

Sub RecordsRetrieved
  Dim oForm As Object
  oForm = ThisComponent.Drawpage.Forms.getByName("Your_Internal_Form_Name") REM usually MainForm
  oForm.FetchSize = 1
  oForm.reload()
End Sub

You would need to use your internal form name as noted in the code. This macro is then run by attaching it to the Open Document event of the form.

It should also be noted, while this may speed up initial loading, each record will be slower because it needs loading.

Additionally, linked images instead of Blob would also be better for loading.

Edit 2018-08-26:

Have done some further testing with LO v 6.1.0.3, MySQL v5.7, JDBC 8.0.12 connector on Mint 18.3, 4 gb memory, quad CPU @ 3.2

Have found no way to limit the problem when using embedded images. The problem is that records are loading into memory and if memory is limited disk swap is used which slows things down even further. Initially when I ran on my system it showed only a couple second delay with 40+ 10-20 mb images in the table. By not closing the .odb & re-running it becomes much slower. So less memory, a slower CPU and other programs running can have an adverse effect on this loading. The problem does not go away when loading only a single record via a table filter. Initially it loads almost immediately. However, each subsequent get of another record only adds to memory being used. This memory remains occupied until the .odb is closed. Have not found a method to free this memory between records retrieved.

Performed the same testing using linked images. There was no delay using any of the forms and memory was never an issue.

[impocta]

You tempted me to try the world of macros. I found out what ‘internal form’ is and I found out how to create events and assign them to events.

When I create the macro and run it by clicking the button in the macro editor, I get the error:

BASIC runtime error.
Property or method not found: Drawpage

When I open a form which contains an internal form called MainForm it still loads up to 40 records (so it’s not working), but it does not create an error dialog.

[impocta]

I used the following:
Sub RecordsRetrieved
Dim oForm As Object
oForm = ThisComponent.Drawpage.Forms.getByName(“MainForm”)
oForm.FetchSize = 1
oForm.reload()
End Sub

[impocta]

I expect that the ‘open document’ is not the right trigger to be used. I added a MsgBox to the macro in order to find out at what actions/events it was triggered. I found out that it was run upon opening the .odb along with display of the above mentioned error message. However, upon opening a form containing an interal form called “MainForm” the macro was NOT run.

Perhaps I need a different event/trigger to assign the macro to, but I don’t know which.

[Ratslinger]

@impocta First, the error. The macro is designed to be run from a form and not the editor. Running from the editor would require much different code. The code you used appears to be the same as what was presented.

It appears you may have attached the code to the wrong Open Document event. There is one for the .odb and another for each form individually. This needs to be attached to the form it applies to.

[Ratslinger]

Remove the attachment to the .odb event. Open the form needing this in Edit mode. Now select Tools->Customize and attach to the Open Document event there. This should resolve your problem. If you are still having a problem, a sample can be provided. I do attempt to test what I post and this works on my end without a problem.

[impocta]

I did indeed attach it to the wrong ‘open document’. I tried it again by opening a form containing MainForm and then tools->customize->events and attach the macro to open document.

Now it runs, however the macro does not have the desired effect. When run on form connected to a table with large files in it the following happens. Upon opening the form it first freezes for a few minutes, then my macro confirmation dialog pops up, then if freezes again and later the number 41 changes to 2.

[impocta]

It looks like the macro is run too late to prevent the first 40 rows from being downloaded.

When used with a table with few bytes of data in the first 40 rows, this delay will probably go unnoticed because the switch from 41 loaded to 2 will happen quickly.

Is there any way to change anything in the basic configuration of Base to change the 40 to a lesser number?
If that’s not the case, I can maybe make the problem less bothering by creating a separate table for the images.

[Ratslinger]

Another interesting note, using MySQL Workbench to view records works similarly. Every image brought on screen uses more memory and the memory is not released until Workbench is fully closed.

BTW I believe the “40” rows is built into the source code. Most likely would need to find, change and re-compile to limit to one. This then would affect ALL forms.

[Ratslinger]

@impocta If you are still looking for a method, see this answer for possible workaround → Base Prevent Form Execute on Page Launch

[impocta]

@Ratslinger I see that I can prevent a large retrieval of data upon opening the form by leaving the form source table in the data tab empty.
However, i do not understand how to use a macro to automatically add the source table and decrease the fetchsize. Could you explain?

[Ratslinger]

The table is not used as the source but rather an SQL statement to retrieve the records. You must therefore control this entire process of record retrieval, forward or back, through macro coding.

This approach may not be favorable to you but I did want to present it as a working solution although not an easy one.