[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 Robert Großkopf changed: What|Removed |Added See Also||https://bugs.documentfounda ||tion.org/show_bug.cgi?id=14 ||0298 --- Comment #11 from Robert Großkopf --- (In reply to Lionel Elie Mamane from comment #9) > > oSQL_Statement.EscapeProcessing = False > You are right, works well in the attached database. Don't know why it has something to do with escaping, because there is changed a '*' for a '%' … > As to "Tools/SQL", it would be useful to have in this dialog a checkbox to > enable/disable escape processing, too. The UI can present it as "Run SQL > command directly" to be consistent with the Query Design UI. TOOLS → SQL should be improved a little bit. See bug 140298. So it would be good to add such a checkbox. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 --- Comment #10 from Robert Großkopf --- (In reply to Julien Nabet from comment #8) > (gdb) p _rSQL > $4 = "SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%*'" > (gdb) n > 486 return m_xAggregateStatement->execute( sSQL ); > (gdb) p sSQL > $5 = "SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%%'" This is the reason for the buggy behavior: Not the Escaping, but the function, which returns '%%' instead of '%*'. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 Lionel Elie Mamane changed: What|Removed |Added Ever confirmed|1 |0 Status|NEW |UNCONFIRMED --- Comment #9 from Lionel Elie Mamane --- (In reply to Robert Großkopf from comment #0) > Expected behavior: Query_direct_SQL should show the same content as Tools → > SQL and queries in a macro. No, that's not the documented behaviour. In the macro, to disable escape processing (that is, to do the query as "direct SQL"), insert a line oSQL_Statement.EscapeProcessing = False after oSQL_Statement = oConnection.createStatement() but before oResult = oSQL_Statement.executeQuery(stSql) As to "Tools/SQL", it would be useful to have in this dialog a checkbox to enable/disable escape processing, too. The UI can present it as "Run SQL command directly" to be consistent with the Query Design UI. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 --- Comment #8 from Julien Nabet --- At least for Tools/SQL the escape processing is done here: dbaccess/source/core/api/statement.cxx:480 Here's a part of gdb session. OStatement::execute (this=0x8661500, _rSQL="SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%*'") at dbaccess/source/core/api/statement.cxx:480 480 MutexGuard aGuard(m_aMutex); (gdb) n 481 ::connectivity::checkDisposed(OComponentHelper::rBHelper.bDisposed); (gdb) n 483 disposeResultSet(); (gdb) n 485 OUString sSQL( impl_doEscapeProcessing_nothrow( _rSQL ) ); (gdb) p _rSQL $4 = "SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%*'" (gdb) n 486 return m_xAggregateStatement->execute( sSQL ); (gdb) p sSQL $5 = "SELECT * FROM \"Person\" WHERE \"Name\" LIKE '%%'" -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 Julien Nabet changed: What|Removed |Added Ever confirmed|0 |1 CC||lio...@mamane.lu, ||serval2...@yahoo.fr Version|unspecified |7.1.5.2 release Status|UNCONFIRMED |NEW --- Comment #7 from Julien Nabet --- On pc Debian x86-64 with master sources updated today, I could reproduce this. (With https://cgit.freedesktop.org/libreoffice/core/commit/?id=5b98dd53c7dc101d3a5ff693d3f0520ec1abd3d1, we can now test again Tools/Sql) I put Lionel on cc but I trust Robert's judgement since, even if he's not a dev, is a functional reference for Base part. Let's put 7.1.5 as earliest version even if we're pretty sure it's older. If someone wants to give it a try with an older version and confirm the behaviour, don't hesitate to update this value. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 --- Comment #6 from Robert Großkopf --- (In reply to m.a.riosv from comment #5) > I didn't find exactly, but should be possible to run the sql as direct in a > macro. > In https://wiki.documentfoundation.org/images/b/b0/BH5009-Macros.pdf, page > 71 shows how to enable direct sql for mail-merge. It's only mail merge and I haven't tested it works not the same way as SQL in macros will work. Mail merge needs to know here, if it should use a table directly (by tablename), a query directly (by name of the query) or SQL-code, which should be written separately instead of the name of a query, as I have written in the Base Handbook. Note: I have written most of the content of Base Handbook. Base Handbook is translated to English from the German Base Handbuch. When I have written all these I haven't detected there is a difference between direct SQL in a query (which is real direct) and SQL in macros and Tools → SQL. I thought all this will be the same - but it isn't. If there is a real good argument for the difference between real direct SQL in a query and SQL in macros and Tools → SQL I have to change the content of the (German) Handbook here. My opinion: The difference, which appears here, isn't intended. It is a buggy behavior. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 --- Comment #5 from m.a.riosv --- I didn't find exactly, but should be possible to run the sql as direct in a macro. In https://wiki.documentfoundation.org/images/b/b0/BH5009-Macros.pdf, page 71 shows how to enable direct sql for mail-merge. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 --- Comment #4 from Robert Großkopf --- (In reply to m.a.riosv from comment #3) > So you are asking for a change of behavior. I think it works as implemented, > even we don't like it. Yes, the behavior should be changed. We had a discussion in a German forum. Someone asks for a code of such a query and I tested this in the query editor. So I answered: This code will work well in direct SQL, wouldn't work through GUI. Answer: No problem - I will use the code in a macro. And then: It doesn't work in a macro, it doesn't work in Tools → SQL. This doesn't make sense, because you couldn't use the features of the GUI, special direct input of data in a query, with Tools → SQL or macro. You have to send an INSERT then (or UPDATE for changing content). We should see if someone knew why it has been implemented in this way. And also should see how it could be removed, if there couldn't be found any reason for. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 --- Comment #3 from m.a.riosv --- So you are asking for a change of behavior. I think it works as implemented, even we don't like it. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 Robert Großkopf changed: What|Removed |Added Resolution|NOTABUG |--- Status|RESOLVED|UNCONFIRMED --- Comment #2 from Robert Großkopf --- Please let it unconfirmed. There shouldn't be a difference between direct connecting to the database with a query and Tools → SQL. I know it will work with SELECT * FROM "Person" WHERE "Name" LIKE '%\*' ESCAPE '\' This will also work in GUI. HSQLDB accepts also SELECT * FROM "Person" WHERE "Name" LIKE '%*' with the right results, when switching to direct SQL. And this should be the way Tools → SQL and also executing through macro should work: Direct to the database without changing the content of a query. By the way: It is the same buggy behavior when connecting to MariaDB. SELECT * FROM `Person` WHERE `Name` LIKE '%*' will work with direct SQL in the query-editor (and also, for example, in PHPMyAdmin), but only SELECT * FROM `Person` WHERE `Name` LIKE '%$*' ESCAPE '$' will give the same result with Tools → SQL Only queries created through the GUI should change the content so LO Base could work with this queries, for example to input data. Changing content of input though macros and Tools → SQL shouldn't be allowed. -- You are receiving this mail because: You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656
m.a.riosv changed:
What|Removed |Added
CC||miguelangelrv@libreoffice.o
||rg
Status|UNCONFIRMED |RESOLVED
Resolution|--- |NOTABUG
--- Comment #1 from m.a.riosv ---
See the help
https://help.libreoffice.org/latest/en-US/text/sdatabase/02010100.html?=BASE=WIN
search for
Like Escape Sequence: {escape 'escape-character'}
Example: select * from Item where ItemName like 'The *%' {escape '*'}
The example will give you all of the entries where the item name begins with
'The *'. This means that you can also search for characters that would
otherwise be interpreted as placeholders, such as *, ?, _, % or the period.
So in LO you need to escape *, with in direct-sql not.
I think it's not a bug, it is how it works. Maybe there was a reason to
implement such think in base.
--
You are receiving this mail because:
You are the assignee for the bug.
[Libreoffice-bugs] [Bug 143656] Direct SQL: Queries in Tools → SQL and macro give wrong results - direct SQL works well
https://bugs.documentfoundation.org/show_bug.cgi?id=143656 Julien Nabet changed: What|Removed |Added Attachment #174003|Open the database, test the |Open the database, test the description|queries and do the saem |queries and do the same |with Direct → SQL |with Direct → SQL -- You are receiving this mail because: You are the assignee for the bug.
