Implementing Multi-Tenant Driver

[besttech]

Dion presented in Devcon 2020 how to setup Multitenant connection string to use the build-in driver. When I was developing my app I didn't realize there was this option and I rolled my own. But now I am converting to the built in driver. I have added a field to all my tables and queries a new field called tenant_id.

The step that wasn't covered is how to set the session variable for the tenant id in a real world situation. I decided to add the code to my login ux under the Server-side onLogin event and I would like to have someone who has more expertise on Xbasic to look over my code let me know if it is solid.

I get the tenant id from the extended user table.

e.url = "index.a5w"

'DIM a SQL arguments object
dim args as sql::arguments

'adds an argument that gets the logged in user id from e.username to be used as a filter in the SQL
args.add("arg_user_id",e.username)

'DIM an Xbasic AlphaDAO connection object
dim cn as sql::connection
dim flag as l
''open the connection object to connect to the Database
flag = cn.open("::Name::AlphaServerConn")
dim sql as c
sql = "SELECT USERID, tenant_id FROM extendeduserinformation WHERE USERID = :arg_user_id"
'turn portable SQL on. (for more information on portable sql search in help (https://www.alphasoftware.com/documentation)
'execute the SQL - pass in the sql::arguments object
flag = cn.execute(sql,args)
if flag = .f. then
'an error occurred - get the error message from cn.callresult.text
'report the error, close the database and then end the script
dim errortext as c
errortext = cn.callresult.text
cn.close()
end if
'DIM a SQL::Resultset object
dim rs as sql::resultset
rs = cn.resultset
'get the JSON from the query
dim json as c
json = rs.toJSON()

'parses the json
dim p as p
p = json_parse(json)
dim msg as c

'Sets the session variable
session.tenant_id = p[1].tenant_id

'Clean up by freeing the resultset and then closing the database
cn.freeresult()
cn.close()

Thanks
David

[Jay Talbott]

I believe that your code will work, but personally, I would modify it to something more succinct:

dim arg_user_id as C = ""
arg_user_id = e.username

dim sql as C = ""
sql = "SELECT tenant_id FROM extendeduserinformation WHERE USERID = '"+arg_user_id+"'"

session.tenant_id = VAL( sql_records_get("::Name::AlphaServerConn",sql,"","") )

e.url = "index.a5w"

Jay

[Sarah Mitchell]

If you're using the Extended User Information Table feature in Alpha Anywhere, we have an Xbasic function for getting the info for a user:

dim userId as c = a5ws_GetCurrentUser()
dim info as p
info = a5w_getExtendedUserInfo(userId)

if (info.error == .f.) then ' successfully got data
    dim userInfo as p = json_parse(info.json)

    ' Assumes tenant_id is a field in your extended user info table:
    context.session.tenant_id = userInfo.tenant_id

else
   ' could not retrieve data
   TRACE.writeln("Error retrieving user info: " + info.errorText)
end if

[besttech]

Thanks for the responses, very helpful.

David

[besttech]

Thanks Sarah, I implemented your code and it works perfectly. Just one question, what is the difference between context.session.tenant_id = and session.tenant_id = (what does context add)?

David

[Sarah Mitchell]

Thanks Sarah, I implemented your code and it works perfectly. Just one question, what is the difference between context.session.tenant_id = and session.tenant_id = (what does context add)?

David

Technically, no difference at this time. But we are encouraging people to use context.session rather than session and it's what we try to remember to use in all of our examples in documentation.