MailMerge with Resultset - missing last step

[cornouws]

Hi *,

I want to do a mail merge with a resultset.
- document with fields from a datasource
- datasource is not registered
- use a ods with the same table name as the original datasource

With use of a connection with the service com.sun.star.sdbc.DriverManager I can get a resultset from a sql statement.
The resultset (oResult in code below) does hold the values.
OK.
Now do the mail merge with the service "com.sun.star.text.MailMerge" ...

The code below show what I have.
- It produces two documents (this is OK for the two record sets).
- But they are empty with CommandType = 2
- And OOo Crashes on oMailMerge.axecute(Array()) with CommandType = 0 (which I expect to be the proper one)

Code: Select all

	oMailMerge = CreateUnoService("com.sun.star.text.MailMerge")
	oMailMerge.DocumentURL =  <sDocumentName>
	oMailMerge.DataSourceName = <sDataSourceName>
	oMailMerge.ResultSet = oResult
	
' choosing the next, will crash OOo	
'	oMailMerge.CommandType = 0  ' 0 = table, 1 = query, 2 = SQL-statement
'	oMailMerge.Command =  <sDataSourceTableName>

' choosing the next, gives no data in the document
	oMailMerge.CommandType = 2  ' 0 = table, 1 = query, 2 = SQL-statement
	oMailMerge.Command =  sSQL

	oMailMerge.OutputType = 2 ' 1 = Printer, 2 = file, 3 = mail
	oMailMerge.OutputURL <= sOutputURL>
	oMailMerge.SaveAsSingleFile = TRUE
	oMailMerge.execute(Array())

The rationale behind this all:
I need to perform mail merge without the use of a registered datasource.
Fields belonging to a datasource in the .odt are allowed.
A tab-delimited .txt file is provided. Converting that to an .ods is no problem.
So for the oMailMerge object, I use the name of the (not used) .odb (datasource), and the name of the table that is used in both the .ods and the .odb.

But, as pointed out above ... I do miss something to make it work.
Any hints?
Thanks - Cor

[acknak]

[Moved to Macros and UNO API]

[Villeroy]

' oMailMerge.CommandType = 0 ' 0 = table, 1 = query, 2 = SQL-statement

If 0, you use an object out of the tables container ("Table1", "View1")
If 1, you use a predefined SELECT statement out of the queries container ("Query1").
With source type 3, you specify a full SQL statement SELECT "FieldX" FROM "Table1" WHERE "Foo"='bar' ORDER BY "Date"
So what's in your symbol sSQL? Does that string return something when you paste it into the query editor?

[cornouws]

Hi Villeroy,

Thanks. But that is not the route.

Reading further in the API reference, I see this
http://api.openoffice.org/docs/common/r ... #ResultSet

So when I give a rowset as argument,
oMailMerge.ResultSet = oResult

I must not give values for
- DataSourceName
- CommandType
- Command

However..
oMailmerge.execute(Array())
fails with an unspecified error.

Have to search more ...

[Villeroy]

Honestly, I spent more than two weekends with that branch of the API and I gave up.
I have absolutely no clue why service MailMerge includes another service DataAccessDescriptor which has basically the same properties as MailMerge itself.
Both services have a property string "DataSourceName" or "DatabaseLocation" pointing to the database document by registered name or URL respectively.
Both use a string "Command" in order to specify a result set. That string can be a table name, a query name or an ad-hoc SELECT statement. You need to specify the string together with a CommandType property. This is the same way how a spreadsheet's import range can be defined, a report's source or how you specify a record set in the Base GUI when building a form or a list box in a form:
Source = Table1, Source Type=Tables
Source = Query1, Source Type=Queries
Source = SELECT * FROM...., Source Type=sql

[cornouws]

It is no problem to do mailmerge when using a datasource name.
However, I want to do it without datasource, and read in the API-ref that also a ResultSet can be used. So that is not about a specific source type (1, 2 or 3).
Cannot find explanation in the wiki dev-guide either, so will have ask on a list, I think.
Reading the Api ref again, and more precice now: "However, in scenarious where the provider of a DataAccessDescriptor has access to an already existent result set, it can pass it along for reusage."

And what I did, in my innocence, is using a resulset retrieved from a connection from the UnoService com.sun.star.sdbc.DriverManager.
I was already wondering how the link between the resultset and the document with merge fields could be made. "Not" is probably the correct answer :-\

So I have to go back to my initial question: is it possible to do a mail merge (by API), when having a database (for example spreadsheet) without having a datasource that registeres it?

[Villeroy]

I can open any database (registered or not), open any grid view and drag field headers into Writer. Dragging fields into Writer results in mail merge fields and they behave as expected when printing the Writer document.
I save two documents merger.odt (having the fields) and merged.odt (the merged output document) restarted the office, make sure that the database was not registered on the fly (Tools>Options>Databases).
I open the database and add a new row to the merged row set, close the database and print the merger once more. The connection is still alive without registration and prints the modified set of rows.

In the merger.odt I fire up Tools>Add-ons>MRIl, browse a little bit for the textFieldMasters and MRI produces this code in Basic:

Code: Select all

Sub Snippet( oInitialTarget )
REM oInitialTarget is the current controller's document
  Dim oLinks As Object
  Dim oElementNames As Object
  Dim sLocation As String
  Dim oTextFields As Object
  Dim oTextFieldMasters As Object
  Dim oObj1 As Object
  Dim sDataBaseURL As String

  oLinks = oInitialTarget.Links
  
  oElementNames = oLinks.ElementNames
  
  sLocation = oInitialTarget.Location
  
  oTextFields = oInitialTarget.TextFields
  
  oTextFieldMasters = oInitialTarget.getTextFieldMasters()
  oObj1 = oTextFieldMasters.getByName( "com.sun.star.text.fieldmaster.DataBase.file:///home/andreas/listbox_filter.odb.Items.Name" )
  sDataBaseURL = oObj1.DataBaseURL
  
End Sub

The getByName() takes the database document's URL.

With regards to this forum's member "hanya" who coded http://extensions.services.openoffice.org/project/MRI