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
