Home
PowerBuilder
Appeon Documentation Center
JavaScript is disabled on your browser. Please enable JavaScript to enjoy all the features of this site.
Home
PowerBuilder
Appeon Documentation Center
PowerServer 2022 Help
Working with Database Connections
Sidebar
Prev
|
Up
|
Next
| Download PDF
Configuring database caches
Table of Contents
Creating database caches in the project settings
Select the database driver
Configure the database connection in the Database Configuration dialog
Create the DB connection profile
Managing database caches in the PowerServer solution
Contents
Search
What's New
PowerServer 2022
New Features
Cloud App Launcher refactoring
PowerServer project painter refactoring
PowerServer performance enhancements
PowerServerLabel and PowerServerResult
Unsupported feature detection enhancements
PowerServer project debugging
PowerServer toolbar changes
HTTP/2 support
TLS 1.3 support
Ignoring PowerServer certificate errors
Enhanced PowerScript features
.NET 6.0 support
Authentication template for AzureAD
Web API default port changes
PowerServer log level changes
Licensing server backup
Upgrade Guide
Bug Fixes & Known Issues
PowerServer 2021
Installation
PowerServer components
Architecture
Installation requirements
Client PC
Development PC
Web Server
.NET Server
Database Server
Network requirements
Quick Start
Guide 1: Deploying to local environment
Overview
Preparing a local development environment
Installing IIS in Windows 10
Verifying the example sales app
Minimal efforts: Deploying the sample PowerServer project
Adjusting the sample PowerServer project according to your environment
Opening the sample PowerServer project
Updating the server configuration
Updating the PowerServer solution path
Verifying the PowerServer license
Verifying the Web API URL
Verifying the database connection
Building and deploying the PowerServer project
Running the installable cloud application
Full experience: Creating and deploying a new PowerServer project
Creating the PowerServer project
Creating a new PowerServer project
Configuring the Client App page
Configuring the Database page
Verifying the Web Server page
Verifying the .NET Server page
Building and deploying the PowerServer project
Running the installable cloud application
Guide 2: Deploying to IIS (.NET server)
Scenario 1: Using local IIS as the .NET server
Setting up the local IIS server
Creating a new web site
Verifying the IIS web site
Installing ASP.NET Core Hosting Bundle
Publishing the Web APIs
Publishing to a folder
Copying files to IIS
Verifying the Web APIs
Modifying the client app
Running the installable cloud application
Scenario 2: Using remote IIS as the .NET server
Setting up a remote IIS server
Preparations
Choosing an appropriate port number
Installing IIS
Creating two IIS web sites
Verifying the IIS web site
Installing ASP.NET Core Hosting Bundle
Packaging the client app
Modifying and packaging the client app
Verifying the client app
Publishing the Web APIs
Modifying and building the PowerServer project
Configuring the database connection
Importing the production license
Building the project
Publishing the Web APIs to IIS
Publishing to a folder
Copying files to IIS
Verifying the Web APIs
Running the installable cloud application
Guide 3: Deploying second app to remote IIS
Creating two IIS web sites
Packaging the client app and Web APIs
Verifying the example graph app
Modifying and building the sample PowerServer project
Verifying the PowerServer license
Verifying the solution location
Specifying the Web API URL
Configuring the database connection
Configuring the client app package
Building the project
Copying the client app
Publishing the Web APIs
Verifying the Web APIs
Running the installable cloud application
How-to Guides
Overview
Configure the Web server for deployment
Install the Cloud App Launcher on the Web server
About cloud app launcher
Customize cloud app launcher
Upload the cloud app launcher and the runtime files
Configure and deploy the PowerServer project
Create the PowerServer project
Configure the PowerServer project
Configure the Web API settings
Specify the Web API URL
Configure the solution basics
Select an authentication template
Import and deploy license
Configure the database connection
Select the database driver
Configure the database connection
Map the transaction object with the cache
Create DB connection profiles
Manually change the database connection in the ServerAPIs project
Analyze the unsupported features
Build the PowerServer project
Build and deploy the PowerServer project
Build & deploy using commands
Upgrade the PowerServer project
Understand and work with the PowerServer C# solution
What is the PowerServer C# solution
New solution vs. existing solution
What settings will be deployed to the solution
What settings can be modified in the solution
Run the ServerAPIs.Tests project
Configure the timeout settings
Configure log settings
Upgrade PowerServer NuGet packages
Run and manage PowerServer
About PowerServer Web APIs and Kestrel
Run the PowerServer Web APIs
Check the status of Web APIs
Support HTTPS
Use SSL certificate
Ignore PowerServer certificate errors
Support HTTP/2
Support TLS 1.3
Load certificate in Kestrel
Load certificate in IIS
Modify Windows 10 registry
Access PowerServer Management APIs
View API docs in Swagger UI
APIs for managing application and transaction mapping
LoadOne
LoadAll
Add
Edit
Remove
AddTransactionMapping
EditTransactionMapping
RemoveTransactionMapping
APIs for managing cache and cache group
LoadOne
LoadGroup
LoadAll
AddOne
AddRange
RemoveOne
Edit
AddGroup
RemoveGroup
APIs for managing license
LoadLicenseInfo
LoadInstanceInfo
APIs for managing session
LoadAll
GetSessionCount
KillById
APIs for managing statistics
LoadRequestSummaryStatistics
LoadRequestsStatistics
LoadTransactionSummaryStatistics
LoadFailedTransactionsStatistics
LoadSucceedTransactionsStatistics
APIs for managing transaction
LoadAll
LoadRequestSql
RollbackById
Run and manage the deployed app
Package and install the client app
Run the installable cloud application
Change the deployed app using commands
Change the Web API URL
Change the External Files
Encrypt the password
Customize the app entry page
Manage app versions/updates
Roll back to an earlier version of app
Support cookie validation
Undeploy the client app
Uninstall the client app
Tutorials
Tutorial 1: Deploying your PowerServer project to remote servers
Overview
Task 1: Preparing the environment
Setting up the client machine
Setting up the database server
Preparations
Configuring Windows Defender Firewall
Starting the database
Setting up the Web server
Supported Web servers
Preparations
Installing Web Server (IIS)
Creating an IIS FTP site
Deploying app files to Web Server
Setting up the development PC
Preparations
Creating the ODBC data source
Setting up the auth server
Setting up the .NET server
Preparations
Creating the ODBC data source
Hosting Web APIs in .NET server
Task 2: Deploying the PowerServer project to remote servers
Creating a Web server profile for remote deployment
Uploading the cloud app launcher & runtime files to the remote server
Modifying the PowerServer project
Selecting the server profile
Importing the production license
Specifying the Web API URL
Selecting the authentication template
Configuring the database connection
Selecting the Full build option
Building & deploying the project
(Optional) Packaging & copying the app files
Publishing the Web APIs
Tutorial 2: Connecting to the Database
Overview
Stage 1: Configuring database connection for project compilation
Introduction
Configuring in PowerBuilder IDE
Creating a cache for the SQL Anywhere database
Starting the SQL Anywhere database
Configuring ODBC for SQL Anywhere
Creating a cache for the SQL Anywhere database
Creating a cache for the PostgreSQL database
PostgreSQL General connection
PostgreSQL SSL connection
Creating a cache for the SQL Server database
Selecting a proper authentication mode when configuring SQL Server connection
Configuring the SQL Server database server
Connecting over TCP/IP protocol
Connecting over Shared Memory protocol
Connecting over Named Pipes protocol
Configuring named pipes
Connecting over Named Pipes
Connecting to Azure SQL
Creating a cache for the MySQL database
Installing MySQL driver
Creating a cache for the MySQL database
MySQL General connection
MySQL SSL connection
Creating a cache for the Informix database
Installing Informix driver
Creating a cache for the Informix database
Creating a cache for the Oracle database
Installing Oracle driver
Creating a cache for the Oracle database
Connection types in Oracle
Creating a cache for the Adaptive Server Enterprise database
Configuring ODBC for ASE
Creating a cache for the ASE database
Troubleshooting
Unable to connect to remote SQL Server database over Named Pipes even though local database can be connected
Using the PostgreSQL CA certificate for SSL connection, the verification fails
Communication error when connecting with the Informix database
Stage 2: Configuring database connection for app runtime
Introduction
Configuring in PowerBuilder IDE
Creating the database cache
Downloading the database driver
Mapping the transaction object
Configuring in PowerServer
Configuring ODBC for SQL Anywhere and ASE
(Optional) Modifying caches and transaction-to-cache mappings in PowerServer solution
Tutorial 3: Setting up a Web server
Overview
Setting up IIS
Preparations
Installing Web Server (IIS)
Configuring SSL on IIS
Creating an IIS FTP site
Configuring SSL on FTP server
Setting up Apache on Windows
Preparations
Installing Apache HTTP Server
Configuring SSL on Apache
Installing FTP server
Setting up Apache on Linux
Preparations
Installing Apache HTTP Server
Configuring SSL on Apache
Configuring Apache to be case-insensitive
Packaging and copying the client app
Setting up Nginx on Windows
Preparations
Installing Nginx
Configuring SSL on Nginx
Installing FTP server
Setting up Nginx on Linux
Preparations
Installing Nginx
Configuring SSL on Nginx
Configuring Nginx to be case-insensitive
Packaging and copying the client app
Tutorial 4: Hosting Web APIs in Docker Containers
Overview
Task 1: Setting up Docker
Setting up a docker host (Docker Engine)
Setting up a docker registry
Task 2: Setting up the database server
Preparations
Starting the database
Task 3: Publishing to Docker
Preparing the development PC
Modifying and re-deploying the PowerServer project
Publishing and deploying Web APIs to Docker
Tutorial 5: Hosting Web APIs in IIS (in-process hosting)
In-process hosting vs. out-of-process hosting
Preparations
Installing IIS
Windows Server OS
Windows Desktop OS
Creating an IIS website
Configuring IIS
Configuring SSL on IIS
Installing Web Deploy
Installing ASP.NET Core Hosting Bundle
Publishing and deploying Web APIs to IIS
Using the Folder method
Using the Web Deploy method
Verifying the Web APIs
Deploying multiple Web APIs to IIS
Tutorial 6: Hosting Web APIs in Kestrel (using reverse proxy server)
Overview
Running Web APIs on Kestrel
Using a reverse proxy server
Configuring Apache reverse proxy server (Windows)
Preparations
Configuring Apache
Modifying and re-deploying the PowerServer project
Verifying the configuration
Configuring Apache reverse proxy server (Linux)
Preparations
Configuring Apache
Modifying and re-deploying the PowerServer project
Verifying the configuration
Configuring Nginx reverse proxy server (Windows)
Preparations
Configuring Nginx
Modifying and re-deploying the PowerServer project
Verifying the configuration
Configuring Nginx reverse proxy server (Linux)
Preparations
Configuring Nginx
Modifying and re-deploying the PowerServer project
Verifying the configuration
Configuring IIS reverse proxy server
Preparations
Configuring IIS
Modifying and re-deploying the PowerServer project
Verifying the configuration
Tutorial 7: Load-balancing PowerServer Web APIs
Overview
Configuring Nginx as a load balancer
Using Nginx Sticky Module
Using Nginx Plus
Using IP hash load-balancing
Configuring IIS as a load balancer
Configuring Apache as a load balancer
Tutorial 8: Deploying installable cloud apps to Kubernetes
Overview
Before you begin
Configuring Azure Kubernetes Service
Creating a Kubernetes cluster in AKS
Connecting to the Kubernetes cluster
Installing ingress controller
Creating public IP address
Creating a Kubernetes namespace
Installing Ingress-Nginx
Using your own TLS certificates in AKS
Creating Kubernetes secret for the TLS certificate
(Optional) Adding the default certificate
Logging into Azure container registry
Creating a database
Containerizing the installable cloud app
Preparing the application
Modifying the Web API URL
Modifying the database connection
Packaging the client app as a zipped file
Building the PowerServer project
Creating the container images
Creating an image for the client app
Creating an image for the Web API
Pushing images to Azure container registry
Deploying the application to the Kubernetes cluster
Creating the YAML manifest files
Deploying the application
Configuring the domain name
Testing the application
Tutorial 9: Publishing PowerServer Projects to Azure App Service
Prerequisites
Deployment Architecture
Steps Summary
Cloud Database
Creating the Database Resource
Configuring the Database
Enabling extensions
Allowing external access
Dumping the Database
Restoring the Database
Web APIs
Creating the App Service
Configuring the PowerServer project
Manual Deployment (SnapDevelop)
Publishing the Web APIs to the file system
Uploading the Web APIs to the App Service through FTP
Direct Deployment (Visual Studio)
Cloud App
With Static Web App
Creating the Static Web App
Installing SWA CLI
Deploying the Cloud App
With Azure Storage
Enabling Static Web Site
Downloading AzCopy
Deploying the Cloud App
Troubleshooting
no pg_hba.conf entry for host
Workflow deployment fails with "Failed to find a default file in the app artifacts folder (/). Valid default files: index.html,Index.html." error
Tutorial 10: Publishing PowerServer Projects to Azure Container Instances
Prerequisites
Overview
Deployment Architecture
Creating an Azure Container Registry
Preparing the PowerServer Project
Containerizing and Deploying Web API
Pushing the container to the Azure Container Repository
Using SnapDevelop
Manually
Creating the Container Instance
Updating the Web API URL on the PowerServer project
Using CustomizeDeploy.dll
On PowerBuilder
Publishing the Cloud App Installer
Using Nginx Container
Using IIS Container
Creating a home page
Tagging and pushing the image
Creating the container instance
Appendix
Customizing the Containers' URLs
Azure Application Gateway
CNAME Record
Troubleshooting
Cannot push image to Azure Container Registry
Tutorial 11: Authenticating your apps
Overview
Using JWT
Preparations
Modifying the PowerBuilder client app
Purpose
Add scripts
Add an INI file
Start session manually by code
Modify and re-deploy the PowerServer project
Appendix
Validate username and password against a database
Enable CORS
Using OAuth 2.0
Preparations
Modifying the PowerBuilder client app
Purpose
Add scripts
Add an INI file
Start session manually by code
Modify and re-deploy the PowerServer project
Appendix
Validate username and password against a database
Validate username and password against an LDAP server
Test the OAuth server
Enable CORS
Using Amazon Cognito
Preparations
Creating the Amazon Cognito user pool
Modifying the PowerBuilder client app
Purpose
Add scripts
Add an INI file
Start session manually by code
Modify and re-deploy the PowerServer project
Modifying the authentication template
(Optional) Testing the Cognito server
Using Azure Active Directory service
Azure Active Directory (AD)
Preparations
Creating an Azure AD tenant
Modifying the PowerBuilder client app
Purpose
Add scripts
Add an INI file
Start session manually by code
Modify and re-deploy the PowerServer project
Modifying the authentication template
Azure Active Directory (AD) B2C
Preparations
Creating an Azure AD B2C tenant
Modifying the PowerBuilder client app
Purpose
Add scripts
Add an INI file
Start session manually by code
Modify and re-deploy the PowerServer project
Modifying the authentication template
Using other authentication servers
Using Okta OIDC (OpenID Connect)
Preparations
Creating a web application to integrate with Okta
Modifying the PowerBuilder client app
Purpose
Add scripts
Add an INI file
Start session manually by code
Modify and re-deploy the PowerServer project
Modifying the authentication template
Using Visual Guard
About this tutorial
Preparations
Declaring the PowerBuilder application in Visual Guard
Adding fine-grain authorizations to control access to DataWindows
Modifying the PowerBuilder client app
Modifying the authentication template
Tutorial 12: Building your PowerServer project with commands
Task 1: Exporting the build file
Preparing the environment
Exporting the build file
Modifying the build file
About the build file
Getting source code from SVN, Git, or VSS
Executing additional commands
Task 2: Building with commands
Preparing the environment
Running the command-line tool
Verifying if PBAutoBuild is successful
Integrating with Jenkins
Tutorial 13: Packaging & Distributing Installable Cloud Apps (for ISVs)
Verifying system requirements
Packaging & deploying the client app
(Optional) Configuring database connections
(Optional) Customizing launcher and app entry page
Packaging launcher, runtime and app
Deploying the client app to Web server
Configuring the deployed app on Web server
Configuring the Web API URL
(Optional) Changing the INI file etc.
Packaging & hosting the PowerServer Web APIs
Packaging the PowerServer Web APIs
Configuring the PowerServer Web APIs
Configuring the database connection
Importing the PowerServer license
Configuring the email settings
Hosting the PowerServer Web APIs
Running the installable cloud app
Running the installable cloud app
Uninstalling the client app
Tutorial 14: Load testing installable cloud apps
Load testing installable cloud apps with LoadRunner
Dynamic Values in the Recorded Script
Enclosing parameters in angle brackets “<>”
Running the application in test mode before recording the script
How to switch to the test mode
Changing the compression method to Gzip
Recording
Specifying the app .exe file as the Application
Disabling the async scan
Correlating the Session ID
How to correlate the session ID in the recorded script
Correlating the Transaction ID
How to correlate the transaction ID in case of single transaction
How to correlate the transaction ID in case of multiple transactions
Parameterizing static values in SQLs
How to parameterize static values in Retrieve
How to parameterize static values in Select
Replaying
Load testing installable cloud apps with JMeter
Overview
Preparing the installable cloud application
Configuring and deploying the application
Switching the application to test mode
Why test mode is required
How to switch to the test mode
Running PowerServer Web APIs and then JMeter recorder or Fiddler
Recording JMeter scripts
Recording scripts automatically (using Recorder)
Creating a test plan from templates
Enabling recorder
Configuring recorder
Configuring your system to use the JMeter Proxy
Recording the scripts
Viewing the recorded scripts
Correlating the scripts
Recording scripts manually (using Fiddler + JMeter)
Obtaining HTTP requests
Creating a test plan and adding HTTP requests
Creating a Test Plan
Adding a Thread Group
Adding HTTP requests
Adding an HTTP header manager
Adding listeners
Running tests and viewing results
Modifying the scripts
Adding a setUp Thread Group
Adding a setUp Thread Group
Adding HTTP requests
Correlating the session ID
Correlating the transaction ID
Adding a tearDown Thread Group
Adding a tearDown Thread Group
Adding HTTP requests
Correlating the transaction ID
Configuring Thread Group
Adding simultaneous users
Replaying the scripts
Parameterization and correlation
Why parameterization and correlation are required
Correlating the access token
Correlating the session ID
Correlating the transaction ID
In single transaction
In multiple transactions
Parameterizing the retrieval argument
Parameterizing the ESQL parameter
Tutorial 15: Applying a production license to an existing PowerServer project
Accessing the production license code (Order Owner only)
Importing the production license into the project
After compilation
Before compilation
In PowerBuilder
In SnapDevelop
Without SnapDevelop
Activating the license
Working with Database Connections
Overview
DB connection differences (C/S app vs. installable cloud app)
Supported database connection options
Techniques for supporting various connection scenarios
Supported database types & versions
ASE database
Enabling long connections
Configuring database caches
Creating database caches in the project settings
Select the database driver
Configure the database connection in the Database Configuration dialog
Create the DB connection profile
Managing database caches in the PowerServer solution
Setting up static database connection for the app runtime
Creating transaction-to-cache mappings in the project settings
Managing transaction-to-cache mappings in the PowerServer solution
Using LogID and LogPass properties
Setting up dynamic database connection for the app runtime
Dynamically mapping transaction object with cache using DBParm
Using CacheName property in DBParm
Using CacheGroup property in DBParm
Using LogID and LogPass properties
Using Transaction object properties
Managing database connections using APIs
Overview
Storing database connections in the database
Managing database connections in the database
Unsupported Features & Workarounds Guide
How to detect unsupported features
Unsupported features & workarounds
Unsupported features that can be detected
SetTrans
Data pipeline
MobiLink
Oracle RPC arrays
SQLPreview
SQLReturnData property
Transaction trace
Retrieve As Needed and Rows to Disk
Commit or Rollback Transaction using Dynamic SQL
Data retrieval in the RetrieveRow event
Unsupported features that cannot be detected
Unsupported use cases in Embedded SQLs
SyntaxFromSQL
DBParm properties
Database synonym
Discrepancies & workarounds
Discrepancies that cannot be detected
DB connection
Alias name
rowsupdated value
DisableBind parameter
TableBlob retrieval
Dynamic DataWindow
TransactionName
Data type in Dynamic SQL Format 4
Decimal data type in static SQL or DataWindow
Timing of transaction rollback
Oracle AutoCommit and Lock
Stored procedure parameter
Transaction commit
Use Describe in Dynamic SQL Format 4
Bit data field
SelectBlob/UpdateBlob supports UTF8 only
SQLNRows property (with Cursor)
SQLCode property (with SP)
Column name from view
Incompatible coding styles
PBLs contain DataWindows with the same name
Object name using C# reserved words
DataWindow name containing special characters
Editing SQL
Column order in data source and Column Specification
One compute expression containing multiple computed columns
Cursor syntax
Syntax after UNION
Troubleshooting Guide
Configuring and deploying PowerServer projects
Permission errors when configuring the Web server profile
Syntax error during the build process
DB driver error during the build process
Error in the Unsupported (DWs) window
Failed to generate the PowerServer Web APIs project
Error uploading application files to FTP
Changed PBL list
Error when converting DataWindow to data model
Failed to load database driver for PowerServer project
Failed to load database driver when building/deploying PowerServer project
Running installable cloud apps
Cloud app launcher and application executable
Failed to get the app publisher from the server
Failed to download the config file (LauncherSetup.json)
App cannot run because the local version expired
Cannot start cloud app launcher
Application not found
Application executable disappeared suddenly
Window is slow to open
Models and controls
Cannot retrieve data when data includes null values
PBSELECT DataWindow error
RibbonBar control displays blank
Server
Session creation failed
SSL certificate common name is incorrect
Failed to send the request
Internal Server Error (HTTP status code: 500)
Not Found (HTTP status code: 404)
Cannot connect to the server when creating the session
Session does not exist
Session not created
App requires login again
File name containing character + cannot be downloaded
"HTTP Error 404.2 - Not Found" error when running the app
Failed to access the *.json file
Failed to connect to the license server: No such host is known
Session fails to respond
Communication protocol inconsistent between server and client
Database
Different results returned from an ASE stored procedure
SelectBlob data truncated
Garbage letters display when retrieving multibyte data
Slow app performance with SQL Anywhere
64-bit database cannot be connected from IIS
License errors
Failed to call the license server API
Failed to login the license server
Cannot access License.json
PowerServer solution errors
Unable to find package PowerServer.Api or PowerServer.Core
Failed to update NuGet packages in PowerServer C# solution
Performance Guide
Introduction
Performance suggestions on project compilation and deployment
Performance suggestions on loading installable cloud apps for the first time
Performance suggestions on running installable cloud apps
Debugging the performance
Working against the impact of Internet and slow networks on runtime performance
Hosting Web APIs and database on the same LAN
Web API publishing method
Optimizing database server performance
Connection Pooling
Command Timeout
Tuning excessive server calls
Overview
Technique #1: grouping multiple server calls with PowerServerLabel
Technique #2: partitioning transactions via stored procedures
Technique #3: partitioning non-visual logic via server-side REST APIs
Technique #4: eliminating recursive embedded SQLs
Technique #5: eliminating DW computed fields calling user functions that have ESQL
Minimizing large data transmissions
Overview
Technique #1: retrieving data incrementally
For Oracle database server
For all other database servers
Technique #2: minimizing excessive number of columns
Debugging Guide
Overview
Debugging with PowerServer Debugger
Preparations
Starting PowerServer Debugger
Running in debug mode
Debugging with Fiddler
Installing Fiddler
Configuring Fiddler
Configuring the PowerServer project
Running the PowerServer Web APIs and then Fiddler
Capturing HTTP(S) with Fiddler
Filtering the results
Inspecting the results
Analyzing the performance
Logs and unsupported features report
Deployment log
Unsupported features report
Web file download log
Web API request log
Debugging log in SnapDevelop
PowerServer logs
Log4net logging
Logging with the settings in Logging.json
Debugging case studies
DataWindow related errors
DataWindow retrieve error
SyntaxFromSQL execution error
Different execution results in different databases
Incompatible data type
PBSELECT retrieve error
Embedded SQL related errors
Data type mapping tables
SQL server data type mappings
ASE server data type mappings
SQL Anywhere server data type mappings
Oracle server data type mappings
PostgreSQL data type mappings
Search Highlighter (On/Off)
