LibreOffice Calc execute a PostgreSQL Function

Question

I'm currently using Microsoft Excel to execute Stored Procedures on a SQL Server database and it is working fine.

If anyone is interested there are really nice instructions here http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

I was wondering if it's possible to do something like this with LibreOffice Calc and PostgreSQL.

I know that LibreOffice supports PostgreSQL connections as you can create a PostgreSQL odb file but I was wondering if it's possible to execute Stored Porcedures/Functions in a similar way to how Excel does it

Answer 1

It is possible to do something similar in LibreOffice Calc, but instead of setting up the database connection with various menus, everything is done with macro code.

The following worked for me using this MySQL stored procedure:

Sub RunStoredProc
    Dim oParms(1) as new com.sun.star.beans.PropertyValue 
    oParms(0).Name = "user" 
    oParms(0).Value = "root" 
    oParms(1).Name = "password" 
    oParms(1).Value = "password" 
    oManager = CreateUnoService("com.sun.star.sdbc.DriverManager")
    sURL = "sdbc:mysql:jdbc:localhost:3306/world"
    oConnection = oManager.getConnectionWithInfo(sURL, oParms())
    sFormat = "Europe"
    oStmt = oConnection.prepareCall("CALL country_hos(?)")
    oStmt.setString(1, sFormat)
    oResult = oStmt.executeQuery()
    sResult = ""
    If Not IsNull(oResult) Then
      While oResult.Next()
        sResult = sResult & oResult.getString(1) & CHR(10)
      Wend
    End If
    MsgBox "Result: " & sFormat & " = " & CHR(10) & sResult
    oStmt.close()
End Sub

The code is adapted from https://forum.openoffice.org/en/forum/viewtopic.php?f=21&t=41149.

To finish the code, modify it to put the results into the spreadsheet instead of displaying them in a message box. Also read the selected value from the drop-down box instead of hardcoding the value of sFormat.

Note: Some information online suggests using an intermediate .odb file. That would involve more menus rather than doing everything in the macro. This works for tables and queries but apparently not for stored procedures, unless perhaps with HSQLDB as mentioned here.