EXEC IAF PUT GLOBAL.OT_TAXES VALUES ( :taxes );
EXEC IAF PUT GLOBAL.OT_GRAND_TOTAL VALUES ( :grand_total );
return(IAPSUCC);
}
}
To call this user exit from a SQL*Forms trigger, you insert the following code:
user_exit('order_total');
Utilizing dynamic SQL or user exits can create problems with performance, and if your program is in a high-demand
system this could become very undesirable. The next section of this chapter will look how to tune your SQL to improve
performance.
Performance Tuning
When developing applications with embedded SQL, performance can become a major issue depending on what type of
platform you may be using. This section provides easy-to-apply methods for improving the performance of your
applications. It looks at what causes poor performance and how performance can be improved.
See Chapter 15, "Performance Tuning and Optimizing," for more information about performance tuning.
Poor Performance
One of the first causes of poor performance is high Oracle communication overhead. Oracle processes each SQL
statement one at a time, which results in numerous calls to Oracle. If you are operating in a network environment, each
call creates additional traffic on the network. The more traffic you have, the slower the performance will become.
The second cause of poor performance is inefficient SQL statements. Just because SQL statements can be written in
several different ways and still get the same results, this does not mean that every statement is running efficiently. In
some cases, full table scans will be occurring (which is time consuming if the table is large); in other cases, using
indexes greatly speeds up the search.
The third cause of poor performance is managing cursors inefficiently. The result of not managing cursors correctly is
additional parsing and binding, which adds noticeable processing overhead for Oracle.
These problems can be improved by reducing Oracle communication overhead or reducing processing overhead. The
next section provides methods that will help reduce overhead and improve performance.
Improving Performance
Improving performance can make a dramatic difference in the way your application functions under normal or high
usage. Two areas always should be considered when writing an SQL statement: Oracle communications and processing
overhead.

current on what new features the precompilers have. Oracle has taken extra effort in improving its tools with each step;
as a programmer, you should capitalize on these features.
New Features in Version 1.4
The new features in Version 1.4 precompilers help meet the needs of professional software developers. Some of the
features are as follows:
●
New debugging aid. The SQLCA stores additional runtime information about the outcome of SQL operations.
●
Enhanced WHENEVER statement. The improved WHENEVER statement now lets you take actions when an
error or warning is detected. With previous versions you only had three choices: GOTO, CONTINUE, or
STOP. Added to version 1.4 is the DO statement, which allows for procedural functions to be taken.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
●
Revised HOST option. With previous versions of precompilers, the HOST parameter indicated what host
language was being used. Version 1.4 uses separate precompilers executables each designed for a specify
language.
●
In previous versions of Oracle precompilers, options for setting the area size (which is initially set for cursor)
had to be specified. With the current version of precompilers, resizing is automatically done. This feature makes
the AREASIZE and REBIND options obsolete.
●
Previous versions of precompilers generated several database calls per embedded SQL statement. In Version
1.4, precompilers generate only one (bundled) database call per embedded SQL statement.
Remember to try and keep current on the new features Oracle includes in its precompilers. This could make a dramatic
difference in the performance and functionality of your program.
Summary
ORACLE precompilers provide an excellent tool for programmers to create dynamic applications. This chapter provided
information on what a precompiler does, the benefits of being able to embed SQL statements, how to use it a
precompiler, and how to create a host program.
This concludes the section on precompilers. I hope that the information has been beneficial to you and has given you

■
Connecting to an ODBC Data Source using the ODBC API
■
Setting Connection Options
■
Applying SQL Transactions
■
Retrieving Result Sets
■
Handling Errors
■
Calling Stored Procedures and Functions
■
Disconnecting and Freeing Resources
■
Debugging ODBC Applications
■
Limitations of ODBC
■
Summary
51
ODBC Applications
ODBC (Open Database Connectivity) is an industry standard programming interface that enables applications to access a
variety of database management systems, residing on many different platforms. ODBC provides a large degree of
database independence through a standard SQL syntax, which can be translated by database-specific drivers to the native
SQL of the DBMS.
Database independence and ease of use are the primary advantages to using ODBC. It is supported by many popular
development tools, including Visual Basic, PowerBuilder, Delphi, and SQLWindows. These tools and numerous others
provide their own interfaces to ODBC, making ODBC easier to use by insulating the developer from many of the
complexities of the ODBC API.

