如何在 BI Publisher 11g 中调用 SQL Server 存储过程? [英] How do you call a SQL Server Stored Procedure in BI Publisher 11g?

问题描述

我正在尝试调用 Microsoft SQL Server 存储过程，该过程在 Oracle BI Publisher 11g (11.1.1.7) 中以类似于表格的格式(行/列)提供数据.

I am trying to call a Microsoft SQL Server Stored Procedure that delivers data in table like format (rows / columns) in Oracle BI Publisher 11g (11.1.1.7).

选择过程调用作为数据模型的数据源不起作用，因为 BIP 期望它的行为类似于对 Oracle 数据库的 PL/SQL 调用.

Selecting procedure call as a data source for the data model does not work because BIP expects it to behave like a PL/SQL call to an Oracle database instead.

Oracle 开发人员声称该软件不支持此功能.

Oracle developers claim this is not supported by the software.

有什么办法可以绕过这个限制吗?

Is there any way around this restriction?

推荐答案

尽管 BI Publisher 11g 不支持开箱即用，但该问题有一个解决方法.它涉及诱使软件认为它正在执行标准的 PL/SQL 调用，而实际上它正在 SQL Server 数据源上执行存储过程.

Although not supported out-of-the-box by BI Publisher 11g, there is a workaround to the problem. It involves tricking the software into thinking it is making a standard PL/SQL call when in fact in reality it is executing a stored procedure on the SQL Server datasource.

它可以从这里的 MSDN 下载:http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx - 根据您的 JRE 版本，您需要使用一个或另一个 jar 文件:

It can be downloaded from MSDN here: http://msdn.microsoft.com/en-us/sqlserver/aa937724.aspx - depending on your JRE version you'll want to use one or the other jar file:

对于 JRE 1.6 及更高版本，请使用 sqljdbc4.jar.对于 1.5 及以下版本，请使用 sqljdbc.jar.

For JRE 1.6 and above, use sqljdbc4.jar. For 1.5 and below, use sqljdbc.jar.

你应该把它放在你的 $MIDDLEWARE_HOME\user_projects\domains\$your_domain_here$\lib\ 文件夹中，然后记得重新启动 weblogic 服务器.

You should place this in your $MIDDLEWARE_HOME\user_projects\domains\$your_domain_here$\lib\ folder and remember to restart weblogic server afterwards.

我们的示例适用于以下属性:

Our example works with following properties:

驱动程序类型: Microsoft SQL Server 2008
数据库驱动程序类: com.microsoft.sqlserver.jdbc.SQLServerDriver
连接字符串: jdbc:weblogic:sqlserver://[主机名]:[端口];databaseName=[数据库名]

Driver Type: Microsoft SQL Server 2008
Database Driver Class: com.microsoft.sqlserver.jdbc.SQLServerDriver
Connection String: jdbc:weblogic:sqlserver://[hostname]:[port];databaseName=[database name]

填写用户名/密码并测试连接(如果驱动安装正确，这应该可以正常工作)

Fill in username/pw and test connection (if driver is installed correctly, this should work just fine)

选择 SQL 查询作为您的数据集.在这里，添加这些属性:

Choose SQL query as your dataset. Here, add in these properties:

数据源:您的 JDBC 数据源
SQL 类型: 非标准 SQL
行标签名称:(您自己选择一个)- 现在只需编写测试.

Data Source: your JDBC data source
Type of SQL: Non-standard SQL
Row Tag Name: (choose one yourself) - for now just write test.

这部分假设您的存储过程传递了 N 个行和列标签.

This part is assuming your stored procedure delivers N amount of rows and column labels over.

以下是我们为名为 nrdart_get_custody_holding_headers_sp '2014-11-25' 的 SP 解决该问题的方法，其中参数是用户提供的日期.

Here is how we solved it for our SP that is called nrdart_get_custody_holding_headers_sp '2014-11-25' where the parameter is a date supplied by the user.

declare @var1 datetime  
declare @sql varchar(255)  
set @var1 = '2014-11-25'  
set @sql = 'nrdart_get_custody_holding_headers_sp' +'''' + cast(@var1 as varchar) + ''''  
exec (@sql)  

在这里，我们声明了一些 SQL Server 数据类型，并在最终对存储过程调用 exec 之前，使用一些创造性地使用强制转换函数和转义字符将它们设置为我们的日期参数和我们的过程调用名称.

Here, we are declaring some SQL Server datatypes, and setting them as our date parameter and as our procedure call name using some creative use of the cast function and escape characters, before finally calling exec on the stored procedure.

如果您使用标准 BIP 参数而不是上面的硬编码示例，则参数 var1 也可以使用

即:userDate 其中 :userDate 指的是数据模型中名为 userDate 的现有参数.

i.e. :userDate where :userDate is referring to an existing parameter called userDate in the datamodel.

如果您在单击确定"后没有看到行/列标签，请不要担心.相反，单击查看数据"即可.包含来自 Microsoft SQL Server 上的 SP 的数据的行和列.现在继续像往常一样将其保存为示例数据和设计报告布局.对于非日期参数，您可能需要稍微处理一下数据类型，但我不明白为什么您不应该让它也与整数或 varchars 一起使用.

Don't worry if you don't see row/column labels after clicking OK. Instead, click on "view data" and there you go. Rows and columns with data from your SP on Microsoft SQL Server. Now proceed to save this as sample data and design report layout as you would normally do. For non-date parameters you might need to play around a little bit with datatypes, but I don't see why you shouldn't get it to work with integers or varchars as well.

这篇关于如何在 BI Publisher 11g 中调用 SQL Server 存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！