[Solved] Mail Merge works in LIVE but not in TEST

[misitu]

I have multiple separate instances of the application. One is called LIVE and another TEST. They share nothing and data is only moved by means of HSQL backups. They are even separated at the network level, LIVE running on port 9001 and TEST on 9002.

I am using the MailMerge AOO Basic Macro support to create formatted statements of account. As an intrinsic part of the system they should work in TEST as well as in LIVE. But they don't. I am at a loss as to what might be the cause. Any clues will be welcome.

They run from a dos batch file which accepts one of three values for a single parameter and calls the macro passing that value.

The relevant portion of the batch job (omitting only a heap of if (not) exist, copy, and move statements) is as follows

Code: Select all

if "%1" NEQ "LIVE" (
	if "%1" NEQ "TEST" (
		if "%1" NEQ "UPGRADES" (
			echo Value Inadmissible
			exit /b 99
		)
	)
)
set _subdir=%1

Code: Select all

"c:\Program Files (x86)\OpenOffice 4\program\soffice.exe" 	-headless -n 	"macro:///accountingReports.bankStatements.Main(%_subdir%)"

The macro itself contains the following code

Code: Select all

REM  *****  BASIC  *****

Sub Main(systemInstance as string)

	Dim args(0) As New com.sun.star.beans.PropertyValue
	Dim sURL As String
	sURL = "file:///C:/Users/Accounts/Bookkeeping/CODE/MASTERS/bankStatements.odt"
	args(0).Name = "MacroExecutionMode"
	args(0).Value = com.sun.star.document.MacroExecMode.ALWAYS_EXECUTE_NO_WARN

	StarDesktop.LoadComponentFromUrl(sURL, "_default", 0, args())
	
	    udp 						= ThisComponent.DocumentProperties.getUserDefinedProperties()
			mm 						= createUnoService("com.sun.star.text.MailMerge")
			 
			with mm
				.DataSourceName 	= systemInstance ' udp.MRG_DataSource
print .DataSourceName
				.CommandType		= 0 ' udp.MRG_SourceType
				.Command 			= "PUBLIC.PUBLIC.View STATEMENTS" ' udp.MRG_Source
				.EscapeProcessing 	= true ' not udp.MRG_DirectSQL
				.DocumentURL 		= ThisComponent.getURL()
print .DocumentURL
				.OutputType 		= com.sun.star.text.MailMergeType.FILE
				.SaveAsSingleFile 	= True
				.OutputURL 			= "file:///C:/Users/Public/Accounts/reports/temp/" ' udp.MRG_OutputPath
				.FileNamePrefix 	= "Bank Statement " ' udp.MRG_FileNamePrefix
				.execute(Array())
			end with

	Dim oDoc
	oDoc = ThisComponent
	If HasUnoInterfaces(oDoc, "com.sun.star.util.XCloseable") Then
		oDoc.close(true)
	Else
		oDoc.dispose()
	End If

End Sub

The two print statements confirm that all the variables are correct within the macro (you will note that I have temporarily for the sake of simplicity removed the user properties from the field of combat).

When I run the batch job with LIVE the formatted and populated statements appear correctly.
When I run it with TEST only the prefilled text appears.

Baffled!

However I note one further thing.
Looking at the mailmerge source document, hovering the mouse over any field label shows the prefix LIVE. to the datasource and field name. So I guess this is a hard coded feature and accordingly I would need a copy of the mailmerge source corresponding to each system instance in which I want to run it.

Is this correct?

Is there any way of deferring evaluation of the datasource name to run time? Well, the macro thinks so, otherwise .DataSourceName would not be a variable. It would seem that the "template" field definitions override this and prevent assigning .DataSourceName at execution time.

The community's observations will be very welcome.

Thanks in advance
David

Edit: I have just had a look at the .odt file, unzipped, and content.xml contains lines such as the following Code: Select all <text:database-display text:database-name="LIVE" text:column-name="BRAND" text:table-type="table" text:table-name="PUBLIC.PUBLIC.View STATEMENTS"><BRAND></text:database-display> such that all the named mailmerge fields have the database name hard coded. So that confirms the above. My next question is: how can I substitute this at run time? I can see DatabaseLocation, ConnectionResource, and ActiveConnection in service DataAccessDescriptor. Are these useful to explore? Or: maybe I need to learn how to create the .odt "raw" with the text fields and their masters etc. Where can I find this? Too many questions...

[Villeroy]

There are many ways to change the data source of a serial letter template.

1) Switch the database connection, for instance from one dBase directory to another or from one spreadsheet to another or from one backend database to another. It is even possible to change the type of database via Edit>Database>Connection... Or rename the spreadsheet, the dBase directory or the backend name with the same effect. Works fine as long as the table names are the same.

2) Switch the query definition within the same database. Rename qA to qB or adjust the FROM clause. Works fine as long as the field names are the same which can be achieved by means of alias names, e.g. something like SELECT "Company" AS "Address Line 1", ...

