Managing database caches in PowerServer
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).12345678"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.1"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.1"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:
1234567891011"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:
1234567891011121314151617"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:1234567891011121314151617181920..."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. -