Creating a SAAS Application with PowerServer 2022

Hi Jim;

  For a no recompilation approach for SaaS PS based applications, may I suggest ...

For the client side, please use the DBParm "CacheName" value. https://docs.appeon.com/pb2022/connection_reference/CacheName.html

For the PS Server side, use the DataBase approach. https://docs.appeon.com/ps2022/Managing_database_connections_using_APIs.html

HTH

Regards ... Chris

Hi Chris

Apologies - I'm providing cachename in my DBParm. It gets appended to the standard DBParms shown above, I just forgot to include it - my brain was frazzled a bit after intensive searching through docs.

I use a function of_set_cachename to switch from initial database holding client details and their cachename/dbname). It disconnects current connection and connects to the new cachename for the client so PS will run against their DB.  Code  snippet below.

// Disconnect from current cache
ll_rc =SQLCA.of_disconnect()

this.dbparm = is_dbparm_base +  ', CacheName= ' + as_cachename
ll_rc = SUPER::of_connect()

I'm not sure what is meant by no recompilation approach - Does this mean that the DBParm settings provided in code like LongConnection and ScopeIdentity will be used in the PS server side connection even if not in config details?

Cheers

Jim

Jim,

"new databases for different clients" you want to use the table driven connection cache setup option for PS.

In there: most parameters must be in the poweserver_connectionconfig table, BUT NOT ALL OF THEM!

in the table in the otheroptions column, you set MARS, not dbparm:   MultipleActiveResultSets=True  

Note that i have it set to TRUE.  The default is FALSE.  I think you want to set it to true, unless you are running it in the mode where each user has their own database connection (dynamicconnection=true).  and even then you probably want to set it to true.  Look in the PS bug reports, i have a bunch listed for PS.

in the dbparm, you want to add:  NCharBind=0  if you use varchar, and most likely TrimSpaces.   If you use nvarchar then do not set this.   if you do have your varchar columns as varchar and not nvarchar, you really want this setting as it fixes a number of issues including trailing spaces and index usage (IMO these are bugs/problems in sql server, not the c# apis)

these are my settings:

SQLCA.DBParm="CacheName='" + as_connection + "',TrimSpaces=1"

SQLCA.DBParm += ",NCharBind=0" //do NOT use NCharBind=0
SQLCA.DBParm +=",DisableBind=0" //tells SQL to BIND //ADO.NET default is 1

Hi Jim,

For your PowerScript, due to the architectural difference between the installable cloud app and the PowerBuilder native C/S app, not all of the DBParm properties are supported by PowerServer; and some DBParm properties are designed exclusively for use in PowerServer. Refer to Supported DBParm properties - - PowerServer 2022 R2 Help (appeon.com) 

For other connection string options supported by SQL Server .NET Data Provider, if required, you can specify them in the OtherOption when adding them to your configuration database by API.

Regards, Logan

Hi Logan - thanks for the link. It helped with clarification. I have to set cachename DBPARM using PowerScript which will use the corresponding configuration in the cache database. Some other DBPARM values can be set as per your link. I then need to go through the documentation for SQL Server .NET Data Provider and set them up using otheroptions in the cache configuration. The otheroptions will be applied to the dataprovider.

I'm sure many other community members using Powerserver will also need to research this as well. Why don’t Appeon document a mapping of existing DBPARMS to otheroptions and include it in the PowerServer documentation?

Documentation on PowerBuilder DBPARM values for SNC SQL Native Driver and other drivers, so I think similar documentation should be available for PowerServer users

Kind Regards

Jim

Hi Mike

Thanks for the info. We need to hold user information in session_context for each connection. so we need longconnection. For C/S we were using SUSER_SNAME() which isn't available in PowerServer approach.

I saw some of your bug reports (you're a busy man) when I was searching for info on otheroptions - I think Appeon should provide documentation on otheroptions -- they provide it for Powerbuilder database connections and it's more or less same principle.

Cheers

Jim