Configure the database connection

Before you can build and deploy a PowerServer project, you MUST configure the database connection in the Database Configuration window (from the PowerServer project painter > Web APIs tab > Database Configuration button).

Database connection is required 1) when converting the PowerBuilder DataWindow objects to C# models during the deployment process; and 2) when accessing data from the database at application runtime. The database information (including the cache settings and the transaction-to-cache mappings) will be deployed to the ServerAPIs project in the PowerServer C# solution.

This section talks about creating a database connection cache and mapping it with the transaction object in the Database Configuration window. (You may want to consider the other database connection methods as discussed in Working with Database Connections).

To configure the database connection in the Database Configuration window:

  1. Click the Database Configuration button at the bottom of the Web APIs tab.

  2. In the Database Configuration dialog, you can create various DB connection profiles which include database connections to be used in different environments, for example, database connections for the development environment, testing environment, production environment, etc.

    To create a new DB connection profile:

    • Click New in the DB connection profile group.

    • In the New DB connection profile dialog box, specify a name for the DB connection profile, for example, production.

    • To create the new connection profile from an existing profile, you can select the check box below and then select an existing profile to clone from.


    You can then decide which profile to be used in the application by selecting the profile and clicking the Set as Current button.

  3. In the Database Configuration dialog, you can create the connection cache that connects with the database.

    For example, you can establish a connection with the SQL Anywhere database for the PowerBuilder demo using the following settings:

    • Click New in the upper part of the Connection configuration group.

    • In the Database Configuration dialog box, specify any text as the cache name.

    • Specify SQL Anywhere (ODBC) as the database provider.

    • Select the data source.

    • Specify the user name (for example, dba) and password (for example, sql).

    • Click Test Connection to make sure the database can be connected successfully.

    The "Allow dynamic connection using the transaction LogID and LogPass" option allows the application to use the LogID and LogPass property values of the Transaction object to log in to the database server as shown in the example below (instead of using the values in the User name and Password fields). For more, refer to Using LogID and LogPass properties.

    Transaction.LogId = "sa"
    Transaction.LogPass = "Appeon123!@#"

    The Advanced button contains additional important settings for the database driver such as DelimitIdentifier, TrimSpaces, etc. If your database has such settings, make sure to click the Advanced button to configure those settings.


    If you select MySQL, Oracle, or Informix from the Provider listbox, you will be asked to specify a location for the required driver (MySql.Data 8.0.25, Oracle.ManagedDataAccess.Core 2.19.110, or IBM.Data.DB2.Core 2.2.0.100) or allow PowerBuilder to download and install the required driver from the NuGet website.

    The packages downloaded from the NuGet website will be stored to %USERPROFILE%\.nuget\packages and cached in %USERPROFILE%\.sd\19.0\dbDrives\, so they can be automatically loaded when the database connection is created.


  4. After the database cache is created, you can map the transaction object with the cache in the Database Configuration dialog. To do this:

    • Click New in the lower part of the Connection configuration group.

    • Input the transaction object name (for example "sqlca2") and then select the cache to map with.


    Rather than making static mappings of the cache and the transaction object (as shown above), you can also create dynamic mappings by using the DBParm CacheName property. For more details, see Working with Database Connections.

To manually configure the database connection in the ServerAPIs project:

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.) and the transaction-to-cache mappings configured in the Database Configuration window will be deployed and stored in PowerServer and you can manually change these settings in the PowerServer C# solution. To do this:

  1. Open the PowerServer C# solution > ServerAPIs project > AppConfig > Applications.json or Applications.[DBConnectionProfile].json file.

  2. In the Applications.json (or Applications.[DBConnectionProfile].json) file, locate the "Applications" block > [application name] > "CloudTransactions'. This is where the transaction-to-cache mapping(s) is stored.

    In the following example, the "sqlca" transaction object is mapped to the "local-sa" database cache. You can modify the existing mapping, or create a new mapping by making a copy of the existing one.

      "Applications": {
        "pssales": {
          "CloudTransactions": {
            "sqlca": {
              "CacheName": "local-sa"
            }
          },
          ...
  3. In the Applications.json (or Applications.[DBConnectionProfile].json) file, locate the "Connections" block. This is where the cache(s) is stored.

    In the following example, there are two caches "local-sa" and "local-postgresql" under the "Default" cache group; and each cache contains the database connection information that are configured and deployed from the Database Configuration window. You can modify the existing cache, or create a new cache by making a copy of the existing one.

    ...
      "Connections": {
        "Default": {
          "local-sa": {
            "ConnectionType": "Odbc",
            "OdbcName": "PB Demo DB V2021",
            "OdbcDriver": "SqlAnywhere",
            "UserID": "dba",
            "Password": "eyJQYXlsb2FkIjoiYlx1MDAyQkxocTNiMUtWSzhBY1FCbVltU0FBPT0iLCJUaW1lc3RhbXAiOjE2MjU2NDYwNDcsIlNpZ25hdHVyZSI6IkF5V253VzNVNVx1MDAyQk5mNUxOd2RGTG83alVQeWRVYlpaUEtWcG5PU012cVx1MDAyQm95RTVtVlkwblQ3NHVqSFBHcm5NdVVQQUhnRFhKSklRZ1hiZ2c3Y3hGSG1jZz09In0=",
            "CommandTimeout": 30,
            "OtherOptions": "",
            "DynamicConnection": false
          },
          "local-postgresql": {
            "ConnectionType": "PostgreSql",
            ...
          }
        }
      }

    But notice that the PowerServer C# solution will be updated every time when the PowerServer project is built and deployed in the PowerBuilder IDE. If you manually modify the settings in Applications.json (or Applications.[DBConnectionProfile].json), and want to keep these changes, you should use the "Overwrite server settings (DB connection, Web API port, and license)" option properly. For more information, refer to What settings will be deployed to the solution.