Connection to odb from macro in standalone form

[hermanv]

LO Base 6.3 Firebird embedded on Linux Mageia 6 , odb OLVvS is registered.
I have created in my odb a form with a button that calls upon a macro to insert some values in another table than the subject table of the form.
This works perfectly OK with the macro inside the odb.
Now I want to run this form ad a standalone form (the odt is in the same directory as the odb), and I copied the macro to My Macros.
Now when I press the button in the standalone form I get the error "Object variable not set, and I get highlighting on the Datasource assignment.
The code goes:

Sub Archiveerlid
Dim DatabaseContext As Object
Dim DataSource As Object
DatabaseContext = createUnoService("com.sun.star.comp,sdbc.firebird,Driver")
DataSource = DatabaseContext.getByName("OLVvS")
oConn = ThisDatabaseDocument.DataSource.getConnection("","")
oQuery = oConn.CreateStatement()

etc…
As this is my first attempt at developing an odb application, I’m stuck here, i need some clarification.

[Ratslinger]

Hello,

Since the Base file is registered, it is not necessary to have in any specific directory.

Change this line:

DatabaseContext = createUnoService("com.sun.star.comp,sdbc.firebird,Driver")

to:

DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")

There is also an outstanding bug concerning a connection via macro using Firebird embedded. The data is not saved if the Base file is not open and a save actually done after the update occurs. (see tdf#128607). There is a workaround as stated in the report. After any potential updating of the database and before closing the connection, issue a flush command:

oConn.getParent().flush()

With this the Base file does not need to be opened or saved.

Another comment. Since you are working with a Standalone form, you can set a connection via the form and just use that. No real need for a separate connection. Either will work. Your choice.

[hermanv]

Tx, that got rid of this problem, but I’m still not out of the woods.
I have now the error “Variable not defined” on the next line

oConn = ThisDatabaseDocument.DataSource.getConnection("","")

I checked the bug you indicated, and took a look at the calc file macro’s and followed that example, so rem’d the above command and tried:

oConn =  DataSource.ConnectWithCompletion(DatabaseContext)

But that resulted in the same “variable not defined”

And yes, the form is connected , but if I put the macro inside the form and use:

oConn = ThisDatabaseDocument.DataSource.getConnection("","")

then i get “Object variable not set”

[Ratslinger]

@hermanv,

Note that notifications are not currently working on the site. Normally would have answered sooner. Only caught by trolling my past responses.

Also should have looked at more of your code than just the line giving you the error. You would call ThisDatabaseDocument if you were operating from Base. That is now replaced by DataSource which is why you create the service.

Therefore this line:

oConn = ThisDatabaseDocument.DataSource.getConnection("","")

should be:

oConn = DataSource.getConnection("","")

That should fix it.

Also note, the ConnectWithCompletion needs additional code. That is used for a dialog to be displayed for entering a user name and password. The lines are:

Dim oHandler As Object : oHandler = createUnoService("com.sun.star.sdb.InteractionHandler")				' Add Dim oHandler As Object :
Conn = Db.ConnectWithCompletion(oHandler)

where oHandler is the service.

A better sample would have been the spreadsheet at the top of the list.

[hermanv]

Tx again.
Now i have the macro working from within Base. The form refers to the macro assigned to the odb file
I really don’t need that ConnectWithCompletion statement as I see it now working. Part of the my problem was due to the fact (and it is still somewhat hit and mis for me) that I do not see very clearly what is exactly the scope i’m in when I open Tools - Macro’s, and I have to decide where I put the macro (My Macros or in the odb at hand). But the macro now works in the form within Base and inserts records in the table in the odb.
But I have exported the form to a standalone form. There I made sure the form Datasource is set correctly, and the form executes a query from the odb , so that should be OK.
I copied the macro to a macro inside the standalone form, to be sure that it would run in the correct context, and it has the statements now

Dim DataSource As Object
oConn = DataSource.getConnection("","")

but that stiil gives “Object variable not set”

[Ratslinger]

@hermanv,

Have taken your code with my changes and using my registered Firebird embedded DB I do not get any error. Here is the code used:

Sub Archiveerlid
    Dim DatabaseContext As Object
    Dim DataSource As Object
    Dim oConn As Object
    Dim oQuery As Object
    DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
    DataSource = DatabaseContext.getByName("JobSitesAndFees") Rem My DB name
    oConn = DataSource.getConnection("","")
    oQuery = oConn.CreateStatement()
    MsgBox "OK2"
End sub

I placed this in the macros for the document because it is only used by this document… Placing code in MyMacros->Standard makes it available to all LO modules and documents.

If you continue to have problems, it may be best to post a sample of the standalone document. This should be enough for me to work with. You do this by editing your question and attach using the paperclip icon on the upper left toolbar.

[Ratslinger]

Note. Added DIM statements for oConn & oQuery also to your original code. Always good practice to define variables. Will run without but is necessary if Option Explicit is defined.

[Ratslinger]

As another example, have been working with another user dealing with this from Calc. This has graduated over a number of questions and the current working code can be found in this post → Calc BASIC + Firebird : How to add flush ?

[hermanv]

Sorry for not posting earlier, but other things…
I copied your code into the standalone’s macros, and that works perfectly, many tx.
Now I have a start to get further with these macros.

[hermanv]

Just to let you know: I’ve been doing this on my Linux Mageia desktop, and all is OK. But now I copied all files to a Windows 10 laptop (made sure the Tools - Options for macros and database connections was OK) and there I had the issue you referred to tdf#128607. Adding the flush command solved the problem.

[hermanv]

Part of the problem solved: there are two “,”(comma) in the DatabaseContext assignment, which should be “.”(period).
But now I get "Property or method not found: getByName.