Page 1 of 1

Error with subform

PostPosted: Tue Feb 26, 2019 6:02 pm
by kenwong
I connect Libreoffice ( Base to a database in PostgreSQL (11.2) via JDBC.

I made two forms with subforms without any issue. Now I get an error with the third form with subform. I created the form and subform by using the Form Navigator, specified the master and slave fields, saved the forms. Everytime when I open the form, the error message pops up, saying:

The data content could not be loaded. ERROR: operator does not exist: integer = character varying Hint: No operator matches the given name and argument types. You might need to add explicit type casts. Position: 92

Further, the SQL Status is 42883.

"Replace named parameters with '?'" has been ticked.

Thanks in advance for any help.

Re: Error with subform

PostPosted: Tue Feb 26, 2019 9:48 pm
by Villeroy
With form-subform relations you should use named parameters in "parsed mode". These relations don't work in "direct SQL mode" where the SQL is interpreted by the database backend.

The SQL syntax in parsed mode is the same syntax as used with the built-in HSQL, for instance:
SELECT "Case sensitive name in dbl-quotes" FROM "Table" WHERE UPPER( :Named_Parameter ) = UPPER("Field Name")

Re: Error with subform

PostPosted: Wed Feb 27, 2019 5:32 pm
by kenwong
Thanks Villeroy.

I forgot to give more details in my first post. There was no the error with only the main form in place, and even up to the point where I added the sub-form and I specified the underlying table for the sub-form. Once I input the master and slave fields in the form Properties, the error was thrown out when I opened the form for data input.

I have just resolved the issue. Previosly I input only the column names in the master and slave fields. Now I include the table name before each of the column names, like "table_name"."column_name", and the form opens with no issue.

Hope the above would help those who might be faced with the similar problem.