FR根据登陆用户连接各自的数据库服务器动态查询

我是社区第133825位番薯,欢迎点我头像关注我哦~
背景:公司有很多客户,每个客户有不同的系统数据库服务器,数据库名也可能不一样,只有数据库结构是一样的,现在要做一个决策系统供所有客户的用户登陆,然后根据各自的用户指向各自公司的服务器和数据库返回数据
数据库版本:SQLSERVER2014以上

发表于 2018-5-4 17:54:59
目前我做了一个简单的方案来实现,希望能有更好的解决方式。
1.指定一个数据库服务器作为主数据服务器,用于FR创建数据集
2.主数据服务器上创建表存储所有用户的用户名、公司,以及各自公司对应的数据库服务器信息,包括IP,数据库用户名,密码和对应的数据库名
3.在主数据服务器上按各客户的数据库信息创建链接服务器linkserver
4.在主服务器上创建存储过程,存储过程按FR传入的用户参数查找该用户所属公司,以及链接服务器信息和数据库名,在存储过程中用动态拼接SQL的方式来从各自公司的数据库中获取数据
  1. --exec p_rep_pos_salemaster 'shinger@faya','','2018-04-01','2018-04-01'
  2. create PROC p_rep_pos_salemaster  
  3.   @userName NVARCHAR(200),      --传入的用户名参数
  4.   @shoplist VARCHAR(max),      --店铺列表
  5.   @begindate VARCHAR(10),       --开始日期
  6.   @enddate VARCHAR(10) AS       --截止日期
复制代码

--如果传入的用户没有记录,直接返回空结果集
  1. IF NOT EXISTS(SELECT 1 FROM fr_user WHERE fruserid=@username)
  2. BEGIN
  3.   SELECT '' AS shopid,NULL billdate,NULL aount
  4.   return
  5. end
  6. ELSE
  7. begin
复制代码

--根据传入的用户参数找到该用户的公司id
  1. SELECT @companyid=companyid FROM fr_user WHERE fruserid=@username
复制代码


--根据公司id找到对应的链接服务器
  1. SELECT @serveralias=serveralias,@ipadress=ipadress,@serveruser=serveruser,@serverpsd=serverpsd,@databasename=databasename
  2.    FROM fr_companyserver
  3.      WHERE companyid=@companyid
复制代码

--判断链接服务器是否存在,没有则创建
  1. IF not exists(select 1 from master.dbo.sysservers where srvname=@serveralias)
  2. begin
  3.     EXEC sp_addlinkedserver @serveralias,'','SQLOLEDB',@ipadress
  4.     EXEC sp_addlinkedsrvlogin @serveralias,'false',NULL,@serveruser,@serverpsd
  5. end
复制代码


--定义拼接SQL用于输出门店列表
  1. DECLARE @sql NVARCHAR(MAX)

  2. SET @sql=N'select shopid,cast(billdate as date) billdate,sum(payamount) amount
  3.                from @serveralias.@databasename.dbo.sd_pos_salemaster where companyid=''faya'' '+CASE WHEN ISNULL(@shoplist,'')='' THEN '' ELSE ' and shopid in ('''+@shoplist+''')' END+
  4.                                   ' and billdate>='''+@begindate+''' and billdate<'''+@enddate+'''
  5.                             group by shopid,cast(billdate as date)
  6.                   union all
  7.                     select shopid,cast(billdate as date) billdate,sum(payamount) amount
  8.                from @serveralias.@databasename.dbo.sd_pos_salemaster2 where companyid=''faya'' '+CASE WHEN ISNULL(@shoplist,'')='' THEN '' ELSE ' and shopid in ('''+@shoplist+''')' END+
  9.                                   ' and billdate>='''+@begindate+''' and billdate<'''+@enddate+'''
  10.                             group by shopid,cast(billdate as date)'
  11. SET @sql=REPLACE( REPLACE(@sql,'@serveralias',@serveralias),'@databasename',@databasename)

  12. PRINT @sql
复制代码

--执行动态拼接脚本输出结果
  1. EXEC sp_executesql @sql
  2. END
  3. SET NOCOUNT OFF
复制代码

发表于 2018-5-4 17:55:43
有没有大神能提供更好的可行方案的?
发表于 2018-6-11 03:06:50
难!用户名相同呢?结合密码判断?密码也相同呢?加前掇?
给你一个参考思路:
建立两个数据源,JDBC1和JDBC2,JDBC1是主数据源,用于登录,用户表里包含你所有客户数据库中的用户信息,其中用户表中设一个字段作为JDBC2的URL连接信息,如:jdbc:sqlserver://IP:1433;databaseName=***JDBC2的URL连接信息用参数表达,如:${UserDB},也就是说,JDBC2的数据库连接信息是动态的,是根据不同用户名来实现不同的数据库连接信息。
前提是SQL后台登录名和口令要全部一样。
那么每一个报表里的数据集都连接到JDBC2中。也就是所有报表都是以JDBC2为数据库。
这只是我的思路,没实现过。但动态产生是连接信息是可行的,官方帮助文档中有提到。
官方绝对不希望每个人都可以买一套实现所有客户共享,因为这是他们的生财之本。所以官方做了许多限制,比如无法把SQL后台登录名和口令包括在连接信息中,而是独立出来。连接信息可以用参数动态传递的初衷应该连接多个年度的数据库,我们只是变通利用了这一点。
编辑于 2018-6-11 03:14  
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

7回帖数 3关注人数 3993浏览人数
最后回复于:2018-6-11 13:36

返回顶部 返回列表