Problem closing an opening Dialog

Hi Everyone

I’m new here - and new to LO Base, so be gentle with me. I’m running LO 24.8.0.3 on Windows 11 and trying to create an application for a limited distribution on Windows platforms that addresses a remote PostGreSQL database. It’s been a struggle, but I now have a working dialog (in the wrong place, I’m working on that) that asks for login details which when correct opens up a screen to address the data

So far so good.

I can get in, and seem to have created a working application. However, on close I get two error messages:

Property or method not found: CancelButtonClickHandler_disposing.
Property or method not found: OKButtonClickHandler_disposing.

which derive from the opening login dialog, and which somehow avoid my error handlers (and crash the db). Can anyone advise me how these errors occur, and how they might be avoided? (Following message 88805 I have had to comment out oDialog.dispose() ).

Happy to attach some code, if anyone thinks that they can help!

Best wishes,

CB

Create a button of type “OK” and another one of type “Cancel”. Both button types will close the dialog.
Catch the return value of your dialog’s execute method.

retVal = myDialog.execute()

If the value is 1, the OK button has been clicked.
If the value is 0, the cancel button has been clicked or the window has been closed otherwise.

Hi Villeroy

Thanks for replying!

Ok, My problem is that actually I have to test some data in the dialog (UserName Password present; UserName, Password valid) if OK is pressed and only to leave dialog if I have valid data or if Cancel is pressed.

Also worth knowing is that these errors only show if the application is called when opening the document.

(Creating the dialog was my first foray into macros… so I was still learning … Here is the code below, warts and all)

Sub ShowLoginDialog

On Error GoTo ErrorHandler

’ oDialog As Object is declared outside the subroutine
Dim oDialogLibrary As Object
Dim oDialogModel As Object

Dim oTextField1 As Object
Dim oTextField1Model as Object
Dim oTextField2 As Object

Dim oButtonOK As Object
Dim oButtonCancel As Object

With GlobalScope.BasicLibraries
   If Not .IsLibraryLoaded("Tools") Then .LoadLibrary("Tools")
End With

' Get the dialog library
oDialogLibrary = GlobalScope.DialogLibraries.GetByName("Standard")

oDialog = Tools.ModuleControls.LoadDialog("Standard", "LoginDialog") 

' Get controls (adjust names to match your dialog)
oTextField1 = oDialog.getControl("fld_UserName")
oTextField2 =  oDialog.getControl("fld_Pwd")
oButtonOK = oDialog.getControl("btn_OK")
oButtonCancel = oDialog.getControl("btn_Cancel")

' Set initial values if needed, e.g.
' oTextField1.Text = ""

' Add event listeners
oButtonOK.addActionListener(CreateUnoListener("OKButtonClickHandler_", "com.sun.star.awt.XActionListener"))
oButtonCancel.addActionListener(CreateUnoListener("CancelButtonClickHandler_", "com.sun.star.awt.XActionListener"))

' Show the dialog
oDialog.execute()


' Dispose the dialog
' oDialog.dispose()

Exit Sub

ErrorHandler:
Reset
MsgBox “Error Encountered: All Files will be closed. [4]”, 0, “Error”

End Sub

Sub OKButtonClickHandler_actionPerformed(oEvent As Object)

On Error GoTo ErrorHandler

Dim oTextField1 AS Object
Dim oTextField2 As Object

With GlobalScope.BasicLibraries
   If Not .IsLibraryLoaded("Tools") Then .LoadLibrary("Tools")
End With

oTextField1 = oDialog.getControl("fld_UserName")
oTextField2 =  oDialog.getControl("fld_Pwd")

 ' Handle button click; MyName and MyPwd are global vars

MyName = oTextField1.Text
MyPwd = oTextField2.Text

IF LogMeIn(MyName, MyPwd) then
   ' Close the dialog
    oDialog.endExecute
Else
    MsgBox "Log in Failed, Please Try Again..."   
End If   

Exit Sub

ErrorHandler:
Resume Next
MsgBox “Error Encountered: All Files will be closed. [5]”, 0, “Error”

End Sub

Sub CancelButtonClickHandler_actionPerformed(oEvent As Object)

On Error GoTo ErrorHandler

' Handle button click

oDialog.endExecute

Exit Sub

ErrorHandler:

Resume Next
MsgBox “Error Encountered: All Files will be closed. [6]”, 0, “Error”

