Mailmerge from query broken in 3.4.6?

users
#1

Using a mailmerge document that worked fine in the previous version
(no, I don't know the number, but very recent, I'm getting updates
through the Fedora repos) hardly works at all now.

The document is an address sheet with three of images. It uses
hidden paragraphs for almost every line, and the images.

The data navigator shows the correct data, the result of a query.

Mailmerge gets the first line of the address correct, and nothing
else.

Some fields appear as field names (eg <Address1>)(yes, I do have
"View Fieldnames" turned off), some fields appear as data from a row
(always the same one) that isn't even in the result set...

This is very peculiar. Is this is a known issue? Surely not.

Mark Stanton
One small step for mankind...

#2

Ok, a little more information, because I've now fixed the problem and
have a bit more idea of the scope of the mess, which is huge, I'd
say.

This mailmerge is linked to a query in a MySQL database.

My query couldn't use the query wizard (Andreas won't be surprised).
In coding the query I wrote the field names in camelcase (eg
"Address1", "PersonName"), the actual database fields are all in
lowercase. Changing this made the mailmerge show data.
However, the query worked prior to that, and in fact the hidden
paragraph conditions worked correctly, even though they were also in
camelcase.

Interestingly, calculated fields given camelcase names with "AS"
work! Uncalculated fields given names (eg `Address1` AS `Address1`)
don't.

Interestingly, again, it seems that some conditional statements don't
work the way they used to, either.

Am I talking complete rubbish here, or are others finding similar
things?

Regards
Mark Stanton
One small step for mankind...

#3

A few questions: If you are using the Fedora repo, might the problem be with the that (probably not)? Have you tried using a View instead of a Query? Could you provide us with the SQL statement used to create the query? This would make creating a database to test the problem much easier for us.
      I don't know if this has anything to do with anything: from the definition I found on camelcase, /person name/ is written personName. (The first word is NOT capitalized.)
      What about using single quotes vs double quotes when giving fields an alias? I thought that double quotes should be used in such cases. Don't single quotes and double quotes have different purposes when used in a SQL statement?

--Dan

#4

Hi Mark,

My query couldn't use the query wizard (Andreas won't be surprised).
In coding the query I wrote the field names in camelcase (eg
"Address1", "PersonName"), the actual database fields are all in
lowercase. Changing this made the mailmerge show data.
However, the query worked prior to that, and in fact the hidden
paragraph conditions worked correctly, even though they were also in
camelcase.

I seem to recall reading that somewhere on the French user list a while ago.

Interestingly, calculated fields given camelcase names with "AS"
work! Uncalculated fields given names (eg `Address1` AS `Address1`)
don't.

Interestingly, again, it seems that some conditional statements don't
work the way they used to, either.

Yes, from what I have seen on the French user list, I would concur, but
have not tried this myself. I gave up on the mailmerge wizard ages ago,
because it kept screwing up my results. There is also a bug where the
fields of the db get shifted towards the right in a Writer document, for
some as yet unexplained reason, which also screws up people's mailmerge
templates...

Labels :
https://bugs.freedesktop.org/show_bug.cgi?id=53673

Duplicate mailmerge data entries :
https://bugs.freedesktop.org/show_bug.cgi?id=47951

Layout :
https://bugs.freedesktop.org/show_bug.cgi?id=54809
https://bugs.freedesktop.org/show_bug.cgi?id=52523

Blank lines :
https://bugs.freedesktop.org/show_bug.cgi?id=35798

Non-ascii characters (e-mail) :
https://bugs.freedesktop.org/show_bug.cgi?id=38713

Pictures in mailmerge labels :
https://bugs.freedesktop.org/show_bug.cgi?id=51303

Conditions :
https://bugs.freedesktop.org/show_bug.cgi?id=43349

and the list goes on...

Alex

#5

Alexander Thurgood wrote:

Hi Mark,

My query couldn't use the query wizard (Andreas won't be surprised). In coding the query I wrote the field names in camelcase (eg "Address1", "PersonName"), the actual database fields are all in lowercase. Changing this made the mailmerge show data.
However, the query worked prior to that, and in fact the hidden paragraph conditions worked correctly, even though they were also in camelcase.
    
I seem to recall reading that somewhere on the French user list a while ago.

8><...

Alex
  

