Description: It would be better if it were possible to create tables in Base (Insert -> Table Design...) with more details on the columns (Field Properties) composing the table, like: - Be able to specify the precision for SQL types TIMESTAMP and TIME - Be able to give a default value for the column which can be an SQL function of the underlying database (ie: CURRENT_TIMESTAMP(9)) Steps to Reproduce: 1. In Base create a new table with: Insert -> Table design... 2. Create a column of SQL type TIMESTAMP(9) (ie: with nanoseconds precision) 3. Set as default value for this column the SQL function CURRENT_TIMESTAMP(9) Actual Results: Neither the precision nor the default value will be taken into account in the creation of the new table. Expected Results: Be able to control how Base displays and handles column property fields to be able to create columns by setting precision and default value. Reproducible: Always User Profile Reset: No Additional Info: If we use the TypeInfoSettings (https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/JDBCConnectionProperties.html#TypeInfoSettings) property, as is used in the jdbcDriverOOo version 1.0.4 driver, then it is possible to manage the precision on the TIMESTAMP and TIME (and TIMESTAMP WITH TIME ZONE and TIME WITH TIME ZONE), however it is impossible to manage a default value.
It seems that Base uses column 6 (CREATE_PARAMS) of the resultset obtained in response to the XDatabaseMetaData.getTypeInfo()[1] method to determine which fields should be displayed when declaring a column in a table. [1] https://www.openoffice.org/api/docs/common/ref/com/sun/star/sdbc/XDatabaseMetaData.html#getTypeInfo Apparently we are able, thanks to the parameter TypeInfoSettings, to modify this resultset in order to obtain from Base the management of the Field Properties. If I overload, thanks to TypeInfoSettings, in this resultset the following columns, for SQL type TIME, TIMESTAMP, TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE: 6. CREATE_PARAMS => SCALE 14. MINIMUM_SCALE => 0 15. MAXIMUM_SCALE => 9 So I am able in the jdbcDriverOOo driver version 1.0.4 to recover the precision and to build the SQL DDL query for creating the table with it. Moreover this SQL DDL query is visible in the log files: Tools -> Options -> Base drivers -> JDBC drivers -> View log This has only been implemented so far for the HsqlDB driver in jdbcDriverOOo 1.0.4.
I don't know if I followed the correct procedure but I already proposed a fix so that the SQL type TIME is treated the same as the SQL type TIMESTAMP. see: https://gerrit.libreoffice.org/c/core/+/155866 If we want to support SQL type: TIME WITH TIME ZONE and TIMESTAMP WITH TIME ZONE, it is necessary to add these two new types to the idl file com.sun.star.sdbc.DataType. I don't know how to make this change, any help is welcome...
Lionel: thought you might be interested in this one. Moreover, there's also a patch proposed by Pierre here: https://gerrit.libreoffice.org/c/core/+/155866
I'm just compiling a new fix that supports WITH TIME ZONE SQL type. It replaces the previous one which also failed the tests. I'll post the link as soon as it's available.
I just submitted a new fix: https://gerrit.libreoffice.org/c/core/+/155882/1 Apparently it is in conflict with my first update which does not pass the tests. I do not know how to do...
(In reply to prrvchr from comment #5) > I just submitted a new fix: > > https://gerrit.libreoffice.org/c/core/+/155882/1 > > Apparently it is in conflict with my first update which does not pass the > tests. I do not know how to do... In fact, you should have amended (see "amend" keyword for git) the first patch. What you can do now: - abandon the second patch - clean your local repo to be sync with master (something like "git reset --hard origin/master") - cherry-pick the first patch with: git fetch https://git.libreoffice.org/core refs/changes/66/155866/1 && git cherry-pick FETCH_HEAD - make the changes you want locally - use "git --amend" - submit the new version of the patch Concerning the error, it's because you got 2 appends in a row line 155: aSql.append(")"); aSql.append(sTypeName.subView(nParenPos)); You can do instead: aSql.append(")" + OUString::Concat(sTypeName.subView(nParenPos)));
(In reply to prrvchr from comment #2) > I don't know if I followed the correct procedure but I already proposed a > fix so that the SQL type TIME is treated the same as the SQL type TIMESTAMP. > see: https://gerrit.libreoffice.org/c/core/+/155866 > > If we want to support SQL type: TIME WITH TIME ZONE and TIMESTAMP WITH TIME > ZONE, it is necessary to add these two new types to the idl file > com.sun.star.sdbc.DataType. > I don't know how to make this change, any help is welcome... File offapi/com/sun/star/sdbc/DataType.idl I would recommend to "just" resynchronise it with the values in Java JDBC, we have a copy of the values in JDBC 3.0 (Java 1.4) while according to Wikipedia the latest version is JDBC 4.3 (Java 9). https://docs.oracle.com/en/java/javase/20/docs/api/java.sql/java/sql/Types.html or is it https://docs.oracle.com/en/java/javase/20/docs/api/java.sql/java/sql/JDBCType.html
(In reply to Julien Nabet from comment #6) > In fact, you should have amended (see "amend" keyword for git) the first > patch. Thanks for all the advice, it worked perfectly. see: https://gerrit.libreoffice.org/c/core/+/155866 (In reply to Lionel Elie Mamane from comment #7) > I would recommend to "just" resynchronise it with the values in Java JDBC, > https://docs.oracle.com/en/java/javase/20/docs/api/java.sql/java/sql/Types. > html > or is it > https://docs.oracle.com/en/java/javase/20/docs/api/java.sql/java/sql/ > JDBCType.html I think we can say that the SQL types are up to date with Java.
Pierre committed a patch related to this issue. It has been pushed to "master": https://git.libreoffice.org/core/commit/5b205d2b3946acd79dcb8f5abed88a0bd8afaef4 tdf#156816 Base handles scale on SQL type TIME as with TIMESTAMP It will be available in 24.2.0. The patch should be included in the daily builds available at https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: https://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.