Created attachment 186308 [details] Macrocode for creating and inserting data in PostgreSQL table. Open a Base file with direct connection to PostgreSQL. Copy the macro-code from attached text file to a macro module of this direct connected Base file (or take the attached Base file and connect this file). Start procedure "NewTable". Start procedure "Insert". Have a look at the new created table - should show one inserted row. Start procedure "PrepareStatementInsert". LO will crash immediately. Now reopen LO, do the same again and let you show "PrepareStatementInsert" step by step. If it reaches "setArray" LO will crash. Now reopen LO, do the same but start procedure "PrepareStatementInsertWithoutArray" instead of "PrepareStatementInsert". Prepared statement will work with setString, which has been created from an array. Bug happens with all LO-versions here on OpenSUSE 15.4 64bit rpm Linux.
Created attachment 186309 [details] Database file with macro code - needs to change direct connection
Tested with the test file and: Version: 7.6.0.0.alpha0+ (X86_64) / LibreOffice Community Build ID: 002f941ec20e594e9702c39fab9cf9f4cc392dab CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded No table "Table_Ar" is created in "public" when using the macros, even with low macro security level and my own PG13 database connected. Am I missing something?
Macros do run for me in 7.5.1.2, the table is created. Not sure what is going on with macros in the master build. However, PrepareStatementInsert does not crash it. Version: 7.5.1.2 (X86_64) / LibreOffice Community Build ID: fcbaee479e84c6cd81291587d2ee68cba099e129 CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded
(In reply to Stéphane Guillou (stragu) from comment #3) > Macros do run for me in 7.5.1.2, the table is created. Not sure what is > going on with macros in the master build. > > However, PrepareStatementInsert does not crash it. Did you see both rows in the table? Crash isn't only happening here. It has been also reported at https://ask.libreoffice.org/t/looping-through-an-array-in-an-stored-function-in-postgres/89806
Looks like I often have issues with the table list view not refreshing. After running the macro, I can't see the new table listed in LO, but \d in the command line shows me: List of relations Schema | Name | Type | Owner --------+----------+-------+-------- public | Table_Ar | table | stragu (1 row) I have to refresh manually from View > Refresh Tables, which is a bit cumbersome. Anyway, running the Insert macro I get: testdb=> select * from "Table_Ar"; ID | Surname | Forenames ----+---------+------------- 1 | Müller | {Lise,Gerd} (1 row) Then, running PrepareStatementInsert, crash. Tested with: Version: 7.5.1.2 (X86_64) / LibreOffice Community Build ID: fcbaee479e84c6cd81291587d2ee68cba099e129 CPU threads: 8; OS: Linux 5.15; UI render: default; VCL: gtk3 Locale: en-AU (en_AU.UTF-8); UI: en-US Calc: threaded Crash report: https://crashreport.libreoffice.org/stats/crash_details/5ecc4dea-2e18-4fc6-9341-1e8be556ddbb
I originally reported this problem in https://ask.libreoffice.org/t/looping-through-an-array-in-an-stored-function-in-postgres/89806. I ran both my code as well as the code at https://bug-attachments.documentfoundation.org/attachment.cgi?id=186308 the Sub: SUB PrepareStatementInsert DIM oDatasource AS OBJECT DIM oConnection AS OBJECT DIM ar oDatasource = thisDatabaseDocument.CurrentController IF NOT (oDatasource.isConnected()) THEN oDatasource.connect() oConnection = oDatasource.ActiveConnection() DIM stSql AS STRING stSql = "INSERT INTO ""public"".""Table_Ar"" (""ID"", ""Surname"", ""Forenames"") VALUES (?, ?, ?)" oSQL_Statement = oConnection.prepareStatement(stSql) oSQL_Statement.setLong(1, 2) oSQL_Statement.setString(2, "Big") ar = array("Will","John","Jack") ' msgbox ar(2) oSQL_Statement.setArray(3, ar) oSQL_Statement.executeUpdate(stSql) END SUB crashes for me at oSQL_Statement.setArray(3, ar)
my version of LO is Version: 7.4.2.3 (x86) / LibreOffice Community Build ID: 382eef1f22670f7f4118c8c2dd222ec7ad009daf CPU threads: 8; OS: Windows 10.0 Build 19044; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL
Created attachment 186339 [details] bt with debug symbols On pc Debian x86-64 with master sources updated today, I got an assertion. Does it happen too with another database type (Firebird, Mysql or HSQLDB)? I mean are we sure the culprit is Postgresql implementation or something lacking in Basic code?
(In reply to Julien Nabet from comment #8) > Created attachment 186339 [details] > bt with debug symbols > > On pc Debian x86-64 with master sources updated today, I got an assertion. > > Does it happen too with another database type (Firebird, Mysql or HSQLDB)? Firebird doesen't offer much functions for arrays - haven't get it working in GUI of Base: https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-datatypes-array.html MySQL/MariaDB and HSQLDB don't support the datatype Array. > > I mean are we sure the culprit is Postgresql implementation or something > lacking in Basic code? Don't know if this is a part of code for PostgreSQL. setArray isn't special PostgreSQL. Have tried the same with JDBC-connection to PostgreSQL, but the driver returns "XParameters::setArray" isn't implemented.
LO does not allow for creating tables with array columns through the UI. The SDBC driver will not support insertion of arrays though it will not crash. https://bugs.documentfoundation.org/show_bug.cgi?id=148663
(In reply to ztminhas from comment #10) > LO does not allow for creating tables with array columns through the UI. The > SDBC driver will not support insertion of arrays though it will not crash. > > https://bugs.documentfoundation.org/show_bug.cgi?id=148663 I am referring to embedded Firebird in this instance.
(In reply to Robert Großkopf from comment #9) > (In reply to Julien Nabet from comment #8) > ... > Firebird doesen't offer much functions for arrays - haven't get it working > in GUI of Base: > https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/ > fblangref40-datatypes-array.html > > MySQL/MariaDB and HSQLDB don't support the datatype Array. Ok so not very useful then > ... > Don't know if this is a part of code for PostgreSQL. setArray isn't special > PostgreSQL. Have tried the same with JDBC-connection to PostgreSQL, but the > driver returns "XParameters::setArray" isn't implemented. When taking a look at JDBC implementation in LO, I see: 309 void SAL_CALL java_sql_PreparedStatement::setArray( sal_Int32 /*parameterIndex*/, const css::uno::Reference< css::sdbc::XArray >& /*x*/ ) 310 { 311 ::dbtools::throwFeatureNotImplementedSQLException( "XParameters::setArray", *this ); 312 } (see https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/jdbc/PreparedStatement.cxx?r=a9c8ac36#309) Then considering https://opengrok.libreoffice.org/search?project=core&full=&defs=setArray&refs=&path=connectivity&hist=&type=&xrd=&nn=1&si=path&si=path, it seems only connectivity/source/drivers/ado/Aolevariant.cxx implements it with Postgresql.
Let's reduce a bit the importance since: - it's not a regression - quite a corner case (most of database types don't support array and is this type often used?)
I can't speak for others but I work in an industry where arrays are heavily used.
(In reply to ztminhas from comment #14) > I can't speak for others but I work in an industry where arrays are heavily > used. Ok then. Personally, I'm not able to fix this and don't know if someone would know and/or have time to do it. We're lacking dev contributors in general and more specifically for Base. So if you know someone who may help or who can pay for help, this person can start with this link: https://wiki.documentfoundation.org/Development/GetInvolved Uncc myself since I can't do anything here.