Trying to insert array into firebird base table

[ztminhas]

Good Morning,

I am trying to insert an array into a table (EMBEDDED FIREBIRD) for testing purposes. To make the table:

RECREATE TABLE USERTYPE
    (
      USER_IDS INTEGER NOT NULL PRIMARY KEY, 
      USER_TYPES VARCHAR(20),
      USER_ROLES INTEGER[5]
    )

The code to insert the data is in Basic:

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

After execution, the first two columns are populated, but the last column, USER_ROLES remains empty.

I am running:

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

[mikekaganski]

This is a bug: first, the setArray call is not implemented, and second, the absence of implementation is not done properly.

Cf: Firebird connector “implementation” just does some unneeded checks, but doesn’t assign anything anywhere; while file connector properly throws an exception (so a user would get a run-time error, and would see the problem immediately).

FTR: no connector in fact implements this currently.

[ztminhas]

Thank you for the quick response. I really do appreciate that. So if this will not work in Basic, will a Python macro work for this issue? Thank you once again.

[mikekaganski]

No, the code is not implemented in the database connector used in LibreOffice; and no matter which scripting language you use, calling the Firebird connector’s prepared statement’s setArray will silently do nothing.

You need to file a bug report please.

[ztminhas]

Time to file a bug report!

[ztminhas]

Hello, for those that are interested, here is the bug report:

148663 – Unable to insert an array in embedded firebird (documentfoundation.org)