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:
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:
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:
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.
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:
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:
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:
A fragment of embedded SQL:
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:
A fragment of dynamic SQL:
// 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:
SQLines offers services to convert PowerBuilder applications from Oracle to Microsoft SQL Server. For more information, please Contact Us.