When the administration application is started, a list of defined data sources is presented, as shown in Figure 51.2.
Figure 51.2. The Data Sources dialog displays a list of defined data sources.
The Close and Help buttons should be self-explanatory, and the Options button will be discussed later, in the section on
debugging. Add is used to define a new data source for one of the installed drivers. Delete is used to delete an existing
data source, but does not delete the driver. The configuration of the selected data source can be edited by clicking on the
Setup button. The Drivers button is used to install additional DBMS-specific ODBC drivers.
The following examples illustrate the installation of the Oracle ODBC driver and the configuration of an Oracle data
source. This process begins with the installation of the Oracle ODBC driver. First, select Drivers from the Data Sources
dialog. When this button is clicked, all installed drivers are displayed, as shown in Figure 51.3.
Figure 51.3. The Drivers dialog displays a list of installed drivers.
To install the Oracle ODBC driver, click the Add button. This will display a dialog requesting the location of the drivers.
Select the drive and directory containing the ODBC.INF file and click OK. The dialog box shown in Figure 51.4
indicates that the Oracle ODBC driver was located.
Figure 51.4. The Install Drivers dialog displays a list of drivers available for installation.
The Advanced button displays a dialog that allows the user to specify installation of the driver manager and code page
translators. The Versions button in the lower-right corner brings up a second dialog that can be used to view extended
version information about each component available to install. These two dialogs are shown in Figure 51.5.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Figure 51.5. The Advanced Installation Options and Versions dialogs can be used to control the installation of the
Oracle ODBC driver.
The Install selected driver(s) with version checking should be selected. This will cause the installation program to
prompt before overwriting an existing driver if it is the same or a newer version of the driver being installed.
The version checking options should also be used for the installation of the Driver Manager and translators. Newer
versions of these DLLs should not be overwritten if they exist.
Code page translators are used to translate between different character sets and languages. In some cases, they are used
for encryption or data type conversion. Although a translator is not needed in most cases, you can install them for
possible future use. After making your selections from these options, click the OK button to return to the Install Drivers
dialog.
To complete the installation of the Oracle7 driver, make sure that Oracle7 is highlighted in the list box and click the OK
button. The Drivers dialog should now appear as shown in Figure 51.6.

Before your application connects to the ODBC data source, some memory allocation and initialization must be
performed. First, the application must call SQLAllocEnv, passing a pointer to memory allocated to store an environment
handle. This handle will be used to establish connections and for transaction processing. The application might need to
establish more than one environment handle, but a single environment handle is usually sufficient, except in
multithreaded environments.
Next, the application should call SQLAllocConnect, passing the previously established environment handle and a pointer
to storage allocated for the connection handle. The driver manager allocates storage for connection information and
associates the resulting connection handle with the environment handle. Multiple connections can be established for a
single environment handle, but each connection can only be associated with a single environment. The connection
handle will be used to allocate statement handles and process embedded SQL transactions.
Finally, the application may call either SQLConnect or SQLDriverConnect, passing the connection handle instantiated
by the call to SQLAllocConnect. The primary difference between these two functions is that SQLDriverConnect accepts
a full connection string, rather than separate arguments for the data source name, userid, and password. This allows for
additional database-specific parameters to be passed to the driver as part of the connection string. Additionally,
SQLDriverConnect provides an argument used to define the behavior of the driver manager and a window handle to be
used as the parent of the data sources dialog (if one will be presented). The arguments to SQLDriverConnect are, in
order:
●
An allocated connection handle.
●
The handle of the parent window from the Data Sources dialog (or NULL, if no dialog will be presented).
●
A connection string.
●
The length of the connection string.
●
A pointer to storage for the connection string actually used by the driver. (It may add information to the
connection string it receives.)
●
A pointer to storage to hold the length of the completed connection string.

(SQL_MAX_MESSAGE_LENGTH - 1),
&iConnectOutLen,
SQL_DRIVER_NOPROMPT);
return(iError);
}
Listing 51.2. This Visual Basic function establishes a connection to a data source.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
' include these prototypes in the module:
Declare Function SQLAllocEnv
Lib "odbc.dll" (hEnv As Long) As Integer
Declare Function SQLAllocConnect
Lib "odbc.dll" (ByVal hEnv As Long,
hDBc As Long) As Integer
Declare Function SQLDriverConnect
Lib "odbc.dll" (ByVal hDBc As Long,
ByVal hWnd As Integer,
ByVal szCSin As String,
ByVal iCSinLen As Integer,
ByVal szCSOut As String,
ByVal iCSOutMaxLen As Integer,
iCSOutLen As Integer,
ByVal iDriverComplete As Integer)
As Integer
' also define these constants:
Global Const SQL_SUCCESS = 0
Global Const SQL_SUCCESS_WITH_INFO = 1
Global Const SQL_STILL_EXECUTING = 2
Global Const SQL_NEED_DATA = 99
Global Const SQL_NO_DATA_FOUND = 100
Global Const SQL_ERROR = 1