Alex,
This may not help nor apply to your particular problem, but it may help point you to the problem.
I am working on with program called DataVision as a replacement for the broken Base report builder. Reading your message reminded me of seeing something about column naming in the DataVision manual. Here is a snippet (my comments in <>).
start snippet------------------------------------------------------------------------

    Database Name Case Sensitivity
    Some databases support case-sensitive names. In those databases, the
    column named "mycol" is different from the column named "MyCol".
    Unfortunately, the programs that access databases, such as JDBC
    drivers and command line tools like PostgreSQL's psql, often confuse
    the issue by changing all names to lower case or upper case
    internally, before sending the query to the database. When you type
    "select MyCol from MyTable", it sends "select mycol from mytable" to
    the database <server>.
    If your database was created using names with only lower case (or
    upper case) letters, you may not see any problem. Your queries
    always work because no matter what case you use, the names are
    converted to all lower case (or upper case) and everything is fine.
    In order to access a column named "MyCol" using a tool that converts
    the names, you have to quote the mixed-case names. For example, the
    query "select MyCol from MyTable" must be entered as

        select "MyCol" from "MyTable"

    The quotes go around the parts of the name that need them, not the
    whole name <sql statement>. For example, the quotes don't go around
    the entire name "MyTable.MyCol". Instead, you would need to type

        select "MyTable"."MyCol"

End snippet

#6

Hi Dan,

The only language I'm aware of that has a difference between single
and double quotes is PHP, and that's only between what it does with
strings rather than whether it recognises them as strings or not. I
expect there will be some, my preference is single to reduce screen
clutter. I'm certainly not aware that SQL makes any distinction.
The query with single quotes, is producing the correct results (as
verified against a separate system).

I only do this run once every couple of months so I don't rememebr
the conversations, but I don't think mailmerge can use a view, can
it?

Yeah, I've seen what they've done to camelcase. I've not seen any
camels with lower case heads... :wink:

I can't imagine that the Fedora repo has caused a problem either.

Regards
Mark

#7

If we're not a database, and we're not a mailmerge engine, we're
running out of functionality... :-s

I've managed to get it doing this non-trivial task without error, so
I'm fairly pleased. I haven't got the pinpoint accuracy requirement,
so I don't know if it's playing me about there.

Really it shouldn't accept case-incorrect fieldnames, so at least
it's halfway to being good, it's just that that progression broke my
code :frowning:

Thanks for the other references too.

Regards
Mark

#8

Thanks for that Girvin.

In fact my query does have the quotes around field names.
I've included it at the bottom here.

I haven't noticed any difference between " ` " (backtick) or either
usual type of quotation mark either.

I appear to be using the MySQL JDBC driver, com.mysql.jdbc.Driver,
which *may* be new since I last ran this query, so it might be Java
causing the problem.

It'll get to the top of my todo list eventually :wink:

Mark Stanton
One small step for mankind...

SELECT LTRIM( CONCAT( `people`.`Title`, ' ',
        LTRIM( `people`.`Firstname` ), ' ',
        LTRIM( `people`.`Surname` ) ) ) AS `PersonName`,
    `people`.`PersonId`, `issues`.`iId` AS `iiID`, `issues`.`Issue`,
  `people`.`LastIssue`, `subscription`.`iId` AS `subiId`,
`subscription`.`Type`, `subscription`.`AdType`,
`subscription`.`Delivery`, `subscription`.`Issues`,
`subscription`.`StartingAt`, `address`.`Company` AS `Co`,
`Address1` AS `Address1`, `Address2`, `Address3`, `City`,
    LTRIM( `Area` ) AS `Area`, `Country`, `PostalCode`,
    `sent`.`iId` AS `SendId`, `sent`.`NumberSent`,
    LEFT( `textvalues`.`Text`, 1 ) AS `Text`,
    LastIssue=Issue AND Type=2 AS RenewalDue
FROM `people`
JOIN `subscription` ON `people`.`iId` = `subscription`.`iPeopleId`
JOIN `sent` ON `sent`.`iSubId` = `subscription`.`iId`
JOIN `issues` ON `sent`.`iIssueId` = `issues`.`iId`
JOIN `address` ON `people`.`iId` = `address`.`iParentId`
JOIN `textvalues` ON `textvalues`.`Id` = `subscription`.`Type`
WHERE `issues`.`Issue` = '74' AND `sent`.`sent` = 0 AND
    `Delivery` = 1 AND `textvalues`.`Field` = 'CBOSUBTYPE'
ORDER BY `FirstName`, `Surname`

#9

Hi Mark,

This may help, but I can only get you pointed in the right direction. I've used base for about 8 or 9 years, first in OO, and now in LibreOffice. Initially I setup all of the fields, and then imported the data. The fields were all case sensitive. About 5 or 6 years ago I ran across a setting for each field which makes the field non-case sensitive. I did that to all of my data bases. If I recall correctly, it needs to be applied to each field directly. You should be able to find help for that from Open Office. I'm sorry I don't remember the details.

Don

#10

Thanks Don,

I'll have a scout about.

Regards
Mark