Many applications are written using the Oracle Programmatic Interfaces (Pro*C, Pro*Cobol, and so on). These interfaces support the use of SQL-92 standard embedded SQL. They also include nonstandard Oracle programmatic extensions.
Oracle embedded SQL applications can be migrated to SQL Server by using the Microsoft Embedded SQL (ESQL) for C development environment. This environment provides adequate but less than optimal control over the performance and the use of SQL Server features compared to an ODBC application.
Some of the Oracle Pro*C features are not supported in Microsoft’s ESQL precompiler. If your Oracle application makes extensive use of these features, a rewrite to ODBC is probably a better migration choice. These features include:
If your Oracle application has been developed in Cobol, it can be moved to Embedded SQL for Cobol from Micro Focus. You may run into some of the same limitations in Cobol as with the Microsoft ESQL for C precompiler.
You can convert your Oracle embedded SQL application to the ODBC environment. This migration process is quite easy and offers many advantages. ODBC does not require the use of a precompiler, as does embedded SQL. Consequently, much of the overhead associated with program development is eliminated.
The table shows the approximate relationship between Embedded SQL statements and ODBC functions.
| Embedded SQL statement | ODBC function |
|---|---|
| CONNECT | SQLConnect |
| PREPARE | SQLPrepare |
| EXECUTE | SQLExecute |
| DECLARE CURSOR and OPEN CURSOR | SQLExecute |
| EXECUTE IMMEDIATE | SQLExecDirect |
| DESCRIBE SELECT LIST | SQLNumResultCols, SQLColAttributes, SQLDescribeCol |
| FETCH | SQLFetch |
| SQLCA.SQLERRD[2] | SQLRowCount |
| CLOSE | SQLFreeStmt |
| COMMIT WORK, ROLLBACK WORK | SQLTransact |
| COMMIT WORK RELEASE, ROLLBACK WORK RELEASE |
SQLDisconnect |
| SQLCA, SQLSTATE | SQLError |
| ALTER, CREATE, DROP, GRANT, REVOKE | SQLExecute, SQLExecDirect |
The most significant change when converting embedded SQL programs to ODBC involves the handling of SQL statement errors. The MODE = ORACLE option is often used when developing embedded SQL programs. When this option is used, the SQL Communications Area (SQLCA) is typically used for error handling operations.
The SQLCA structure provides:
In most cases, you should check the value in the sqlca.sqlcode variable following the execution of each SQL statement. If the value is less than zero, an error has occurred. If the value is greater than zero, the requested statement executed with warnings. The Oracle error message text can be retrieved from the sqlca.sqlerrm.sqlerrmc variable.
In ODBC, a function returns a numeric status code that indicates its success or failure following the requested operation. The status codes are defined as string literals, and include SQL_SUCCESS, SQL_SUCCESS_WITH_INFO, SQL_NEED_DATA, SQL_ERROR, and others. It is your responsibility to check these return values following each function call.
An associated SQLSTATE value can be obtained by calling the SQLError function. This function returns the SQLSTATE error code, the native error code (specific to the data source), and the error message text.
An application typically calls this function when a previous call to an ODBC function returns SQL_ERROR or SQL_SUCCESS_WITH_INFO. However, any ODBC function can post zero or more errors each time it is called, so an application may call SQLError after every ODBC function call.
Here are examples of error handling for each environment.
| Oracle Pro*C and EMBEDDED SQL | Oracle ODBC |
|---|---|
| EXEC SQL DECLARE CURSOR C1 CURSOR FOR SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY SSN; EXEC SQL OPEN C1; if (sqlca.sqlcode) != 0 { /* handle error condition, look at sqlca.sqlerrm.sqlerrmc for error description...*/} |
if (SQLExecDirect(hstmtl, (SQLCHAR*)"SELECT SSN, FNAME, LNAME FROM STUDENT ORDER BY SSN", SQL_NTS) != SQL_SUCCESS) { /* handle error condition, use SQLError for SQLSTATE details regarding error...*/} |