Chapter 16 - Developing: Applications - Migrating Oracle Pro*C

Article
12/05/2007

Introduction and Goals

Third generation languages (3GL) such as C and C++ do not have the capability to interface with databases. Databases can only communicate using the SQL fourth generation language (4GL). Using embedded SQL allows a database to interface directly with the 3GL languages. Pro*C is Oracle's proprietary interface to the C language which permits the use of embedded SQL.

As discussed in the "Define the Solution Concept" section of Chapter 2, "Envisioning Phase," there are four different strategies available for transitioning applications in an Oracle to Microsoft® SQL Server™ migration project. The strategies are:

Interoperate the application with UNIX
Port or rewrite the application to the Microsoft .NET platform
Port or rewrite the application to the Microsoft Win32® platform
Quick port using the Microsoft Windows® Services for UNIX 3.5 platform

Because Pro*C is specific to Oracle, migration strategies are limited. Interoperation and porting cannot be utilized because no methods exist that allow Pro*C to communicate with SQL Server. A complete rewrite is the only option.

This chapter focuses on the preferred scenario that can be used in Pro*C application migrations: Scenario 1: Rewriting the application to the .NET platform.

Rewriting an application is a challenging endeavor because a one-to-one mapping from the source environment to the target environment is very difficult to achieve. When an application is rewritten, there is always a risk that some functionality from the original application may be lost during the transition. Commonly, Pro*C programs are procedural and do not have a graphical user interface. Migrating procedural code is easier than migrating GUI-based applications. Extensive testing needs to be performed on the rewritten application to ensure that all of the business logic and functionality has accurately been recreated.

This chapter only discusses the transformation that needs to occur to the most common database-related components when rewriting a Pro*C application for the .NET environment. A comprehensive study of the entire rewrite is beyond the scope of this guidance.

Understanding the Technology

Pro*C is available on the Windows platform. Unfortunately, there are no methods available to allow Pro*C code to interface with SQL Server. Microsoft offers a product similar to Pro*C called Embedded SQL for C (ESQL/C) which allows Transact-SQL statements to be embedded in C language programs. ESQL/C can be used as the target language for rewriting Pro*C programs because of its close resemblance to Pro*C. However, using this language is not recommended because Microsoft no longer develops this product, and future versions of SQL Server will not support ESQL/C.

Visual Basic .NET is recommended for rewriting the application. Visual Basic .NET provides a robust scripting language and can easily be interfaced with SQL Server.

When migrating the database from Oracle to SQL Server, the Pro*C code and its interactions with Oracle should be examined to ensure that the functions can be recreated in the rewritten application.

Understanding Pro*C

Before the application can be rewritten, you must evaluate the functionality currently provided by the Pro*C application. While the computational and business logic is provided by the host C language, Pro*C provides the syntax to implement the database transactions. The embedded SQL in Pro*C offers the following functionality that will have to be migrated:

Connect to and maintain a connection to Oracle database.
Declare, open, and use cursors to manipulate data.
Interact with Oracle databases through SQL statements.
Provide mechanisms (host variables) to interface between the host code and the database code.
Close any open cursors.
Handle any database exceptions.
Close the connection to the database.

Each of these functions will need to be recreated in the new application. Pro*C is mostly used as an imperative programming language and is used for constructing programs which involve computation. The target programming environment should have programming capabilities and features that are similar to those of C/C++ and also interface with SQL Server for retrieving and manipulating data. The GUI capabilities will not be discussed in this guide.

Understanding .NET and ADO.NET

The Microsoft .NET Framework provides a programming infrastructure for building, deploying, and operating applications and services. When working with databases, ADO.NET is a key component.

ADO.NET is a set of libraries included with the Microsoft .NET Framework that enables communication with various data stores from .NET applications. The ADO.NET libraries include classes for connecting to a data source, submitting queries, and processing results. ADO.NET provides the tools to efficiently implement data access and functionality provided by Pro*C. Data access in ADO.NET relies on the following two entities:

DataSet
Data provider

DataSet

