带有Access MDB文件的CONTEXT_INFO [英] CONTEXT_INFO with Access MDB file

问题描述

这与我先前的问题是分开的，我认为它保证了它自己的位置。这个问题得到了充分回答。这是不同的。我正在使用CONTEXT_INFO将用户信息从ADP项目传递给SQL中的活动日志记录触发器。它很棒。我正在尝试从MDB实施相同的策略，但它不起作用。看来虽然ADP在ADP打开时ADP与后端保持有效连接，但我猜MDB没有。谁能证实这一点？如果是这样，有人建议在MDB打开时强制建立永久连接的方法，以便CONTEXT_INFO可以工作吗？我更喜欢这种策略而不是另一个人建议但如果我别无选择，我会采取另一种方式。在我看来这应该工作，因为CONTEXT_INFO最初打算在Web应用程序和SQL Server之间使用。



这是后端的设置代码：

  ALTER   PROCEDURE  [Audit]。[SetUsersContextInfoForSession_SP] 
（
  @ UserInitials   VARCHAR （ 5 ），
  @ CI   VARCHAR （ 100 ） OUTPUT  
）
 
  AS  
 
 < span class =code-keyword> BEGIN  TRY 
 
  SET   NOCOUNT   ON  
  SET   XACT_ABORT   ON  
 
  DECLARE   @ucv   VARBINARY （MAX）
  SET   @ ucv  =（ SELECT  UsersContextInfo  FROM  dbo.tblUsers  WHERE  UserInitials =  @ UserInitials ）
 
  SET   CONTEXT_INFO   @ ucv  
  SET   @ CI  = CAST（ CONTEXT_INFO （） AS   VARCHAR （ 100 ））
 
  END  TRY 
  BEGIN  CATCH 

我也是有一个get例程仅用于测试：

  ALTER   PROCEDURE  [审核]。[GetUsersContextInfoForSession_SP] 
（
  @ UserIntitials   VARCHAR （ 5 ） OUTPUT  
）
 
  AS  
 
  BEGIN  TRY 
 
  SET   NOCOUNT   ON  
  SET   XACT_ABORT   ON  
 
  SET   @ UserIntitials  =（ SELECT  UserInitials  FROM  dbo.tblUsers  WHERE  UsersContextInfo =  CONTEXT_INFO （））
 
  END  TRY 
  BEGIN  CATCH 

接下来是Access中的代码：



'设置此用户会话的上下文信息

  Dim  cmdSetCI  As  ADODB.Command 
 设置 cmdSetCI = 新 ADODB.Command 
 使用 cmdSetCI 
 .ActiveConnection = sConnectionForStoredProcedures 
 .CommandType = adCmdStoredProc 
 .CommandText =   Audit.SetUsersContextInfoForSession_SP  
 .Parameters.Append .CreateParameter（  @ UserIntitials，adVarChar ，adParamInput， 5 ，strCurrentUserInitials）
 .Parameters.Append .CreatePa rameter（  @ CI，adVarChar，adParamOutput， 30 ，strCurrentUserInitials）
。执行
 MsgBox .Parameters.Item（  @ CI ）。价值
 结束   
 

'测试一下是否有效

 Dim cmdGetCI As ADODB.Command 
设置cmdGetCI =新ADODB.Command 
使用cmdGetCI 
 .ActiveConnection = sConnectionForStoredProcedures 
 .CommandType = adCmdStoredProc 
 .CommandText =Audit.GetUsersContextInfoForSession_SP
 .Parameters.Append。 CreateParameter（@ UserIntitials，adVarChar，adParamOutput，5，strCurrentUserInitials）
。执行
 MsgBox .Parameters.Item（@ UserIntitials）。Value 
 End with 
 

再次，Access中的第一个代码块工作正常。 ADP项目中的第二个也是如此。它返回了我的期望。但是在MDB中，第二个代码块失败，因为它调用的例程返回@UserIntials = NULL



