Description: Cannot create a subform on a form in Base using postgresql tables connected over jdbc, always shows the error: The data content could not be loaded." "SQL Status: 22023 The column index is out of range: 1, number of columns: 0." Steps to Reproduce: 1. Create two related tables in postgresql, as simple as relating the integer field in a second table to the primary key integer field in the first table 2. Connect base to this postgresql database using jdbc 3. Create a new form, use the first table as the outer data source, the second table as the subform data source, then use the integer field as the master and slave link field, then run the form, at that point you get the error message Actual Results: error: The data content could not be loaded." "SQL Status: 22023 The column index is out of range: 1, number of columns: 0." The main form gets data, the subform does not Expected Results: A running form and subform Reproducible: Always User Profile Reset: No Additional Info: postgresql 9.6.4 postgresql-42.1.3.jar User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/61.0.3163.100 Safari/537.36
See : https://stackoverflow.com/questions/36439305/error-the-column-index-is-out-of-range-1-number-of-columns-0
Under Advanced Database Properties, is the "Replace named parameters by '?'" box ticked or not ? Does it make any difference if you tick/untick and try your subform again ?
(In reply to Alex Thurgood from comment #2) > Under Advanced Database Properties, is the "Replace named parameters by '?'" > box ticked or not ? > > Does it make any difference if you tick/untick and try your subform again ? Was not ticked initially, ticked it and the error is the same.
@Jeff : I found this thread : https://forum.openoffice.org/en/forum/viewtopic.php?f=39&t=47480 that might be related to your problem.
@Jeff : I'm afraid we're going to need more information. Can you provide screenshots of the General Tab and Data tab of the properties of : 1) your Form in general; and 2) each linked control ?
@Jeff : do you get the same error if you use the postgres native driver instead of the JDBC driver ?
Currently, but without more information on the form/subform relationship and any other linked subforms or filtering in the ODB file, it is difficult to be able to be more precise. However, from the brief information you have provided, this looks like a duplicate of bug 45148, which was closed after a long while for lack of information from the original bug poster.
See also this report, which seems similar to yours, using pg9.1 : https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=47100#p288816
Created attachment 137762 [details] main form general tab
Created attachment 137763 [details] main form data tab
Created attachment 137764 [details] subform general tab
Created attachment 137765 [details] subform data tab
-- Table: public.test1 -- DROP TABLE public.test1; CREATE TABLE public.test1 ( id1 integer NOT NULL, text1 character varying COLLATE pg_catalog."default", CONSTRAINT test1_pkey PRIMARY KEY (id1) ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; ALTER TABLE public.test1 OWNER to postgres; -- Table: public.test2 -- DROP TABLE public.test2; CREATE TABLE public.test2 ( id2 integer NOT NULL, id1 integer, text2 character varying COLLATE pg_catalog."default", CONSTRAINT test2_pkey PRIMARY KEY (id2), CONSTRAINT test1_fkey FOREIGN KEY (id1) REFERENCES public.test1 (id1) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION ) WITH ( OIDS = FALSE ) TABLESPACE pg_default; ALTER TABLE public.test2 OWNER to postgres;
Read closely 108105 At the end it said you can't just tick the box about parameter ?, you must also close and reopen Libreoffice. This made it work. I'm not sure what the bug is here - that a database setting does not take effect or that you have to use that setting at all?
In other words, shouldn't ticking the box and hitting 'okay' be enough - why does one have to save the odb and quit Libreoffice?
(In reply to jeffsummer from comment #15) > In other words, shouldn't ticking the box and hitting 'okay' be enough - why > does one have to save the odb and quit Libreoffice? So, as I understand it, the driver parameters for JDBC drivers (including SQL statement parameter substitution) are generally loaded at creation of the database connection instance. They are not automatically updated in a running active session (I don't know why, perhaps it would render the whole thing potentially unstable, or have some other unforeseeable consequence, like a performance hit or threading issues). As you have solved the problem you were experiencing, with the indication given in bug 108105, and Comment 2 here, this can be set to resolved works for me.