PowerBuilder Tutorial - Oracle to SQL Server Migration

This article can help you understand how PowerBuilder applications interact with Oracle and what changes are required when you migrate to SQL Server.

We will create a demo PowerBuilder application that access Oracle. For more information about converting PowerBuider, see Migrating PowerBuilder from Oracle to SQL Server.

PowerBuilder is an enterprise development tool that allows you to build database-driven applications with rich user interface and advanced business logic written in PowerScript language.

In PowerBuilder, the first step to create a project is to create a workspace and then add a target:

• Workspace

Select File → New → Workspace to create a new workspace (.pbw file).

• Target

Select File → New → Target to create a new target (.pbt file). A target specifies the type of application, for example, client/server application (.exe), .NET assembly etc. A workspace can have multiple targets.

When you create a target, PowerBuilder automatically adds a library (.pbl file) and an application object (.sra file when you export it) to the workspace. The application object contains code to initialize the application.

Select the application object in the object tree, right-click and select Edit source, then add the following code at the end of the file:

  event open;
    MessageBox("PowerBuilder Demo1",  "Hello, World!")
  end event

Note. Save the changes (Ctrl+S) before running the application.

Then when you run the application, the following message box appears:

In a PoweBuilder script you can initialize the SQLCA structure with the connection information, connect and execute SQL statements.

SQLCA is SQL communication structure that contains the connection information, the last SQL error code, message etc.

PowerBuilder:

  event open;
 
    // Specify connection information for Oracle
    sqlca.DBMS = "ORA Oracle"   /* Native connection to Oracle */
    sqlca.ServerName = "xe"      /* TNS service name */
    sqlca.LogId = "ora"               /* User name */
    sqlca.LogPass = "ora"           /* Password */
    sqlca.AutoCommit = False
    sqlca.DBParm = ""
 
    // Connect to Oracle
    CONNECT USING sqlca;
 
    DATE ld_date     /* Local variable */
 
    // Run a query to select the current date from the database
    SELECT SYSDATE INTO :ld_date FROM dual USING sqlca;
 
    MessageBox("PowerBuilder Demo1",  "Today is " + string(ld_date))
 
  end event

Now when you run the application, it shows the following message box:

You can see that the SELECT statement is embedded to PowerScript, and in this particular example, INTO :ld_date and USING sqlca is a part of the PowerScript language (remain unchanged when you convert the application to SQL Server), while SYSDATE function and dual table belong to Oracle syntax and require conversion.

When you run a SQL statement, it is associated with a database connection using a SQLCA structure. It is typically declared in the application object (.sra file) as follows:

  // SQL Communication area
  global transaction sqlca

After you have executed a SQL statement, you can refer to sqlca fields to find out whether the last statement executed successfully, define the database native error code, and the error message:

• sqlca.sqlcode - Return code (0 - Success, <0 - Error, 100 - No data found etc.)
• sqlca.sqldbcode - Database native code
• sqlca.sqlerrtext - Error text

The previous example shows how to specify a connection and execute SQL statements from PowerScript code. But PowerBuilder IDE provides rich capabilities to build advanced data-driven UI elements using various wizards. Before you can use these features, you have to specify a connection that PowerBuilder IDE will use to access and extract metadata as well as to validate your queries.

Go to Tools → Database Profile and select the required Oracle version (for native connection to Oracle), ODBC, or OLE DB database interface, then click New.

Set the database profile settings:

After you have created the profile, select it again and click Connect to connect to the database, and to allow PowerBuilder IDE to extract the metadata whenever required.

DataWindow is one of the most powerful features of PowerBuilder that allows you to retrieve data from a database, represent them in various visual styles, perform updates etc.

A typical PowerBuilder application consists of a lot of DataWindow objects.

Once you have set up a database connection in PowerBuilder IDE, you can use various wizards to build DataWindows based on the metadata information retrieved from the database.

Go to File → New and select DataWindow tab. There are wizards for many data representation styles available:

Once you selected a style, you can choose to how retrieve the data:

When you use Quick Select option to define the data, PowerBuilder allows you to visually specify the DataWindow data by selecting tables, columns and conditions.

When this option is used, PowerBuilder represents the query in the internal form (PBSELECT statement), for example:

  // Selecting ID and NAME columns from country table (Equivalent to SQL SELECT id, name FROM counties)
  PBSELECT(TABLE(NAME="countries") COLUMN(NAME="countries.id")COLUMN(NAME="countries.name"))

Internal PBSELECT allows you to define joins (including outer joins), filters, and typically you do not need to convert such DataWindow objects when you migrate Oracle to SQL Server.

When you use SQL Select option to define the DataWindow data, you are able to specify any SQL SELECT statement.

This statement may contain Oracle native language elements such as the concatenation operator ||, outer join operator (+), built-in functions etc., so such DataWindow objects often require modification when you convert the database from Oracle to SQL Server:

  -- An example of SELECT in DataWindow that uses Oracle SYSDATE function
  SELECT SYSDATE, id, name FROM countries

When you convert a PowerBuilder application from Oracle to SQL Server, you have to look at the PowerScript code that may contain embedded SQL statements in Oracle syntax as well as DataWindow queries defined using SQL Select option.

Resources to assist you with Oracle to SQL Server migration for PowerBuilder applications:

• PowerBuilder Migration Guide
• Export PowerBuilder Library to Source Code

SQLines offers services to convert PowerBuilder applications from Oracle to Microsoft SQL Server. For more information, please Contact Us.