End Sub

Loop the dialog execution until it is canceled or until you got the database connected.

bContinue = True
do while bContinue
retVal = myDlg.execute()
if retVal = 1 then
  oConnection = oDBDoc.DataSource.getConnection(sUser, sPwd)
  bContinue = Not isObject(oConnection)  'not sure about this test
else
  bContinue = False
loop

Forget the above. Just let the application handle the log-in:

Sub test_Connect()
oDB = ThisComponent 'current db document
' oDB = ThisDatabaseDocument 'embedding db document
oSource = oDB.DataSource
oAuth = createUnoService("com.sun.star.sdb.InteractionHandler")
on error goto resume_Err
	oConnection = oSource.ConnectWithCompletion(oAuth)
exit sub
resume_Err:
	msgbox err
	resume
End Sub

This shows the built-in login dialog until you cancel or log in correctly.

1 Like

After playing some hour with this stuff, I store the following function in my permanent database library:

Function getConnectionByInteractionHandler(oDataSource) As Object
Dim oConnection As Object
oAuth = createUnoService("com.sun.star.sdb.InteractionHandler")
on error goto resume_Err
	oConnection = oDataSource.ConnectWithCompletion(oAuth)
getConnectionByInteractionHandler = oConnection
exit Function
resume_Err:
	if Err = 1 then
		REM Err seems to be 1 no matter what
		sMsg = "Connection failed for some unknown reason."
	else
		sMsg = "Unknown Error"
	endif
	iAnswer = Msgbox(sMsg, MB_ICONSTOP + MB_RETRYCANCEL, "macro:getConnectionByInteractionHandler")
	if iAnswer = IDRETRY then 
		resume
	else
		getConnectionByInteractionHandler= oConnection
	endif
End Function

You pass the DataSource of an arbitrary database definition and get a connection in return or an object variable with the value Nothing. The connection handler tries to connect automatically. In case of a requirement for authentification it shows the built-in dialog. In case of wrong authentification, it raises error No. 1 and prompts for cancel or retry. The user-defined Msgbox is necessary because you get into an endless loop if the connection can’t be established for any other reason (e.g. wrong connection URL, driver URL).
This is the routine I used for testing:

Sub test_InteractionHandler()
Const bSetCredentials = False
oDB = ThisComponent 'current db document
bModified = oDB.isModified()
' oDB = ThisDatabaseDocument 'embedding db document
oSource = oDB.DataSource
If bSetCredentials then
	oSource.User = "SA"
	oSource.Password = "villeroy"
else
	REM connect automatically or prompt user
endif
oDB.setModified(bModified)
oConnection = getConnectionByInteractionHandler(oSource)
Msgbox iif(oConnection is Nothing, "Canceled", "Connected")
End Sub

You can set the user and the password of the DataSource before calling the function. In this case, no dialog is shown and you get a connection if the user and password where correct. Caveat: Setting the user and password turns the document into a modified state.

P.S. c.s.s.sdb.InteractionHandler is depricated. The new c.s.s.task.InteractionHandler is more powerful (complicated) but entirely undocumented unless you dig into the source code. The above function works for me well enough with LO 24.4.

Hi VilleRoy

Thank you for replying so quickly: this is good stuff. Currently I have it that the user logs in with standard credentials, and must be present in a table in the remote DB where a number of global attributes personal to the user are set that are used in the application. Also, the connection dialog is not as pretty as my dialog(!) , but I can see that this may present a solution. Also the remote DB is not entirely under my control…

What I am most concerned about though is that I want to understand exactly what is going on with dialogs so that I can use them elsewhere. For example, is my code incorrect? Do I need ActionListeners etc. ? Is that where my errors are originating from? How can I suppress those error messages?

But if I can’t sort that, then your proposal for a solution will definitely come into the reckoning!

Thanks, Villeroy!

Best wishes,

CB

LibreOffice: InteractionHandler Service Reference is depricated.
Should be LibreOffice: InteractionHandler Service Reference which gives more control. I ran out of time for now.

The user interface connected by LibreOffice Base to a database server consists of

  1. A stored configuration that helps to connect with the server.
  2. A log-in dialog whenever you access a database that requires log-in.
  3. Input forms, either embedded in a Base document or attached to stand-alone office documents.
  4. A parameter substitution dialog. In most cases you want to substitute parameters by the parent form of a subform. Sometimes this simple dialog is good enough to specify the details of a parameter query.
  5. Reports embedded in a Base document. The result is a text document.
  6. Serial letters aka form letters aka mail merge.
  7. Database ranges in Calc linking spreadsheet cells to database row sets.
  8. Pivot tables in Calc with some database row set as source.

