Programmer's Guide to the Pro*C/C++ Precompiler | ![]() Library |
![]() Product |
![]() Contents |
![]() Index |
Figure 1 - 1. Embedded SQL Program Development
Unlike many application development tools, the Pro*C/C++ Precompiler lets you create highly customized applications. For example, you can create user interfaces that incorporate the latest windowing and mouse technology. You can also create applications that run in the background without the need for user interaction.
Furthermore, Pro*C/C++ helps you fine-tune your applications. It allows close monitoring of resource use, SQL statement execution, and various runtime indicators. With this information, you can tweak program parameters for maximum performance.
Although precompiling adds a step to the application development process, it saves time because the precompiler, not you, translates each embedded SQL statement into several calls to the Oracle runtime library (SQLLIB).
SQL has become the database language of choice because it is flexible, powerful, and easy to learn. Being non-procedural, it lets you specify what you want done without specifying how to do it. A few English-like statements make it easy to manipulate Oracle data one row or many rows at a time.
You can execute any SQL (not SQL*Plus) statement from an application program. For example, you can
The main advantage of embedded PL/SQL is better performance. Unlike SQL, PL/SQL allows you to group SQL statements logically and send them to Oracle in a block rather than one by one. This reduces network traffic and processing overhead.
For more information about PL/SQL, including how to embed it in an application program, see Chapter 5, "Using Embedded PL/SQL''.
Figure 1 - 2. Features and Benefits
For example, Pro*C/C++ allows you to
A conforming SQL implementation must support at least Entry SQL. The Oracle Pro*C/C++ Precompiler does conform to Entry SQL92.
NIST standard FIPS PUB 127-1, which applies to RDBMS software acquired for federal use, also adopts the ANSI standards. In addition, it specifies minimum sizing parameters for database constructs and requires a "FIPS Flagger" to identify ANSI extensions.
For copies of the ANSI standards, write to
American National Standards Institute
1430 Broadway
New York, NY 10018
USA
For a copy of the ISO standard, write to the national standards office of any ISO participant. For a copy of the NIST standard, write to
National Technical Information Service
U.S. Department of Commerce
Springfield, VA 22161
USA
The Pro*C Precompiler also complies 100% with the NIST standard. It provides a FIPS Flagger and an option named FIPS, which enables the FIPS Flagger. For more information, see the section "FIPS Flagger" below.
For more information about the tests, write to
National Computer Systems Laboratory
Attn: Software Standards Testing Program
National Institute of Standards and Technology
Gaithersburg, MD 20899
USA
You can use the FIPS Flagger to identify
Question:
I'm confused by VARCHAR. What's a VARCHAR?
Answer:
Here's a short description of VARCHARs:
VARCHAR2
A kind of column in the database that contains variable-length character data, up to 2000 bytes. This is what Oracle calls an "internal datatype'', because it's a possible column type. See page 3 - 49.
VARCHAR
An Oracle "external datatype'' (datatype code 9). You use this only if you're doing dynamic SQL Method 4, or datatype equivalencing. See page 3 - 52 for datatype equivalencing, and Chapter 12 for dynamic SQL Method 4.
VARCHAR[n] varchar[n]
This is a Pro*C/C++ "pseudotype'' that you can declare as a host variable in your Pro*C/C++ program. It's actually generated by Pro*C as a struct, with a 2-byte length element, and a [n]-byte character array. See page 3 - 35.
Question:
Does Pro*C/C++ generate calls to the Oracle Call Interface (OCI)?
Answer:
No. Pro*C/C++ generates data structures, and calls to its runtime library: SQLLIB (libsql.a in UNIX). SQLLIB, in turn, calls the UPI to communicate with the database.
Question:
Then why not just code using SQLLIB calls, and not use Pro*C/C++?
Answer:
SQLLIB is not externally documented, is unsupported, and might change from release to release. Also, Pro*C/C++ is an ANSI/ISO compliant product, that follows the standard requirements for embedded SQL.
If you need to do low-level coding, use the OCI. It is supported, and is guaranteed to stay compatible from release to release. Also, Oracle is committed to supporting the OCI.
You can also mix OCI and Pro*C. See page 3 - 97.
Question:
Can I call a PL/SQL stored procedure from a Pro*C/C++ program?
Answer:
Certainly. See Chapter 5. There's a demo program starting on page 5 - 21.
Question:
Can I write C++ code, and precompile it using Pro*C/C++?
Answer:
Yes. Starting with Pro*C/C++ release 2.1, you can precompile C++ applications. See Chapter 6.
Question:
Can I use bind variables anyplace in a SQL statement? For example, I'd like to be able to input the name of a table in my SQL statements at runtime. But when I use host variables, I get precompiler errors.
Answer:
In general, you can use host variables at any place in a SQL, or PL/SQL, statement where expressions are allowed. See page 3 - 18. The following SQL statement, where table_name is a host variable, is illegal:
EXEC SQL SELECT ename,sal INTO :name, :salary FROM :table_name;
To solve your problem, you need to use dynamic SQL. See Chapter 11. There is a demo program that you can adapt to do this starting on page 11 - 9.
Question:
I am confused by character handling in Pro*C/C++. It seems that there are many options. Can you help?
Answer:
There are many options, but we can simplify. First of all, if you need compatibility with previous V1.x precompilers, and with both Oracle V6 and Oracle7, the safest thing to do is use VARCHAR[n] host variables. See page 3 - 35.
The default datatype for all other character variables in Pro*C/C++ is CHARZ; see page 3 - 55. Briefly, this means that you must null-terminate the string on input, and it is both blank-padded and null-terminated on output.
If neither VARCHAR nor CHARZ works for your application, and you need total C-like behavior (null termination, absolutely no blank-padding), use the TYPE command and the C typedef statement, and use datatype equivalencing to convert your character host variables to STRING. See page 3 - 59. There is a sample program that shows how to use the TYPE command starting .
Question:
What about character pointers? Is there anything special about them?
Answer:
Yes. When Pro*C/C++ binds an input or output host variable, it must know the length. When you use VARCHAR[n], or declare a host variable of type char[n], Pro*C/C++ knows the length from your declaration. But when you use a character pointer as a host variable, and malloc() the buffer in your program, Pro*C/C++ has no way of knowing the length.
So, what you must do on output is not only allocate the buffer, but pad it out with some non-null characters, then null-terminate it. On input or output, Pro*C/C++ calls strlen() for the buffer to get the length. See page 3 - 33.
Question:
Why doesn't SPOOL work in Pro*C/C++?
Answer:
SPOOL is a special command used in SQL*Plus. It is not an embedded SQL command. See page 2 - 3.
Question:
Where can I find the on-line versions of the sample programs?
Answer:
Each Oracle installation should have a demo directory. On UNIX systems, for example, it is located in $ORACLE_HOME/proc/demo. If the directory is not there, or it does not contain the sample programs, see your system or database administrator.
Question:
How can I compile and link my application?
Answer:
Compiling and linking are very platform specific. Your system-specific Oracle documentation has instructions on how to link a Pro*C/C++ application. On UNIX systems, there is a makefile called proc.mk in the demo directory. To link, say, the demo program sample1.pc, you would enter the command line
make -f proc.mk sample1
If you need to use special precompiler options, you can run Pro*C/C++ separately, then do the make. Or, you can create your own custom makefile. For example, if your program contains embedded PL/SQL code, you can do
proc cv_demo userid=scott/tiger sqlcheck=semantics make -f proc.mk cv_demo
On VMS systems, there is a script called LNPROC that you use to link your Pro*C/C++ applications.
Question:
I have been told that Pro*C/C++ now supports using structures as host variables. How does this work with the array interface?
Answer:
You can use arrays inside a single structure. See page 3 - 21. However, you cannot use an array of structures with the array interface. See page 3 - 22.
Question:
Is it possible to have recursive functions in Pro*C/C++, if I use embedded SQL in the function?
Answer:
Yes. With release 2.1 of Pro*C/C++, you can also use cursor variables in recursive functions.
Question:
Can I use any release of the Oracle Pro*C or Pro*C/C++ Precompiler with any version of the Oracle Server?
Answer:
No. You can use an older version of Pro*C or Pro*C/C++ with a newer version of the server, but you cannot use a newer version of Pro*C or Pro*C/C++ with an older version of the server.
For example, you can use release 1.4 of Pro*C with Oracle7, but you cannot use Pro*C/C++ release 2.1 with the Oracle version 6 server.
Question:
When my application runs under Oracle7, I keep getting an ORA-1405 error (fetched column value is null). It worked fine under Oracle V6. What is happening?
Answer:
You are selecting a null into a host variable that does not have an associated indicator variable. This is not in compliance with the ANSI/ISO standards, which is why Oracle7 changed.
If possible, rewrite your program using indicator variables, and use indicators in future development. Indicator variables are described .
Alternatively, if precompiling with MODE=ORACLE and DBMS=V7 or V6_CHAR, specify UNSAFE_NULL=YES the command line (see "UNSAFE_NULL" for more information) to disable the ORA-01405 message, or precompile with DBMS=V6.
Question:
Are all SQLLIB functions private?
Answer:
No. There are some SQLLIB functions that you can call to get information about your program, or its data. The SQLLIB public functions are shown here:
sqlald() sqlaldt()
Used to allocate a SQL descriptor array (SQLDA) for dynamic SQL Method 4. See page 12 - 4.
sqlcda() sqlcdat()
Used to convert a Pro*C cursor variable to an OCI cursor data area. See page 3 - 81.
sqlclu() sqlclut()
Used to free a SQLDA allocated using sqlald(). See page 12 - 32.
sqlcur() sqlcurt()
Used to convert an OCI cursor data area to a Pro*C cursor variable. See page 3 - 81.
sqlglm() sqlglmt()
Returns a long error message. See page 9 - 22.
sqlgls() sqlglst()
Used to return the text of the most recently executed SQL statement. See page 9 - 29.
sqlld2() sqlld2t()
Used to obtain a valid Logon Data Area for a named connection, when OCI calls are being used in a Pro*C program. See page 3 - 115.
sqllda() sqlldat()
Used to obtain a valid Logon Data Area for the most recent connection, when OCI calls are being used in a Pro*C program. See page 3 - 97.
sqlnul() sqlnult()
Returns an indication of null status, for dynamic SQL Method 4. See page 12 - 15.
sqlprc() sqlprct()
Returns precision and scale of NUMBERS. See page 12 - 13.
sqlpr2() sqlpr2t()
A variant of sqlprc(). See page 12 - 15.
sqlvcp() sqlvcpt()
Used for getting the padded size of a VARCHAR[n]. See page 3 - 37.
In the preceding list, the function names ending in t are the thread-safe SQLLIB public functions. Use these functions in multi-threaded applications. For more information about thread-safe public functions, see "Thread-safe SQLLIB Public Functions" .
![]() ![]() Prev Next |
![]() Copyright © 1997 Oracle Corporation. All Rights Reserved. |
![]() Library |
![]() Product |
![]() Contents |
![]() Index |