Description: Made a table in embedded firebird: RECREATE TABLE USERTYPE ( USER_IDS INTEGER NOT NULL PRIMARY KEY, USER_TYPES VARCHAR(20), USER_ROLES INTEGER[5] ) my basic code is as follow: Option Explicit Sub Main Dim stm as com.sun.star.sdbc.XPreparedStatement Dim testVals(5) As Integer Dim i As Integer getDbConnection(ThisdatabaseDocument) stm =fcon.preparestatement("DELETE FROM USERTYPE") stm.execute() stm =fcon.preparestatement("insert into USERTYPE (USER_IDS,USER_TYPES,USER_ROLES) VALUES (?,?,?) ") stm.setInt(1,0) stm.setString(2,"test insert") For i = lbound(testVals) To ubound(testVals) testVals(i)=i Next i stm.setArray(3, testVals) stm.executeUpdate() End Sub The first two columns are populated, but the last column, which is an integer array was not populated whatsoever. Steps to Reproduce: 1. make the table: RECREATE TABLE USERTYPE ( USER_IDS INTEGER NOT NULL PRIMARY KEY, USER_TYPES VARCHAR(20), USER_ROLES INTEGER[5] ) 2. run the basic code to insert data in the table Option Explicit Sub Main Dim stm as com.sun.star.sdbc.XPreparedStatement Dim testVals(5) As Integer Dim i As Integer getDbConnection(ThisdatabaseDocument) stm =fcon.preparestatement("DELETE FROM USERTYPE") stm.execute() stm =fcon.preparestatement("insert into USERTYPE (USER_IDS,USER_TYPES,USER_ROLES) VALUES (?,?,?) ") stm.setInt(1,0) stm.setString(2,"test insert") For i = lbound(testVals) To ubound(testVals) testVals(i)=i Next i stm.setArray(3, testVals) stm.executeUpdate() End Sub 3. run select * from USERTYPE Actual Results: returns a single row of values: 0,test_insert, , Expected Results: the software should have inserted: 0 in the first column test_insert in the second column an array of integers in the last column Reproducible: Always User Profile Reset: No Additional Info: Version: 7.1.5.2 (x86) / LibreOffice Community Build ID: 85f04e9f809797b8199d13c421bd8a2b025d52b5 CPU threads: 8; OS: Windows 10.0 Build 19042; UI render: Skia/Raster; VCL: win Locale: en-US (en_US); UI: en-US Calc: CL
There is no datatype ARRAY supported by the driver we are using for Firebird. Didn't know if it is supported for any other database … Special for Firebird see: https://firebirdsql.org/file/documentation/chunk/en/refdocs/fblangref40/fblangref40-datatypes-array.html "Firebird does not offer much in the way of language or tools for working with the contents of arrays, and there are no plans to improve this. This limits the usefulness and accessibility of array types. Therefore, the general advice is: do not use arrays." This is a valid ask for an enhancement, because filling an array-field with data doesn't work. Have tested it with OpenSUSE 15.3 64bit rpm Linux and LO 7.3.3.1
(In reply to Robert Großkopf from comment #1) > There is no datatype ARRAY supported by the driver we are using for > Firebird. Didn't know if it is supported for any other database … See it is working in PostgreSQL with direct connection in SQL and also as input directly in the table through GUI.
Hello, I thought I would dig into this problem a bit deeper. I downloaded and installed the latest firebird with jaybird 4.0.5 driver for java 8 on windows 10. also installed flamerobin 0.9.3. I made a new table: CREATE TABLE TABLE_ARRAY ( TABLE_NAME integer NOT NULL, TABLE_NUMBER integer NOT NULL, TABLE_ROLE integer[3], CONSTRAINT INTEG_5 PRIMARY KEY (TABLE_NAME) ); Using jdk 8 on netbeans jaybird I tried to insert the array value [2,3,4]. The result was the following exception message: org.firebirdsql.jdbc.FBDriverNotCapableException: Type ARRAY not yet supported. HOWEVER.... I wrote similar code in python which successfully inserted an array. import fdb def conFB(): global con con = fdb.connect(dsn='C:/Users/jacka/OneDrive/Documents/second.fdb',user='sysdba',password='masterkey') cur = con.cursor() cur.execute("delete from TABLE_ARRAY") t=[2,3,4] ar=(t) cur.execute("insert into TABLE_ARRAY (TABLE_NAME, TABLE_NUMBER, TABLE_ROLE) VALUES(?,?,?)",(1,2,ar)) cur.execute("select * from TABLE_ARRAY") row = cur.fetchone() print(row) con.commit() cur.close() con.close() which then prints out: (1, 2, [2, 3, 4]) This is exactly what I am looking for. If firebird's python driver can handle this situation, then I think it would be definitely worth LibreOffice's efforts to also handle this, since it proves that Firebird can handle array type, even if it is limited in its ability to do so.