Created attachment 69003 [details] Working HSQL example Problem description: I have a master form and a slave form. It appears that if the master form doesn't have any data in it for the slave form to access, the slave form is broken for the rest of the session. For example, I have: Table1 Table2 Table3 Table 3 is linked to table 2, which is linked to table 1. If the first record in table 1 doesn't return any records in table 2, then table 3 doesn't have any master data OR slave data, and it breaks with the error message: SQL Status: 42883 ERROR: operator does not exist: integer = character varying Hint: No operator matches the given name and argument type( s ). You might need to add explicit type casts. Position: 53 The SQL command leading to this error is: SELECT * FROM "public"."sys_pos" WHERE ( ( "sys_id" = :link_from_sys_id ) AND ( "model_id" = :link_from_model_id ) ) AND ( 0 = 1 )[/quote] It appears to only happen when linking to a jdbc datasource (I was unable to reproduce the problem with an internal HSQL database). I think the problem in this example is that there is no :link_from_sys_id available, so the query konks out, and that form doesn't work for the remainder of the session, even if you move to a record that has that link available. I've attached an HSQLDB with a form that WORKS for reference, but if you attempt this same thing with an external datasource, it errors. This has been an ongoing problem since I started using LibreOffice (around 3.4). Steps to reproduce: 1. Link to External DB 2. Create 3 Tables with linking fields in external db. 3. Create 3 levels of subforms in a Base form. Current behavior: If there is no data in the master form, the query to retrieve the subform data errors out. Expected behavior: If there is no data in the master form, the query should not attempt to retrieve data for the subform. Platform (if different from the browser): Browser: Mozilla/5.0 (Windows NT 6.1; WOW64) AppleWebKit/537.16 (KHTML, like Gecko) Chrome/24.0.1297.0 Safari/537.16
Forgot to add, this only happens when the form opens. If there is master/slave data when the form opens, there is no error message, and the form works as expected. If the form opens on a record with the explained scenario, the offended form is broken for the rest of the session. You can scroll through master records, but the subform doesn't work. The only workaround I've found is to make sure the form opens on a record with master/slave data. This workaround works in development, but isn't a solution for real world application.
I have tried this with three tables. Didn't connect them in the relationships (as you doesn't) and set up a form with a subform and a subsubform. The connection is made with an external HSQLDB database. The hsqldb.jar from LO was copied to another position and linked to work with LO in Java-Classpath. I have added data to the tables with the form. Then I deleted some data from the tables (destroyed the not defined relationships), opened the form again - no error appears. I have tested this with LO 4.0.0.1 rc and LO 3.6.4.3, both an OpenSuSE 11.4 32bit rpm-Linux. I could not confirm the behavior you described.
I should have been more clear. When I said "external" I meant non-HSQLDB. I'm using a postgres db with the postgres jdbc drivers. It's happened every time I've tried it, from the 8.2 to the 9.2 drivers. Master Form Subform 1 Subform 2 If subform 1 doesn't have any records when the form opens, the query to populate subform 2 errors out. Even if you change records in your master form and subform 1 populates, subform 2 is still broken until for the remainder of your session. I've had forms with up to 8 levels of subforms, and if a parent form on any level doesn't have records, the child form will error out. Thanks.
Surely this depends on the backend ? If your backend returns a NULL result for a query where no data is present in one of the fields, e.g. when using a CONCAT() or other function string, as in the case in mysql, then this is normal behaviour. As you don't say which external backend you are using, this is a bit difficult to reproduce or even consider as a bug. Please provide more detail as to how you can reproduce the problem, preferably with instructions on the create table statement, db connection, etc. Alex
I use an existing postgresql backend (I've used 8.2 through 9.2 with the same results). LO connects to this backend through a jdbc driver. The data collection to populate the LO controls is done with the properties dialog for that particular control. I'm using table controls with textboxes and master/slave links in the LO form. If there is no data in the database, the table control isn't populated, which is expected behavior. But then any slave table/control that links to that unpopulated control throws an error, and no longer works, even if the parent table is later populated by moving to a new record. The link is permanently broken (until you reopen the form). For example, say you have a form like so: State Customer Orders In the State table, you have a list of all states. Click on a state, it populates the Customer table with all customers in that State. Click a customer, it shows all orders for that customer. A base form always opens with a record selected. And then the slave forms are populated based on the selected record. Say the first state is AK (Alaska) and you don't have any customers in Alaska. If the form opens with a State that has no customers, the Customer table is empty. The Orders table then attempts to populate, and since there are no Customers, it throws an error and that Orders table is broken for the rest of the session. This only happens when the form opens. If the form opens on a state with customers, then there is data in all the links, and there are no problems. You can then move to a state with no customers, and there will be no problems. But if the form opens on a state with no customers, the error is thrown and the link no longer works. The error that is thrown from the orders table would be: SELECT * FROM "public"."customer_order" WHERE ( ( "customer_id" = :link_from_customer_id ) ) AND ( 0 = 1 ) Since there is no "link_from_customer_id", the query errors out. In this case I haven't made any queries. This is all internal LO Base functionality. I assume this query is the one it created to create the Orders subform. Is that the intended behavior? To me it would seem that if the parent control wasn't populated, the slave control shouldn't attempt to populate and throw the error and break itself. This doesn't happen when using an internal HSQLDB but it happens in every instance when I'm using a PostgreSQL backend.
So I have tested the same with MySQL and JDBC. Also no problem. Three tables, fist table "Country", second table "Town" and third table "Person". No error appears, when there is no "Town" and only "Country". No surching for a "Person" in a non-existing town. So it could be a special problem with PostgreSQL and JDBC. I have no PostgreSQL installed. @pfreeman99: Could you test other connections (ODBC?) I will change the title of this bug, because it is missleading for other users to every external database.
Now I have installed PostgreSQL. Was a little bit tricky to connect it outside psql. I can confirm, that the JDBC-Connection didn't work right with subforms as described. When I connect directly to PostgreSQL (you could do this since LO 3.5), the same form with the same table runs correct as in the integrated HSQLDB. I don't know, if this is a problem of the external JDBC-Driver (the *.jar-archive), or if it is a problem, that could be solved by the design of LO.
Have had a look at https://bugs.freedesktop.org/show_bug.cgi?id=50747 . I think this is a duplicate. There it was MySQL with JDBC (not the special connection integrated in LO), here it is PostgreSQL. Seems to be a problem of the JDBC-driver inside LO. I will mark this bug as a duplicate of the other. *** This bug has been marked as a duplicate of bug 50747 ***