PowerBuilder - Oracle to SQL Server Migration

If you use PowerBuilder applications that access Oracle, you have to convert them when you migrate the Oracle databases to Microsoft SQL Server.

PowerBuilder applications usually contain embedded SQL statements in Oracle syntax that needs to be converted to equivalent SQL Server syntax.

If you are new to PowerBuilder, and want to understand a general structure of a PowerBuilder application, how it can interact with Oracle, and what changes are required when you migrate to SQL Server, see PowerBuilder Tutorial.

Usually a PowerBuilder application contains the following files in the application directory:

• .pbl - Library Files
  
  A PowerBuilder libary file is a binary file containing the application code (PowerBuilder objects).

• .pbt - Target File
  
  An application target is a text file containing the list of libraries (.pbl files) in the application, the library containing the application object (the entry point to the application).

• .pbw - Workspace File
  
  A workspace file is a text file containing references to one or more targets, and used in PowerBuilder IDE to open the project.

Additionally, the application directory can contain images, various configuration and data files (.ini, .xml, .txt etc.)

Although you can open a PowerBuilder application in the PowerBuilder IDE, select a library, an object within the library and right-click Edit Source to modify the code, it is more typical to export all PowerBuilder objects and run a tool for automated conversion of SQL statements from Oracle to SQL Server syntax.

Working with source files are especially useful during the testing phase, when you can use text editors and merge utilities to find and validate all changes made to the application.

A PowerBuilder library (.pbl file) contains PowerBuilder objects that exported to the following files:

• .sra - Application Object (The entry point to the application)
• .srd - DataWindow Object (Retrieves, represents and modifies data in the database)
• .srw - Window Object
• .sru - User Object
• .srm - Menu Object (List of commands and handlers)
• .srf - Function Object
• .srs - Structure Object (Structure definition)
• .srq - Query Object

For more information how to export a PowerBuilder library to source files, see How Export a Library.

When you convert a PowerBuilder application from Oracle to SQL Server, you have to look at the following PowerBuilder objects:

• DataWindow Objects
• Window Objects
• User Objects
• Function Objects

A DataWindow object (.srd file) contains a query to retrieve the data. In many cases, this is a PBSELECT clause that does not require conversion as it is an internal query representation in PowerBuilder that does not contain database specific clauses:

For example, the following PBSELECT from a .srd file specifies to select id and name columns from countries table:

  -- This PBSELECT is equivalent to SQL SELECT id, name FROM countries
  retrieve="PBSELECT(TABLE(NAME=~"countries~") COLUMN(NAME=~"countries.id~")
       COLUMN(NAME=~"countries.name~"))"

At the same time, instead of PBSELECT, a DataWindow object can contain a SQL SELECT statement to define the data. This SQL statement can use Oracle native syntax and may require conversion when you migrate the database to SQL Server:

For example, the following SQL SELECT statement contains Oracle SYSDATE function that needs to be replaced with GETDATE() or other equivalent function in SQL Server:

  -- Fragment of a .srd file using SQL SELECT to define the data
  retrieve="SELECT SYSDATE, id, name FROM countries"

A Window object (.srw file) consists of event handlers that can contain embedded SQL statements to retrieve and modify the data as well as to manage transactions:

PowerBuilder for Oracle:

  DATE ld_date
 
  SELECT SYSDATE INTO :ld_date FROM dual USING sqlca;
 
  IF sqlca.sqlcode <> 0 THEN
	ROLLBACK USING sqlca;
	MessageBox("Demo1", "Error raised")
	return -1
  ELSE
	COMMIT USING sqlca;
  END IF

Some SQL statements and clauses (INTO, USING sqlca, sqlca.sqlcode, ROLLBACK and COMMIT) as well as data types (DATE) are part of the PowerBuilder language and do not require conversion, while others (SYSDATE, dual) belong to the Oracle syntax and require conversion to work with SQL Server.

Dynamic SQL

In addition to embedded SQL, a Window object can contain a dynamic SQL (a SQL statement in a string expression). It is typical for a PowerBuilder program to dynamically build SQL statements (add filters, sorting etc.):

  IF TRIM(created_column.text) <> "" THEN
	ls_Where = ls_Where + " AND"
	ls_Where = ls_Where + " SYSDATE - " + created_column.text + " <= created"
  END IF

A User object (.sru file) is similar to the concept of a class in object-oriented languages such Java, C#, C++ etc. It contains properties and methods that you can reuse in different parts of an application, or even in different applications.

Similar to Window objects, a User object can contain embedded and dynamic SQL statements that may require conversion when you migrate the database from Oracle to SQL Server.

Before converting a PowerBuilder application from Oracle to SQL Server, you should understand how PowerBuilder accesses databases, and what statements belong to the PowerBuilder language, and do not require conversion.

PowerBuilder allows you to embed SQL statements to PowerScript code, so they look as usual statements:

PowerBuilder:

  Date ld_today
 
  // Selecting into a local variable
  SELECT SYSDATE INTO :ld_today FROM dual USING sqlca
 
  // Insert using the value of a local variable
  INSERT INTO log VALUES (:ld_today) USING sqlca

This example includes a local variable declaration, and an embedded SQL statement: SELECT SYSDATE FROM dual.

Note that although Oracle PL/SQL language supports INTO clause in the SELECT statement, in this example, INTO is a PowerBuilder clause, not related to Oracle PL/SQL, and specifies to retrieve the value to the ld_today local variable.

USING sqlca clause also belongs to PowerBuilder and links the statement with a database connection (transaction context).

Embedded SQL Statements

Embedded SQL statements that belong to the PowerBuilder language:

• SELECT select_list INTO :var1, … FROM …
• DECLARE cursor_name CURSOR FOR select_statement USING sqlca
• OPEN cursor_name
• FETCH NEXT cursor_name INTO :var1, …
• CLOSE cursor_name

Again, you can see that most statements look like Oracle PL/SQL statements, but they are PowerBuilder statements and do not require conversion when you migrate to SQL Server.

Additionally, PowerBuilder application usually contain the following code to check for database errors:

• SQLCA.sqlcode = 0 - Successful execution
• SQLCA.sqlcode <> 0 - Error

A fragment of embedded SQL:

PowerBuilder:

  DECLARE cur_countries CURSOR FOR 
     SELECT name FROM countries 
    USING sqlca;
 
  OPEN cur_countries;
  FETCH NEXT cur_countries INTO :ls_name;
 
  IF sqlca.sqlcode <> 0 THEN							
  	/* Error handling */
  END IF
 
  CLOSE cur_countries;

Unlike an embedded SQL which is known at the compile time, a dynamic SQL statement is known at the run-time. PowerBuilder provides the following statement to work with dynamic SQL:

• DECLARE cursor_name DYNAMIC CURSOR FOR sqlca
• PREPARE SQLSA FROM “SQL_statement” or :var
• OPEN DYNAMIC cursor_name

A fragment of dynamic SQL:

PowerBuilder:

  // Declare a cursor
  DECLARE cur_countries DYNAMIC CURSOR FOR sqlca;
 
  PREPARE sqlca FROM "SELECT name FROM countries";
 
  // Open the cursor and fetch a row
  OPEN DYNAMIC cur_countries;
  FETCH NEXT cur_countries INTO :ls_name;
 
  IF sqlca.sqlcode <> 0 THEN							
  	// Error handling 
  END IF
 
  CLOSE cur_countries;

Besides data manipulation SQL statements, a PowerBuilder application also usually contains transaction control statements:

• COMMIT USING sqlca
• ROLLBACK USING sqlca