3) Switch the registration name or the file name of the database document, so the same name refers to another database document. Works fine if you have an equally named query with equal field aliases.

4) Writer menu:Edit>Change database... changes the source name and the query name. Since the field names remain the same, this works fine if you can switch from one query in database X with fields A,B,C,D to another query in database Y with the same fields A,B,C,D. For this reason I have mail merge queries with equal alias names in all potential merge sources. I always refer to a query, never to a table because renaming a table name breaks most database features. With queries you can access all fields and rename them on the fly to match your Writer templates.
A short MRI snippet on this:

Code: Select all

Sub Snippet
  Dim oObj1 As Variant
  Dim sCurrentDatabaseCommand As String
  Dim nCurrentDatabaseCommandType As Long
  Dim sCurrentDatabaseDataSource As String

  oObj1 = ThisComponent.createInstance("com.sun.star.document.Settings")
  
  sCurrentDatabaseCommand = oObj1.CurrentDatabaseCommand
  nCurrentDatabaseCommandType = oObj1.CurrentDatabaseCommandType
  sCurrentDatabaseDataSource = oObj1.CurrentDatabaseDataSource
  
End Sub

5) Switch all mail merge fields in Writer, so they refer to anything else. I think, this is the most obvious but least viable method if you have to do it manually. There is a collection of TextFieldMasters if you want to try to do it programmatically.

6) Sometimes I need to collect arbitrary data from arbitrary sources for a mail merge. Paste-special text only into a spreadsheet, move around columns and rows until they match, apply some formula magic, add the right column labels according to your letter template, replace a dBase source with this spreadsheet (point 1 above) and tell the girls that they find everything in that data source so they can apply method 4). This requires spreadsheet editing skills and works best if you have a dBase connection where you store the resulting sheet. dBase has a lot of advantages over spreadsheets, e.g. you can edit the data directly in the data source window.

[misitu]

Villeroy, many thanks.

I believe that number 5)

Switch all mail merge fields in Writer, so they refer to anything else.,

is my way to go. As you mention, programmatically is my method of choice here. I want to be able to change the system and verify in TEST without affecting LIVE.

There is a collection of TextFieldMasters if you want to try to do it programmatically.

What I'm thinking is that I should replace text:database-name="LIVE" with a Master Text Field name and have that located in a Connection. I don't have a clue as to the steps required but I do have knowledge of how to acquire a connection.

Edit: I've been trying to use an existing document. One of my problems is not understanding this. The existing document is a model for a new automated image of the same produced programmatically. So: I need to create a new document in macro language

[Villeroy]

I have multiple separate instances of the application. One is called LIVE and another TEST. They share nothing and data is only moved by means of HSQL backups. They are even separated at the network level, LIVE running on port 9001 and TEST on 9002.

Are you really, really sure that you receive any data in both scenarios? How do you specify the non-default port number on the client side?

[misitu]

Database properties -> Advanced properties -> Datasource URL ->
jdbc:hsqldb:hsql://localhost:9002/TEST

and yes, because the servers run on separate ports, I can start and stop them independently, and verify* from the client response that the connection is up or down.

Couldn't survive any other way

cheers
David

* and because the data is usually different in each instance.

[Villeroy]

ok,ok.

[misitu]

[misitu]

I need to move on to creating the "base" writer doco. Once I have that it will be possible to fire off the mailmerge with sysInstance set to LIVE or TEST.
I guess it will be possible to create this base document using the UNO API.
I'm going to make a start on it at least creating an image with the ordinary text present.
Then I need to find out how to add the text field masters. If I can't do that I'll have to come back here.
Thanks for the help so far, I may need more
David

[Villeroy]

Do NOT generate documents by macro code. This is hopelessly inefficient and error prone. Templates with well prepared fields and styles are the way to go. I do not use a single line of macro code for our letter templates. I just take care of the data sources.

[misitu]

Thanks... but I have to disagree from the viewpoint of my application.
(1) templates do not allow me to vary the database name (registered name) at run time.
(2) I generate quite complex docs using macros doing date, percentage, & other calcs, these run daily and without fault.

I am going to have to generate the data, LIKE a mailmerge, but throwing my mailmerge in the museum as I can't mod it directly to run TEST when I want to.
I already generate text contents from a datasource (select * from ViewXXXX). The only bit I don't know is how to create a master field and link it to a db column.
The other bit I need to know is, at present I retrieve columns by getstring(1) (2) etc, would rather do this using column names.

Am already familiar with RowSet etc.

Thanks.. any snippets on those 2 topics would be very welcome.

Cheers
David.

Edit: Meanwhile: (1) Have created a working macro that accepts the variable systemInstance and locates the appropriate database and substitutes systemInstance in the output filename. (2) Have created a blank "master" document with tabs. (3) Have produced a formatted master using tabs, bold, and normal text. todo: loop through database using rowset or similar and use col(N) directly in the new document instance. So, solved NOT todo: get columns by name NOT todo: associate named column with text masters in master document