[Solved] No connection to database exists when open report

Creating and using forms
Post Reply
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

[Solved] No connection to database exists when open report

Post by Nocton »

I have a menu form, MainMenu, with buttons which open other forms or report documents. The buttons call macros with the lines:

Code: Select all

ThisDatabaseDocument.FormDocuments.getbyname("FormName").open
for a form

Code: Select all

ThisDatabaseDocument.reportDocuments.getbyname("ReportName").open
for a report.
This all works fine.

However, if I load MainMenu automatically when the database opens, by calling the following macro from the database's Open Document event

Code: Select all

Sub AutoExec 
On Error Goto HandleError 
	Dim FormDocs, DBDoc, DataSource, Conn, FormDoc As Object
	Dim Args(1) As New com.sun.star.beans.PropertyValue 
	Dim FormName, UserName, Password As String
   
	UserName="" : Password=""
	DBDoc=ThisComponent
	DataSource=DBDoc.DataSource
	Conn=DataSource.getConnection(UserName,Password)
	
	FormName="MainMenu" 
	
	FormDocs=DBDoc.FormDocuments 
	Args(0).Name="ActiveConnection" : Args(0).Value=Conn 
	If FormDocs.hasByName(FormName) Then 
		FormDoc=FormDocs.loadComponentFromURL(FormName,"_self",0,Args() ) 
		'FormDoc.CurrentController.Frame.ContainerWindow.setFocus()
		FormDoc.CurrentController.Frame.ComponentWindow.setFocus()
	Else
		MsgBox "No form named: " & FormName
	End If 
HandleError: 
	If Err<>0 Then 
		MsgBox "An error occured while loading the form [ " & FormName & " ]"
		Exit Sub 
	End If 
End Sub 
although the forms open OK as before, the report gives an error, including at the end of the error message "No connection to the database exists".

If I close MainMenu and reopen manually, then the report opens without an error. Can anyone explain what is going on here? And perhaps how to solve the problem.

Regards,
Nocton
Last edited by Nocton on Sun Nov 13, 2011 3:02 pm, edited 1 time in total.
OpenOffice 4.1.12 on Windows 10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: No connection to the database exists when opening report

Post by RPG »

Hello

Maybe this can help you.
http://user.services.openoffice.org/en/ ... sh#p150827
Maybe you find the solution nearly half way the thread or more to the end.

It is not clear to me what you are doing.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
User avatar
DACM
Volunteer
Posts: 1138
Joined: Tue Nov 03, 2009 7:24 am

Re: No connection to the database exists when opening report

Post by DACM »

Nocton wrote:However, if I load MainMenu automatically when the database opens, by calling the following macro from the database's Open Document event
That is precisely the culprit.

You cannot open a Form/Report using the 'Open Document' event without adding additional "connection" and "flush" code to your macro because this method bypasses the connection/flush code in Base somehow. I know for a Form, you'll have to make sure the connection is established programmatically, and flush the connection regularly, or risk losing all session data.
See: http://user.services.openoffice.org/en/ ... 51#p184851 for the download and code I've used in the past to circumvent this issue. I'm not exactly sure how much of this applies to opening Reports as opposed to Forms, but Romke has been instrumental in identifying and contributing code to this issue.
 Edit: After looking over my old notes and macros pertaining to this issue, I can report that there's no need to 'flush' the connection if you utilize Thisdatabasedocument.currentcontroller.connect to establish the data-source connection...that is when utilizing the 'Open Document' Form Event in a macro to open a Form/Report automatically upon starting Base. The code is remarkably similar to that derived from the same sources by Nocton below:

Code: Select all

Sub OpenForm 'Form > Events > Open Document
    With Thisdatabasedocument.currentcontroller
        If  Not .isConnected Then .connect
    End With
    ThisDatabaseDocument.FormDocuments.getbyname("MainMenu").open
End Sub
 
As I recall, the best answer is to use standalone Forms and avoid "embedded databases"... Although I don't think you're an embedded database user...so that may not be a contributing factor here...?
Last edited by DACM on Sat Dec 17, 2011 10:42 am, edited 6 times in total.
AOO 4.1.x; LO 4.2.x; Windows 7/8 64-bit
Warning: Avoid embedded databases --> Solution: Adopt a portable 'split database' folder
Soli Deo gloria
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: No connection to the database exists when opening report

