Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (2)

Resolved Set Context_info in PowerServer

  1. How-to
0
Votes
Undo
  1. Mark Lundell
    Mark Lundell
  2. PowerServer 2020 or older (Obsolete)
  3. Wednesday, 15 July 2020 18:31 PM UTC

My application currently uses the following to set a variable for use by a MS SQL Sever 2014 database trigger.  This does not work in PowerServer.  Is there a workaround of some variety?

 

ls_SQL = "Declare @TheUser VARCHAR(128)  SET @TheUser='" + as_userid + "'" &
+ " Declare @BinUser varbinary(128) SET @BinUser=CONVERT(varbinary(128), @TheUser)" &
+ " SET CONTEXT_INFO @BinUser"
EXECUTE IMMEDIATE :ls_SQL USING SQLCA;

  1. SQL Server
Appeon FileService - File upload way too slow
get image QR Code different between exe in window ...
Responses (2)
Mark Lundell
Mark Lundell Accepted Answer Pending Moderation
  1. Friday, 17 July 2020 17:38 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 1

I tried this with pooling off on my local version of PowerServer.  This gives no error, but does not work.  I can see the SQL in the PS logs, but it indicates no error.   Might it have something to do with the varbinary(128).

Comment
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Wednesday, 15 July 2020 18:48 PM UTC
  2. PowerServer 2020 or older (Obsolete)
  3. # 2

by default powerserver uses pooled (shared) database connections for each connection.  So setting the value would not do much of anything.  You might be able to set it prior to every database interaction and it should work as long as you have an open transaction started before you set that.

There is an option for the database connections to turn off pooling.  That is setup in AEM:  Server | Resources | Data Source.  If you create your datasource at runtime, you would have to set it for pooling off there too.

 

 

Comment
Mark Lundell
  1. Mark Lundell
  2. Wednesday, 15 July 2020 18:58 PM UTC
Please define "Open Transaction". Thanks
  1. Helpful
mike S
  1. mike S
  2. Wednesday, 15 July 2020 19:19 PM UTC
a database transaction.

i *think* having it open (not committed) forces the IIS server to not share that pooled database connection.

It would be a bit of PITA to ensure that is being called prior to your database processing.



also, this isn't specific to powerserver - so a search in stack exchange for pooled transactions and context_info might yield more/better info



if you can get it to work with pooled database connections, post back on here to let us know it is possible
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.