Description: I'm using a database connected to PostgreSQL 9.6. there are some simple forms and others more complex (5 levels of subforms). When loading subform including at least one subform, many error messages are generated by PostgreSQL: STATEMENT: SELECT * FROM "public"."logement" WHERE ( 0 = 1 ) AND ( ( ( "public"."logement"."id" = :link_from_id_logement ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 88 STATEMENT: SELECT * FROM "public"."immeuble" WHERE ( 0 = 1 ) AND ( ( ( "public"."immeuble"."id" = :link_from_immeuble ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 82 STATEMENT: SELECT * FROM "public"."ville" WHERE ( 0 = 1 ) AND ( ( ( "public"."ville"."id" = :link_from_adresse_ville ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 94 *STATEMENT: SELECT * FROM "public"."code_postal" WHERE ( 0 = 1 ) AND ( ( ( "public"."code_postal"."id" = :link_from_adresse_cp ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 78 STATEMENT: SELECT * FROM "public"."rue" WHERE ( 0 = 1 ) AND ( ( ( "public"."rue"."id" = :link_from_adresse_rue ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 96 STATEMENT: SELECT * FROM "public"."l_zone_rue" WHERE ( 0 = 1 ) AND ( ( ( "public"."l_zone_rue"."id_rue" = :link_from_adresse_rue ) AND ( "public"."l_zone_rue"."numero_rue_de" = :link_from_numero_rue ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 82 STATEMENT: SELECT * FROM "public"."zones" WHERE ( 0 = 1 ) AND ( ( ( "public"."zones"."id" = :link_from_id_zone ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 92 STATEMENT: SELECT * FROM "public"."l_cq_rue" WHERE ( 0 = 1 ) AND ( ( ( "public"."l_cq_rue"."id_rue" = :link_from_adresse_rue ) AND ( "public"."l_cq_rue"."numero_rue_de" = :link_from_numero_rue ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 104 STATEMENT: SELECT * FROM "public"."contrat_quartier" WHERE ( 0 = 1 ) AND ( ( ( "public"."contrat_quartier"."id" = :link_from_id_cq ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 84 STATEMENT: SELECT * FROM "public"."usager" WHERE ( 0 = 1 ) AND ( ( ( "public"."usager"."id" = :link_from_id_proprietaire ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 84 STATEMENT: SELECT * FROM "public"."usager" WHERE ( 0 = 1 ) AND ( ( ( "public"."usager"."id" = :link_from_id_usager ) ) AND ( 0 = 1 ) )** Forms are working correctly but loading is very slow, probably due to those errors. Connectors SDBC et JDBC give same result Have tried with Mac & Linux Ubuntu 16.04 version --> same result Steps to Reproduce: 1.Create an ODB file connected to a PostgreSQL database 2.Create a form including at least one subform (and some fields, of course). Subform must be connected to mai form via master-slave fields. 3.Load the form and check the PostgreSQL log Actual Results: SQL errors, such as: SELECT * FROM "public"."logement" WHERE ( 0 = 1 ) AND ( ( ( "public"."logement"."id" = :link_from_id_logement ) ) AND ( 0 = 1 ) ) ERROR: syntax error at or near ":" at character 88 Expected Results: No SQL errors, loading faster Reproducible: Always User Profile Reset: Yes Additional Info: JDBC & SDBC connectors have been tried --> same result Tested on Mac OS 10.12 & Ubuntu 16.04 When setting "Analyse SQL command" parameter (subform details, DATA tab) to "NO", Libreoffice displays a pop-up message mentionning "Error code 1 The data content could not be loaded. pq_preparedstatement: parameter index out of range (expected 1 to 0, got 1, statement 'SELECT * FROM "PUBLIC"."SUBFORM_TABLE_NAME"' and loading is stopped. When setting "Analyse SQL command" parameter (subform details, DATA tab) to "YES", loading is OK but errors are logged by PostgreSQL User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_12_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/58.0.3029.110 Safari/537.36
Did you set Edit > Database > Advanced Settings > Replace named parameters by '?' (don't know if this is the right English text ...)
@Robert I'm sorry but I don't know where to find such a setting. Nevertheless, I don't use any parameter. SQL commands are generated by Libreoffice itself, result of subforms which are linked to a main form by setting master-slave fields.
Created attachment 133651 [details] Advanced settings for PostgreSQL Here you could see advanced settings for PostgreSQL. Don't know if this settings look the same way with JDBC. :link_from_id_logement is such a named parameter. Base creates this parameters when you create a form with a subform. Edit > Database > Advanced Settings is the path to the advanced settings of databases when you have opened the Base-file with the connection to the external PostgreSQL database.
@sorha-consulting : Please try setting the optional parameter that Robert mentioned in comment 1 and report back here with your findings. On the face of it, it would appear that LO's creation of the named parameter, used when subforms are created and linked to main form via the GUI, is causing the error. Perhaps the postgres documentation might provide more information as to why that might be. The problem might also lie in the way LO is forming the statement, although from your comments this named parameter statement seems to be formatted in the way that LO usually does things. Setting to NEEDINFO, please set back to UNCONFIRMED once you have tried the above and reported back your findings.
From the description, this seems like a duplicate of bug 45148
Note that bug 45148 was closed as WORKSFORME because the reporter did not provide a sample SQL and ODB file for us to attempt to reproduce. The same will no doubt happen here unless that information is provided : - minimal schema, table structures and data ; - ODB file containing the queries/forms/reports, etc, that refer to the pg schema; - detailed steps on how to reproduce - your step 2 does not specify on which fields to create a master/slave relationship, this could be the key to the problem if it is indeed a duplicate of bug 45148, otherwise how are we to determine whether an INT field is being converted to a STRING, etc, or any other weirdness; - the type and version of the connector being used to access the pg db - whether direct, JDBC, or ODBC, and any Advanced Properties that are set or unset.
Created attachment 134016 [details] database schema
Created attachment 134017 [details] DB dump
Created attachment 134018 [details] odb file Used as front-end of Postgresql db. Connected via JDBC connector
Created attachment 134019 [details] JDBC-PG error log Postgresql error log when opening LO form
Created attachment 134020 [details] JDBC connector settings
Created attachment 134021 [details] Error message nr 1 when opening form
Created attachment 134022 [details] Error message nr 1 (detail) when opening form
Created attachment 134023 [details] JDBC connection string + driver class JDBC Connection string + JDBC driver class
Created attachment 134024 [details] Postgresql driver connection string
Created attachment 134025 [details] Postgresql driver - PG error log error log when using postgresql driver
Comment on attachment 134019 [details] JDBC-PG error log postgresql error log when using JDBC driver
All requested info have been submitted as attachment. I don't know how to identify driver's version. LO pops up error messages when using JDBC connector only BUT at postgresql level both drivers generate errors. Hope this helps. Change status back to UNCONFIRMED
Have tried this one with direct connection (Driver from LO) to the PostgreSQL-Database. No problem to use the form. Shows data when opening. Will see if I could also connect to PostgreSQL through JDBC. My testsystem: OpenSUSE 42.2 64bit rpm Linux, LO 5.3.3.2 direct connection.
I opened the attachment for JDBC-Advanced settings. I could see "Replace named parameters with '?' isn't chosen. Didn't you try this, as described in comment 1 and comment 3?
Yes, I did. Same result, unfortunately
(In reply to robert from comment #20) > I opened the attachment for JDBC-Advanced settings. I could see "Replace > named parameters with '?' isn't chosen. Didn't you try this, as described in > comment 1 and comment 3? Yes, I did. Same result, unfortunately
(In reply to robert from comment #19) > Have tried this one with direct connection (Driver from LO) to the > PostgreSQL-Database. No problem to use the form. Shows data when opening. > Will see if I could also connect to PostgreSQL through JDBC. > > My testsystem: OpenSUSE 42.2 64bit rpm Linux, LO 5.3.3.2 direct connection. Did you check the the Postgresql error log ?
I connected also with JDBC to the PostgreSQL-Database. First I got the same error as you when opening the form. Changed "Replace named parameters with '?'" to "YES". Closed the *.odb-file. Reopened *.odb-file. No error when opening the form. So I couldn't confirm the buggy behavior is there when replacing parameters with '?'.
Tested against Version: 5.4.0.0.alpha1+ Build ID: 5ac9c1ce61c516800cef503662d80d92394fb3f9 CPU threads: 2; OS: Mac OS X 10.12.4; UI render: default; Locale: fr-FR (fr_FR.UTF-8); Calc: group with the DB dump and ODB file provided. As in comment 24, when the the form is opened with the default Advanced Properties, ie. in which the parameter substitution character is not set, I get the error message about the index being out of range. However, when I activate the parameter substitution character option in the Advanced Properties dialog, save the ODB file, quit LO, then re-open, I can open the form without any error. I come to the same conclusion as Robert. This is WFM. Closing as such.