When the PowerServer project is built and deployed in the PowerBuilder IDE, the cache settings (including database server host/port, database name, login ID, password, advanced settings etc.) configured in the PowerServer project painter > Database page will be deployed and stored in the PowerServer. You can manually change these settings in the PowerServer.
To manage database caches in the PowerServer:
-
Go to the compiled Web APIs > AppConfig folder (or the solution > UserExtensions project > AppConfig folder), and open the Applications.json (or Applications.[DBConnectionProfile].json) file in a text editor.
The Applications.json file contains the configuration of the "Default" DB connection profile. If you have another connection profile, the profile name is added in the middle of the file name. For example, Applications.Development.json file contains the configuration of the "Development" DB connection profile.
-
In the Applications.json (or Applications.[DBConnectionProfile].json) file, locate the "Connections" block. This is where the cache(s) is stored.
Take the following settings for example, the "Default" cache group contains the "sales" cache, and the connection settings vary according to the database type (refer to your DBMS documentation for more information).
"Connections": { "Default": { "sales": { "ConnectionType": "Odbc", ...... } } }"ConnectionType": The connection type. For SQL Anywhere and ASE databases, only ODBC connection type is supported; for the other databases, the native connection type is supported.
-
0: SqlServer
-
2: Oracle
-
3: MySql
-
4: DB2
-
5: PostGreSql
-
6: Odbc
-
7: Informix
"OdbcName": (For ODBC connections) The ODBC data source name.
"OdbcDriver": (For ODBC connections) The ODBC driver.
"Database": (For native connections) The database name.
"Host": (For native connections) The host name or IP address of the database server.
"Port": (For native connections) The port number of the database server.
"UserID": The database login user name.
"Password": The database login password. The password can be an encrypted value (encrypted by the CustomizeDeploy.dll tool) or a plain-text string.
"EnablePooling": Whether to enable the pooling feature.
"MinPoolSize": The minimum number of connections that are allowed in the pool. Refer to your DBMS documentation for more information.
"MaxPoolSize": The maximum number of connections that are allowed in the pool. Refer to your DBMS documentation for more information.
"ConnectionLifetime": When a connection is returned to the pool, its creation time is compared with the current time, and the connection is destroyed if that time span (in seconds) exceeds the value specified by Connection Lifetime. This is useful in clustered configurations to force load balancing between a running server and a server just brought online. Refer to your DBMS documentation for more information.
"ConnectionTimeout": The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. Refer to your DBMS documentation for more information.
"CommandTimeout": The wait time before terminating the attempt to execute a command and throwing an error. Refer to Configure timeout settings for more information.
"SecurityOptions": The security options for the database connection, such as Authentication, Encrypt, Integrated Security, Trust Server Certificate, Persist Security Info, User ID, Password etc.
The security options vary according to the database type (refer to your DBMS documentation for more information).
You can view all available options in the project painter > Database page > Basic tab > More button > Advanced dialog > Security category.
"SecurityOptions": "encrypt=True;trust server certificate=True"
"OtherOptions": Any database connection option that is available in the project painter > Database page > Basic tab > More button > Advanced dialog.
The options vary according to the database type (refer to your DBMS documentation for more information).
"OtherOptions" should not include those listed separately as standalone settings (such as Database, UserID, Password, EnablePooling, CommandTimeout etc.), because the settings in "OtherOptions" have higher priority (and will overwrite the standalone settings).
"OtherOptions" must not include the settings in the Advanced dialog > Other category, such as DelimitIdentifier, Is Nullable Type, and Outer Join Syntax; these settings are used for DataWindow-to-model conversions only, not for database connections; if these settings are included in "OtherOptions", database connection errors will occur.
"OtherOptions": "Connect Retry Count=1; Connect Retry Interval=10"
"DynamicConnection": Whether the app connects to the database based on the user credentials provided at runtime. When it is set to true, the application will either use the LogID and LogPass property values of the Transaction object or the UID and PWD values in the ConnectString DBParm parameter to log in to the database server (instead of using the values in the User name and Password fields of the cache). Refer to Using LogID and LogPass properties for more information.
Following is a cache for SQL Anywhere:
"sales": { "ConnectionType": "Odbc", "OdbcName": "PB Demo DB V2022R2", "OdbcDriver": "SqlAnywhere", "UserID": "dba", "Password": "eyJQYXlsb2FkIj******", "CommandTimeout": 30, "SecurityOptions": null, "OtherOptions": null, "DynamicConnection": false },Following is a cache for PostgreSQL:
"sales_postgresql": { "ConnectionType": "PostgreSql", "Database": "PBDemo", "Host": "127.0.0.1", "Port": 5432, "UserID": "postgres", "Password": "eyJQYXlsb2FkIj******", "EnablePooling": true, "MinPoolSize": 0, "MaxPoolSize": 100, "ConnectionLifetime": 0, "ConnectionTimeout": 15, "CommandTimeout": 30, "SecurityOptions": null, "OtherOptions": null, "DynamicConnection": false },Following are two caches "local-sa" and "local-postgresql" under the "Default" cache group:
... "Connections": { "Default": { "local-sa": { "ConnectionType": "Odbc", "OdbcName": "PB Demo DB V2022R2", "OdbcDriver": "SqlAnywhere", "UserID": "dba", "Password": "eyJQYXlsb2FkIjoiYlx1MDAyQkxocTNiMUtWSzhBY1FCbVltU0FBPT0iLCJUaW1lc3RhbXAiOjE2MjU2NDYwNDcsIlNpZ25hdHVyZSI6IkF5V253VzNVNVx1MDAyQk5mNUxOd2RGTG83alVQeWRVYlpaUEtWcG5PU012cVx1MDAyQm95RTVtVlkwblQ3NHVqSFBHcm5NdVVQQUhnRFhKSklRZ1hiZ2c3Y3hGSG1jZz09In0=", "CommandTimeout": 30, "SecurityOptions": null, "OtherOptions": null, "DynamicConnection": false }, "local-postgresql": { "ConnectionType": "PostgreSql", ... } } }The cache contains the database connection information that is configured and deployed from the Database page > Advanced tab. You can modify the existing cache, or create a new cache by making a copy of the existing one.
Note: (1) You can directly make changes to the compiled Web APIs; you can also make changes to the PowerServer C# solution and then compile the Web APIs. If you make changes to the PowerServer C# solution, notice that the PowerServer C# solution will be updated every time when the PowerServer project is built and deployed in the PowerBuilder IDE. For more information, refer to What settings will be deployed to the solution. (2) If you want to change the database type, you must change the database type in the project painter and then re-deploy the project from the PowerBuilder IDE. Changing the driver directly in PowerServer would cause failure in the running of the installable cloud app.
-
