Scenario 1: DataWindows work with different database types in different user environments (ISV use case)
In ISV scenarios, DataWindows are often designed to work with multiple database types so that different users can connect to different types of databases from the same application, for example, user A connects to an Oracle database, user B connects to a SQL Server database.
The PowerBuilder traditional C/S application can support this scenario seamlessly, however, when deployed as the PowerServer installable cloud application, the PowerServer C# model generated for each DataWindow is specific to the database type (either Oracle or SQL Server in this example) and cannot be used across different database types. If you have DataWindow objects that work for multiple database types (for example, a single DataWindow is intended to work with both Oracle and SQL Server), you may encounter datatype mismatch issues at runtime.
In this case, the recommended approach is to create separate PowerServer projects, each configured for one specific database type, which involves the following steps:
-
Create separate PowerServer projects, one for each target database type.
Example:
PowerServerProject_Oracle
PowerServerProject_SQLServer
For each project, configure the appropriate database cache:
In PowerServerProject_Oracle: Oracle_DB_cache
In PowerServerProject_SQLServer: SQL_Server_DB_cache
Map the database cache to the transaction object.
-
Generate C# solution for each project.
In each PowerServer project, choose a different output folder for the C# solution to avoid overwriting.
Example:
C:\Users\appeon\source\repos\salesdemo_cloud\API_for_Oracle\
C:\Users\appeon\source\repos\salesdemo_cloud\API_for_SQLServer\
Build each PowerServer project separately. This will generate two C# solutions, each containing C# models tailored to the specific database type.
-
Deploy the PowerServer Web API applications separately.
Publish each PowerServer Web API application (via IIS, Docker, or cloud platform) to different endpoints.
Example:
Oracle: https://yourserver.com/api-oracle/
SQL Server: https://yourserver.com/api-sqlserver/
-
Configure the client app to connect to the correct Web API application.
The client app files (EXE/PBDs etc.) remain the same across both deployments. The difference lies in which Web API application the client connects to.
You have two options:
Option A: Pre-configure the Web API URL in the PowerServer project settings before deployment or in the apprun.json file after deployment.
Option B: Dynamically set at runtime by calling the SetPowerServerURL function.
This ensures compatibility and avoids potential SQL errors when running the application against different databases.
Alternatively, it is technically possible to use different DB connection profiles (Cache Groups) within one project. However, it requires manually switching the profile before build & deploy, which can be cumbersome and inefficient.
If you have specific database syntax or object differences, it is recommended to handle them by either moving DBMS-specific syntax to the database (using views, stored procedures, or functions with the same name across all databases) or generating the syntax at runtime. This approach helps ensure compatibility across different database types.
Scenario 2: Some DataWindows work with one database type, and others work with a different database type
If some DataWindows work with one database type and others work with a different database type, for example, dw_1 connects to an Oracle database and dw_2 connects to a SQL Server database, you will need to configure multiple separate database caches, one for each database type.
Steps to configure multiple caches in a single PowerServer project:
-
In the same PowerServer project, create separate database caches.
One cache per target database type.
Example:
Oracle_cache -- used by DataWindows (like dw_1) that connect to the Oracle database.
SQLServer_cache -- used by DataWindows (like dw_2) that connect to the SQL Server database.
-
Map each database cache to the appropriate transaction object in your application.
-
Deploy the PowerServer project.
How the compiler handles multiple caches
During deployment, the compiler attempts to connect using the configured database caches in sequence until it finds the correct table schema for each DataWindow.
Example:
-
When generating the model for dw_1, the compiler first tries Oracle_cache. It successfully locates the required table in the Oracle database based on the DataWindow definition and generates the model.
-
When generating the model for dw_2, the compiler also begins with Oracle_cache. However, it fails to find the corresponding table in Oracle, so it automatically tries the next cache, SQLServer_cache. There, it finds the table and generates the model successfully.
