Apache OpenOffice (AOO) Bugzilla – Issue 33790
MailMerge with SQL Command doesn't fill the fields in document
Last modified: 2013-02-24 21:07:17 UTC
This is probably a bug or the API has a lack of documentation in this thing. Several other users reported on this problem but obviously nobody of them posted to the IssueZilla yet. http://www.oooforum.org/forum/viewtopic.php?t=6733&highlight=mail+merge http://www.oooforum.org/forum/viewtopic.php?t=7768&highlight=mail+merge http://www.oooforum.org/forum/viewtopic.php?t=7872&highlight=mailmerge The problem occurs with the CommandType 2 (SQL): Sub MailMerge_with_SQL Dim odoc as Object Dim oForm as Object Dim combo as Object Dim mitglieds_id as String Dim id_mitgliedsinformation as String Dim MyProps1 as Object myMerge = createunoservice("com.sun.star.text.MailMerge") MyMerge.DataSourceName="Ballettschule" MyMerge.DocumentURL=ConvertToUrl("c:\mitgliederverwaltung_nicht_loeschen\neu\druckdokumente\Mitgliedsinformation.sxw") MyMerge.CommandType=2 MyMerge.Command="Select * From ballettschule.mitglieder Where mitglied_id LIKE '115' ;" MyMerge.OutputType=1 MyMerge.execute(MyProps1()) Exit Sub Also tried with the Select command directly naming the fields which are in use in the MailMerge document. No Succes. Kind regards Helge Kraak
Of course it's End Sub at the end (had an If statement in there).
forwarding to responsible engineer
TL->SW: Can you please try to reproduce? Thanks!
Created attachment 17832 [details] file with mailmerge fields
SW->TL: I've attached a file with mailmerge fields. Copy it to an arbitrary location and adjust the "BaseFileForMailMerge" and "OutputDirForMailMerge" in the following macro according to your settings. 'base file for the mailmerge BaseFileForMailMerge="file:///<path>/base.sxw" 'directory where the merged files should be OutputDirForMailMerge="file:///<path>" myMerge = createunoservice("com.sun.star.text.MailMerge") 'set mailmergeproperties MyMerge.DataSourceName="Bibliography" MyMerge.DocumentURL=BaseFileForMailMerge MyMerge.CommandType=com.sun.star.sdb.CommandType.COMMAND MyMerge.Command="SELECT * FROM biblio WHERE Author LIKE '%Bor%'" 'MyMerge.CommandType=com.sun.star.sdb.CommandType.TABLE 'MyMerge.Command="biblio" MyMerge.OutputType=com.sun.star.text.MailMergeType.FILE MyMerge.OutputURL=OutputDirForMailMerge MyMerge.FileNamePrefix="mm" 'execute the mailmerge query MyMerge.execute(dimArray()) After executing the macro you will see that the corresponding files have been created but the fields inside haven't been replaced with the corresponding values. Works fine if you use the CommandType "TABLE"
setting target
Because of a bug, in order to have the biblio database working a version >= m54 is required for the sample by SW.
.
The database fields in the document must match the database that's used for the mail merge. The same database, command ( table, query or SQL command) have to be used. In the example document the fields point to the table biblio but they should point to the SQL command. It is not possible to insert such fields with the user interface. They have to be modified with via API. A test macro changes the first document field and afterwards the data is filled in. sub changefield xcrsr = thiscomponent.gettext.createtextcursor 'msgbox xcrsr.TextField.TextFieldMaster.dbg_properties xcrsr.TextField.TextFieldMaster.DataCommandType = 2 'print xcrsr.TextField.TextFieldMaster.databaseresource xcrsr.TextField.TextFieldMaster.datatablename = "SELECT * FROM biblio WHERE Author LIKE '%Bor%'" end sub
If nobody complains, closed.