LibreOffice Basic操作PostgreSQL数组遇错及函数调用问题求助

一、初始读取数组报错问题 #

1. 数据库表结构及数据 #

CREATE TABLE temp.table2
(
  names character varying[],
  names2 text[]
);
insert into temp.table2(names, names2) values (ARRAY['a','b','c','d','e'], ARRAY['a','b','c','d','e']);

insert into temp.table2(names, names2) values (ARRAY['1','2','3','4','5'], ARRAY['1','2','3','4','5']);

insert into temp.table2(names, names2) values (ARRAY['e','d','c','b','a'], ARRAY['e','d','c','b','a']);

2. LibreOffice Basic代码及错误 #

Sub Main
    If isnull(ThisDatabaseDocument.currentcontroller.activeconnection) Then
        ThisDatabaseDocument.currentcontroller.connect("root", "root")
    End if
    pgCon=ThisDatabaseDocument.currentcontroller.activeconnection
    stmt = pgCon.createStatement()
    
    rs = stmt.executeQuery("select names,names2 from temp.table2")
    rs2 = rs.getMetadata()
    While rs.Next
        dar = rs.getString(1).getArray(Null)
    Wend 
End Sub

执行到dar = rs.getString(1).getArray(Null)时触发错误：

BASIC runtime error.
Object variable not set.

二、目标需求：调用PL/pgSQL函数批量插入数组数据 #

1. PL/pgSQL函数代码 #

create or replace function temp.f_iu7YttW(dname text[]) returns void as $$
declare
counter integer = 1;
begin
    while counter<=50 loop
        insert into temp.table1(name) values(dname[counter]);
        counter=counter+1;
    end loop;
end; $$ language plpgsql;

2. LibreOffice Basic代码及问题 #

Sub Main
Dim stmt As Object
Dim rs As Object
Dim sql As String
Dim a As Variant
Dim x As Long
    If isnull(ThisDatabaseDocument.currentcontroller.activeconnection) Then
        ThisDatabaseDocument.currentcontroller.connect("root", "root")
    End if
    pgCon=ThisDatabaseDocument.currentcontroller.activeconnection
    executeDDLPg("drop function if exists temp.f_iu7YttW(text[])")
    myDict= getScriptForgeDictionary()
    x=0
    a= Array(x & "")
    SF_Array.Append(a)
    For x = 1 To 50
        a=SF_Array.Append(a,x & "")
    Next x
    max = ubound(a)
    sql ="drop function if exists temp.f_iu7YttW(text[]);" & _
    "create or replace function temp.f_iu7YttW(dname text[]) returns void as $$ " & _
    "declare "  & _
    "counter integer = 1; "  & _
    "begin "  & _
    "   while counter<" & max & " loop "  & _
    "       insert into temp.table1(name) values(dname[counter]); "  & _
    "       counter=counter+1; "  & _
    "   end loop; "  & _
    "end; $$ language plpgsql;"
    executeDDLPg(sql)
    sql="select temp.f_iu7YttW(?);"
    stmt = pgCon.prepareStatement(sql)
    
    stmt.setString(1,a)
    stmt.executeUpdate()
    stmt.clearParameters()
    closeDbObject(stmt)
    executeDDLPg("drop function temp.f_iu7YttW(text[])")
End Sub

• 使用stmt.setString(1,a)时代码无法正常运行
• 替换为stmt.setArray(1,a)时LibreOffice直接崩溃

解决方案 #

1. 读取PostgreSQL数组的正确方式 #

rs.getString(1)返回的是数组的字符串表示（如{a,b,c}），并非JDBC数组对象，因此无法调用getArray()。正确做法是用rs.getArray()获取JDBC数组对象，再转换为VB数组：

Sub Main
    If isnull(ThisDatabaseDocument.currentcontroller.activeconnection) Then
        ThisDatabaseDocument.currentcontroller.connect("root", "root")
    End if
    pgCon=ThisDatabaseDocument.currentcontroller.activeconnection
    stmt = pgCon.createStatement()
    
    rs = stmt.executeQuery("select names,names2 from temp.table2")
    While rs.Next
        Dim xArray As Object
        xArray = rs.getArray(1)
        dar = xArray.getArray(Null)
        ' 遍历输出数组元素示例
        For i = LBound(dar) To UBound(dar)
            Print dar(i)
        Next i
    Wend 
End Sub

2. 传入数组到PL/pgSQL函数的正确方式 #

LibreOffice Basic不能直接将VB数组传入setArray()，需要先通过数据库连接创建JDBC兼容的Array对象，再传入参数。同时修正数组构造和函数逻辑的问题：

Sub Main
Dim stmt As Object
Dim sql As String
Dim a() As String
Dim x As Long
Dim jdbcArray As Object

    If isnull(ThisDatabaseDocument.currentcontroller.activeconnection) Then
        ThisDatabaseDocument.currentcontroller.connect("root", "root")
    End if
    pgCon=ThisDatabaseDocument.currentcontroller.activeconnection
    
    ' 构造1到50的字符串数组
    ReDim a(1 To 50)
    For x = 1 To 50
        a(x) = CStr(x)
    Next x
    
    ' 创建JDBC Array对象（指定PostgreSQL数组类型为text）
    jdbcArray = pgCon.createArrayOf("text", a)
    
    ' 创建PL/pgSQL函数（改用数组长度判断循环，避免硬编码）
    sql = "create or replace function temp.f_iu7YttW(dname text[]) returns void as $$ " & _
          "declare "  & _
          "counter integer = 1; "  & _
          "begin "  & _
          "   while counter <= array_length(dname, 1) loop "  & _
          "       insert into temp.table1(name) values(dname[counter]); "  & _
          "       counter=counter+1; "  & _
          "   end loop; "  & _
          "end; $$ language plpgsql;"
    executeDDLPg(sql)
    
    ' 调用函数
    sql = "select temp.f_iu7YttW(?);"
    stmt = pgCon.prepareStatement(sql)
    stmt.setArray(1, jdbcArray)
    stmt.execute() ' 返回void用execute即可
    
    ' 清理资源
    stmt.clearParameters()
    closeDbObject(stmt)
    executeDDLPg("drop function temp.f_iu7YttW(text[])")
End Sub

关键修正点：

• 直接用VB原生数组构造数据，避免ScriptForge数组的用法错误
• 用pgCon.createArrayOf()创建符合JDBC标准的数组对象，解决setArray()崩溃问题
• PL/pgSQL函数改用array_length(dname, 1)获取数组实际长度，兼容任意长度输入

内容的提问来源于stack exchange，提问作者Z T Minhas