using SQLSetConnectOption. There are numerous parameters available, the most significant of which is the
SQL_AUTOCOMMIT option. By default, this option is enabled, which means that transactions are committed as sent,
with no possibility of rollback. This can be very dangerous if the application uses multiple statements to process one
logical transaction. For DBMSs that do not support stored procedures and triggers, this situation is nearly unavoidable.
The function examples in Listing 51.3 demonstrates the ODBC API call to set a connection option.
Listing 51.3. This C function disables the AUTOCOMMIT option for a connection.
/* not part of ODBC.H */
enum ConnectOptionValues
{
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
OFF,
ON
);
int DisableAutoCommit(
HDBC hDBc) /* connection handle created using SQLAllocConnect */
{
RETCODE iError;
iError = SQLSetConnectOption(hDBc, SQL_AUTOCOMMIT, OFF);
return(iError);
}
Other connection options enable connections to be made read-only, to specify a translation DLL, to specify a trace file
for debugging, and to set transaction isolation levels. In addition, SQLSetConnectOption can be called using any of the
SQLSetStatementOption parameters. In this case, the option applies not to a specific statement handle, but to all
statement handles processed by the connection.
Applying SQL Transactions
Transaction control through ODBC is dependent on SQL_AUTOCOMMIT being set to OFF, as described in the
previous section. Before applying SQL, the application must call SQLAllocStmt to create a statement handle. After
allocating a statement handle, the application can then apply SQL using either prepared, or direct execution.
Prepared execution should be used when the statement to be processed is complex and will be called repeatedly. Under
the prepared execution method, the statement is compiled and the access plan is bound before the SQL is executed. For

