Description: If Base has access to updatable XResultSet (com.sun.star.sdbcx.ResultSet.isBookmarkable is set to true) when you insert a new row you must use the menu: Data -> Refresh to view the new inserted row. Steps to Reproduce: 1. Install jdbcDriverOOo extension (https://prrvchr.github.io/jdbcDriverOOo/) 2. Install HsqlDriverOOo extension (https://prrvchr.github.io/HsqlDriverOOo/) 3. Create a new HsqlDB embedded database and open the database for editing (https://prrvchr.github.io/HsqlDriverOOo/#how-to-create-a-new-database) 4. Create new table with an INTEGER autoincrement Column as primary key and an VARCHAR column 5. Edit the content of this new table and insert a new row. Actual Results: If Base uses bookmarks (updatable ResultSet) then it is necessary to use the menu: Data -> Refresh to see the content of the row that has just been inserted. Expected Results: If you prohibit the use of bookmarks to Base (Tools -> Options -> Base drivers -> JDBC Driver -> UNO drivers settings -> Use bookmarks is unchecked) then the refresh of the insertions is done correctly Reproducible: Always User Profile Reset: No Additional Info: It appears that when opening a table for displaying the content, Base performs the following actions: - It call the com.sun.star.sdbc.XResultSetUpdate.moveToInsertRow() interface. - It call the com.sun.star.sdbcx.XRowLocate.getBookmark() interface for each row of the ResultSet. I understand the need to cache bookmarks but can't find an explanation for the moveToInsertRow() method call.
(In reply to prrvchr from comment #0) > Expected Results: > If you prohibit the use of bookmarks to Base (Tools -> Options -> Base > drivers -> JDBC Driver -> UNO drivers settings -> Use bookmarks is > unchecked) then the refresh of the insertions is done correctly Don't know where you find the path. It stop here after Tools → Options. Then I could choose "LibreOffice Base". There I could set the (useless) connection-pooling and that is all. I don't want to install extension to confirm bugs, because I don't know if this will be a bug of the extension or a bug of LO. Please test first with internal drivers, then with external databases, which will be connected in different ways.
> Don't know where you find the path. You must install jdbcDriverOOo to see this path... > I don't want to install extension to confirm bugs, because I don't know if this > will be a bug of the extension or a bug of LO. Do you know a driver able to work with or without updatable ResultSet?
After further investigation, it appears that the driver must provide a connection offering at least the UNO service com.sun.star.sdbcx.Connection or com.sun.star.sdb.Connection and must support the updatable resultset to be able to reproduce. Obviously the same is true for all the services provided by the connection (Statement, PreparedStatement, CallableStatement and ResultSet) they must be of the same level (com.sun.star.sdbcx or com.sun.star.sdb respectively) jdbcDriverOOo offers the big advantage of being polymorphic and of being able to provide the levels of UNO services which are requested from it in: Tools -> Options -> Base drivers -> JDBC Driver -> UNO drivers settings On the other hand, this issue is indeed the only one where I am not sure that the problem comes from LibreOffice and not from my extension, since I have this problem regardless of the version of LibreOffice. But if I look at how Base uses an updatable resultset I still don't understand why the first thing it does is to switch to insert mode (with moveToInsertRow()) when it is just supposed to display the contents of the table... Normally, at least with JDBC, when you switch to insert mode you can no longer move in the resultset until you exit insert mode. That's not what Base does.... This forces me to manage this very special case in my driver and do not really know if it is really a Base problem or incompatibility between UNO SDBC and JDBC.
I don't know how to force Base to use updatable resultset with a driver other than jdbcDriverOOo. I don't even know if this other driver exists. On the other hand, it is possible for me to record in a log file all the activity of the ResultSet and therefore to know exactly how Base proceeds with an updatable resultset. Wouldn't that be a good starting point for resolving this issue?
> Normally, at least with JDBC, when you switch to insert mode you can no longer > move in the resultset until you exit insert mode. That's not what Base does.... It seems the UNO documentation states the same thing[1]: > Only the updateXXX , getXXX , and XResultSetUpdate::insertRow() methods may be > called when the cursor is on the insert row. All of the columns in a result set > must be given a value each time this method is called before calling insertRow. > The method updateXXX must be called before a getXXX method can be called on a > column value. [1] https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/XResultSetUpdate.html#moveToInsertRow This would confirm that Base switch to insert mode when it shouldn't...
(In reply to Robert Großkopf from comment #1) > (In reply to prrvchr from comment #0) > > I don't want to install extension to confirm bugs, because I don't know if > this will be a bug of the extension or a bug of LO. Please test first with > internal drivers, then with external databases, which will be connected in > different ways. Ok Robert, I followed your recommendations: I installed the following 3 drivers in Tools -> Options -> LibreOffice -> Advanced -> Class Path... - hsqldb-2.7.2.jar - h2-2.2.220.jar - mariadb-java-client-3.1.4.jar If I use these three different drivers through LibreOffice's internal JDBC driver, using a url starting with jdbc:*, I get the following results: - hsqldb can open and insert a row (which is properly refreshed) in table created with the jdbcDriverOOo driver. - h2 can't see the PUBLIC schema and its Table1, and it can't create a table with autoincrement. - mariadb can open and insert a row (which is properly refreshed) in table created with the jdbcDriverOOo driver. If I use these same three drivers in jdbcDriverOOo version 1.0.3, I get the following results: - hsqldb can create a table with autoincrement and insert a row (which is not properly refreshed). - h2 can create a table with autoincrement and insert a row (which is not properly refreshed). - mariadb can create a table with autoincrement and insert a row (which is properly refreshed). From these different tests, I think we can suspect that the native JDBC driver of LibreOffice does not offer the com.sun.star.sdbcx.XRowLocate interfaces necessary for Base in order to use the updatable resultset. I suspect it works like jdbcDriverOOo does if you disable the use of bookmarks. On the other hand, if we look carefully at the logs generated by jdbcDriverOOo then we see that the refreshing of the insets is conditional on the underlying driver and more precisely on the response it gives to the method: com.sun.star.sdbc.XResultSet.rowInserted(). I would like to do more tests to confirm this. As a result, only the positively responding mariadb driver manages to refresh the newly inserted rows. We also see that the UNO SDBC API does not follow the recommendations of the java.sql.ResultSet API and requires me in my transcription to manage the insertion mode. I'm afraid few of us are looking to use bookmarks (com.sun.star.sdbcx.XRowLocate) with Base.
Created attachment 188933 [details] HsqlDB First insert row.log
Created attachment 188934 [details] HsqlDB Second insert row.log
Created attachment 188935 [details] H2 First insert row.log
Created attachment 188936 [details] H2 Second insert row.log
Created attachment 188937 [details] MariaDB First insert row.log
Created attachment 188938 [details] MariaDB Second insert row.log
After more research it seems that the refresh of the insert lines is managed by the underlying driver and is conditional on the response to the method: com.sun.star.sdbc.XDatabaseMetaData.insertsAreDetected(int type) hsqldb and h2 do not support this mode. LibreOffice Base is aware that the underlying driver does not support this mode, why does it not refresh the table automatically after insert, update, delete?