Export (0) Print
Expand All

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

Published: April 27, 2005
On This Page

Introduction and Goals Introduction and Goals
Understanding the Technology Understanding the Technology
Scenario 1: Rewriting Pro*C to the .NET Platform Scenario 1: Rewriting Pro*C to the .NET Platform

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 http://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 http://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.

  1. 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 http://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 http://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 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconCreatingWindowsFormsApplications.asp.

  2. 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.

      ' 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 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqlconnectionclasstopic.asp

  3. 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: http://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:

      EXEC SQL OPEN cust_cursor; 

      Fetching rows using the cursor:

      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:

      EXEC SQL CLOSE cust_cursor;
    • Although SQL Server does not support cursors, equivalent functions can be created using ADO.NET:

      Defining the query:

      strSQL = "SELECT CustomerID, CompanyName FROM Customers"

      Creating a command object:

      Dim cmd As New SqlCommand(strSQL, cn)

      Creating a data reader object:

      Dim rdr As SqlDataReader = cmd.ExecuteReader()

      Displaying the data reader result set:

      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:

      rdr.Close()

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

  4. 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:

      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:

      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:

      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:

      Dim strSQL As String
      strSQL = " DELETE FROM Customer WHERE CustomerID = 'ACME'" 
      Dim cmd As New SqlCommand(strSQL, cn)
      Dim InsertRecordsAffected As Integer = cmd.ExecuteNonQuery()
  5. 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.

  6. 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:

      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 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadvnet/html/vbnet09102002.asp.

  7. 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:

      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 http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnvbdev01/html/vb01f1.asp.

  8. 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.

Download

Get the Solution Guide for Migrating Oracle on UNIX to SQL Server on Windows

Update Notifications

Sign up to learn about updates and new releases

Feedback

Send us your comments or suggestions

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft