Accessing SQL array element from Libreoffice Basic

Question

I have a postgresql database that contains program data. In Libreoffice Calc, I have Basic macros that interact with the postgresql database and uses Calc as the user client. One of the postgresql tables has an array and I can't index into that array directly from Basic.

Here is the table setup, as shown in pgAdmin:

  sq_num integer,
  year_start integer,
  id serial NOT NULL,
  "roleArray" text[]

Say I want to SELECT roleArray[50]. My every attempt to do this out of Basic results in the entire array being passed. I can certainly split the array myself and get the element I'm after, but I was using SQL arrays to help automate this stuff.

My Basic code uses a Libreoffice Base file for the connection to the postgresql database. Going to the Base file, I cannot create a query that will select an individual element and not return the entire array UNLESS I select the button "Run SQL command directly" and run this query:

SELECT "roleArray"['50'] FROM myTableThatHasArrays

Then I get element 50 from every record as intended.

I believe there is a bug report that describes this, where the Base command parser can't handle indexing an array. My question is what is the best method to overcome this?

The best scenario is to be able to index an element in the SQL array directly from Basic.

Answer 1

It sounds like you used XRow.getString, which (sensibly enough) retrieves the array as a single large string. Instead, use XRow.getArray and then XArray.getArray. Here is a working example:

sSQL = "SELECT id, ""roleArray""[2] FROM mytablethathasarrays;"
oResult = oStatement.executeQuery(sSQL)
s = ""
Do While oResult.next()
    sql_array = oResult.getArray(2)
    basic_array = sql_array.getArray(Null)
    s = s & oResult.getInt(1) & " " & basic_array(1) & CHR$(10)
Loop
MsgBox s