Regardless of the type of execution used, the application should call SQLTransact to commit or roll back transactions
based on the return code of the call to SQLExecute or SQLExecDirect. If SQL_AUTOCOMMIT is set to OFF and the
application calls SQLTransact with SQL_ROLLBACK, all statements processed by the connection since the last commit
will be rolled back. The statement handle can then be freed using SQLFreeStmt. If the statement handle is not freed, it is
available to be reused or overwritten. The second parameter to SQLFreeStmt is an integer constant used to close an open
cursor, release buffers for parameters and bound columns, or free all resources associated with the statement
(invalidating the handle).
Listings 51.4 and 51.5 demonstrate how an application can insert values into a table, using either the prepared or the
direct execution method.
Listing 51.4. This C function inserts records into a table using prepared execution.
/* structure containing company information */
typedef struct
{
long ID;
char *Company;
char *Notes;
}
COMPANY;
int InsertCompanyRecords(
HENV hEnv, /* pre-allocated environment handle */
HDBC hDBc, /* pre-allocated connection handle */
COMPANY *Companies, /* pointer to an array of COMPANYs */
int iNumCompanies) /* number of COMPANYs in the array */
{
RETCODE iError;
int i;
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
HSTMT hStmt;
iError = SQLAllocStmt(hDBc, &hStmt);
if (iError == SQL_SUCCESS)

Declare Function SQLAllocStmt Lib "odbc.dll" (ByVal hStmt As Long)
As Integer
Declare Function SQLExecDirect Lib "odbc.dll" (ByVal hStmt As Long,
ByVal szSQL As String, ByVal iSQLLen As Long)
As Integer
Declare Function SQLFreeStmt Lib "odbc.dll" (ByVal hStmt As Long
ByVal iOption As Integer) As Integer
Declare Function SQLTransact Lib "odbc.dll" (ByVal hEnv As Long,
ByVal hDBc As Long, ByVal iType As Integer)
As Integer
' also define these constants:
Global Const SQL_CHAR = 1
Global Const SQL_INTEGER = 4
Global Const SQL_C_CHAR = 0
Global Const SQL_C_LONG = 1
Global Const SQL_COMMIT = 0
Global Const SQL_ROLLBACK = 1
Global Const SQL_CLOSE = 0
Global Const SQL_DROP = 1
Global Const SQL_UNBIND = 2
Global Const SQL_RESET_PARAMS = 3
Type COMPANY
Dim ID As Long
Dim Company As String
Dim Notes As String
End Type
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Function InsertCompanyRecords(ByVal hEnv As Long,
ByVal hDBc As Long, Companies() As COMPANY,
ByVal iNumCompanies As Integer) As Integer

documentation for the SQLParamData and SQLPutData functions.
Retrieving Result Sets
The prepared execution and direct execution methods also apply to SQL SELECT statements. The additional methods
available to retrieve results through ODBC, however, are almost too numerous to mention. SQLSetStmtOption can be
used to enable asynchronous processing, which allows single-threaded environments such as Windows 3.x to process
multiple statements simultaneously. When used with SQLSetScrollOptions, SQLSetStmtOption can enable multiple
rows to be fetched with a single call to SQLExtendedFetch. SQLSetScrollOptions and SQLExtendedFetch can also be
used to create cursors that scroll in both directions; and when used with SQLSetPos, the record pointer can be placed at a
specific row in the result set.
Unfortunately, these extended functions are not part of the core ODBC standard and are currently unsupported by the
Oracle ODBC driver. Although third-party driver vendors might supply some of these functions, the code examples in
this section will focus on bound and unbound fetches using prepared and direct execution. These examples use core
functions and level 1 extensions, all of which are supported by the current Oracle ODBC driver available from Oracle
Corporation.
Although the SQL to retrieve result sets can be executed in exactly the same manner as SQL to-process transactions, the
application must take additional steps to bind result set columns to application variables. Columns may be prebound
using SQLBindCol, or bound after execution, using SQLGetData.
In order to bind columns prior to execution, SQLBindCol must be called once for each column to be bound. The
arguments to SQLBindCol are somewhat similar to those for SQLSetParam, as enumerated here:
●
The statement handle for the executed SQL.
●
The number of the column to be bound (position, starting at 1, within the result set).
●
The C data type of the variable to be bound.
●
A pointer to storage allocated for the variable
●
The maximum length of the variable, in bytes.
●

long iLenOut1, iLenOut2, iLenOut3;
char tempName[80];
char tempNotes[255];
char szSQL[255];
iError = SQLAllocStmt(hDBc, &hStmt);
if (iError == SQL_SUCCESS)
{
sprintf (szSQL,
"SELECT ID, Company, Notes FROM Company WHERE
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
Company LIKE '%");
strcat(szSQL, szName);
strcat(szSQL, "%'");
iError = SQLExecDirect(hStmt, szSQL, SQL_NTS);
if (iError == SQL_SUCCESS)
{
iError = SQLBindCol(hStmt, 1, SQL_C_LONG, &tempID, 0
, &iLenOut1);
iError = SQLBindCol(hStmt, 2, SQL_C_CHAR, tempName
, 80, &iLenOut2);
iError = SQLBindCol(hStmt, 3, SQL_C_CHAR, tempNotes
, 255, &iLenOut3);
iError = SQLFetch(hStmt);
if (iError >= SQL_SUCCESS)
{
Company>ID = tempID;
Company>Company = strdup(tempName);
Company>Notes = strdup(tempNotes);
}
}

While (iError >= SQL_SUCCESS)
ReDim Preserve Companies(iNumCoOut)
iError = SQLFetch(hStmt)
iError = SQLGetData(hStmt, 1, SQL_C_CHAR,
Companies(iNumCoOut).Company,
80, iOut1)
iError = SQLGetData(hStmt, 2, SQL_C_CHAR,
Companies(iNumCoOut).Notes, 255, iOut2)
If (iError >= SQL_SUCCESS) Then
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
iNumCoOut = iNumCoOut + 1
End If
Wend
End If
End If
iTemp = SQLFreeStmt(hStmt, SQL_CLOSE)
iTemp = SQLFreeStmt(hStmt, SQL_DROP)
GetCompanies = iError
End Function
The examples in Listings 51.6 and 51.7 also demonstrate dynamic SQL building by the application. Although prepared
execution could be used to parameterize the SQL statements, prepared execution is not always the best choice for
dynamic SQL. When retrieving result sets, the SQL is typically not executed repeatedly, and in these cases, direct
execution is often preferable.
Handling Errors
The return values of ODBC functions should always be checked to determine whether an error has occurred. The return
code SQL_SUCCESS is defined as 0. Error codes are defined as negative numbers, whereas positive numbers are used
to indicate that additional information is required or is being provided by the driver. How these error and informational
return codes are handled within a program is entirely application-specific. The ODBC API provides a function to retrieve
standard ODBC error codes, DBMS-specific error codes, and error and informational text from the driver. This function,
SQLError, has the following arguments: