most people can figure out an sql command syntax to do what they need much easier than they can write a macro to do the same thing because of the requisite concepts of 'subroutine' and 'dimension' which most people dont know about and the different interface you need to work with to write a macro. if we provide these types of query functions it would speed adoption of Base with a lot of smaller simpler project users and make others more productive to boot. 0) we should probably control access rights directly not by limiting functionality of update/insert/drop functions in sql-editor. ----if a non-destructive query is considered a requirement such that update/insert/drop are not acceptable then the macro for these queries should be made simpler to access/setup. ----the problem with adding destructive queries to the sql editor is presumably that it concerns adminsitrators because it makes potential tinkering damage by random users more likely. so the real problem to be tackled should be not IF to add the update/add/remove functionality but HOW to add the add/update/remove query functionality to sql editor while limiting any pottential misuse. so maybe the most productive place to put that access control would be to pull it from the permission structure of the hsqldb/mysql/postgresql database instead of limiting the functionality of the Base database. http://hsqldb.org/doc/2.0/guide/accesscontrol-chapt.html "Access Rights By default, the objects in a schema can only be accessed by the schema owner. But the schema owner can grant privileges (access rights) on the objects to other users or roles. Things can get far more complex, because the grant of privileges can be made WITH GRANT OPTION. In this case, the role or user that has been granted the privilege can grant the privilege to other roles and users. Privileges can also be revoked from users or roles. The statements for granting and revoking privileges normally specify which privileges are granted or revoked. However, there is a shortcut, ALL PRIVILEGES, which means all the privileges that the <grantor> has on the schema object. The <grantor> is normally the CURRENT_USER of the session that issues the statement. The user or role that is granted privileges is referred to as <grantee> for the granted privileges." ... "The INSERT privilege designates the columns into which explicit values can be inserted. To be able to insert a row into the table, the user must therefore have the INSERT privilege on the table, or at least all the columns that do not have a default value." so access rights are part of hsqldb and if we apply them we dont have to limit functionality. 1) further to make it easy to access maybe we should add an "access controls" functionality group to the base gui: (tables, queries, forms, reports) ---> (tables, queries, forms, reports, accesscontrols) or add "access controls" as an item to the secondaryclick menu for tables, queries, forms, reports icons in the Base gui. example of desired use of update/insert/delete is: -- a 'reset field' button which activates an update query changing values in some particular field which have some particular value and changing that value to some other particular value -- an 'add record' button that takes inforamtion entered in display fields and adds a record for them in the database -- a 'delete record' button that removes a record from the database 2) test of update query: version: LibreOffice 3.3.0 OOO330m9 (Build:1) libreoffice-build 3.2.99.2 test file done on mswindows-os make a table: Table1.key, integer, autonumber, primary key Table1.fld-flag, boolean Table1.fld-desc, string Table1.fld-int, integer populate the table: (auto number didnt seem to work in key when enter in table view so entered number manually) key, fld-flag, fld-desc, fld-int 1, No, one, 1 2, YES, two, 2 3, YES, three, 3 4, No, four, 4 when run as query fails: UPDATE "Table1" set "Table1"."fld-flag" = False where "Table1"."fld-flag" = True; (error was: "LibraOffice Base: The data content could not be loaded. Statement does not generate a result set.") when run same sql code as tools-sql-execute_command it succeeds ("Command successfully executed."): UPDATE "Table1" set "Table1"."fld-flag" = False where "Table1"."fld-flag" = True; which is comfirmed by running query as: SELECT "fld-flag", "fld-desc", "fld-int" FROM "Table1" WHERE "fld-flag" = True which correctly returns empty since none were true: key, fld-flag, fld-desc, fld-int 1, No, one, 1 2, NO, two, 2 3, NO, three, 3 4, No, four, 4 if go back in to table view to try to edit boolean flags to true get an error: 'File input/output error: C:\Program Files\Office\New Database1.odb.log in statement [UPDATE "Table1" SET "fld-flag" = ? WHERE "ID-key" = ?] ' but if then exit base and then restart base and reopen the file then there is no problem editing the values in the table view. this data edit error was not reproduceable.
*** Bug 37599 has been marked as a duplicate of this bug. ***
[This is an automated message.] This bug was filed before the changes to Bugzilla on 2011-10-16. Thus it started right out as NEW without ever being explicitly confirmed. The bug is changed to state NEEDINFO for this reason. To move this bug from NEEDINFO back to NEW please check if the bug still persists with the 3.5.0 beta1 or beta2 prereleases. Details on how to test the 3.5.0 beta1 can be found at: http://wiki.documentfoundation.org/QA/BugHunting_Session_3.5.0.-1 more detail on this bulk operation: http://nabble.documentfoundation.org/RFC-Operation-Spamzilla-tp3607474p3607474.html
hi, yes it still seems to be there as of 3.5.0beta2 Build ID: 8589e48-760cc4d-f39cf3d-1b2857e-60db978 where trying to run the test in section2 of original comment as update query still gives: The data content could not be loaded. SQL Status: 00000 Error code: -155 Statement does not generate a result set The SQL command leading to this error is: UPDATE "Table1" SET "Table1"."fld-flag" = FALSE WHERE "Table1"."fld-flag" = TRUE; while running the same sql code through the tools-sql-execute produces the change and gives no errors. setting status to 'unconfirmed' to clear the 'needinfo' flag.
sorry misread, setting status from 'unconfirmed' to 'new' to clear the 'needinfo' flag.
status is still the same as in comment#3 as of LibreOffice 3.5.4.2 Build ID: 350m1(Build:2) (as in fedora package libreoffice-base-1:3.5.4.2-1.fc17.x86_64)
Please read this message in its entirety before responding. Your bug was confirmed at least 1 year ago and has not had any activity on it for over a year. Your bug is still set to NEW which means that it is open and confirmed. It would be nice to have the bug confirmed on a newer version than the version reported in the original report to know that the bug is still present -- sometimes a bug is inadvertently fixed over time and just never closed. If you have time please do the following: 1) Test to see if the bug is still present on a currently supported version of LibreOffice (preferably 4.2 or newer). 2) If it is present please leave a comment telling us what version of LibreOffice and your operating system. 3) If it is NOT present please set the bug to RESOLVED-WORKSFORME and leave a short comment telling us your version and Operating System Please DO NOT 1) Update the version field 2) Reply via email (please reply directly on the bug tracker) 3) Set the bug to RESOLVED - FIXED (this status has a particular meaning that is not appropriate in this case) + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + + LibreOffice is powered by a team of volunteers, every bug is confirmed (triaged) by human beings who mostly give their time for free. We invite you to join our triaging by checking out this link: https://wiki.documentfoundation.org/QA/BugTriage There are also other ways to get involved including with marketing, UX, documentation, and of course developing - http://www.libreoffice.org/get-help/mailing-lists/. Lastly, good bug reports help tremendously in making the process go smoother, please always provide reproducible steps (even if it seems easy) and attach any and all relevant material
Hi, With Version: 4.3.3.1 Build ID: 7d55112667c8fcddb67bc3803796b46c93aa56b0 under Win7/x86 I create and populate Table1. I execute query: UPDATE "Table1" set "Table1"."fld-flag" = False where "Table1"."fld-flag" = True; - in Tools > SQL: "Command successfully executed" and result is good; - in Query > Create a SQL Query: "Syntax error". Bernard
Adding self to CC if not already on
Created attachment 124891 [details] sample database for query testing status is still same as in comment#3 as of LibreOffice 5 Version: 5.0.6.2 Build ID: 5.0.6.2-3.fc23 (as in fedora package libreoffice-base-1:5.0.6.2-3.fc23.x86_64) 0) observation on comment#7 > - in Tools > SQL: "Command successfully executed" and result is good; > - in Query > Create a SQL Query: "Syntax error". > in order to get the query to save from sql view you have to select Edit-"Run SQL command directly" otherwise it wont let you save but just gives syntax error: SQL Status: HY000 Error code: 1000 Syntax error in SQL statement syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE 1) run the saved update query doesnt make any changes to table and still gives the error: The data content could not be loaded. SQL Status: 00000 Error code: -155 Statement does not generate a result set 2) input sql of update query into tools-sql-menu of main base window running the same sql code through the tools-sql-execute still works sets the true flagged records to be false flagged and gives no errors: 1: Command successfully executed.
distantly related bug as testing embedded_firebird replacement for this hsql rfe: error when try open a database: https://bugs.documentfoundation.org/show_bug.cgi?id=85146
I would suggest that it would be even netter to provide update and delete queries directly through the IDE rather than expecting the user to enter SQL commands.
*** Bug 112272 has been marked as a duplicate of this bug. ***
One hint: At this moment it is possible to update tables through the query-editor if using MySQL/MariaDB and the native connector. There won't appear any error, only a blank page of the query if query is created in direct SQL and executed in directly. Problem: The "query" will be executed every time you go to "Queries" if you change at the right 'none' to 'document' (which should show the content of the query when the query is chosen).
Created attachment 138143 [details] MySQL-native-connector with possibility to update data through query
repeated test, some improvement. LibreO_6.1.5.2 (tested on linux) has same results as described in comment9 where update query fails to run and embedded hsqldb database is not updated. however repeating the test with an embedded firebird_db is an improvement as described below. LibreO_6.1.6.3 (tested on macos) using an embedded firebird_db (instead of hsqldb) still gives errors, BUT the Table Gets Updated :') (as before the query wont save unless select "Run SQL command Directly" button on the far right of query toolbar) the error generated by running the saved update query is "The data content could not be loaded." "Error code: 1" "firebird_sdbc error: Cursor is not open caused by isc_dsql_fetch" despite the error, the saved update query did indeed update the table which is confirmed by running the select query (or by then opening the table to verify that the fields had changed in case you were in surprised disbelief). I didnt have time to test other modification query types or read up on possible parallel bugs of the firebird error so I am leaving this bug open.
Created attachment 195202 [details] Error Message At the moment I get the above error message. What does a user expect here? Should the query just be updated and nothing else happens?
(In reply to Andreas Heinisch from comment #17) > > At the moment I get the above error message. What does a user expect here? > Should the query just be updated and nothing else happens? If the enhancement is working the query should be executed. There should be shown an empty result, not an error message. But: Do we really want to get this behavior in queries? Queries should produce a result. They should never change the content of any table in the database. I would prefer to execute all other commands, which should be executed often, by a macro. There you could suppress to execute the SQL code after it has already been executed. If the SQL code could be executed through a "query" you will get another security problem with "normal users", who execute the query again and again. Here is such a problematic "query": Table with a column "class" for pupils. UPDATE "tablePersons" SET "class" = "class" + 1 Gives new values every time I execute this "query".
It is not that difficult to implement this feature (use execute instead of executeQuery, i.e., [1] and [2]), but users may change their data without knowing it. Should we improve the error message in the case where the query does not return a result set and point to Tools -> SQL? [1] https://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/dlg/directsql.cxx?r=d52a4dba#230 [2] https://opengrok.libreoffice.org/xref/core/dbaccess/source/core/api/RowSet.cxx?r=493b8ab9#1662