Post by Nocton »

Thank you DACM and Romke. DACM you have pointed me in the right direction and I shall look at the use of 'standalone' forms. You are correct that I am not an embedded database user, but while developing I am using the database in embedded form, because it allows one to edit the tables easily. When I have finished I shall convert it to file server mode for practical use.

I have used the Switchboard extension, but have found it to often give errors and there seems to be no support for it. At least one does not get a reply when one asks a question on the extension page. For example, in the present database which is not particulrly complicated the Switchboard extension gives an error and does not create the form and the tables. But never mind, I would rather be in complete control of the database - hence my 'MainMenu' form.

I am still not clear from your explanation, why opening a form always works, but opening a report gives a problem in the circumstances I mention.

At any rate, after looking through the links suggested I have solved my immediate problem by added the code:

Code: Select all

with Thisdatabasedocument.currentcontroller
   if  not .isConnected then  .connect
end with
As mentioned, I shall look also at the use of standalone forms.

Regards, Nocton
OpenOffice 4.1.12 on Windows 10
User avatar
RoryOF
Moderator
Posts: 34586
Joined: Sat Jan 31, 2009 9:30 pm
Location: Ireland

Re: No connection to the database exists when opening report

Post by RoryOF »

Might it be that there is a race condition? Perhaps worth putting a delay at the start of the macro run at the opendocument event to see if that cures the problem.
Apache OpenOffice 4.1.15 on Xubuntu 22.04.4 LTS
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: No connection to the database exists when opening report

Post by RPG »

Hello

It is not clear to me what is your problem. You are talking about the error and that people do not give answers but there is no real information. What are you doing. Step by step.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: No connection to the database exists when opening report

Post by Nocton »

Sorry, RPG, but I think I gave the problem step by step in my original post. (The Switchboard extension problem is something else which I mentioned in passing, but is not the topic of this thread.) DACM seems to have understood the original post clearly and as a result I have a solution plus suggestion for improved
OpenOffice 4.1.12 on Windows 10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: No connection to the database exists when opening report

Post by RPG »

Hello
Nocton wrote:I am still not clear from your explanation, why opening a form always works, but opening a report gives a problem in the circumstances I mention.
Maybe I do react only to much on this part!
You can open a report from a standalone form when the form does have a connection to the database. But it is not clear to me if that is the problem.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: No connection to the database exists when opening report

Post by Nocton »

In the circumstances, exactly as I described in my original post after auto-loading the menu form when the databse opens the button calling the report document gave the error, but the button calling the form document did not. My 'final' solution is to get rid of the AutoExec code that I gave and replace it with the simple code:

Code: Select all

sub Start
with Thisdatabasedocument.currentcontroller
   if  not .isConnected then  .connect
end with
ThisDatabaseDocument.FormDocuments.getbyname("MainMenu").open
end sub 
which I assign to the Open Document event of the database.

All the buttons on MainMenu then work OK.

Separately, re the Switchboard extension, when I try to use it I get the error message:
"Unable to establish connection to [FEMCC]. Could not create/access required tables." where the database name is FEMCC and it is properly registered. I have even tried deleting and re-registering. It would be nice to know what the cause of this is, but in the end I am happier with some simple code which works and which I can understand. And I don't have to spend more time on this, but can get on developing the database itself.

Regards, Nocton
OpenOffice 4.1.12 on Windows 10
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: No connection to the database exists when opening report

Post by RPG »

Hello

Partly I do write on this code so there can be an error but I have test it real as possible I can. Pass parameter in a tag of a button. The code is a long code but I have also time the code. I does not made a great difference between short code long code. It can be when there is no connection then it cost more time but that is only the first time. When you want closing the old form I think do it after making the report and not before making the report: You need the connection.

Romke

Code: Select all

sub CreateReportCalledfromButton(oEvent as object)
' We need this sub for a button in a standalone form
dim oCreateButton,oForm ' This are form details
dim oConnection,oReportsDocuments 
' The connection we need for finding the database document
oCreateButton=oEvent.source.model

if oCreateButton.tag = "" then
	msgbox "No report name in the button tag" & chr(13) & "This is the third from below of the property on the common tabpage"
