To support the scenario where different users 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 will need to be deployed with different PowerServer projects, each project connects to a specific database type.
The application can be modified to 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# solution can generate PowerServer C# models for only one database type. That is to say, PowerServer Web APIs can support only one database type in one project. If you have DataWindow objects that work for multiple database types (for example, both Oracle and SQL Server), you may encounter datatype mismatch issues during runtime.
In this case, the suggested approach is to create separate PowerServer projects, each configured with a different database type. This involves building the PowerServer C# solutions to different paths and deploying different PowerServer Web API applications. This ensures compatibility and avoids potential SQL errors when running the application against different databases. (Note: The client app files deployed from these projects will be the same, however, you need to configure each client app to access the specific PowerServer Web API app or call the SetPowerServerURL function to set the Web API URL at runtime).
Alternatively, it is possible to use different DB connection profiles (Cache Groups) within one project. However, it requires manually switching the profile before building and deploying, 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.