Writing macro code for a proper user/password log-in is a waste of time. Whenever any LibreOffice component tries to access some database row set, LibreOffice will prompt for name/password automatically.
Most of the macro code I see on places like this is a waste of time and effort because it is written in complete ignorance of the underlying application, re-inventing something that has been implemented already. The Base related part of the API is as obscure and faulty as can be. Once your Base document uses an adequate driver and correct log-on, you should study how to build arbitrary hierarchies of forms and subforms (no, the form wizard can’t do that, not even close), how to work with parameter queries, how to fill office documents with row sets and how to build reports (the report wizard is useful but incomplete).
When the user interface to your database is fairly well usable without macro code, few macro routines may help to save some clicks.

This is not MS Access!

Hi Villeroy

Firstly, let me say how much I appreciate the time you have spent sorting me out. I came to this in September with the promise that this was indeed like MSAccess and I spent many hours to my cost realising that the two are very different. Because of this misapprehension, my learning of Base has been scattergun and a couple of really basic things have been overlooked. This is my first time in this forum, and it is really good to know that I am not alone!

You have provided a number of solutions for me to chew over, and I will report back when I have the suitable solution.

Thanks again,

CB

Some more general info from a native English speaker: Apache OpenOffice Community Forum - [Example] Invoice Forms (without macros) - (View topic) (scroll down to “Some Background”).

Hi VilleRoy

Here is a working solution - not necessarily the final one! - that I’ve got going. I may well try letting the application handle the login directly later.

What flummoxed me at first was that I did not know you could define OK and Cancel buttons as OK and Cancel types. Duh! Secondly, I had great fun in trying to load my libraries which were both attached to the application rather than under ‘My Macros & Dialogs’ - I think one of your comments to another user put me on the right track, so thanks for that as well. Finally, the dialog would not work on startup (i.e. from document startup) until I had explicitly loaded all my libraries beforehand.

Anyway, for what it’s worth, here it is:

Function ShowLoginDialog

On Error GoTo ErrorHandler

Dim oDialog As Object
Dim oDialogLibrary As Object
Dim oModule as Object

Dim oTextField1 As Object
Dim oTextField2 As Object
    
Dim x As Boolean
Dim rtnValue as Integer


x=True

With GlobalScope.BasicLibraries
   If Not .IsLibraryLoaded("Tools") Then .LoadLibrary("Tools")
End With

'my macros and dialogs are stored with the component, and the macros library MUST be explicitly called on startup

'I also load the dialog library explicitly (not sure I need to, but it works)

ThisComponent.BasicLibraries.loadLibrary("DlgLib")
ThisComponent.BasicLibraries.loadLibrary("Standard")    

ThisComponent.DialogLibraries.loadLibrary("DlgLib")

' Get the dialog from dialog library name

oDialogLibrary = ThisComponent.DialogLibraries.GetByName("DlgLib")
oModule=oDialogLibrary.getByName("LoginDialog")
oDialog=CreateUnoDialog(oModule)


' Set initial values if needed
' oTextField1.Text = ""


' Show the dialog
Do While x
   'Login Dialog has 2 fields for username and password (names as outlined below)
 'as well as OK (of type OK) and Cancel (of type Cancel) buttons
 
   oTextField1 = oDialog.getControl("fld_UserName")
   oTextField2 = oDialog.getControl("fld_Pwd")
   
   
   rtnValue = oDialog.execute()

   If rtnValue= 1 then 'we have OK
     ' Handle button click
      MyName = oTextField1.Text
      MyPwd = oTextField2.Text
      
      IF LogMeIn(MyName, MyPwd) then 'call procedure to log in with these details
    
         ' Close the dialog
         ShowLoginDialog=True
         x=False
      Else
      
         MsgBox "Log in Failed, Please Try Again..."   
         
      End If   
    
   else 'we have cancel
 
      ShowLoginDialog=False
      x = False
    
   end if
   
Loop   

Exit Function

ErrorHandler:

Reset 'or whatever you want …
MsgBox “Error Encountered: All Files will be closed. [4]”, 0, “Error”

End Function