[Solved] Sub-form link sometimes works, sometimes not

[sjmullock]

I am setting up a simple database (single table) of client names and addresses from my colleagues to use. The idea was to use Base as the friendly front-end to MySQL, which has mostly worked out fine. I am using the MySQL connector for OO, running OO 3.4.1 under Windows XP.

I have a friendly form showing all the address fields for my colleagues to view and edit the database. I started it with the wizard, then fixed it up to look nicer by hand.

I then constructed a form letter, linked via the .odb file to print address labels for the Christmas card list. I made use of the "hidden paragraph" formatting options to follow the Post Office recommendations for things like whether to include a county. I spent a bit of effort getting this right and to fit the limited space on a address label.

Now, the data was inherited from a previous system and is a little chaotic. I decided that to help users be clear how fields were used (e.g. which fields appear on the address label) it would be helpful to display a full address label in a multi-line text box. This would also allow people to make a simple cut&paste off into, say, a letter being written elsewhere. I added a sub-form to the form document with the multiline text-box. I set the "Content type" to "SQL Command" and initially I typed in the command:

SELECT ID, CONCAT_WS('\n', Contact, Company, Address1, Address2, Address3, City,
County, UPPER(Country)) AS FullAddress FROM contactsworking

I then tied the multi-line address box to "FullAddress". I also set the properties "Link master fields" and "Link Slave fields" to ID in both cases, so that the current record in the main form would show in the sub-form. This worked fine! As the user navigates through the addresses, the FullAddress box gets a full address from the current main form record.

Flushed with success I decided to construct SQL to be equivalent to the slightly more complex address label logic I has used in the form letter. To test the SQL I built a query using "Create query in SQL view..". I worked with the "Run SQL command direcly button" active and came up with SQL that did exactly what I wanted:

SELECT ID, CONCAT_WS('\n', Contact, Company, Address1, Address2, Address3, City,
IF(City && Country='uk', Postcode, CONCAT(County, ' ', Postcode)),
UPPER(Country) ) AS LabelAddress FROM contactsworking

So far, so good. Now I came to edit my sub-form. My first thought was that it made sense to use the query I had already constructed so I made the sub-form "Content type" "Query" and pointed it at my working query. The address label duly filled in for the first record but it would not change in step with the main form. I tried various minor variations to no avail.

Question1: Is it possible to get a sub-form based on a Query to change in step with the main form record by linking fields?

Ok, I thought, I will simply stick to the previous method and make the sub-form type "SQL command" and put the whole command in the "Content" property. This time there was an objection to the SQL syntax.

I had been using MySQL syntax rather than HSQL syntax, partly because it seemed perverse and risky to require a translation (there is after all no HSQL engine involved), partly because the MySQL syntax seemed simpler (fewer quotes) and importantly because the MYSQL documentation was much better. (I have found myself a copy of the HSQL guide but it appears to be only a partial description). However, I then attempted to rewrite the query in HSQL. This proved quite painful and there were suggestions in my googling (e.g. here: viewtopic.php?f=61&t=20072)that I might be doomed anyway if Base insists on interpreting the code and can't cope with the full HSQL syntax.

Question2: Is there somewhere a good description of how Base communicates with the database engine, particularly when it interprets SQL commands and when it simply passes them on. I feel I could waste a lot of time if I don't understand this a bit better.

Question3: Is there a better way to link main and sub-forms? I wondered whether there should be a WHERE element to the query that would link to the main form, but don't know where to find an explicit syntax for that. I suspect that my SQL is too complex for Base to make the link implicitly.

Thanks in advance for any light that can be thrown on this. I would rather, if at all possible, do it without starting to write macros. It somehow seems wrong when the SQL is already there and makes it quite easy to pick out the information I need.

[rudolfo]

I had a similar experience when using SQLite as database backend. I had a master form based on a query that build a summary of the base table with aggregation functions and group by some_category. Additionally I had a detail (sub-)form based on the plain table to show all the records of that category whenever I changed the active record in the master form.

Apart from that the detailed form did only show always the same record I got also an error message that said something like:
Can't execute SELECT col_a, col_b, col_c FROM the_table WHERE some_attribute = :1

