[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #31 from Buovjaga  ---
(In reply to ansari.afcs from comment #29)
> I was not referring to windows 10 file preview as I need to start the file
> preview with specific path selected from the basic macro. Application users
> are not expected to browse. The 'SetDefaultDirectory' method does not work
> with "com.sun.star.ui.dialogs.SystemFilePicker" or 
> "com.sun.star.ui.dialogs.FilePicker".
> 
> I did test with LO 6.2.0.2, same behaviour as 6.1.4. Filepicker started with
> basic macro using "com.sun.star.ui.dialogs.OfficeFilePicker", shows 3 panes
> and is re-sizeable. Can change sizes for 'Myplaces and or file list panes
> but the preview area remain the same.

Maybe open a report about the problem with SetDefaultDirectory.

The internal file picker is going away, so you should not rely on it: bug
114484

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #30 from Julien Nabet  ---
(In reply to ansari.afcs from comment #29)
>...
> I was not referring to windows 10 file preview as I need to start the file
> preview with specific path selected from the basic macro. Application users
> are not expected to browse. The 'SetDefaultDirectory' method does not work
> with "com.sun.star.ui.dialogs.SystemFilePicker" or 
> "com.sun.star.ui.dialogs.FilePicker".
I noticed setDisplayDirectory in
https://opengrok.libreoffice.org/xref/core/offapi/com/sun/star/ui/dialogs/XFilePicker.idl?r=c657740b
Perhaps it may help.

About resizeable panes, I don't know.

Anyway, feel free so submit a bug or an enhancement.
You may also require some help on IRC or dev mailing list, see
https://wiki.documentfoundation.org/Development

About building LO, nothing complicated (at least on Linux) so you can let you
guided by this same page.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #29 from ansari.a...@gmail.com ---
(In reply to Buovjaga from comment #28)
> (In reply to Julien Nabet from comment #27)
> > (In reply to ansari.afcs from comment #26)
> > > Forgive for asking this. But Please build a standalone sdbc-postgresql
> > > driver with this patch. 
> > > ...
> > 
> > Rather try the filepicker on recent LO version with different rendering if
> > necessary. If there's a problem, feel free to submit a bugtracker if there's
> > none corresponding to your problem.
> > Also, I don't know if it's possible to build a standalone sdbc-postgresql
> > but you may also retrieve the code and give it a try.

 I wish I could but am not a programmer. My journey with openoffice, basic,
macro programming started many years back with free CD of StarOffice 5.2 and
the need to change the dbase4 management system  we were using then. I managed
to cook up something which works for us. I lurked and read through the forum
pages and learned from many people. Many many people contrirbuted in my
learning. And wait many years for this PG-log syntax error to be fixed. Since a
solution is working, I asked for the possibility of a new  extension, the last
sdbc-extension available in sdbc-openoffice.org is many years old. Well forgive
me, I know this is not OpenOffice bugzilla. Should I make a request directed to
'Lionel'
> 
> Adding ansari to CC. The filepicker is dependent on the operating system.
> Indeed, try with 6.2 and create a report, if necessary. Include all the
> relevant information.
> 
> On Windows 10, the preview ("Show the preview pane") in filepicker is
> certainly resizeable.

I was not referring to windows 10 file preview as I need to start the file
preview with specific path selected from the basic macro. Application users are
not expected to browse. The 'SetDefaultDirectory' method does not work with
"com.sun.star.ui.dialogs.SystemFilePicker" or 
"com.sun.star.ui.dialogs.FilePicker".

I did test with LO 6.2.0.2, same behaviour as 6.1.4. Filepicker started with
basic macro using "com.sun.star.ui.dialogs.OfficeFilePicker", shows 3 panes and
is re-sizeable. Can change sizes for 'Myplaces and or file list panes but the
preview area remain the same.

This behaviour is different in OO/AOO which shows only 2 panes, file list and
preview and can be enlarged together making it functional for me.

Thank you
Best Regards
Ansari

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

Buovjaga  changed:

   What|Removed |Added

 CC||ansari.a...@gmail.com,
   ||todven...@suomi24.fi

--- Comment #28 from Buovjaga  ---
(In reply to Julien Nabet from comment #27)
> (In reply to ansari.afcs from comment #26)
> > Forgive for asking this. But Please build a standalone sdbc-postgresql
> > driver with this patch. 
> > ...
> 
> Rather try the filepicker on recent LO version with different rendering if
> necessary. If there's a problem, feel free to submit a bugtracker if there's
> none corresponding to your problem.
> Also, I don't know if it's possible to build a standalone sdbc-postgresql
> but you may also retrieve the code and give it a try.

Adding ansari to CC. The filepicker is dependent on the operating system.
Indeed, try with 6.2 and create a report, if necessary. Include all the
relevant information.

On Windows 10, the preview ("Show the preview pane") in filepicker is certainly
resizeable.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #27 from Julien Nabet  ---
(In reply to ansari.afcs from comment #26)
> Forgive for asking this. But Please build a standalone sdbc-postgresql
> driver with this patch. 
> ...

Rather try the filepicker on recent LO version with different rendering if
necessary. If there's a problem, feel free to submit a bugtracker if there's
none corresponding to your problem.
Also, I don't know if it's possible to build a standalone sdbc-postgresql but
you may also retrieve the code and give it a try.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #26 from ansari.a...@gmail.com ---
Forgive for asking this. But Please build a standalone sdbc-postgresql driver
with this patch. 

I am stuck with OO 3.1.1.Application in production with many standalone forms
with 5 levels of sub-forms filtering. We use over 10 computers daily for work.
And postgresql log become pretty un-manageable within a short period of time,
as it fills with this syntax error on each form load/reload.

We cannot change to LO as its file filecker with preview is modified in such a
way that the file-preview pane is not enlargeable any more. We use the OO file
picker with preview image function to look through the report/images scanned in
as .jpg, and is a much needed function for us.

Thank you
Ansari

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #25 from Albrecht Dreß  ---
(In reply to Commit Notification from comment #24)
> tdf#119569 try most likely to succeed method to get column metadata _before_
> 
> It will be available in 6.1.3.
> 
> The patch should be included in the daily builds available at
> http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours.

I can confirm that the issue has been fixed in
* Version: 6.1.3.0.0+
* Build ID: 8ef25505303dcd744d20abf7e328ce1f0eda4dbf
running on Debian Stretch/64 Bit.

Again, thanks a lot for your help!

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #24 from Commit Notification 
 ---
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "libreoffice-6-1":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=f19cba204552ecb11b97c8047320733c498cf518=libreoffice-6-1

tdf#119569 try most likely to succeed method to get column metadata _before_

It will be available in 6.1.3.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

Commit Notification  changed:

   What|Removed |Added

 Whiteboard|target:6.2.0|target:6.2.0 target:6.1.3

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #23 from Albrecht Dreß  ---
I tested
- Version: 6.2.0.0.alpha0+
- Build ID: 9927a3ab9edec7ae9f41ecaa8025a2a920321997
on Debian Stretch/64 and can confirm the PostgreSQL errors are now gone. 
Compared to the PostgreSQL log in
https://bugs.documentfoundation.org/show_bug.cgi?id=119569#c8, opening the form
the messages now read

LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1"
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( (
"public"."tab2"."ref_id" = NULL ) )
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( "public"."tab2"."ref_id"
= '2' )

Thanks a lot for your help!

Best, Albrecht.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #22 from Lionel Elie Mamane  ---
Backport to 6.1 series (for 6.1.3) waiting for 2nd opinion.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

Commit Notification  changed:

   What|Removed |Added

 Whiteboard||target:6.2.0

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #21 from Commit Notification 
 ---
Lionel Elie Mamane committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=6bebb05bd4b338e1ed3dac2c02b8b4186dc79675

tdf#119569 try most likely to succeed method to get column metadata _before_

It will be available in 6.2.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #20 from Lionel Elie Mamane  ---
Sorry, the LibreOffice git repo was down last week-end, so I could not commit
the change I made, and I had a super-busy week, so couldn't do it until now.

The submitted patches are at https://gerrit.libreoffice.org/60885 and
https://gerrit.libreoffice.org/60884

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #19 from Albrecht Dreß  ---
(In reply to Lionel Elie Mamane from comment #18)
> That algorithm is used on _all_ queries / SQL commands executed by
> LibreOffice (when column information is needed).
Ooops, didn't understand that correctly from your previous comment, sorry! 
Yes, I agree that a modification may be risky.

> Well, let's give it a try. I'll change the algorithm to do only step 4, or
> maybe turn the algorithm around: first step 4, if that fails step 2 or 3,
> then step 3 or 2. I'll sleep on it to take a decision on the alternatives.
> Then in the next months let's see what regressions this brings, or not.
Thanks a lot!  I look forward to testing it…

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #18 from Lionel Elie Mamane  ---
(In reply to Albrecht Dreß from comment #17)
> (In reply to Lionel Elie Mamane from comment #16)

>> 3) If that fails, try to execute the SQL _unchanged_ as a statement (without
>> normal LibreOffice processing)
> […]
>> what you see is the result of step 3, so "it works as designed".

> To be honest, I doubt that /any/ halfway SQL-compliant server will ever
> accept the broken statement.

That algorithm is used on _all_ queries / SQL commands executed by LibreOffice
(when column information is needed). So it is "broken" when there are
parameters, but it is 100% legit when there are no parameters, and actually
corresponds to trying to send the SQL command/query as unchanged as possible to
the DB engine. Subforms just happen to use a parametrised query, which is why
you see it with subforms.

>> Unless, Albrecht, you explain to me why it is a problem?

> I fully agree with you that from the users pov Libreoffice behaves as
> expected, and that PostgreSQL can deal with broken input properly.

> However, from the IT operations pov, it *is* a problem.

> In our use case, the database is running on a central server with high
> security requirements.  We run several monitoring components, inter alia
> (using CheckMK) monitoring the PostgreSQL log file for issues.  Every
> “ERROR” there is of course brought to the admin's attention – remember that
> it may be a serious server issue, or an indicator for attack or compromise.

I see.

Well, let's give it a try. I'll change the algorithm to do only step 4, or
maybe turn the algorithm around: first step 4, if that fails step 2 or 3, then
step 3 or 2. I'll sleep on it to take a decision on the alternatives. Then in
the next months let's see what regressions this brings, or not. Maybe I'll do:

step 4
if that fails, step2&3 or 3&2 with, if those succeed, a debug message "if you
see this, please file a bug and inform Lionel, should not happen"

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #17 from Albrecht Dreß  ---
(In reply to Lionel Elie Mamane from comment #16)
Thanks a lot for the detailed description of the bug!

> 3) If that fails, try to execute the SQL _unchanged_ as a statement (without
> normal LibreOffice processing)
[…]
> what you see is the result of step 3, so "it works as designed".

To be honest, I doubt that /any/ halfway SQL-compliant server will ever accept
the broken statement.  Would be interesting what Oracle/Mysql/… report in this
case.

> Unless, Albrecht, you explain to me why it is a problem?

I fully agree with you that from the users pov Libreoffice behaves as expected,
and that PostgreSQL can deal with broken input properly.  In a single-user
environment, I guess ≥99% of the users will even never notice the PostgreSQL
error messages.

However, from the IT operations pov, it *is* a problem.

In our use case, the database is running on a central server with high security
requirements.  We run several monitoring components, inter alia (using CheckMK)
monitoring the PostgreSQL log file for issues.  Every “ERROR” there is of
course brought to the admin's attention – remember that it may be a serious
server issue, or an indicator for attack or compromise.

With several users using the Libreoffice frontend, the admin gets bombarded
with issues, of which /almost/ all are caused by the Libreoffice bug.  Needles
to say that there is a considerable risk that real issues just slip through.

So, apart from a fixed Libreoffice version, my options are:
(1) Disable monitoring for PostgreSQL (I actually did this temporarily) or
error reporting from PostgreSQL.  No long-term solution as security auditing is
mandatory in our use case, and a /very/ bad idea anyway.
(2) Replace Libreoffice by an other solution.  Drawback: loose all amenities of
Libreoffice, time/money for implementation.
(3) Create a new monitoring component which ignores *only* the broken queries,
iff coming from Libreoffice (but not from other sources).  Probably less effort
than (2), but adds complexity to the setup (maintenance, security auditing of
the new component, etc.)

In short, it's neither “RESOLVED” nor “NOTABUG” for me…

Apart from that, IMHO it's not good engineering practice to issue “something”
which is broken by design and hope that the recipient will deal with it
properly, but this may be philosophical question.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

Lionel Elie Mamane  changed:

   What|Removed |Added

 Resolution|--- |NOTABUG
 Status|NEW |RESOLVED

--- Comment #16 from Lionel Elie Mamane  ---
For me, when changing the setting in the file, the error in the PostgreSQL log
changes from:

ERROR:  syntax error at or near ":" at character 82
STATEMENT:  SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( (
"public"."tab2"."ref_id" = :link_from_id ) )

to

ERROR:  syntax error at or near ")" at character 84
STATEMENT:  SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( (
"public"."tab2"."ref_id" = ? ) )

which kinda shows that the setting is taking effect. However, I realised that
this setting has no importance for Postgresql (native/SDBC driver), since the
PostgreSQL-SDBC driver will postprocess any SQL to be executed and will replace
both unnamed and named parameters by their value... when the DB-agnostic part
of LibreOffice sets the parameters to some value, obviously. Which explains why
everything was working even in the face of an incorrect setting. Also,
according to my notes the PostgreSQL parameter syntax is yet another one,
namely $1, $2, etc.



As to this bug. I traced this back to file
dbaccess/source/core/api/SingleSelectQueryComposer.cxx function
OSingleSelectQueryComposer::getColumns() around line 750 to 815, where the
basic algorithm is:

1) Add to the SQL's WHERE clause "0=1" (to get 0 results, but get column
information)
2) Try to execute the SQL as a _prepared_ statement (with normal LibreOffice
processing)
3) If that fails, try to execute the SQL _unchanged_ as a statement (without
normal LibreOffice processing)
4) If that fails, try to execute the SQL as a prepared statement setting all
parameters to NULL

what you see is the result of step 3, so "it works as designed". Frankly, if I
had to write this code from scratch, I would scrap steps 2 and 3, and do only
step 4, which makes the most sense to me. However, given the age of this code
and the amount of work-arounds for exotic "misbehaviours" of obscure (or not so
obscure) database engines it probably contains, I'd rather not touch it and
reintroduce the problems it probably works around. Unless we get better
visibility on the issues, or the current behaviour leads to an actual problem.

My conclusion is: the behaviour is not a problem. LibreOffice tries stuff, some
of which is anticipated that it could fail, and the failure is treated by
LibreOffice, with a sensible fallback. We could rework that part of LibreOffice
so that it does not, in this particular case, try this broken SQL, but who
knows what other problems it will introduce with other DB engines. So I'm going
to close this bug report and not do anything about it, because it is not a
problem. (Unless, Albrecht, you explain to me why it is a problem?)

This being said, if some brave soul wants to change the algorithm to be more
clean (as outlined above) and then watch for regressions, I'll gladly accept
the patch.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #15 from t...@tim-passingham.co.uk ---
Sorry Lionel, having done more searching I found #118112 which is the same
issue as mine, and has a form of resolution for existing databases.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #14 from Lionel Elie Mamane  ---
(In reply to Albrecht Dreß from comment #0)
> Side note: there are some similar but closed issues regarding the JDBC
> driver where setting an advanced connection option should solve the bug. The
> PostgreSQL driver apparently doesn't support these options, though (the
> “Advanced Settings…” menu entry is inactive).

For clarity, that setting is supposed to be _hardcoded_ to the correct value
for PostgreSQL. That is why it is not available to the user. It should be the
correct one, automatically, punto basta.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #13 from Lionel Elie Mamane  ---
(In reply to tim from comment #12)
> I am getting much the same error on Windows version 6.1.0.3 and 

> I'm using base with mysql/mariadb sql.  When I open a form with a subform I
> get (at least) one of these for each subform:
> 
> "The data content could not be loaded.
> 
> Parameter index out of range (1 > number of parameters, which is 0)."
> 
> And the detail: "SQL Status: S1009
> 
> Parameter index out of range (1 > number of parameters, which is 0)."

Tim, this bug is about the error showing in the PostgreSQL log, but otherwise
LibreOffice Base works fine. LibreOffice Base does not show the error. It seems
you get the error in LibreOffice Base, and it breaks usage of your forms.
Please fork this in a separate bug report. Please also specify which MySQL
driver you are using (ODBC? JDBC? "native"?).

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

t...@tim-passingham.co.uk changed:

   What|Removed |Added

 CC||t...@tim-passingham.co.uk

--- Comment #12 from t...@tim-passingham.co.uk ---
I am getting much the same error on Windows version 6.1.0.3 and 

ubuntu Version: 6.1.1.2
Build ID: 1:6.1.1~rc2-0ubuntu0.18.04.1~lo3
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-GB (en_GB.UTF-8); Calc: group threaded

ubuntu version 6.0.5.2 is OK on my system.

I'm using base with mysql/mariadb sql.  When I open a form with a subform I get
(at least) one of these for each subform:

"The data content could not be loaded.

Parameter index out of range (1 > number of parameters, which is 0)."

And the detail: "SQL Status: S1009

Parameter index out of range (1 > number of parameters, which is 0)."

I have checked that it is the queries used by the subforms that are giving the
errors (not the subform code itself), but opening the query and supplying
parameters.  On 6.0.5.2 it works.


I have not yet tried to produce a demo form but will try to do so if asked.

This is critical for me.  I use Base a lot, and all my applications makes
extensive use of subforms.  I have had to revert back to 6.0.5.2 on my main
system.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

rob...@familiegrosskopf.de changed:

   What|Removed |Added

Version|6.0.4.2 release |5.4.6.2 release

--- Comment #11 from rob...@familiegrosskopf.de ---
Error appears also in earlier versions. Haven't installed much of the but could
reproduce with
Version: 5.4.6.2
Build-ID: 4014ce260a04f1026ba855d3b8d91541c224eab8
on OpenSUSE 15, 64bit rpm Linux.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

rob...@familiegrosskopf.de changed:

   What|Removed |Added

 Status|UNCONFIRMED |NEW
 Ever confirmed|0   |1

--- Comment #10 from rob...@familiegrosskopf.de ---
Could confirm this buggy behaviour. Have found the logfiles for PostgreSQL
where alls this syntax-errors were logged. Every time I open a form with
subform one error appears. When I add a new row in the mainform there appear
two syntax-errors.

The forms work as expected. Seems the queries will be repeated in another
(right) way so you couldn't recognize this error in Base.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

Julien Nabet  changed:

   What|Removed |Added

 CC||lio...@mamane.lu,
   ||serval2...@yahoo.fr

--- Comment #9 from Julien Nabet  ---
Lionel:
do you think it would be ok to add 

  
true
  

in registry/postgresql/org/openoffice/Office/DataAccess/Drivers.xcu or is this
following comment "
" from 2011 you had put is still relevant?
Unless it applied only to "EscapeDateTime" and "UseBracketedOuterJoinSyntax"
therefore other settings may be added?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #8 from Albrecht Dreß  ---
I changed the (In reply to robert from comment #6)
> ... and also tested with LO 6.0.5.2 and LO 6.0.0.1 on OpenSUSE 15, 64bit rpm
> Linux. Form with subform works.
> 
> When I see the error of the description and the statement here one question:
> Do you know where this comes from - "WHERE ( 0 = 1 )"?

Good question…  I changed the PGSql server setting to “log_statement = 'all'”. 
Opening the form (using my db example) triggers the following statements (only
those to my db; in addition, there is a bunch of queries to the PGSql control
tables):

LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1"
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( 0 = 1 )
LOG:  Anweisung: SELECT * FROM "public"."tab1" WHERE ( 0 = 1 )
FEHLER:  Syntaxfehler bei »:« bei Zeichen 82
ANWEISUNG:  SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( (
"public"."tab2"."ref_id" = :link_from_id ) )
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( (
"public"."tab2"."ref_id" = NULL ) )
LOG:  Anweisung: SELECT * FROM "public"."tab2" WHERE ( "public"."tab2"."ref_id"
= '2' )

The last statement actually loads the data from tab2 referenced by the 1st row
in tab1.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #7 from Albrecht Dreß  ---
(In reply to robert from comment #5)
> Could input data in form and also in subform without any problems.
> Data were saved.

Yes.  The form itself works for me, too.  It's just the initial query when
opening the form which is broken, triggering the error message in the
Postgresql server log.  The latter is monitored by Nagios, which is somewhat
annoying…

> All tests with OpenSUSE 15, 64bit rpm Linux, LO Version: 6.1.0.3, Build ID:
> efb621ed25068d70781dc026f7e9c5187a4decd1
> 
> Could be you are working with LO directly distributed by Ubuntu or Debian?
> What's the Build ID from your versions?

I installed a fresh VM with Debian Stretch/64 plus all updates, and made a full
install of the package from 
which is actually “Version: 6.1.0.3 Build ID:
efb621ed25068d70781dc026f7e9c5187a4decd1” (i.e. the same as yours).

I /still/ get the server error message when I open the form, even when I
created a completely new LibreOffice database document (German locale of PGSql;
log file /var/log/postgresql/postgresql-9.6-main.log):

FEHLER:  Syntaxfehler bei »:« bei Zeichen 82
ANWEISUNG:  SELECT * FROM "public"."tab2" WHERE ( 0 = 1 ) AND ( (
"public"."tab2"."ref_id" = :link_from_id ) )

I have no idea why this statement is submitted at all, as it will never (due to
“0=1”) return any data.

In addition to Libreoffice, I installed openjdk-9-jre-headless:amd64 ver.
9~b181-4~bpo9+1 and postgresql ver. 9.6+181+deb9u2, if that is of any
importance.  The version on Ubuntu is “Version: 6.0.4.2 Build-ID:
1:6.0.4~rc2-0ubuntu0.16.04.1”, btw.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #6 from rob...@familiegrosskopf.de ---
... and also tested with LO 6.0.5.2 and LO 6.0.0.1 on OpenSUSE 15, 64bit rpm
Linux. Form with subform works.

When I see the error of the description and the statement here one question:
Do you know where this comes from - "WHERE ( 0 = 1 )"?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #5 from rob...@familiegrosskopf.de ---
(In reply to Albrecht Dreß from comment #4)
> (In reply to robert from comment #3)
> > PostgreSQL: the connections could be set in
> > Edit > Databases > Advanced Settings
> > You have to switch named Parameter to '?'.
> 
> As I mentioned in my original post, this option is inactive (greyed out) for
> the PostgreSQL driver.  Or did I miss your point?

You are right. Have set up PostgreSQL here with direct connection. The options
aren't available there. But: I can't confirm the buggy behaviour. 
Have created two tables, connected with foreignkey. 
Created a form with subform.
Could input data in form and also in subform without any problems.
Data were saved.

All tests with OpenSUSE 15, 64bit rpm Linux, LO Version: 6.1.0.3, Build ID:
efb621ed25068d70781dc026f7e9c5187a4decd1

Could be you are working with LO directly distributed by Ubuntu or Debian?
What's the Build ID from your versions?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #4 from Albrecht Dreß  ---
(In reply to robert from comment #3)
> PostgreSQL: the connections could be set in
> Edit > Databases > Advanced Settings
> You have to switch named Parameter to '?'.

As I mentioned in my original post, this option is inactive (greyed out) for
the PostgreSQL driver.  Or did I miss your point?

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

rob...@familiegrosskopf.de changed:

   What|Removed |Added

 CC||rob...@familiegrosskopf.de

--- Comment #3 from rob...@familiegrosskopf.de ---
(In reply to Albrecht Dreß from comment #2)
> Thanks a lot for your fast reply!
> 
> (In reply to Drew Jensen from comment #1)
> > Went ahead and hand change that in the content.xml and attached the updated
> > file here. Give it a try and see if that clears your problem and let us
> > know. 
> 
PostgreSQL: the connections could be set in
Edit > Databases > Advanced Settings
You have to switch named Parameter to '?'.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

--- Comment #2 from Albrecht Dreß  ---
Thanks a lot for your fast reply!

(In reply to Drew Jensen from comment #1)
> Went ahead and hand change that in the content.xml and attached the updated
> file here. Give it a try and see if that clears your problem and let us
> know. 

Unfortunately, this doesn't help.  I can confirm content.xml now contains
“”, but opening
the form still throws the same PostgreSQL error as above.

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs

[Libreoffice-bugs] [Bug 119569] Open Sub-Form causes PostgreSQL error

[bugzilla-daemon]

https://bugs.documentfoundation.org/show_bug.cgi?id=119569

Drew Jensen  changed:

   What|Removed |Added

 CC||drewjensen.in...@gmail.com

--- Comment #1 from Drew Jensen  ---
Created attachment 144519
  --> https://bugs.documentfoundation.org/attachment.cgi?id=144519=edit
updated odb file

Believe this is a problem with the db:parameter-name-substitution property
having been erroneously set to 'false' with this version of the application; 

Went ahead and hand change that in the content.xml and attached the updated
file here. Give it a try and see if that clears your problem and let us know. 

Thanks

-- 
You are receiving this mail because:
You are the assignee for the bug.___
Libreoffice-bugs mailing list
Libreoffice-bugs@lists.freedesktop.org
https://lists.freedesktop.org/mailman/listinfo/libreoffice-bugs