[Solved] Open a database query from a button

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
Echelon
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm

[Solved] Open a database query from a button

Post by Echelon »

What can I use in place of ".FormDocuments" when I want to open a query rather than a form?

I have a database which is being accessed via a single menu form with buttons that link to three other forms, using a macro similar to that described in this previous topic:
http://user.services.openoffice.org/en/ ... 54&p=10437

The problem I am having is that I also want to have a button that opens a database query using a similar macro, but this is not working since the query is not a form and therefore not recognised in the "OEvent.Source.Model.Parent.ActiveConnection.Parent.DatabaseDocument.FormDocuments" parameter.

Any assistance in finding the right parameter or a macro that would achieve this would be appreciated, or even if this is actually possible.
Last edited by Echelon on Sat Feb 09, 2008 10:38 pm, edited 1 time in total.
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Open a database query from a button

Post by DrewJensen »

Do you want to simply run the query and display the records ( as if you had double clicked it in the GUI )

Or

Do you want to open it for editing?
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Echelon
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm

Re: Open a database query from a button

Post by Echelon »

I simply wanted to run the query as if it had been double clicked from the list of queries in the GUI.
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Open a database query from a button

Post by DrewJensen »

OK

Well, I am having a little difficulty with that myslef - although it has been a little while since I last tried.

The issue is that the query window is not acutally a document ( as is a form ) but rather a child window of the Base document. My thnking is that it should be doable with something like

aconnection.queries.getByname( "Query1").Execute( <parameters> )

( as an aside it is not the only way to go, and I might be wrong - last time I worked this I was able to get an empty query definition window open but never successfully connected it back to the parent datasource - which is kind of important if you really want the data...LOL )

The parameters for the execute function is what are driving me a little bonkers - anyway I am sending an email to the developers list at the base project now asking for some help on this ( as I need this for some community work presently ) - they are usually pretty good about getting back so should have something in the next day or 2.

I'll post as soon as I hear back.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Echelon
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm

Re: Open a database query from a button

Post by Echelon »

Thanks very much Drew. If you do hear anything from the devs I would be much obliged if you would let me know. It seems like there should be a simple solution, but I just can't seem to find it and the constant supply of runtime and syntax errors are driving me insane!
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: Open a database query from a button

Post by DrewJensen »

Well, not sure this is THE final answer - but it works.

You have form with a button, the form is in a database with a query "Query1".

The button could assigned then to this procedure:

Code: Select all

sub onClickButton( oEvent as object )
      openQueryDataView( "Query1", oEvent.Source.Model.Parent.ActiveConnection )
end sub
And here is the procedure openQueryDataView:

Code: Select all

sub OpenQueryDataView( aQueryName as string, aConnection as variant )

    oDesktop = createUnoService("com.sun.star.frame.Desktop")
   
    Dim aURL as New com.sun.star.util.URL
    aURL.Complete = ".component:DB/DataSourceBrowser"
    
    oDispatchObject = oDesktop.queryDispatch(_
                        aURL, _
                        "_Blank",_
                        com.sun.star.frame.FrameSearchFlag.CREATE)
    
    Dim aProps(5) as New com.sun.star.beans.PropertyValue
    aProps(0).Name = "ActiveConnection"
    aProps(0).Value = aConnection
    aProps(1).Name = "CommandType"
    aProps(1).Value = com.sun.star.sdb.CommandType.QUERY
    aProps(2).Name = "Command"
    aProps(2).Value = aQueryName
    aProps(3).Name = "ShowMenu"
    aProps(3).Value = True
    aProps(4).Name = "ShowTreeView"
    aProps(4).Value = FALSE
    aProps(5).Name = "ShowTreeViewButton"
    aProps(5).Value = FALSE    
    
    oDispatchObject.dispatch(aURL, aProps)

End Sub
With a few changes you can make that work with a Table or a SQL select statement. ( I will put together a more expansive example for the snippet forum ASAP witht those options )

By the way - didn't ever hear from a developer, rather this was result of some collaborative 'digging' on the list with Ariel Constenla-Haile..who gets most of the credit here.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Echelon
Posts: 11
Joined: Sun Feb 03, 2008 12:37 pm

Re: Open a database query from a button

Post by Echelon »

Drew, many thanks for this. The code appears to be working perfectly and I have been able to achieve exactly what I was looking for, so I'll mark this as Solved.
User avatar
DrewJensen
Volunteer
Posts: 1734
Joined: Sat Oct 06, 2007 9:01 pm
Location: Cumberland, MD - USA

Re: [Solved] Open a database query from a button

Post by DrewJensen »

Well- for one thing there is a better version of the macro now - but alas it is still not quite correct. The new window is not linked into the hierarchy of Base windows as it should be - so if you use the macro above and then close the Base file, while the query result window is still open it will be left on screen as an empty shell. Not the worst bug in the world, but a bug none the less - hopefully on Monday we can get a little help with that last step of linking in the propert fashion. When that is finished I;ll put a revised version here.
Former member of The Document Foundation
Former member of Apache OpenOffice PMC
LibreOffice on Ubuntu 18.04
Post Reply