As you might have guessed some_category was the linked field of the main form and the subform. So internally Base uses the WHERE clause that you expected to see somewhere.

The problem seems to be that for some database backend engines it can't evaluate the :1 bind variable. And consequently the filter based on the main form won't work. I had success when I changed my backend from SQLite to HDBSQL. And I really changed only the backend -- well, okay adding also some double quotes at several places to please HDBSQL. But now what is interesting for this particular case: I had the same positive results if I used MySQL (instead of SQLite and/or HSQLDB).

With MySQL you can use an alternative approach. If you have enough permission on the MySQL database you can create a view directly in the MySQL database and use that instead of the query stored in the Base frontend. For the Base frontend parser a view in the backend database is treated just like a table. (Hm, not exactly: the view will appear among the other available tables, but with a special icon):

Code: Select all

CREATE VIEW formatted_address_data AS
SELECT ID, CONCAT_WS('\n', Contact, Company, Address1, Address2, Address3, City,
IF(City && Country='uk', Postcode, CONCAT(County, ' ', Postcode)),
UPPER(Country) ) AS LabelAddress FROM contactsworking

And then simply use TABLE and formatted_address_data as data source for your subform.

Of course you have to build the view in MySQL. You cannot use the Query Builder of OpenOffice Base. You would either use a separate client for MySQL (nothing beats the mysql command line client) or you make use of the direct path to the database engine in OpenOffice Base: Tools -> SQL...

[sjmullock]

Many thanks rudolfo, your suggestion of using a view, whilst somewhat lateral to the methods I was struggling with, certainly solves the issue with the address label. It also has the useful side effect of educating me on an aspect of MySQL I had not used before. I have full access to the database, so have been able to set up a view with no problem. I use HeidiSQL as a MySQL client, which I find quite friendly and seems to have sufficient flexibility to do pretty much anything I want.

So it only seems right to mark the question as solved. However, thinking ahead, I would still be keen to understand better at which points an SQL parser in Base has to get involved. Is there any documentation on that, or on the syntax it can cope with that you or anyone else, can point me at? Is there a syntax that allows me to make the link explicit, instead of relying on the Base parser? Frankly, given I'm using MySQL, I don't really want to involve another SQL parser, given I have no interest in being presented with a graphical view of my queries. Maybe I'm odd, but I find the SQL easier to understand than the graphical view!

Maybe I should be creating a separate topic for this, but if you have any suggestions, I think they would still be relevant to the current topic title. Once again, many thanks for your help.

[rudolfo]

Don't worry too much about the content of the thread leaving its original topic. You are right the preferred way to deal with such a situation would be to spawn another thread leaving here a link to that thread and also placing in that other thread a link back to these posts.
But I tend to say that it is a sign of a living forum that every 15th or 20th thread goes off-topic. As long as the later posts are only off topic and not starting a flame war, that's fine with me. After all the phpBB forum has a MySQL database as backend and supports full text search. It may be not organized in a nice way, but with a little bit of search engine intelligence you will find what you need. That's just like the internet: the structure is very loose, but most people are use to this and have figured out ways to find what they need.

So I'll try to explain the implication of the Base parser:

The Base parser is used whenever you have a parameterized statement to supply a (or maybe several values) for this parameter. The two case that come to mind for this are Queries with parameters where a popup dialog will ask the Base user to provide values for the parameter and the linked field in a form -- subform relation. I am 100% sure for the queries. And for that case it is also easy to prevent the Base parser from trying to parse the query. The toolbar of the query window in SQL view has a button to skip the Base parser: That's the button with the green check mark and a SQL text, the rightmost button of all toolbar buttons. Just click this button to activate direct mode for the query that you are currently processing.

I think -- but don't count on this, I am rather writing based on memory that is 2 or 3 years old -- that listbox controls for that you can build the list of values based on tables, queries or directly entered select statements, this direct select statements have also an option to use direct SQL mode. But I am really not sure ... and it may have changed since version 3.1 when I used listboxes quite often.

