1. Arthur Hefti
  2. PowerServer
  3. Tuesday, 19 October 2021 06:40 AM UTC

Hi 

is there a way that I can intercept the SQL executed on the PowerServer side. We have some temporary tables with connection specific information that can't be used when working with a connection pool.

So my idea is that each client provides some information when communicating with the server that allows to modify the SQL before executing it.

Regards
Arthur

Arthur Hefti Accepted Answer Pending Moderation
  1. Friday, 29 October 2021 10:49 AM UTC
  2. PowerServer
  3. # 1

Hi

I want to share what I did to replace some temporary tables with connection specific information (sample is in SQL Anywhere). We have a table that e.g. contains the language of the logged in user. This is filled in startup and used e.g. in queries for DDDW so we don't need to pass a language argument. This could look like:

SELECT (CASE language 
      WHEN 'FR' then textFR
      WHEN 'IT' then textIT
      ELSE textDE
    END),
  (SELECT language FROM #templang) as language
FROM lookuptable1

We used to have a generic user that logs into the database.

As suggested by Armeen in https://community.appeon.com/index.php/qna/q-a/ps-2021-handling-of-db-context-variables I created for each user a user on the database. Each new users will be granted the same rights as the generic user has. On the connection cache I checked "Allow dynamic connection using the transaction LogID and LogPass".  However for ODBC I had to pass the user information as UID and PWD in DBParm.

We already have a table with users where the language and other information including the username are stored. I created a view with the same name as the temporary table, where "current user" is the logged in user.

CREATE VIEW #templang
AS SELECT userlanguge as language
FROM usertable
WHERE username = current user;

Other temporary tables that we use with inserts and selects I will change to "normal" tables and add a column for the username or another information set when filling it in.

Regards
Arthur

Comment
There are no comments made yet.
Marco Meoni Accepted Answer Pending Moderation
  1. Tuesday, 19 October 2021 07:18 AM UTC
  2. PowerServer
  3. # 2

Hi Arthur,

you mean intercept = inspect the SQL? I suggest you use Fiddler for that:

https://docs.appeon.com/ps2021/bk09ch02s07.html

Regarding your issue with temporary tables, I think it has same key reason of my issue with context variables

https://community.appeon.com/index.php/qna/q-a/ps-2021-handling-of-db-context-variables

Connection pool doesn’t allow these schemas because as soon as connection is closed and returns to the pool, they [temp tables or context vars] get dropped.

But I am confident Appeon is working on this and will come up with a workaround ;-)

Best,

.m

 

Comment
  1. Arthur Hefti
  2. Tuesday, 19 October 2021 15:15 PM UTC
Hi Marco

it's less that I want to know what's send from the PB Client to the server. I want to change the SQL that the server executes against the database based on the client i.e. the ID or the user log into the client. This would allow me to overcome the problem with the temporary tables and long running connections.
  1. Helpful
  1. Marco Meoni
  2. Tuesday, 19 October 2021 16:16 PM UTC
hi Arthur,

so, you need to send the modified (portion of) SQL to the Web API before it executes a query.

If you use .NET Datastore, have a look at :

- https://docs.appeon.com/net_datastore/3.1/api_reference/DWNet.Data/DataStore/IDataStoreBase/Method/SetSqlSelect.html

- https://docs.appeon.com/net_datastore/3.1/api_reference/DWNet.Data/DwMeta/IDwNode/Method/Modify.html

Otherwise you can opt for SQlBuilder and SqlExecutor to build SQL dynamically on the server-side?

Best,

.m

  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.