When opening a form, in LibreOffice Base, I get the following error box displayed The data content could not be loaded. SQL Status: S1009 Parameter index out of range (1 > number of parameters, which is 0). The form has worked on all versions of LibreOffice up to and including 6.0.4 I'm using Slackware 14.2 Mariadb 10.0.35 mysql-connector-java-5.1.39
Created attachment 142656 [details] git bisect log Attached is a git bisect log. The first bad commit is described as - a29d97e6ddab8ec002ba9827bd5fc874117712e0 is the first bad commit commit a29d97e6ddab8ec002ba9827bd5fc874117712e0 Author: Lionel Elie Mamane <lionel@mamane.lu> Date: Tue Jan 2 22:49:31 2018 +0100 tdf#104986 move named parameters substitution into generic layer Previously, drivers were responsible for making the substitution themselves. In practice they all (Firebird, ODBC and JDBC) used the LibreOffice SQL parser to parse the SQL statement and do the substitution. This had a few negative consequences: * The substitition was applied to _all_ SQL commands, including queries having the "execute SQL directly" bit set. Which means that the SQL was _not_ sent to the DBMS exactly as typed by the user. Even if there was no substitution to be made, since the SQL command was always round-tripped through the parser, thus "normalising" it (which is what led to tdf#104986). * "execute SQL directly" queries "magically" behaved slightly differently depending on whether the LibreOffice SQL parser succeeded in parsing them or not. Change-Id: Ieedc643f33467435a139e2565a01e6f398af041f Reviewed-on: https://gerrit.libreoffice.org/47283 Tested-by: Jenkins <ci@libreoffice.org> Reviewed-by: Lionel Elie Mamane <lionel@mamane.lu> :040000 040000 21498e671d4de80e991c7b51dec9934941e385a2 9ad610a6a942238862876f30a23e287fdbbda9b4 M connectivity :040000 040000 e9abefeaa3966bf120917e674b181db122593c34 7e986739f2e48cc68966a085f241f46fd2ec80ae M include
Hello Alex, Could you please attach the document you're using to reproduce this problem ?
Created attachment 142660 [details] Base application and mysql data tar gz file with database document and sql data for mariadb/mysql Steps to recreate problem in LibreOffice 6.1 1. untar the attached btuc.mysql.tar.gz 2. Create the btuc database using the attached mysqldump file 3. Open attached btuc_mysql.odb file with libreoffice 4. Register btuc database in LibreOffice 5. Open the form FMBTUC_DATA_ENTRY 6. Error Box is displayed.
I've created an equivalent application using the embedded hsqldb database. The application behaves as expected - no error box.
The commit that caused this regression was to fix bug tdf#104986. In that bug report there's a discussion (In reply to Lionel Elie Mamane from comment #18) > There is probably a much more robust way to do it... One only has to grossly > lex the SQL, just keeping track of whether on is inside a '-delimited string > or a "-delimited identifier. And outside of that, replace any word that > starts with ":" with "?". In GDB of tdf#118112 I see this Thread 7 "UNO AffineBridg" hit Breakpoint 1, connectivity::java_sql_Connection::prepareStatement (this=0x35f5350, sql="SELECT * FROM `btuc`.`tblBTUC_details` WHERE ( `btuc`.`tblBTUC_details`.`Contact_ID` = :link_from_Contact_ID )") at /home/alex/master/connectivity/source/drivers/jdbc/JConnection.cxx:474 474 java_sql_PreparedStatement* pStatement = new java_sql_PreparedStatement( t.pEnv, *this, sql ); Does this mean that :link_from_Contact_ID is being changed to ? and causing the bug that I'm seeing.
Either: 1) The data source of the form is configured as "execute directly: yes" / "analyse SQL command: no", and then, well, it was kinda based on a bug that it worked before. Or: 2) Looks like on the contrary :link_from_Contact_ID is _not_ being replaced by "?" anymore, and that's your problem. Assuming my guess is correct, then the reason is probably that ParameterNameSubstitution is not set to "true", and the solution is to set ParameterNameSubstitution to "true". It needs to be set in existing files manually / by macro (sorry....), and to set it in new files, connectivity/registry/mysql/org/openoffice/Office/DataAccess/Drivers.xcu needs the same change as https://gerrit.libreoffice.org/#/c/47283/5/connectivity/registry/firebird/org/openoffice/Office/DataAccess/Drivers.xcu Julien, you want to take this?
I submitted this patch to gerrit: https://gerrit.libreoffice.org/#/c/55771/1 but it doesn't seem to work. See comment in gerrit. Anyway, I could reproduce this, let's put this to NEW.
(In reply to Lionel Elie Mamane from comment #6) > the reason is probably that ParameterNameSubstitution is not set to "true", > and the solution is to set ParameterNameSubstitution to "true". It needs to > be set in existing files manually / by macro (sorry....), Refer to https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c11 https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15 for how to do that by editing the files in the odb manually or by macro, respectively.
Julien Nabet committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c179e8a7ea273a48713192c3ba7dfea313149cd8 tdf#118112: Use ParameterNameSubstitution for Mysql 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.
Just tested this. Can see the patch in my git log. > commit c179e8a7ea273a48713192c3ba7dfea313149cd8 > Author: Julien Nabet <serval2412@yahoo.fr> > Date: Wed Jun 13 22:16:44 2018 +0200 > > tdf#118112: Use ParameterNameSubstitution for Mysql > > Change-Id: Ic48aee378d0a89466c49de85baa96a3bea127d2f > Reviewed-on: https://gerrit.libreoffice.org/55771 > Tested-by: Jenkins <ci@libreoffice.org> > Reviewed-by: Julien Nabet <serval2412@yahoo.fr> Have recompiled. Still have the same problem. Is there anything else I'm supposed to have done?
(In reply to Alex Kempshall from comment #10) > Just tested this. > > Have recompiled. > > Still have the same problem. Is there anything else I'm supposed to have > done? See comment 8
Thanks for pointing me in the right direction. Now works as expected.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-6-1": http://cgit.freedesktop.org/libreoffice/core/commit/?id=90d77c7d18050437d47ba967e4c4ad7e523ced0e&h=libreoffice-6-1 tdf#118112: Use ParameterNameSubstitution for Mysql It will be available in 6.1.0.1. 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.
(In reply to Lionel Elie Mamane from comment #8) > ... > Refer to > https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c11 > https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15 > for how to do that by editing the files in the odb manually or by macro, > respectively. Just for information, I tested both methods (even if first one has already been confirmed), I confirm it works.
Lionel: about your comment https://gerrit.libreoffice.org/#/c/55771/, "force ParameterNameSubstitution to true", I checked YDriver.cxx and there's already the property at true (see https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/mysql/YDriver.cxx#211) I didn't test but it seems that default value of "ParameterNameSubstitution" is in https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/dataaccess/ModelImpl.cxx#992 Thought it might help for tdf#117589 put in see also if the by default value is true.
(In reply to Julien Nabet from comment #15) > Lionel: about your comment https://gerrit.libreoffice.org/#/c/55771/, "force > ParameterNameSubstitution to true", I checked YDriver.cxx and there's > already the property at true (see > https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/mysql/ > YDriver.cxx#211) Hmm... Maybe the bug then is that if ParamterNameSubstitution is _already_ in info, it is added a second time, and only the first value takes effect. In that case, IMHO it needs to be *changed* in info, not appended to it. Can you check if my guess is correct? > I didn't test but it seems that default value of "ParameterNameSubstitution" > is in > https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/dataaccess/ > ModelImpl.cxx#992 > Thought it might help for tdf#117589 put in see also if the by default value > is true. No, that's a LibreOffice-wide default for all drivers, I wouldn't touch it for the needs of a few drivers.
Julien Nabet committed a patch related to this issue. It has been pushed to "libreoffice-6-0": http://cgit.freedesktop.org/libreoffice/core/commit/?id=4de33eb2b5a802e8eda36c5806913efeead68441&h=libreoffice-6-0 tdf#118112: Use ParameterNameSubstitution for Mysql It will be available in 6.0.6. 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.
Is my understanding correct in that this patch - 1. will stop the problem in new applications; 2. applications built before this patch will have to be fixed by manually applying one of the work arounds. If the above is true can we make a comment in the change log and/or release notes to that effect and also provide a macro in LibreOffice Macros & Dialogs that can be manually run to fix the problem in old applications.
A polite ping to Julien Nabet: Is this bug fixed? if so, could you please close it as RESOLVED FIXED ? Otherwise, Could you please explain what's missing? Thanks
Xisco: I must recognize I don't know. We may say it's fixed because the brand new files won't have the pb + we can fix this with a macro. We may say it's not fixed yet (or partly fixed) because we'd like "force ParameterNameSubstitution to true" and I don't know how to do it.
I manually applied one of the work arounds and it fixed the problem for me. I've not built an application from scratch to see whether the problem never occurs.
Just read this in the users-mailinglist: OLD SETTING:<db:driver-settings db:system-driver-settings="" db:base-dn="" db:parameter-name-substitution="false"/> NEW SETTING:<db:driver-settings db:system-driver-settings="" db:base-dn=""/> You have to change the content of content.xml inside the *.odb-file for *.odb-files, which are created before 6.1. So we couldn't say the bug has been solved. http://document-foundation-mail-archive.969070.n3.nabble.com/LibreOffice-Base-now-does-not-apply-named-parameter-substitution-after-upgrade-from-LO-6-0-to-LO-6-1s-td4246734.html
While waiting for a better way to convert the .odb file, can someone please post the steps to extract and edit the content.xml file manually? Thanks!
(In reply to david from comment #23) > While waiting for a better way to convert the .odb file, can someone please > post the steps to extract and edit the content.xml file manually? Thanks! Try the following with a copy of your *.odb-file: Open the *.odb-file with a packing-program ("Zip", here on Linux it's called "Ark"). content.xml should be moved outside the *.odb-file (Unzip only this file). Take a simple text-editor (I use Kate in Linux ...) and open content.xml. Search for db:parameter-name-substitution="false" and delete this. Save the new content.xml Delete old content.xml in the *.odb-file with the packing-program. Move the new content.xml into the *.odb-file. Hope this works for you. Regards Robert
Thanks Robert! Its working now. : )
I reported this against #119569, which may have been the wrong bug, so I'm repeating here. 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. 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.
Having read through more of the comments above I can confirm that deleting db:parameter-name-substitution="false" does work for me, so it's no longer 'critical' for me. Editing content.xml turned out to be slightly tricky because on my system using xml editors such as kate or geany, it's all one line, with no xml structure. That fix could be a lot of work for a lot of users of existing databases. If this is the only fix I suggest the ability to do this from within Base itself is added to the database edit menu.
(In reply to tim from comment #27) > Editing content.xml turned out to be slightly tricky because on my system > using xml editors such as kate or geany, it's all one line, with no xml > structure. Using the macro could be considered easier. See https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15
I think there may be users for which some of this could be difficult. I still think some for of easy to understand migration tool for pre 6.1 odbs should be provided. Many users may not find it easy to find stuff in bugzilla (I struggle at times), let alone find and execute a macro or edit an odb.
(In reply to tim from comment #29) > I think there may be users for which some of this could be difficult. I > still think some for of easy to understand migration tool for pre 6.1 odbs > should be provided. Yes. No one disputes your statement, nor said anything to the contrary. Possibly, the settings the LibreOffice *knows* are the correct one should just overwrite the ones in the file...
(In reply to Lionel Elie Mamane from comment #30) > (In reply to tim from comment #29) > > I think there may be users for which some of this could be difficult. I > > still think some for of easy to understand migration tool for pre 6.1 odbs > > should be provided. > > Yes. No one disputes your statement, nor said anything to the contrary. > Possibly, the settings the LibreOffice *knows* are the correct one should > just overwrite the ones in the file... Yes, apologies if I was misunderstanding the drift of the suggestions and commments.
I just noticed that using MYSQL(Native) avoids this problem. I was using MYSQL(JDBC) when I came across it.
(In reply to tim from comment #32) > I just noticed that using MYSQL(Native) avoids this problem. I was using > MYSQL(JDBC) when I came across it. I have recently installed MySQL and MySQL workbook. I use LibreOffice 6.0.6.2 and 6.1.4.1 downloaded from LibreOffice website. I also have two Java connectors for MySQL (8.0.13 and 5.1.23) which I downloaded from the MySQL website in the last couple of days. Both of them work with LO 6.0.6.2 but give the same error message with 6.1.4.1.
*** Bug 121997 has been marked as a duplicate of this bug. ***
(In reply to Dan Lewis from comment #33) > (In reply to tim from comment #32) > > I just noticed that using MYSQL(Native) avoids this problem. I was using > > MYSQL(JDBC) when I came across it. > > I have recently installed MySQL and MySQL workbook. I use LibreOffice > 6.0.6.2 and 6.1.4.1 downloaded from LibreOffice website. I also have two > Java connectors for MySQL (8.0.13 and 5.1.23) which I downloaded from the > MySQL website in the last couple of days. Both of them work with LO 6.0.6.2 > but give the same error message with 6.1.4.1. I have recently downloaded and installed LO 6.2.0.1 RC1. When using the native MySQL connector, I still get this error message. I also get it when using both JDBC connectors (8.0.13, 5.1.23). Both of them work properly for LO 6.0.6.3 and 6.0.7.3.
Same effect with 6.2 and Mariadb 10.1.37 on debian 9.6 following Dan Lewis falling back to 6.0.7 made my day Thanks !!
It's the same with LO 6.1 and 6.2 and Mysql 5.7 on Linux Mint 18.2. I also have this after upgrading LO. My subforms don't show anything anymore and I get the same error message, using the native MySQL connector or JDBC connectors (8.0.13, 5.1.23). I tried to find a solution that I could understand but no success. It all worked properly up to 6.0.7.3. So the only solution was (thnx Dan Lewis) falling back to 6.0.7.3 :-( It's an important database with many forms, so no more upgrading until an easy to understand solution is available. Would be really nice if a migration tool of some kind for pre 6.1 odbs could be provided.
In comment 18 I observed that this patch - 1. will stop the problem in new applications; 2. applications built before this patch will have to be fixed by manually applying one of the workarounds. In comment 21, July 2018, I reported that I applied one of the workarounds to an existing application. Not had the problem since. The LO version I was using then was 6.0.4. I'm now on LO 6.2.0
Dan, Erich, Roland, Alex: Just for the remind, as Lionel indicated in the comment 28, he created a macro to convert odb files created before this patch. See https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15 Of course, make a backup of your file before applying it. The parameter management was buggy before. LO code has been fixed but the ODB files created before the fix need a manual fix, it's what the Lionel's macro does.
*** Bug 123999 has been marked as a duplicate of this bug. ***
On upgrading from 6.5 to 6.2.2 this problem re-occurred for me on an old odb that was working fine. Running the macro fixed it. I have had to revert to using jdbc since the mysql(native) connector is not appearing for my mariadb database - I don't know if these things are related.
Sorry (I wish I could edit posts!) - for 6.5 read 6.1.5.
And note that it seems a time change can trigger the same problem, although here in the UK we haven't changed the clocks yet in 2019 (and maybe never will after 2021...). See https://bugs.documentfoundation.org/show_bug.cgi?id=123999
*** Bug 124427 has been marked as a duplicate of this bug. ***
I still encounter this problem, although with a different message starting with "Could not set parameter at position 1 (values was..." with Libo 6.2.2.2 (Windows 64 bit) with MariaDB and the JDBC connector (a native connector unfortunately still does not seem to be available); erich reported nearly the same in comment 36. The macro provided (see comment 28) works fine for me, now that I know, but this could be a problem for new users, indeed it could be a show-stopper.
(In reply to Gerhard Weydt from comment #45) > ... > The macro provided (see comment 28) works fine for me, now that I know, but > this could be a problem for new users, indeed it could be a show-stopper. I noticed the change was in release notes (see https://wiki.documentfoundation.org/ReleaseNotes/6.1) but not the way to fix it with the macro.
Just let you know: The problem still exists with 6.3 beta using an existing (older) .odb So Lionels macro treatment becomes a MUST using existing .odb's in future ??? as Julien says in comment-46 what about all the other users who dont find the way into this forum ? Erich
I raised this bug report initially. As the problem has been fixed going forward, users creating new applications shouldn't encounter the problem. I've not tested this assumption, just accept what the developers have said. If users are still using old applications that haven't yet encountered the problem they must be using an old version of LibreOffice. Those should be a diminishing number. If a user encounters the problem, they have either inherited an application or written it themselves, in the latter case I would have thought they are reasonably expert and would find themselves to this fix.
This issue was incorrectly moved to UNCONFIRMED
I created a completely new mariadb database today using Version: 6.2.2.2 Build ID: 1:6.2.2-0ubuntu2 CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: x11; Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB Calc: threaded I still get the parameter error. What am I doing wrong?
I am using mysql-connector-java.jar, and com.mysql.jdbc.Driver. (The mariadb ones still give me other problems, so I am avoiding them for now). This is completely new odb file, so I did not expect to get this problem again. I know how to fix it, but surely it should not be happening?
(In reply to tim from comment #51) > ... > This is completely new odb file, so I did not expect to get this problem > again. I know how to fix it, but surely it should not be happening? On connectivity/registry, there are 2 subdirs related to mysql: - mysqlc - mysql_jdbc Both have "ParameterNameSubstitution" blocks in "Properties" superblock with "true" value. Re reading "connectivity/registry/README", I noted: " Property: actual setting with its underlying type and its default value Feature: a boolean that says "let the user change that setting in the Advanced Settings UI" " So it shouldn't be necessary to add "ParameterNameSubstitution" in "Features" parts. Did you rename your LO directory profile before creating your brand new odb file?
(In reply to Julien Nabet from comment #52) > (In reply to tim from comment #51) > > ... > > This is completely new odb file, so I did not expect to get this problem > > again. I know how to fix it, but surely it should not be happening? > > On connectivity/registry, there are 2 subdirs related to mysql: > - mysqlc > - mysql_jdbc > > Both have "ParameterNameSubstitution" blocks in "Properties" superblock with > "true" value. > Re reading "connectivity/registry/README", I noted: > " > Property: actual setting with its underlying type and its default value > > Feature: a boolean that says "let the user change that setting in the > Advanced Settings UI" > " > So it shouldn't be necessary to add "ParameterNameSubstitution" in > "Features" parts. > > Did you rename your LO directory profile before creating your brand new odb > file? You've slightly lost me there. Are you saying I need to start a completely new LO user profile? If so, others are also going to encounter this problem every time they create new databases.
(In reply to tim from comment #53) > ... > > Did you rename your LO directory profile before creating your brand new odb > > file? > You've slightly lost me there. Are you saying I need to start a completely > new LO user profile? If so, others are also going to encounter this problem > every time they create new databases. I'm not sure here but can you give it a try just to know if it helps?
(In reply to Julien Nabet from comment #54) > (In reply to tim from comment #53) > > ... > > > Did you rename your LO directory profile before creating your brand new odb > > > file? > > You've slightly lost me there. Are you saying I need to start a completely > > new LO user profile? If so, others are also going to encounter this problem > > every time they create new databases. > > I'm not sure here but can you give it a try just to know if it helps? I renamed my old profile, so a new one was created (LO 6.2.2.2). I created a new base document, using mysql jdbc (and Driver) to a test mariadb database. I created a new form (and subform). I got the same parameter error.
(In reply to tim from comment #55) > ... > I renamed my old profile, so a new one was created (LO 6.2.2.2). > > I created a new base document, using mysql jdbc (and Driver) to a test > mariadb database. > > I created a new form (and subform). > > I got the same parameter error. Did you try to apply macro even if it shouldn't be necessary? Indeed, I expect you still reproduce the problem even when apply macro. Also, would it be possible you attach the odb file with a script to create a minimum database to reproduce this?
(In reply to Julien Nabet from comment #56) > (In reply to tim from comment #55) > > ... > > I renamed my old profile, so a new one was created (LO 6.2.2.2). > > > > I created a new base document, using mysql jdbc (and Driver) to a test > > mariadb database. > > > > I created a new form (and subform). > > > > I got the same parameter error. > > Did you try to apply macro even if it shouldn't be necessary? Indeed, I > expect you still reproduce the problem even when apply macro. > > Also, would it be possible you attach the odb file with a script to create a > minimum database to reproduce this? I will shortly attach: Test.sql is the sql for my (empty) test database TestNew4.odb is a test odb with no macro having been run, giving the error when the only form is open TestNew5.odb is the same, but the macro is included and has been run, and no error is detected when opening the only form.
Created attachment 152485 [details] Sql to create test database
Created attachment 152486 [details] odb file returning error when form opened
Created attachment 152487 [details] odb file with macro having been run - no error
Since running the macro solves the problem, it really looks like the bug is back, at file creation time.
Thank you Tim for your feedback. I don't see any location in LO code which could explain this and you indicate your LO profile is new. I must recognize I'm stuck.
(In reply to Julien Nabet from comment #62) > Thank you Tim for your feedback. > > I don't see any location in LO code which could explain this and you > indicate your LO profile is new. > I must recognize I'm stuck. I should clarify that after the test yesterday I restored my LO profile (too many settings I don't want to lose). TestNew4.dob is as it was yesterday, created with a new profile. Today I copied it to TestNew5.odb, added the macro and ran it, but that was under my old profile. If I new to redo TestNew5.odb with a new profile, let me know. All on: Version: 6.2.2.2 Build ID: 1:6.2.2-0ubuntu2 CPU threads: 8; OS: Linux 5.0; UI render: default; VCL: x11; Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB Calc: threaded
On pc Debian x86-64 with master sources updated today, I gave a new try. First I wanted to recreate the bug from scratch so I created a brand odb file. On MariaDBServer, I got a simple table with ID (Integer primary key), Test (Varchar) containing just 2 values: 1, "Test1", 2 "Test2" With Mysql direct and Mysql JDBC, here what I did: - create a query: SELECT "Test" FROM "Table1" WHERE "ID" = :id - create a form from query with by default options. I unzip the 2 odb files generated from this process, but found no "ParameterNameSubstitution" at all. Waht step did I miss so this param is present on the content.xml?
(In reply to Julien Nabet from comment #64) > On pc Debian x86-64 with master sources updated today, I gave a new try. > First I wanted to recreate the bug from scratch so I created a brand odb > file. > On MariaDBServer, I got a simple table with ID (Integer primary key), Test > (Varchar) containing just 2 values: 1, "Test1", 2 "Test2" > > > With Mysql direct and Mysql JDBC, here what I did: > - create a query: > SELECT "Test" FROM "Table1" WHERE "ID" = :id > > - create a form from query with by default options. > > I unzip the 2 odb files generated from this process, but found no > "ParameterNameSubstitution" at all. > > Waht step did I miss so this param is present on the content.xml? Sorry, I can't help with that. It's way beyond my knowledge.
*** Bug 132377 has been marked as a duplicate of this bug. ***
The reporter of tdf#132377 indicates that he doesn't reproduce the problem with 6.4.3, a brand new LO profile and a brand new odb file, see https://bugs.documentfoundation.org/show_bug.cgi?id=132377#c4 For those who encountered this problem, could you: - uninstall completely LO - rename your LO profile - install LO 6.4.3 - test again from a brand new odb file ?
(In reply to Julien Nabet from comment #67) > The reporter of tdf#132377 indicates that he doesn't reproduce the problem > with 6.4.3, a brand new LO profile and a brand new odb file, see > https://bugs.documentfoundation.org/show_bug.cgi?id=132377#c4 > > For those who encountered this problem, could you: > - uninstall completely LO > - rename your LO profile > - install LO 6.4.3 > - test again from a brand new odb file > ? I purged my LO, renamed my profile. Installed LO 6.4.3. The old TestNew4.odb still returns the parameter error. A brand new odb (still using JDBC) to the same old mariadb test database does not return the error. It's been a while since I created any new forms, and the form design wizard doesn't seem to exist. Whether that's an ubuntu version issue or more general I don't yet know. I copied the old form from TestNew4.odb to the new file, since I couldn't remember how to set it up the same way as before. My LO version is: Version: 6.4.3.2 Build ID: 1:6.4.3-0ubuntu0.19.10.1 CPU threads: 8; OS: Linux 5.3; UI render: default; VCL: gtk3; Locale: en-GB (en_GB.UTF-8); UI-Language: en-GB Calc: threaded
(In reply to tim from comment #68) > ... > I purged my LO, renamed my profile. Installed LO 6.4.3. > > The old TestNew4.odb still returns the parameter error. It's expected since the fix will work only for the odb files generated from LO version which is indeed fixed and LO 6.4.3 should be ok. For old odb, you must use the macro proposed by Lionel here: https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15 > > A brand new odb (still using JDBC) to the same old mariadb test database > does not return the error. It's normal since the pb isn't the MariaDB or Mysql database. The new odb is generated from a version of LO which is fixed. > > It's been a while since I created any new forms, and the form design wizard > doesn't seem to exist. Whether that's an ubuntu version issue or more > general I don't yet know. I copied the old form from TestNew4.odb to the > new file, since I couldn't remember how to set it up the same way as before. > or as I said, just run the macro quoted on your odb file. In brief, if you don't reproduce the problem with a brand new odb file, it shows the bug is indeed fixed. With old odb files, just run the macro once and it'll fix the odb.
(In reply to Julien Nabet from comment #69) > (In reply to tim from comment #68) > > ... > > I purged my LO, renamed my profile. Installed LO 6.4.3. > > > > The old TestNew4.odb still returns the parameter error. > It's expected since the fix will work only for the odb files generated from > LO version which is indeed fixed and LO 6.4.3 should be ok. > For old odb, you must use the macro proposed by Lionel here: > https://bugs.documentfoundation.org/show_bug.cgi?id=117053#c15 > > > > > A brand new odb (still using JDBC) to the same old mariadb test database > > does not return the error. > It's normal since the pb isn't the MariaDB or Mysql database. The new odb is > generated from a version of LO which is fixed. > > > > > It's been a while since I created any new forms, and the form design wizard > > doesn't seem to exist. Whether that's an ubuntu version issue or more > > general I don't yet know. I copied the old form from TestNew4.odb to the > > new file, since I couldn't remember how to set it up the same way as before. > > > or as I said, just run the macro quoted on your odb file. > > In brief, if you don't reproduce the problem with a brand new odb file, it > shows the bug is indeed fixed. > > With old odb files, just run the macro once and it'll fix the odb. Julian, Yes, it's as expected. I was just running the test as you requested to confirm that it works as you expect. Having run the macro on my old databases quite a long time ago, all still works normally.
Thank you for your feedback. Let's put this to FIXED then.
(In reply to Julien Nabet from comment #71) > Thank you for your feedback. > Let's put this to FIXED then. Good. I just hope that any with older databases that haven't yet moved to newer versions of LO manage to find the macro.
*** Bug 128554 has been marked as a duplicate of this bug. ***
*** Bug 120091 has been marked as a duplicate of this bug. ***
SUMMARY OF THIS BUG AND HOW TO FIX IT PROBLEM: Some time ago subforms linking (forms that have subforms) got broken in existing, working *.odb files when LO was revised, i.e. the newer LO version permanently broke these existing older *.odb files. Newly created LO subforms don't have this problem. Only older *.odb files run on newer LO. Newer LO versions don't fix the older subforms. So your older base subforms are forever broken unless and until you manually fix them. >>>> To fix them you have to manually edit xml in them, as described below. EXAMPLE: In my case I got this error message: Could not set parameter at position C1 (values was 7) Query - conn:73(M) - "SELECT * FROM `engine_maintenance`.`log, vehicle maintance` WHERE ( `engine_maintenance`.`log, vehicle maintance`.`Equipment ID` = :link_from_Equipment_ID ) ORDER BY `Date` ASC" at ./connectivity/source/drivers/jdbc/Object.cxx:173 The problem is in this clause: WHERE ( `engine_maintenance`.`log, vehicle maintance`.`Equipment ID` = :link_from_Equipment_ID ) And specifically, but don't quote me on this, in the ":link...". SOLUTION: You fix your .odb files one of several ways. The method I used was described above in #24 by Robert Großkopf: https://bugs.documentfoundation.org/show_bug.cgi?id=118112#c24 0) Made a backup copy of my broken database file, let's call it foo.odb 1) I opened this foo.odb with Archive Manager (in GUI). 2) Right clicked on settings.xml and clicked Extract. Keep this screen open. 3) Opened the extracted settings.xml with kate to edit it. 4) Searched for the string; db:parameter-name-substitution="false" 5) Deleted the text: db:parameter-name-substitution="false" 6) Saved settings.xml, and closed kate. 7) Returned to the Archive Manager screen. 8) Dragged my updated settings.xml into the still open Archive Manager screen, thus replacing settings.xml with the new one. 9) Opened foo.odb with LO and now the subform works properly again. (There is also a "macro" talked about as a workaround, but the description of it and how to use it is obscure, at least to me, and the link to info on it is crossed out above, further obscuring it.) _____ FEEDBACK! Breaking older things is not a best practice for any software development. It would be one thing to break it and also have the newer versions detect the problem and fix it, but that's not what happened. Few I think will ever find this bug log and successfully apply the fixes needed. I for one, waited for years to see if this bug would ever get fixed, all the while staying with an older version of LO which worked properly. Every time I tried to update LO I would run into this stupid problem again. Anger built but it served no purpose. Finally, desperately taking another look at this I found this huge bug log above, and was able to figure out how to make my older databases work again. Thank you Robert for your steps to fix this above and restated again here at the current bottom of this log, in hopes it might make it easier to fix one's database files
SUMMARY OF THIS BUG AND HOW TO FIX IT [CORRECTED*1] * Rev 1) you must edit the file named content.xml below, not settings.xml ! PROBLEM: Some time ago subforms linking (forms that have subforms) got broken in existing, working *.odb files when LO was revised, i.e. the newer LO version permanently broke these existing older *.odb files. Newly created LO subforms don't have this problem. Only older *.odb files run on newer LO. Newer LO versions don't fix the older subforms. So your older base subforms are forever broken unless and until you manually fix them. >>>> To fix them you have to manually edit xml in them, as described below. EXAMPLE: In my case I got this error message: Could not set parameter at position C1 (values was 7) Query - conn:73(M) - "SELECT * FROM `engine_maintenance`.`log, vehicle maintance` WHERE ( `engine_maintenance`.`log, vehicle maintance`.`Equipment ID` = :link_from_Equipment_ID ) ORDER BY `Date` ASC" at ./connectivity/source/drivers/jdbc/Object.cxx:173 The problem is in this clause: WHERE ( `engine_maintenance`.`log, vehicle maintance`.`Equipment ID` = :link_from_Equipment_ID ) And specifically, but don't quote me on this, in the ":link...". SOLUTION: You fix your .odb files one of several ways. The method I used was described above in #24 by Robert Großkopf: bugs.documentfoundation.org/show_bug.cgi?id=118112#c24 0) Made a backup copy of my broken database file, let's call it foo.odb 1) I opened this foo.odb with Archive Manager (in GUI). 2) Right clicked on content.xml and clicked Extract. Keep this screen open. 3) Opened the extracted content.xml with kate to edit it. 4) Searched for the string; db:parameter-name-substitution="false" 5) Deleted the text: db:parameter-name-substitution="false" 6) Saved content.xml, and closed kate. 7) Returned to the Archive Manager screen. 8) Dragged my updated content.xml into the still open Archive Manager screen, thus replacing settings.xml with the new one. 9) Opened foo.odb with LO and now the subform works properly again.