Scenario 1: DataWindows work with different database types in different user environments (ISV situation)
DataWindows are designed to work with multiple database types so that different users can connect to different database types from the same application, for example, user A connects with the Oracle database, user B connects with the SQL Server database.
The application can support this scenario and run successfully when deployed as a PowerBuilder C/S application, however, when this application is deployed via PowerServer, 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 with multiple database types at the same time. If you have DataWindow objects that work for multiple database types (for example, a single DataWindow is designed to work with both Oracle and SQL Server), you may encounter datatype mismatch issues during runtime.
In this case, the recommended approach is to create separate PowerServer projects, each configured with one 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\
This will generate two separate C# solutions, each with C# models tailored to a 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:
For Oracle: https://yourserver.com/api-oracle/
For SQL Server: https://yourserver.com/api-sqlserver/
-
Configure the client app to connect to the correct Web API application.
The client app files can be 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 each client app in the PowerServer project settings (before deployment) or in the apprun.json config 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 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 time-consuming.
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 your application connects with multiple database types, for example, dw_1 connects with an Oracle database and dw_2 connects with a SQL Server database, you will have to configure multiple separate caches, one for each database type.
This typically involves the following steps:
-
In the same PowerServer project, create separate database connection caches, one for each target database type.
Example:
Oracle_cache for DataWindows such as dw_1 connecting with the Oracle database
SQLServer_cache for DataWindows such as dw_2 connecting with the SQL Server database
-
Map the database cache to the transaction object.
-
Deploy the PowerServer project.
The compiler will attempt to connect using the cache in sequence until it finds the correct table that maps to each DataWindow.
For example, when generating the model for dw_1, the compiler first uses the configured Oracle cache Oracle_cache. It successfully locates the table in the Oracle database based on the DataWindow definition and generates the model accordingly. When generating the model for dw_2, the compiler initially uses Oracle_cache as well. However, since the corresponding table cannot be found in Oracle, it automatically switches to the configured SQL Server cache SQLServer_cache. There, it locates the table schema and successfully generates the model.