The DataSet is a copy of the data retrieved from the database cached in memory. Data can be loaded into a DataSet from any valid data source, such as a SQL Server database, or an XML file. The DataSet persists in memory, and the data therein can be manipulated and updated independent of the database. When appropriate, the DataSet can then be used for updating the database.

Data Provider

A data provider forms the link between the Visual Basic .NET program and the database. A data provider is not a single component; rather it is a set of related components that work together to provide data in an efficient, performance-driven manner.

Two data providers are available for Visual Basic .NET. The SQL Server Data Provider is designed specifically to work with SQL Server 7 or later, and the OLE DB Data Provider, which can also connect to other types of databases. Each data provider consists of versions of the following generic component classes:

The Connection object provides the connection to the database.
The Command object executes a command against a data source. It can execute non-query commands, such as INSERT, UPDATE, or DELETE, or return a DataReader with the results of a SELECT command.
The DataReader object provides a forward-only, read-only, connected recordset.
The DataAdapter object populates a disconnected DataSet or DataTable with data and performs updates.

The following classes are also offered: Transaction, CommandBuilder, Parameter, Exception, Error, and ClientPermission.

For more information on data provider components, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingadonetproviderstoaccessdata.asp.

Even though the syntax and use of the functions differ, ADO.NET provides equivalent database functions for SQL Server. It is important to ensure that these functions are translated to the new environment during the rewriting process.

Scenario 1: Rewriting Pro*C to the .NET Platform

Rewriting the application for Windows makes use of native SQL Server APIs, ADO.NET, Microsoft ActiveX Data Objects (ADO), or Open Database Connectivity (ODBC) running natively in the Windows subsystem. Rewriting the application can result in a more stable and integrated configuration after the project is complete.

Case 1: Rewrite the Application using Visual Basic.NET

Visual Basic.NET provides all the functionality of the C programming language as well as several interfaces to SQL Server databases to execute SQL statements. This scenario describes how to rewrite a Pro*C application with Visual Basic .NET.

The figure that follows shows a graphical representation of how the Pro*C code will migrate over to Visual Basic .NET during the rewrite.

Figure 16.1 Database access in Pro*C and Visual Basic .NET

Figure 16.1 Database access in Pro*C and Visual Basic .NET

Each function that currently resides in Pro*C code will need to be rewritten, from the user interface to the database connection. There are two options available to connect to the SQL Server data source from the .NET framework. These options are:

The SQL Server .NET Data Provider is implemented in the SQLClient namespace.
The OLE DB provider is in the OLE DB namespace. There are OLE DB providers for both Oracle and SQL Server.

For more information on data access services available with ADO.NET, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconaccessingdatawithadonet.asp.

To rewrite an application using Visual Basic .NET, follow these steps:

The examples used in these procedures are based on SQL Server .NET DataProvider and DataReader objects. If an OLE DB provider is used, there may be slight variations to the following steps.

Install the .NET Framework SDK and redistribution. The SDK contains the resources for building, testing, and deploying .NET Framework applications. For access to the latest SDK, refer to https://msdn.microsoft.com/netframework/downloads/updates/default.aspx.

The Microsoft Visual Basic .NET software is required for programming the application. For more information, refer to https://www.microsoft.com/downloads/details.aspx?FamilyID=dd6ec730-fd9a-4e36-8552-8accc4cd8fb3&DisplayLang=en.

At this point, it is assumed that you have all the components in place to write a VB .NET application using the SQL Server .NET DataProvider. It is also assumed that the migrated SQL Server database is in place.

For access to several references for creating Windows forms applications, refer to https://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconCreatingWindowsFormsApplications.asp.
Rewrite connectivity statements within application code.
- In Pro*C, database open and close statements are similar to the following example:
```
/*  assign username and password to host variables */
```

strcpy(username.arr,"SCOTT"); username.len=strlen(username.arr); strcpy(password.arr,"TIGER"); password.len=strlen(password.arr); /* execute connect statement / EXEC SQL CONNECT :username IDENTIFIED BY :password; / execute close statements */ EXEC SQL COMMIT WORK RELEASE;

  - These same functions can be recreated using ADO.NET. Here are examples of the same functions as written for .NET.
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">' declare a connection object