If you are in doubt you can always throw your select statement in question into a quick temporary query and see if it helps if you check the green direct SQL tick mark.

And finally a quick list about the limits of the Base parser:

Supported:

• JOIN, LEFT JOIN
• Query parameter inside a function: substr(:name,1,3) (was not possible before OOo 3.3: It did not throw an error, but it never returned any rows)

Not Supported:

• UNION and UNION ALL
• SELECT 3+4 AS seven (the Base parser needs always a FROM ...)
• case expression when ... a casewhen() function can be used instead
• time_to_sec() or other mysql functions not in the standard
• CALL function_returning_result_set(...)

[sjmullock]

Thanks for the extra detail. I had found the green check mark button, so my queries have been working fine. It just came unravelled when I needed the form sub-form link to work.

The list of limits us useful; I'll bookmark that. I ran into the fussiness for the boolean bit of a CASEWHEN when I was struggling to translate my MySQL dialect. The VIEW tactic was definitely the right way to go!

[Arineckaig]

However, thinking ahead, I would still be keen to understand better at which points an SQL parser in Base has to get involved. Is there any documentation on that, or on the syntax it can cope with that you or anyone else, can point me at? Is there a syntax that allows me to make the link explicit, instead of relying on the Base parser?

Due to my limited understanding I can only offer some tentative suggestions, but hopefully others reading this thread may provide more definitive answers.

Though it is not well documented, I suspect the Base parser has to be involved whenever a sub-form in linked to a parent form. This is indicated in two places in the Base Help file:

"Help>Index>sub-forms>description
"What is a sub-form
"When the user navigates through the data, the form always displays the current data record. If there are sub-forms defined, the contents of the sub-forms will be displayed after a short delay of approximate 200 ms. This delay enables you to quickly browse through the data records of the master form. If you navigate to the next master data record within the delay limit, the sub-form data need not be retrieved and displayed."

"Linkmaster fields
"The sub-form is based on an SQL query; more specifically, on a Parameter Query. If a field name is entered in the Link master fields box, the data contained in that field in the main form is read to a variable that you must enter in Link slave fields. In an appropriate SQL statement, this variable is compared to the table data that the sub-form refers to. Alternatively, you can enter the column name in the Link master fields box."

In effect it would appear that the sub-form is synchronised with the parent by the Base GUI issuing a parameter query each time the record pointer in the parent form is changed to point to a different record or row set.

If the "Analyse SQL command" property of the sub-form is set to 'No' the GUI appears to issue directly a parameter query the syntax of which is almost unique to Base: it is not even understood by the embedded HSQL database. Thus, for Base GUI to manage sub-forms as intended it is usually wiser to ensure the Base parser is set. This would appear to be case whatever the content type for the sub-form - even if it is 'Table'.

This does mean that where the Content type for the sub-form is either a query or SQL that requires syntax outwith what the Base parser can handle, it is necessary to create a suitable View in the underlying database.

Further but limited documentation on the subject can be found under two heading in the Developers Guide:

https://wiki.openoffice.org/wiki/Docume ... /Sub_Forms
https://wiki.openoffice.org/wiki/Docume ... Parameters

[sjmullock]

Thanks for the additional detail. As a result I've had a little play with explicit parameter based queries and settled things a little more in my head (BTW for anyone else the [var] syntax mentioned in the help file does not work, but the :var syntax does). It is clear that you have to have "Analyse SQL command" set to yes for the parameter substitution to be attempted, either by prompt or by link to parent. I have also found that it can cope with simple SQL even in the MySQL dialect (i.e. without all those double-quotes). As soon as the SQL gets too complex it falls over. I did try a sort of simple HSQL wrapper around a direct SQL query, it worked until I set the "Analyse..." to yes then fell over. I suppose it was to be expected - I was just curious.

The VIEW solution works so well that I'm not going to spend any more time on it. In case anyone else is following - the VIEW solution has another great advantage: You can use a column from the view directly in the form mail. This means you can avoid all that conditional hidden paragraph stuff in the form mail document and do it all via SQL in the view creation.

So thanks guys and until next time!