谢谢



AR

解决方案

MS Access 不支持 [ ^ ]类似 CONTEXT_INFO [ ^ ]。



每次打开* .mdb数据库时，数据库引擎都会创建* .ldb文件，其中包含有关连接，登录用户等的几个信息。您可以r使用VBA进行使用 [ ^ ]。

This is separate enough from my previous question I think it warrants its own spot. That question was answered sufficiently. This is different. I'm using CONTEXT_INFO to pass user information to an activity logging trigger in SQL from an ADP project. It works great. I'm trying to implement the same strategy from an MDB but it's not working. It appears that although an ADP maintains an active connection with the backend while the ADP is open, I guess an MDB does not. Can anyone confirm this? And if so, can anyone suggest a way to force a permanent connection while the MDB is open so that CONTEXT_INFO will work? I prefer this strategy over another someone suggested but I will go the other way if I have no choice. It seems to me this should work since CONTEXT_INFO was originally intended for use between web apps and SQL Server.

Here is the "set" code in the backend:

ALTER PROCEDURE [Audit].[SetUsersContextInfoForSession_SP]
(
	@UserInitials VARCHAR(5),
	@CI VARCHAR(100) OUTPUT
)

AS

BEGIN TRY

	SET NOCOUNT ON
	SET XACT_ABORT ON

	DECLARE @ucv VARBINARY(MAX)
	SET @ucv = (SELECT UsersContextInfo FROM dbo.tblUsers WHERE UserInitials = @UserInitials)

	SET CONTEXT_INFO @ucv 
	SET @CI = CAST(CONTEXT_INFO() AS VARCHAR(100))

END TRY
BEGIN CATCH

I also have a "get" routine for testing only:

ALTER PROCEDURE [Audit].[GetUsersContextInfoForSession_SP]
(
	@UserIntitials VARCHAR(5) OUTPUT
)

AS

BEGIN TRY

	SET NOCOUNT ON
	SET XACT_ABORT ON
		
	SET @UserIntitials = (SELECT UserInitials FROM dbo.tblUsers WHERE UsersContextInfo = CONTEXT_INFO())

END TRY
BEGIN CATCH

And then here's the code in Access:

'set the context info for this user's session

Dim cmdSetCI As ADODB.Command
Set cmdSetCI = New ADODB.Command
With cmdSetCI
    .ActiveConnection = sConnectionForStoredProcedures
    .CommandType = adCmdStoredProc
    .CommandText = "Audit.SetUsersContextInfoForSession_SP"
    .Parameters.Append .CreateParameter("@UserIntitials", adVarChar, adParamInput, 5, strCurrentUserInitials)
    .Parameters.Append .CreateParameter("@CI", adVarChar, adParamOutput, 30, strCurrentUserInitials)
    .Execute
    MsgBox .Parameters.Item("@CI").Value
End With

'test to see if it worked

Dim cmdGetCI As ADODB.Command
      Set cmdGetCI = New ADODB.Command
      With cmdGetCI
          .ActiveConnection = sConnectionForStoredProcedures
          .CommandType = adCmdStoredProc
          .CommandText = "Audit.GetUsersContextInfoForSession_SP"
          .Parameters.Append .CreateParameter("@UserIntitials", adVarChar, adParamOutput, 5, strCurrentUserInitials)
          .Execute
          MsgBox .Parameters.Item("@UserIntitials").Value
      End With

Again, the first block of code in Access works fine. And so does the second one in the ADP project. It returns what I expect. But in the MDB, the second block of code fails because the routine it calls returns @UserIntials = NULL

Thanks

AR

解决方案

MS Access does not support[^] something like CONTEXT_INFO[^] in MS SQL Server.

Every time when *.mdb database is opened, the database engine creates *.ldb file which contains several info about connection, logged users, etc. You can read it using VBA[^].

这篇关于带有Access MDB文件的CONTEXT_INFO的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！