Dim cn As New SqlConnection ' define a connection string Dim strConn As String 'This is the connection string strConn = "Data Source=(local)\DataSourceName;" & _ "Initial Catalog=databaseName;" & _ "Trusted_Connection=Yes;" ' instantiate connection object cn = SqlConnection(strConn) ' open connections cn.Open() ' close connections cn.Close()

For more details on SqlConnection, refer to <https://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.asp>

Rewrite the queries and cursor functions.

Pro*C uses embedded SQL to store the output of a query statement in a cursor from which rows can be fetched. Visual Basic.NET does not support cursors; instead, it uses several other methods for retrieving data sets from SQL queries. One of these data retrieval methods is the DataReader object, which is a lightweight object that is read-only and, hence, ideal for queries. The following link provides access to more information on retrieving data using DataReader object: https://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp.
- Here are some common cursor operations encountered in Pro*C applications:
  
  Defining a cursor:
```
EXEC SQL DECLARE cust_cursor FOR
```

SELECT CustomerID, CompanyName FROM Customers;

    Opening the cursor:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">EXEC SQL OPEN cust_cursor; </pre>

    
    Fetching rows using the cursor:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">for (;;){

EXEC SQL WHENEVER NOT FOUND DO break; EXEC SQL FETCH cust_cursor INTO :customerid, :companyname; printf("CustomerID: %s – CompanyName: %s\n", customerid, companyname); }

    Closing the cursor:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">EXEC SQL CLOSE cust_cursor;</pre>


  - Although SQL Server does not support cursors, equivalent functions can be created using ADO.NET:
    
    Defining the query:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">strSQL = "SELECT CustomerID, CompanyName FROM Customers"</pre>

    
    Creating a command object:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Dim cmd As New SqlCommand(strSQL, cn)</pre>

    
    Creating a data reader object:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Dim rdr As SqlDataReader = cmd.ExecuteReader()</pre>

    
    Displaying the data reader result set:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">While rdr.Read()

Console.WriteLine(rdr("CustomerID") & " – " & rdr("CompanyName")) End While Do While rdr.Read() Console.WriteLine(rdr.GetInt32(0) & " " & _ “ rdr.GetString(1) & " " & rdr.GetFloat(2)) Loop

    Closing the data reader object:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">rdr.Close()</pre>

    
    For more information on implementing a DataReader, refer to <https://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconimplementingdatareader.asp>.

Rewrite the embedded DML statements

The embedded DML is used to insert, update, and delete data. The SqlCommand object can be used to perform these SQL operations. The DataAdapter and DataSet objects are required for executing DML and stored procedures. Migration of these operations is shown in the following examples:
- The DML operations using Pro*C:
  
  Executing an INSERT DML statement:
```
EXEC SQL INSERT INTO Customer VALUES (:newcompanyname, :customerid);
```
  Executing an UPDATE DML statement:
```
EXEC SQL UPDATE Customer 
```

SET CompanyName = :newcompanyname WHERE CustomerID = 'ACME';

    Executing a DELETE DML statement:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">EXEC SQL DELETE FROM Customer

WHERE CustomerID = 'ACME';

  - For each of the DML operations shown, the equivalent operations can be performed using ADO.NET:
    
    Executing an INSERT DML statement:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Dim strSQL As String

strSQL = " INSERT INTO Customer VALUES " & _ " (@newcompanyname,@customerid)" Dim cmd As New SqlCommand(strSQL, cn) Dim param1, param2 As SqlParameter Param1 = cmd.Parameters.Add("@newcompanyname", SqlDbType.VarChar, 10) Param2 = cmd.Parameters.Add("@customerid", SqlDbType.NChar, 5) Param1.Value = ”COMPANY1" Param1.Value = "VALUE1" cmd.ExecuteNonQuery()

    Executing an UPDATE DML statement:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Dim strSQL As String

strSQL = "UPDATE Customer SET CompanyName = @newcompanyname " & _ "WHERE CustomerID = 'ACME'" Dim cmd As New SqlCommand(strSQL, cn) Dim param1 As SqlParameter Param1 = cmd.Parameters.Add("@newcompanyname", SqlDbType.VarChar, 10) Param1.Value = "NewName" Dim InsertRecordsAffected As Integer = cmd.ExecuteNonQuery()

    Executing a DELETE DML statement:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Dim strSQL As String

