LibreOffice Calc执行PostgreSQL函数 [英] LibreOffice Calc execute a PostgreSQL Function

问题描述

我目前正在使用Microsoft Excel在SQL Server数据库上执行存储过程，并且工作正常。

如果有任何人感兴趣，这里有很好的说明
http://datapigtechnologies.com/blog/index.php/running-a-sql-stored-procedure-from-excel-with-dynamic-parameters/

$ b $我想知道是否可以使用LibreOffice Calc和PostgreSQL这样做。

我知道LibreOffice支持PostgreSQL连接，因为您可以创建一个PostgreSQL odb文件，但我想知道是否可以以类似于Excel的方式执行Stored Porcedures / Functions。

解决方案

可能在LibreOffice Calc中做类似的事情，而不是使用各种菜单设置数据库连接，一切都是用宏代码完成的。

以下内容使用了这个MySQL存储过程：

  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 
 sURL =sdbc：mysql：jdbc：localhost：3306 / world
 oConnection = oManager.getConnectionWithInfo（sURL，oParms（））
 sFormat =欧洲
 oStmt = oConnection.prepareCall（CALL country_hos（？））
 oStmt.setString（1，sFormat）
 oResult = oStmt.executeQuery（）
 sResult = 
如果不是IsNull（oResult）然后
 while oResult.Next（）
 sResult = sResult& oResult.getString（1）& CHR（10）
 Wend 
 End If 
 MsgBoxResult：& sFormat& =& CHR（10）& sResult 
 oStmt.close（）
 End Sub 
  

代码改编自 https://forum.openoffice.org/en/论坛/ viewtopic.php？f = 21& t = 41149 。

要完成代码，请修改它以将结果放入电子表格，而不是显示他们在一个消息框中。还请从下拉框中读取所选值，而不是对 sFormat 的值进行硬编码。

注意：某些在线信息建议使用中间的.odb文件。这将涉及更多的菜单，而不是在宏中做所有的事情。这适用于表和查询，但显然不适用于存储过程，除非可能使用HSQLDB，如 here < a>。

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

解决方案

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.

这篇关于LibreOffice Calc执行PostgreSQL函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！