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.

In case there is no answer, the expedient way to get around this is to just accept the whole array. It will be received as a csv string with curly braces on the end. The solution is to split off the curly braces, then split on comma, and then index the element you wanted. However, this is a work-around and not the answer I was hoping to use. — Smith
Welcome to StackOverflow! What is the Basic code that you tried? — Jim K
Another thing to mention: don't let pgAdmin put your table name in quotes. It appears it will do this if you don't enter the column name in lowercase letters. — Smith
@Jim K The Basic code is along the lines of: roleSet = sqlStmnt.executeQuery( "SELECT sq_num,rolearray[50] FROM myTableThatHasArrays ORDER BY sq_num ASC" ) — Smith
It would have been helpful to post more of your code in the question. However I think I figured out what you must have tried, as explained in my second answer. — Jim K

Jim K Jim K · Accepted Answer · 2016-05-19T22:25:32

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

Accessing SQL array element from Libreoffice Basic

1 Answers