Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (6)

Resolved PowerServer C# Project DBConnectionProfile DBPARM values

  1. How-to
0
Votes
Undo
  1. Jim Nesbitt
    Jim Nesbitt
  2. PowerServer
  3. Wednesday, 24 May 2023 19:11 PM UTC

I'm converting a Powerbuilder application to PowerServer so it can operate using the SaaS model.

I've been following the guidelines in this video.

Creating a SAAS Application with PowerServer 2022

https://www.youtube.com/watch?v=a5U4XblFJbA&ab_channel=Appeon

We need to hold database configuration in the database also so we can set up new databases for different clients. While preparing for this, I checked the docs in 

https://docs.appeon.com/ps2022/Introduction_to_the_PowerServerApp_solution.html

We set a number of values in the DBPARM for SQLCA when connecting with PowerBuilder, but I couldn't see how our required values can be set from this documentation. I thought OtherOptions might be where to place them, but there's no info on adding the DBPARM values we need. It also mentions OtherOptions can be set up in the project painter, but it hasn't got the options I need. 

Ideally, I want to be able to use the  same details in PowerServer as we are providing in our PowerBuilder connectionstring DBPARM, but couldn't find out how to do this. 

My question is - will the DBPARM values be applied to the cachename connection by PowerServer (hopefully) - If not, how do I configure them

Samples of our DBParms and SnapDevelop configuration. 

-- DBPARM Settings
TrimSpaces=1;
StaticBind=0;
RecheckRows=1;
Identity='SCOPE_IDENTITY()'  ;
AppName='LoadmaX';
OJSyntax='ANSI';
TrustedConnection=1;
ProviderString = 'MARS Connection=False' ;
PBMaxBlobSize=21000000;
Database='loadmax_ps2022';
LongConnection=1

 

-- SNAPDEVELOP
  "Connections": {
    "Default": {
      "loadmax_cache": {
        "ConnectionType": "SqlServer",
        "Database": "loadmax_ps2022",
        "Host": "PBS41",
        "Port": 1433,
        "UserID": "sa",
        "Password": "*********",
        "EnablePooling": true,
        "MinPoolSize": 0,
        "MaxPoolSize": 100,
        "ConnectionLifetime": 0,
        "ConnectionTimeout": 15,
        "CommandTimeout": 30,
        "SecurityOptions": "encrypt=False",
        "OtherOptions": null,
        "DynamicConnection": false
      },

 

Cheers

Jim

 

  1. Configuration
  2. PowerServer
  3. SaaS
  4. SQL Server
  5. SnapDevelop
avoid dberror on Datawindow not existing initially
PowerServer SQL Server Insert row Identity functio...
Responses (6)
Jim Nesbitt
Jim Nesbitt Accepted Answer Pending Moderation
  1. Thursday, 25 May 2023 21:12 PM UTC
  2. PowerServer
  3. # 1

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

Comment
Armeen Mazda @Appeon
  1. Armeen Mazda @Appeon
  2. Friday, 26 May 2023 16:59 PM UTC
Hi Jim, If you need long connection then in your DBParm please set LongConnection=1: https://docs.appeon.com/ps2022r2/Enabling_long_connections.html
  1. Helpful 1
Jim Nesbitt
  1. Jim Nesbitt
  2. Friday, 26 May 2023 17:44 PM UTC
Hi Armeen



I was already setting longconnection=1. Logan pointed me to a section in the documentation that describes Supported DBParm Properties for Poweserver, which was really helpful.



I mentioned this to Logan - it would be really helpful if Appeon provided a mapping/cross reference of existing DBPARMS that are not supported in PowerServer showing now to set them up in otheroptions. This could help lots of PowerServer users in one go. Otherwise, they all have to go through Microsoft Docs.



I spent a lot of time going through docs etc. and if that was available in Supported DBParm Properties section, it would have been really useful.



I think I know exactly what I need to set up now. I just need to confirm something about PowerServer processing for PowerBuilder identityid - we use SQL Server. I'll raise a new Q&A for this.



My experience is that Appeon are very responsive which is great.



Have a good weekend - already starting mine here so transitioning into chill mode.



Cheers



Jim

  1. Helpful
There are no comments made yet.
Jim Nesbitt
Jim Nesbitt Accepted Answer Pending Moderation
  1. Thursday, 25 May 2023 21:10 PM UTC
  2. PowerServer
  3. # 2

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

 

Comment
There are no comments made yet.
Logan Liu @Appeon
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Thursday, 25 May 2023 09:17 AM UTC
  2. PowerServer
  3. # 3

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

Comment
There are no comments made yet.
mike S
mike S Accepted Answer Pending Moderation
  1. Wednesday, 24 May 2023 23:01 PM UTC
  2. PowerServer
  3. # 4

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

 

 

 

 

 

Comment
There are no comments made yet.
Jim Nesbitt
Jim Nesbitt Accepted Answer Pending Moderation
  1. Wednesday, 24 May 2023 21:12 PM UTC
  2. PowerServer
  3. # 5

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

 

 

 

 

 

 

 

Comment
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Thursday, 25 May 2023 17:57 PM UTC
Hi Jim;

1) Only CacheName & LongConnection are recognized PowerServer App DBParm settings.

2) For "No Recompilation" in a no SaaS customer implementation, you can drive the PS App "CacheName" value as a parameter driven approach and for the PS Server, it can use a DB Table to drive new CacheNames "on-the-fly". Thus, no recompile, deploy, etc of the PS App or PS Server is required.

HTH

Regards ... Chris
  1. Helpful
Jim Nesbitt
  1. Jim Nesbitt
  2. Thursday, 25 May 2023 21:13 PM UTC
Hi Chris



Thanks for the reply. The link from Logan was also useful.



Kind Regards



Jim
  1. Helpful
There are no comments made yet.
Chris Pollach @Appeon
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Wednesday, 24 May 2023 20:21 PM UTC
  2. PowerServer
  3. # 6

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

Comment
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.
We use cookies which are necessary for the proper functioning of our websites. We also use cookies to analyze our traffic, improve your experience and provide social media features. If you continue to use this site, you consent to our use of cookies.