else ' Now we have to start to find all kind of objects
	oForm=oCreateButton.parent
	if oForm.filter="" then
		print "You have not filtered data in your form"
	else
		oConnection=oForm.Activeconnection
		oReportsDocuments=oConnection.parent.DatabaseDocument.ReportDocuments ' This point to the report containers
		' Test if the name exist.
		if oReportsDocuments.hasByHierarchicalName(oCreateButton.tag) then
			'Call openreport with the parameters
			openReport(oConnection, oReportsDocuments, oCreateButton.tag , oForm.Filter)
		else print "There is a wrong report name"
		end if
	end if
end if
end sub





function openReport(oConnection, oReportsDocuments,aReportName,sFilter) as object
	' With working in the way I do now I can use these sub for calling from a document
	' what is stored in a databasedocument but also from a standalone form
	' It is assumend you do all the test before you call this sub. This means
	' You must be sure there is a connection to the database.
	' The document you want call must be
	'	a)in the databasedocument from this connection.
	' 	b) The document must be a report
	
	' When I use a standalone form then the databasedocument does not have a 
	' currentcontroller. It can be that the database is already and it does have a currentroller
	' but this is also working 
			
	' We make here the array we use in both commands
	dim aProp(2) as new com.sun.star.beans.PropertyValue
	dim aProp2 ' Make a variant
	dim oReportDesign
	aProp(1).Name = "ActiveConnection"
	aProp(1).Value = oConnection
	aProp(0).Name = "Hidden"
	aProp(0).Value = true	
	aProp(2).Name = "OpenMode"
	aProp(2).Value = "openDesign"
	let aProp2=aProp 'assign the array. The reason it is possible that the values
	' are changed when I use it in loadComponentFromURL
	'Open report in design mode and hidden
	oReportDesign=oReportsDocuments.loadComponentFromURL(aReportName ,"",0,aProp())
	oReportDesign.filter=sFilter ' Change the filter
	
	' Two Values must be changed
	aProp2(0).Value = false
	aProp2(2).Value = "open"
	' Now the result of the report wil be showed. and the the hidden report in design mode is closed
	openReport=oReportsDocuments.loadComponentFromURL(aReportName ,"",23,aProp2())
end function
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Nocton
Volunteer
Posts: 533
Joined: Fri Nov 05, 2010 10:27 am
Location: UK

Re: No connection to the database exists when opening report

Post by Nocton »

Thank you, Romke. A very useful bit of code. I think I shall set this thread as solved now.

Regards, Nocton
OpenOffice 4.1.12 on Windows 10
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] No connection to database exists when open repo

Post by arfgh »

i tried these openReport functions, but it isnt accepting 'oReport.filter', why ?
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] No connection to database exists when open repo

Post by Villeroy »

What makes you believe that your oReport object has a property "filter"?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] No connection to database exists when open repo

Post by arfgh »

good question, just because i have seen these functions, so i tried.
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] No connection to database exists when open repo

Post by Villeroy »

Can you manually apply filters to reports? No? Then it is very unlikely that reports have a "Filter" property and as a programmer you should be able to find out.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
arfgh
Posts: 566
Joined: Tue Mar 05, 2013 6:44 pm

Re: [Solved] No connection to database exists when open repo

Post by arfgh »

so then Villeroy, how you explain the RPG's functions ? it is related to reports ....
OpenOffice last version | Mageia Linux x64 | Ubuntu Linux | Windows 8.1 Enterprise x64 | Java last version
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] No connection to database exists when open repo

Post by Villeroy »

I don't know. He refers to a report's design object. Possibly it's a new style report. YOU are the programmer. It is YOUR task to find out about these things. YOU have to test code, inspect objects and variables until you understand every single word in every line. This may take hours. I don't have that much time.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
RPG
Volunteer
Posts: 2250
Joined: Tue Apr 14, 2009 7:15 pm
Location: Netherlands

Re: [Solved] No connection to database exists when open repo

Post by RPG »

Hello

As far I remember me it is only for a newstyle report. For an old style report it is more complicated. I will not study for the code.

Romke
LibreOffice 7.1.4.2 on openSUSE Leap 15.2
Post Reply