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
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.
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