strSQL = " DELETE FROM Customer WHERE CustomerID = 'ACME'" Dim cmd As New SqlCommand(strSQL, cn) Dim InsertRecordsAffected As Integer = cmd.ExecuteNonQuery()

Rewrite Transaction Management operations
- The Transaction Management operations in Pro*C are:
  
  Beginning a transaction:
```
EXEC SQL SET TRANSACTION READ ONLY;
```
  Committing a transaction:
```
EXEC SQL COMMIT WORK RELEASE;
```
  Setting a savepoint:
```
EXEC SQL SAVEPOINT save_point;
```
  Rollback a transaction:
```
EXEC SQL ROLLBACK WORK RELEASE;
```
  Rollback to a savepoint:
```
EXEC SQL ROLLBACK TO SAVEPOINT save_point; 
```
- Transaction Management in ADO.NET
  
  Changes are made through the DataSet object. The Update method is used for copying the changes made to the dataset back to the database which implicitly commits the changes.
Rewrite calls to stored procedures.
- Stored procedures are executed in Pro*C using the syntax shown in the following example. In this example, the host variables represent IN or OUT parameters.
```
EXEC SQL EXECUTE
```

BEGIN GET_ROOM_DETAILS(:HotelId, :RoomType, :AvailableRooms, :StandardRate); END; END-EXEC;

  - Stored procedure calls in ADO.NET: SqlCommand objects can be used to call SQL Server and MSDE stored procedures. The SqlCommand object exposes a CommandType property that can be used to help simplify the code to call stored procedures.  Set SqlCommand object’s CommandText property to the stored procedure name, and the CommandType property to StoredProcedure, and then call the stored procedure, as shown in the following code:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Dim strConn As String

strConn = "Data Source=(local)\NetSDK;Initial Catalog=Northwind;" & _ "Trusted_Connection=Yes;" Dim cn As New SqlConnection(strConn) Dim cmd As New SqlCommand("GET_ROOM_DETAILS", cn) cmd.CommandType = CommandType.StoredProcedure Dim param As SqlParameter param = cmd.Parameters.Add("@HotelId", SqlDbType.NChar, 5) param.Value = "HOTEL1" ......... ......... cn.Open() Dim rdr As SqlDataReader = cmd.ExecuteReader() Do While rdr.Read() Console.WriteLine(rdr("OrderID")) ............ Loop rdr.Close()

    A more detailed discussion on using stored procedures in Visual Basic.NET is available at <https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp>.

Rewrite exception handling.
- There are two ways to handle Oracle errors in Pro*C:
  - SQL embedded statements. The syntax is:
```
EXEC SQL WHENEVER [condition] [action];
```
  - Checking error codes in SQLCA
    
    The generic syntax is
```
if ( sqlca.sqlcode == [a value  i.e., -1] ) 
```

{ Handler code }

  - Exception handling in Visual Basic .NET
    
    Use a Try block to add exception handling to a block of code. Add Catch blocks, as necessary, to trap individual exceptions. The .NET runtime handles Catch blocks in order, looking for an "is a" match against the current exception. It uses the first block it finds that matches. You can nest Try blocks, making it easy to effectively push and pop exception-handling states. Add a Finally block to your Try block to run code unconditionally, regardless of whether an error occurs or not.
    
    You can create your own exception classes that inherit from the base exception class (or any class that inherits from that class) to add your own functionality:
    
    <pre IsFakePre="true" xmlns="https://www.w3.org/1999/xhtml">Try

' Code that throws exceptions Catch E As OverflowException ' Catch a specific exception Catch E As Exception ' Catch the generic exceptions Finally ' Execute some cleanup code End Try

    A more detailed discussion of error handling in VB.NET can be found at <https://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev01/html/vb01f1.asp>.

Change all embedded SQL statements to T-SQL. This is a step common to all migrations. Refer to Chapter 11, "Developing: Applications — Migrating Oracle SQL and PL/SQL," for a detailed discussion on modifying Oracle SQL to be SQL Server-compliant.