Release 8.0
A58233-01
 Library |
 Product |
 Contents |
 Index |
| Pro*C/C++ Precompiler Programmer's Guide Release 8.0 A58233-01 |
|
This chapter provides the basic information you need to write a Pro*C/C++ program. This chapter covers the following topics:
This chapter also includes several complete demonstration programs that you can study. These programs illustrate the techniques described. They are available on-line in your demo directory, so you can compile and run them, and modify them for your own uses.
Pro*C/C++ supports most C preprocessor directives. Some of the things that you can do using the Pro*C/C++ preprocessor are:
The Pro*C/C++ preprocessor recognizes most C preprocessor commands, and effectively performs the required macro substitutions, file inclusions, and conditional source text inclusions or exclusions. The Pro*C/C++ preprocessor uses the values obtained from preprocessing, and alters the source output text (the generated .c output file).
An example should clarify this point. Consider the following program fragment:
#include "my_header.h" ... VARCHAR name[VC_LEN]; /* a Pro*C-supplied datatype */ char another_name[VC_LEN]; /* a pure C datatype */ ...
Suppose the file my_header.h in the current directory contains, among other things, the line
#define VC_LEN 20
The precompiler reads the file my_header.h, and uses the defined value of VC_LEN (i.e., 20), declares the structure of name as VARCHAR[20).
char is a native type. The precompiler does not substitute 20 in the declaration of another_name[VC_LEN].
This does not matter, since the precompiler does not need to process declarations of C datatypes, even when they are used as host variables. It is left up to the C compiler's preprocessor to physically include the file my_header.h, and perform the substitution of 20 for VC_LEN in the declaration of another_name.
The preprocessor directives that Pro*C/C++ supports are:
Some C preprocessor directives are not used by the Pro*C/C++ preprocessor. Most of these directives are not relevant for the precompiler. For example, #pragma is a directive for the C compiler-the precompiler does not process it. The C preprocessor directives not processed by the precompiler are:
While your C compiler preprocessor may support these directives, Pro*C/C++ does not use them. Most of these directives are not used by the precompiler. You can use these directives in your Pro*C/C++ program if your compiler supports them, but only in C or C++ code, not in embedded SQL statements or declarations of variables using datatypes supplied by the precompiler, such as VARCHAR.
Pro*C/C++ predefines a C preprocessor macro called ORA_PROC that you can use to avoid having the precompiler process unnecessary or irrelevant sections of code. Some applications include large header files, which provide information that is unnecessary when precompiling. By conditionally including such header files based on the ORA_PROC macro, the precompiler never reads the file.
The following example uses the ORA_PROC macro to exclude the irrelevant.h file:
#ifndef ORA_PROC #include <irrelevant.h> #endif
Because ORA_PROC is defined during precompilation, the irrelevant.h file is never included.
The ORA_PROC macro is available only for C preprocessor directives, such as #ifdef or #ifndef. The EXEC ORACLE conditional statements do not share the same namespaces as the C preprocessor macros. Therefore, the condition in the following example does not use the predefined ORA_PROC macro:
EXEC ORACLE IFNDEF ORA_PROC; <section of code to be ignored> EXEC ORACLE ENDIF;
ORA_PROC, in this case, must be set using either the DEFINE option or an EXEC ORACLE DEFINE statement for this conditional code fragment to work properly.
The Pro*C/C++ Precompiler for each system assumes a standard location for header files to be read by the preprocessor, such as sqlca.h, oraca.h, and sqlda.h. For example, on most UNIX systems, the standard location is $ORACLE_HOME/proc/lib. For the default location on your system, see your system-specific Oracle documentation. If header files that you need to include are not in the default location, you must use the INCLUDE= option, on the command line or as an EXEC ORACLE option. See Chapter 9, "Running the Pro*C/C++ Precompiler", for more information about the precompiler options, and about the EXEC ORACLE options.
To specify the location of system header files, such as stdio.h or iostream.h, where the location might be different from that hard-coded into Pro*C/C++ use the SYS_INCLUDE precompiler option. See Chapter 9, "Running the Pro*C/C++ Precompiler", for more information.
You can use the #define command to create named constants, and use them in place of "magic numbers" in your source code. You can use #defined constants for declarations that the precompiler requires, such as VARCHAR[const]. For example, instead of potentially buggy code such as:
... VARCHAR emp_name[10]; VARCHAR dept_loc[14]; ... ... /* much later in the code ... */ f42() { /* did you remember the correct size? */ VARCHAR new_dept_loc[10]; ... }
you can code:
#define ENAME_LEN 10 #define LOCATION_LEN 14 VARCHAR new_emp_name[ENAME_LEN]; ... /* much later in the code ... */ f42() { VARCHAR new_dept_loc[LOCATION_LEN]; ... }
You can use preprocessor macros with arguments for objects that the precompiler must process, just as you can for C objects. For example:
#define ENAME_LEN 10 #define LOCATION_LEN 14 #define MAX(A,B) ((A) > (B) ? (A) : (B)) ... f43() { /* need to declare a temporary variable to hold either an employee name or a department location */ VARCHAR name_loc_temp[MAX(ENAME_LEN, LOCATION_LEN)]; ... }
You can use the #include, #ifdef and #endif preprocessor directives to conditionally include a file that the precompiler requires. For example:
#ifdef ORACLE_MODE # include <sqlca.h> #else long SQLCODE; #endif
There are restrictions on the use of the #define preprocessor directive in Pro*C/C++ You cannot use the #define directive to create symbolic constants for use in executable SQL statements. The following invalid example demonstrates this:
#define RESEARCH_DEPT 40 ... EXEC SQL SELECT empno, sal INTO :emp_number, :salary /* host arrays */ FROM emp WHERE deptno = RESEARCH_DEPT; /* INVALID! */
The only declarative SQL statements where you can legally use a #defined macro are TYPE and VAR statements. So, for example, the following uses of a macro are legal in Pro*C/C++
#define STR_LEN 40 ... typedef char asciiz[STR_LEN]; ... EXEC SQL TYPE asciiz IS STRING(STR_LEN) REFERENCE; ... EXEC SQL VAR password IS STRING(STR_LEN) REFERENCE;
The preprocessor ignores directives # and ## to create tokens that the precompiler must recognize. You can, of course, use these commands (if your C compiler's preprocessor supports them) in pure C code, that the precompiler does not have to process. Using the preprocessor command ## is not valid in this example:
#define MAKE_COL_NAME(A) col ## A ... EXEC SQL SELECT MAKE_COL_NAME(1), MAKE_COL_NAME(2) INTO :x, :y FROM table1;
The example is incorrect because the precompiler ignores ##.
Because of the way the Pro*C/C++ preprocessor handles the #include directive, as described in the previous section, you cannot use the #include directive to include files that contain embedded SQL statements. You use #include to include files that contain purely declarative statements and directives; for example, #defines, and declarations of variables and structures required by the precompiler, such as in sqlca.h.
You can include the sqlca.h, oraca.h, and sqlda.h declaration header files in your Pro*C/C++ program using either the C/C++ preprocessor #include command, or the precompiler EXEC SQL INCLUDE command. For complete information on the contents of these header files, see Chapter 11, "Handling Runtime Errors". For example, you use the following statement to include the SQL Communications Area structure (SQLCA) in your program with the EXEC SQL option:
EXEC SQL INCLUDE sqlca;
To include the SQLCA using the C/C++ preprocessor directive, add the following code:
#include <sqlca.h>
When you use the preprocessor #include directive, you must specify the file extension (such as .h).
Note: If you need to include the SQLCA in multiple places, using the #include directive, you should precede the #include with the directive #undef SQLCA. This is because sqlca.h starts with the lines
#ifndef SQLCA #define SQLCA 1and then declares the SQLCA struct only in the case that SQLCA is not defined.
When you precompile a file that contains a #include directive or an EXEC SQL INCLUDE statement, you have to tell the precompiler the location of all files to be included. You can use the INCLUDE= option, either in the command line, or in the system configuration file, or in the user configuration file. See Chapter 9, "Running the Pro*C/C++ Precompiler", for more information about the INCLUDE precompiler option , the precedence of searches for included files, and configuration files.
The default location for standard preprocessor header files, such as sqlca.h, oraca.h, and sqlda.h, is built into the precompiler. The location varies from system to system. See your system-specific Oracle documentation for the default location on your system.
When you compile the .c output file that Pro*C/C++ generates, you must use the option provided by your compiler and operating system to identify the location of included files.
For example, on most UNIX systems, you can compile the generated C source file using the command
cc -o progname -I$ORACLE_HOME/sqllib/public ... filename.c ...
On VAX/OPENVMS systems, you pre-pend the include directory path to the value in the logical VAXC$INCLUDE.
When you use an EXEC SQL INCLUDE statement in your program, the precompiler includes the source text in the output (.c) file. Therefore, you can have declarative and executable embedded SQL statements in a file that is included using EXEC SQL INCLUDE.
When you include a file using #include, the precompiler merely reads the file, and keeps track of #defined macros.
Warning: VARCHAR declarations and SQL statements are NOT allowed in #included files. For this reason, you cannot have SQL statements in files that are included using the Pro*C/C++ preprocessor #include directive.
If you define macros on the C compiler's command line, you might also have to define these macros on the precompiler command line, depending on the requirements of your application. For example, if you compile with a UNIX command line such as
cc -DDEBUG ...
you should precompile using the DEFINE= option, namely
proc DEFINE=DEBUG ...
Release 8.0 of the Pro*C/C++ Precompiler is compatible with earlier Pro*C and Pro*C/C++ releases. However, there are several things that you should consider when you migrate your application from earlier Pro*C/C++ releases. This section discusses some of these issues.
Many applications have been written under the assumption that character strings are of varying length (such as VARCHAR2). By default, Oracle8 uses fixed-length, blank-padded, 0-terminated character strings (CHARZ), to conform to the current SQL standards.
If your application assumes that character strings are varying in length (and this is especially important in the string comparison semantics), then you should precompile your application using the options DBMS=V8 and CHAR_MAP=VARCHAR2. See "National Language Support" on page 4-2 for details.
DBMS=V6 provides Oracle V6 semantics in several areas, not just character string semantics.
See the description of the DBMS options on "DBMS" on page 9-14 for a complete list of the effects of the DBMS options.
In release 8.0, the option value DBMS=V6_CHAR is deprecated (an ANSI term meaning that it will become obsolete in the next release) and is replaced by the option CHAR_MAP=VARCHAR2 (see "Precompiler Option CHAR_MAP" on page 3-50).
Error and warning codes (PCC errors) are different between earlier releases of Pro*C/C++ and the current release. See Oracle8 Error Messages for a complete list of PCC codes and messages.
The runtime messages issued by SQLLIB now have the prefix SQL-, rather than the RTL- prefix used in earlier Pro*C/C++ and Pro*C releases. The message codes remain the same as those of earlier releases.
When precompiling with SQLCHECK=FULL, PLS is the prefix used by the PL/SQL compiler. Such errors are not from Pro*C/C++.
The location of all included files that need to be precompiled must be specified on the command line, or in a configuration file. (See Chapter 9, "Running the Pro*C/C++ Precompiler" for complete information about precompiler options and configuration files.)
For example, if you are developing under UNIX, and your application includes files in the directory /home/project42/include, you must specify this directory both on the Pro*C/C++ command line and on the cc command line. You use commands like these:
proc iname=my_app.pc include=/home/project42/include . . . cc -I/home/project42/include . . . my_app.c
or you include the appropriate macros in a makefile. For complete information about compiling and linking your Pro*C/C++ release 8.0 application, see your system-specific Oracle documentation.
The output file (.c) is generated in the same directory as the input file.To generate an output file in a different directory from the input file, use the ONAME option to explicitly specify the desired location of your output file. For more information, see "ONAME" on page 9-30.
If you are migrating an application from Pro*C release 1.3 or release 1.4, used since Oracle V6, to Oracle8, there is a major change in behavior if you do not use indicator variables. Oracle V6 does not return an error if you SELECT or FETCH a NULL into a host variable that has no associated indicator variable. With Oracle7, the normal behavior is that SELECTing or FETCHing a NULL into a host variable that has no associated indicator variable does cause an error.
The error code is ORA-01405 in SQLCODE and "22002" in SQLSTATE.
To avoid this error without re-coding your application, you can specify DBMS=V6, or you can specify UNSAFE_NULL=YES (as described on "UNSAFE_NULL" on page 9-37) with DBMS=V7 or V8 and MODE=ORACLE. See the description of the DBMS option on "DBMS" on page 9-14 for complete information.
However, Oracle recommends that you always use indicator variables in new Pro*C/C++ applications.
This section deals with embedded SQL syntax, coding conventions, and C-specific features and restrictions. Topics are arranged alphabetically for quick reference.
The Pro*C/C++ Precompiler can optionally generate code that can be compiled using supported C++ compilers. See Chapter 7, "Using C++", for a complete explanation of this capability.
You can place C-style Comments (/* ... */) in a SQL statement wherever blanks can be placed (except between the keywords EXEC SQL). Also, you can place ANSI-style Comments (- - ...) within SQL statements at the end of a line, as the following example shows:
EXEC SQL SELECT ENAME, SAL INTO :emp_name, :salary -- output host variables FROM EMP WHERE DEPTNO = :dept_number;
You can use C++ style Comments (//) in your Pro*C/C++ source if you precompile using the CODE=CPP precompiler option.
An L or l suffix specifies a long integer constant, a U or u suffix specifies an unsigned integer constant, a 0X or 0x prefix specifies a hexadecimal integer constant, and an F or f suffix specifies a float floating-point constant. These forms are not allowed in SQL statements.
While C uses single quotes to delimit single characters, as in
ch = getchar(); switch (ch) { case 'U': update(); break; case 'I': insert(); break; ...
SQL uses single quotes to delimit character strings, as in
EXEC SQL SELECT ENAME, SAL FROM EMP WHERE JOB = 'MANAGER';
While C uses double quotes to delimit character strings, as in
printf("\nG'Day, mate!");
SQL uses double quotes to delimit identifiers containing special or lowercase characters, as in
EXEC SQL CREATE TABLE "Emp2" (empno number(4), ...);
The Pro*C/C++ Precompiler cannot process arbitrarily long source files. Some of the variables used internally limit the size of the generated file. There is a limit to the number of lines allowed; the following aspects of the source file are contributing factors to the file-size constraint:
To prevent problems related to this limitation, use multiple program units to sufficiently reduce the size of the source files.
The ANSI C standard (X3.159-1989) provides for function prototyping. A function prototype declares a function and the datatypes of its arguments, so that the C compiler can detect missing or mismatched arguments.
The CODE option, which you can enter on the command line or in a configuration file, determines the way that the precompiler generates C or C++ code.
When you precompile your program with CODE=ANSI_C, the precompiler generates fully prototyped function declarations. For example:
extern void sqlora(long *, void *);
When you precompile with the option CODE=KR_C (KR for "Kernighan and Ritchie"), the precompiler generates function prototypes in the same way that it does for ANSI_C, except that function parameter lists are commented out. For example:
extern void sqlora(/*_ long *, void * _*/);
So, make sure to set the precompiler option CODE to KR_C if you use a C compiler that does not support ANSI C. When the CODE option is set to ANSI_C, the precompiler can also generate other ANSI-specific constructs; for example, the const type qualifier.
Host variable names can consist of upper or lowercase letters, digits, and underscores, but must begin with a letter. They can be any length, but only the first 31 characters are significant to the Pro*C/C++ Precompiler. Your C compiler or linker might require a shorter maximum length, so check your C compiler user's guide.
For SQL92 standards conformance, restrict the length of host variable names to 18 or fewer characters.
You can continue SQL statements from one line to the next. You must use a backslash (\) to continue a string literal from one line to the next, as the following example shows:
EXEC SQL INSERT INTO dept (deptno, dname) VALUES (50, 'PURCHAS\ ING');
In this context, the precompiler treats the backslash as a continuation character.
The precompiler option MAXLITERAL lets you specify the maximum length of string literals generated by the precompiler. The MAXLITERAL default value is 1024. Specify a smaller value if required. For example, if your C compiler cannot handle string literals longer than 512 characters, you then specify MAXLITERAL=512. Check your C compiler user's guide.
The logical operators and the "equal to" relational operator are different in C and SQL, as the list below shows. These C operators are not allowed in SQL statements.:
|
SQL Operator |
C Operator |
|
NOT |
! |
|
AND |
&& |
|
OR |
|| |
|
= |
== |
Operators also not allowed are:
|
Type |
C Operator |
|
address |
& |
|
bitwise |
&, |, ^, ~ |
|
compound assignment |
+=, -=, *=, etc. |
|
conditional |
?: |
|
decrement |
-- |
|
increment |
++ |
|
indirection |
* |
|
modulus |
% |
|
shift |
>>,<< |
You can associate standard C statement labels (label_name:) with SQL statements, as this example shows:
EXEC SQL WHENEVER SQLERROR GOTO connect_error; ... connect_error: EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK RELEASE; printf("\nInvalid username/password\n"); exit(1);
Label names can be any length, but only the first 31 characters are significant. Your C compiler might require a different maximum length. Check your C compiler user's guide.
Embedded SQL statements are always terminated by a semicolon, as the following example shows:
EXEC SQL DELETE FROM emp WHERE deptno = :dept_number;
Currently, Pro*C/C++ allows numeric literals and simple constant expressions involving numeric literals to be used when declaring the sizes of host variables (such as char or VARCHAR), as in the following examples:
#define LENGTH 10 VARCHAR v[LENGTH]; char c[LENGTH + 1];
You can use numeric constant declarations such as:
const int length = 10; VARCHAR v[length]; char c[length + 1];
This is highly desirable, especially for programmers who use ANSI or C++ compilers that support such constant declarations.
Pro*C/C++ has always done constant folding of precompile-time evaluable constant expressions, but it has never allowed the use of a numeric constant declaration in any constant expression.
Pro*C/C++ supports the use of numeric constant declarations anywhere that an ordinary numeric literal or macro is used, provided that the macro expands to some numeric literal.
This is used primarily for declaring the sizes of arrays for bind variables to be used in a SQL statement.
In Pro*C/C++, normal C scoping rules are used to find and locate the declaration of a numeric constant declaration.
const int g = 30; /* Global declaration to both function_1() and function_2() */ void function_1() { const int a = 10; /* Local declaration only to function_1() */ char x[a]; exec sql select ename into :x from emp where job = 'PRESIDENT'; } void function_2() { const int a = 20; /* Local declaration only to function_2() */ VARCHAR v[a]; exec sql select ename into :v from emp where job = 'PRESIDENT'; } void main() { char m[g]; /* The global g */ exec sql select ename into :m from emp where job = 'PRESIDENT'; }
Variables which are of specific static types need to be defined with static and initialized. The following rules must be kept in mind when declaring numeric constants in Pro*C/C++:
const qualifier must be used when declaring the constant
Any attempt to use an identifier that does not resolve to a constant declaration with a valid initializer is considered an error.
The following shows examples of what is not permitted and why.
int a; int b = 10; volatile c; volatile d = 10; const e; const f = b; VARCHAR v1[a]; /* No const qualifier, missing initializer */ VARCHAR v2[b]; /* No const qualifier */ VARCHAR v3[c]; /* Not a constant, missing initializer */ VARCHAR v4[d]; /* Not a constant */ VARCHAR v5[e]; /* Missing initializer */ VARCHAR v6[f]; /* Bad initializer.. b is not a constant */
Oracle recognizes two kinds of datatypes: internal and external. Internal datatypes specify how Oracle stores column values in database tables, as well as the formats used to represent pseudocolumn (values such as NULL, SYSDATE, USER, etc.) values. External datatypes specify the formats used to store values in input and output host variables. For descriptions of the Oracle datatypes, see PL/SQL User's Guide and Reference.
For values stored in database columns, Oracle uses the internal datatypes shown in Table 3-1
These internal datatypes can be quite different from C datatypes. For example, C has no datatype that is equivalent to the Oracle NUMBER datatype. However, NUMBERs can be converted between C datatypes such as float and double, with some restrictions. For example, the Oracle NUMBER datatype allows up to 38 decimal digits of precision, while no current C implementations can represent doubles with that degree of precision.
The Oracle NUMBER datatype represents values exactly (within the precision limits), while floating-point formats cannot represent values such as 10.0 exactly.
Use the LOB datatypes to store unstructured data (text, graphic images, video clips, and sound waveforms). BFILE data is stored in an operating system file outside the database. LOB types store locators that specify the location of the data. For more information, see "Handling LOB Types" on page 4 - 8.
NCHAR and NVARCHAR2 are used to store NLS (NATIONAL Language Support) character data. See "National Language Support" on page 4-2 for a discussion of these datatypes.
As shown in Table 3-2, the external datatypes include all the internal datatypes plus several datatypes that closely match C constructs. For example, the STRING external datatype refers to a C null-terminated string.
Brief descriptions of the Oracle datatypes follow.
You use the VARCHAR2 datatype to store variable-length character strings. The maximum length of a VARCHAR2 value is 64K bytes.
You specify the maximum length of a VARCHAR2(n) value in bytes, not characters. So, if a VARCHAR2(n) variable stores multi-byte characters, its maximum length can be less than n characters.
When you precompile using the options DBMS=V6 or CHAR_MAP=VARCHAR2, Oracle assigns the VARCHAR2 datatype to all host variables that you declare as char[n] or char.
Oracle reads the number of bytes specified for the input host variable, strips any trailing blanks, then stores the input value in the target database column. Be careful. An uninitialized host variable can contain nulls. So, always blank-pad a character input host variable to its declared length, and do not null-terminate it.
If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value contains nothing but blanks, Oracle treats it like a null.
Oracle can convert a character value to a NUMBER column value if the character value represents a valid number. Otherwise, Oracle generates an error.
Oracle returns the number of bytes specified for the output host variable, blank-padding if necessary, then assigns the output value to the target host variable. If a null is returned, Oracle fills the host variable with blanks.
If the output value is longer than the declared length of the host variable, Oracle truncates the value before assigning it to the host variable. If there is an indicator variable associated with the host variable, Oracle sets it to the original length of the output value.
Oracle can convert NUMBER column values to character values. The length of the character host variable determines precision. If the host variable is too short for the number, scientific notation is used. For example, if you SELECT the column value 123456789 into a character host variable of length 6, Oracle returns the value `1.2E08'.
You use the NUMBER datatype to store fixed or floating-point Oracle numbers. You can specify precision and scale. The maximum precision of a NUMBER value is 38; the magnitude range is 1.0E-129 to 9.99E125. Scale can range from -84 to 127.
NUMBER values are stored in a variable-length format, starting with an exponent byte and followed by 19 mantissa bytes. The high-order bit of the exponent byte is a sign bit, which is set for positive numbers. The low-order 7 bits represent the magnitude.
The mantissa forms a 38-digit number with each byte representing 2 of the digits in a base-100 format. The sign of the mantissa is specified by the value of the first (leftmost) byte. If greater than 101 then the mantissa is negative and and the first digit of the mantissa is equal to the leftmost byte minus 101.
On output, the host variable contains the number as represented internally by Oracle. To accommodate the largest possible number, the output host variable must be 21 bytes long. Only the bytes used to represent the number are returned. Oracle does not blank-pad or null-terminate the output value. If you need to know the length of the returned value, use the VARNUM datatype instead.
There is seldom a need to use this external datatype.
You use the INTEGER datatype to store numbers that have no fractional part. An integer is a signed, 2-byte or 4-byte binary number. The order of the bytes in a word is system dependent. You must specify a length for input and output host variables. On output, if the column value is a real number, Oracle truncates any fractional part.
You use the FLOAT datatype to store numbers that have a fractional part or that exceed the capacity of the INTEGER datatype. The number is represented using the floating-point format of your computer and typically requires 4 or 8 bytes of storage. You must specify a length for input and output host variables.
Oracle can represent numbers with greater precision than most floating-point implementations because the internal format of Oracle numbers is decimal. This can cause a loss of precision when fetching into a FLOAT variable.
The STRING datatype is like the VARCHAR2 datatype, except that a STRING value is always null-terminated. When you precompile using the option CHAR_MAP=STRING, Oracle assigns the STRING datatype to all host variables that you declare as char[n] or char.
Oracle uses the specified length to limit the scan for the null terminator. If a null terminator is not found, Oracle generates an error. If you do not specify a length, Oracle assumes the maximum length of 2000 bytes. The minimum length of a STRING value is 2 bytes. If the first character is a null terminator and the specified length is 2, Oracle inserts a null unless the column is defined as NOT NULL; if the column is defined as NOT NULL, an error occurs. An all-blank value is stored intact.
Oracle appends a null byte to the last character returned. If the string length exceeds the specified length, Oracle truncates the output value and appends a null byte. If a NULL is SELECTed, Oracle returns a null byte in the first character position.
The VARNUM datatype is like the NUMBER datatype, except that the first byte of a VARNUM variable stores the length of the representation.
On input, you must set the first byte of the host variable to the length of the value. On output, the host variable contains the length followed by the number as represented internally by Oracle. To accommodate the largest possible number, the host variable must be 22 bytes long. After SELECTing a column value into a VARNUM host variable, you can check the first byte to get the length of the value.
Normally, there is little reason to use this datatype.
You use the LONG datatype to store fixed-length character strings.
The LONG datatype is like the VARCHAR2 datatype, except that the maximum length of a LONG value is 2147483647 bytes or two gigabytes.
You use the VARCHAR datatype to store variable-length character strings. VARCHAR variables have a 2-byte length field followed by a <=65533-byte string field. However, for VARCHAR array elements, the maximum length of the string field is 65530 bytes. When you specify the length of a VARCHAR variable, be sure to include 2 bytes for the length field. For longer strings, use the LONG VARCHAR datatype.
You can use the ROWID datatype to store binary rowids in (typically 13-byte) fixed-length fields. The field size is port specific. Check your system-specific Oracle documentation.
You can use character host variables to store rowids in a readable format. When you SELECT or FETCH a rowid into a character host variable, Oracle converts the binary value to an 18-byte character string and returns it in the format
BBBBBBBB.RRRR.FFFF
where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file. These numbers are hexadecimal. For example, the rowid
0000000E.000A.0007
points to the 11th row in the 15th block in the 7th database file.
Typically, you FETCH a rowid into a character host variable, then compare the host variable to the ROWID pseudocolumn in the WHERE clause of an UPDATE or DELETE statement. That way, you can identify the latest row fetched by a cursor. For an example, see "Mimicking CURRENT OF" on page 12-27.
Note: If you need full portability or your application communicates with a non-Oracle database using Oracle Open Gateway technology, specify a maximum length of 256 (not 18) bytes when declaring the host variable. Though you can assume nothing about the host variable's contents, the host variable will behave normally in SQL statements.
You use the DATE datatype to store dates and times in 7-byte, fixed-length fields. As Table 3-3 shows, the century, year, month, day, hour (in 24-hour format), minute, and second are stored in that order from left to right.
| Byte |
1 |
2 |
3 |
4 |
5 |
6 |
7 |
|---|---|---|---|---|---|---|---|
| Meaning |
Century |
Year |
Month |
Day |
Hour |
Minute |
Second |
| Example 17-OCT-1994 at 1:23:12 PM |
119 |
194 |
10 |
17 |
14 |
24 |
13 |
The century and year bytes are in excess-100 notation. The hour, minute, and second are in excess-1 notation. Dates before the Common Era (B.C.E.) are less than 100. The epoch is January 1, 4712 B.C.E. For this date, the century byte is 53 and the year byte is 88. The hour byte ranges from 1 to 24. The minute and second bytes range from 1 to 60. The time defaults to midnight (1, 1, 1).
Normally, there is little reason to use this datatype.
You use the VARRAW datatype to store variable-length binary data or byte strings. The VARRAW datatype is like the RAW datatype, except that VARRAW variables have a 2-byte length field followed by a <=65533-byte data field. For longer strings, use the LONG VARRAW datatype.
When you specify the length of a VARRAW variable, be sure to include 2 bytes for the length field. The first two bytes of the variable must be interpretable as an integer.
To get the length of a VARRAW variable, simply refer to its length field.
You use the RAW datatype to store binary data or byte strings. The maximum length of a RAW value is 255 bytes.
RAW data is like CHARACTER data, except that Oracle assumes nothing about the meaning of RAW data and does no character set conversions when you transmit RAW data from one system to another.
You use the LONG RAW datatype to store binary data or byte strings. The maximum length of a LONG RAW value is 2147483647 bytes or two gigabytes.
LONG RAW data is like LONG data, except that Oracle assumes nothing about the meaning of LONG RAW data and does no character set conversions when you transmit LONG RAW data from one system to another.
You use the UNSIGNED datatype to store unsigned integers. An unsigned integer is a binary number of 2 or 4 bytes. The order of the bytes in a word is system dependent. You must specify a length for input and output host variables. On output, if the column value is a floating-point number, Oracle truncates the fractional part.
You use the LONG VARCHAR datatype to store variable-length character strings. LONG VARCHAR variables have a 4-byte length field followed by a string field. The maximum length of the string field is 2147483643 (2**31 - 5) bytes. When you specify the length of a LONG VARCHAR for use in a VAR or TYPE statement, do not include the 4 length bytes.
You use the LONG VARRAW datatype to store variable-length binary data or byte strings. LONG VARRAW variables have a 4-byte length field followed by a data field. The maximum length of the data field is 2147483643 bytes. . When you specify the length of a LONG VARRAW for use in a VAR or TYPE statement, do not include the 4 length bytes.
You use the CHAR datatype to store fixed-length character strings. The maximum length of a CHAR value is 255 bytes.
Oracle reads the number of bytes specified for the input host variable, does not strip trailing blanks, then stores the input value in the target database column.
If the input value is longer than the defined width of the database column, Oracle generates an error. If the input value is all-blank, Oracle treats it like a character value.
Oracle returns the number of bytes specified for the output host variable, doing blank-padding if necessary, then assigns the output value to the target host variable. If a NULL is returned, Oracle fills the host variable with blanks.
If the output value is longer than the declared length of the host variable, Oracle truncates the value before assigning it to the host variable. If an indicator variable is available, Oracle sets it to the original length of the output value.
When DBMS=V7 or V8, Oracle, by default, assigns the CHARZ datatype to all character host variables in a Pro*C/C++ program. The CHARZ datatype indicates fixed-length, null-terminated character strings. The maximum length of a CHARZ value is 255 bytes.
On input, the CHARZ and STRING datatypes work the same way. You must null-terminate the input value. The null terminator serves only to delimit the string; it does not become part of the stored data.
On output, CHARZ host variables are blank-padded if necessary, then null-terminated. The output value is always null-terminated, even if data must be truncated.
The CHARF datatype is used in EXEC SQL TYPE and EXEC SQL VAR statements. When you precompile with the DBMS option set to V7 or V8, specifying the external datatype CHAR in a TYPE or VAR statement equivalences the C type or variable to the fixed-length, null-terminated datatype CHARZ. When you precompile with DBMS=V6, the C type or variable is equivalenced to VARCHAR2.
However, you might not want either of these type equivalences, but rather an equivalence to the fixed-length external type CHAR. If you use the external type CHARF, the C type or variable is always equivalenced to the fixed-length ANSI datatype CHAR, regardless of the DBMS value. CHARF never allows the C type to be equivalenced to VARCHAR2 or CHARZ. Alternatively, when you set the option CHAR_MAP=CHARF, all host variables declared as char[n] or char are equivalenced to a CHAR string.
You use the MLSLABEL datatype to store variable-length, binary operating system labels. Trusted Oracle uses labels to control access to data. For more information, see the Trusted Oracle7 Administrator's Guide.
You can use the MLSLABEL datatype to define a column. However, with standard Oracle, such columns can store NULLs only. With Trusted Oracle, you can insert any valid operating system label into a column of type MLSLABEL.
Trusted Oracle translates the input value into a binary label, which must be a valid operating system label. If it is not, Trusted Oracle issues an error message. If the label is valid, Trusted Oracle stores it in the target database column
Trusted Oracle converts the binary label to a character string, which can be of type CHAR, CHARZ, STRING, VARCHAR, or VARCHAR2.
Host variables are the key to communication between your host program and Oracle. Typically, a precompiler program inputs data from a host variable to Oracle, and Oracle outputs data to a host variable in the program. Oracle stores input data in database columns, and stores output data in program host variables.
A host variable can be any arbitrary C expression that resolves to a scalar type. But, a host variable must also be an lvalue. Host arrays of most host variables are also supported. See "Pointer Variables" on page 3-41 for more information.
You declare a host variable according to the rules of C, specifying a C datatype supported by the Oracle program interface. You do not have to declare host variables in a special Declare Section. However, if you do not use a Declare Section, the FIPS flagger warns you about this, as the Declare Section is part of the SQL Standard.
The C datatype must be compatible with that of the source or target database column. Table 3-4 shows the C datatypes and the pseudotypes that you can use when declaring host variables. Only these datatypes can be used for host variables. Table 3-5 shows the compatible Oracle internal datatypes.
For a description of the Oracle datatypes, see "Datatype Conversion" on page 3-58.
One-dimensional arrays of simple C types can also serve as host variables. For char[n] and VARCHAR[n], n specifies the maximum string length, not the number of strings in the array. Two-dimensional arrays are allowed only for char[m][n] and VARCHAR[m][n], where m specifies the number of strings in the array and n specifies the maximum string length.
Pointers to simple C types are supported. Pointers to char[n] and VARCHAR[n] variables should be declared as pointer to char or VARCHAR (with no length specification). Arrays of pointers, however, are not supported.
Pro*C/C++ lets you use the auto, extern, and static storage-class specifiers when you declare host variables. However, you cannot use the register storage-class specifier to store host variables, since the precompiler takes the address of host variables by placing an ampersand (&) before them. Following the rules of C, you can use the auto storage class specifier only within a block.
To comply with the ANSI C standard, the Pro*C/C++ Precompiler allows you to declare an extern char[n] host variable with or without a maximum length, as the following examples shows:
extern char protocol[15]; extern char msg[];
However, you should always specify the maximum length. In the last example, if msg is an output host variable declared in one precompilation unit but defined in another, the precompiler has no way of knowing its maximum length. If you have not allocated enough storage for msg in the second precompilation unit, you might corrupt memory. (Usually, "enough" is the number of bytes in the longest column value that might be SELECTed or FETCHed into the host variable, plus one byte for a possible null terminator.)
If you neglect to specify the maximum length for an extern char[ ] host variable, the precompiler issues a warning message. Also, it assumes that the host variable will store a CHARACTER column value, which cannot exceed 255 characters in length. So, if you want to SELECT or FETCH a VARCHAR2 or a LONG column value of length greater than 255 characters into the host variable, you must specify a maximum length.
You can also use the const and volatile type qualifiers when you declare host variables.
A const host variable must have a constant value, that is, your program cannot change its initial value. A volatile host variable can have its value changed in ways unknown to your program (by a device attached to the system, for instance).
You use host variables in SQL data manipulation statements. A host variable must be prefixed with a colon (:) in SQL statements but must not be prefixed with a colon in C statements, as the following example shows:
char buf[15]; int emp_number; float salary; ... gets(buf); emp_number = atoi(buf); EXEC SQL SELECT sal INTO :salary FROM emp WHERE empno = :emp_number;
Though it might be confusing, you can give a host variable the same name as an Oracle table or column, as this example shows:
int empno; char ename[10]; float sal; ... EXEC SQL SELECT ename, sal INTO :ename, :sal FROM emp WHERE empno = :empno;
A host variable name is a C identifier, hence it must be declared and referenced in the same upper/lower case format. It cannot substitute for a column, table, or other Oracle object in a SQL statement, and must not be an Oracle reserved word. See Appendix B, "Oracle Reserved Words, Keywords, and Namespaces".
A host variable must resolve to an address in the program. For this reason, function calls and numeric expressions cannot serve as host variables. The following code is invalid:
#define MAX_EMP_NUM 9000 ... int get_dept(); ... EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES (:MAX_EMP_NUM + 10, 'CHEN', :get_dept());
You can associate every host variable with an optional indicator variable. An indicator variable must be defined as a 2-byte integer and, in SQL statements, must be prefixed with a colon and immediately follow its host variable (unless you use the keyword INDICATOR). If you are using Declare Sections, you must also declare indicator variables inside the Declare Sections.
Note: This applies to relational columns, not object types, which are discussed in Chapter 8, "Object Support in Pro*C/C++".
To improve readability, you can precede any indicator variable with the optional keyword INDICATOR. You must still prefix the indicator variable with a colon. The correct syntax is :
:host_variable INDICATOR :indicator_variable
which is equivalent to
:host_variable:indicator_variable
You can use both forms of expression in your host program.
Possible indicator values, and their meanings, are:
Typically, you use indicator variables to assign nulls to input host variables and detect nulls or truncated values in output host variables. In the example below, you declare three host variables and one indicator variable, then use a SELECT statement to search the database for an employee number matching the value of host variable emp_number. When a matching row is found, Oracle sets output host variables salary and commission to the values of columns SAL and COMM in that row and stores a return code in indicator variable ind_comm. The next statements use ind_comm to select a course of action.
EXEC SQL BEGIN DECLARE SECTION; int emp_number; float salary, commission; short comm_ind; /* indicator variable */ EXEC SQL END DECLARE SECTION; char temp[16]; float pay; /* not used in a SQL statement */ ... printf("Employee number? "); gets(temp); emp_number = atof(temp); EXEC SQL SELECT SAL, COMM INTO :salary, :commission:ind_comm FROM EMP WHERE EMPNO = :emp_number; if(ind_comm == -1) /* commission is null */ pay = salary; else pay = salary + commission;
For more information about using indicator variables, see "Using Indicator Variables" on page 5-3.
The following guidelines apply to declaring and referencing indicator variables. An indicator variable must
An indicator variable must not
When DBMS=V6, Oracle does not issue an error if you SELECT or FETCH a null into a host variable that is not associated with an indicator variable. However, when DBMS=V7 or V8, if you SELECT or FETCH a null into a host variable that has no indicator, Oracle issues the following error message:
ORA-01405: fetched column value is NULL
When precompiling with MODE=ORACLE and DBMS=V7 or V8 specified, you can specify UNSAFE_NULL=YES to disable the ORA-01405 message. For more information, see "UNSAFE_NULL" on page 9-37.
You can use a C structure to contain host variables. You reference a structure containing host variables in the INTO clause of a SELECT or a FETCH statement, and in the VALUES list of an INSERT statement. Every component of the host structure must be a legal Pro*C/C++ host variable, as defined in Table 3-4.
When a structure is used as a host variable, only the name of the structure is used in the SQL statement. However, each of the members of the structure sends data to Oracle, or receives data from Oracle on a query. The following example shows a host structure that is used to add an employee to the EMP table:
typedef struct { char emp_name[11]; /* one greater than column length */ int emp_number; int dept_number; float salary; } emp_record; ... /* define a new structure of type "emp_record" */ emp_record new_employee; strcpy(new_employee.emp_name, "CHEN"); new_employee.emp_number = 9876; new_employee.dept_number = 20; new_employee.salary = 4250.00; EXEC SQL INSERT INTO emp (ename, empno, deptno, sal) VALUES (:new_employee);
The order that the members are declared in the structure must match the order that the associated columns occur in the SQL statement, or in the database table if the column list in the INSERT statement is omitted.
For example, the following use of a host structure is invalid, and causes a runtime error:
struct { int empno; float salary; /* struct components in wrong order */ char emp_name[10]; } emp_record; ... SELECT empno, ename, sal INTO :emp_record FROM emp;
The example is wrong because the components of the structure are not declared in the same order as the associated columns in the select list. The correct form of the SELECT statement is
SELECT empno, sal, ename /* reverse order of sal and ename */ INTO :emp_record FROM emp;
An array is a collection of related data items, called elements, associated with a single variable name. When declared as a host variable, the array is called a host array. Likewise, an indicator variable declared as an array is called an indicator array. An indicator array can be associated with any host array.
Host arrays can increase performance by letting you manipulate an entire collection of data items with a single SQL statement. With few exceptions, you can use host arrays wherever scalar host variables are allowed. Also, you can associate an indicator array with any host array.
For a complete discussion of host arrays, see Chapter 12, "Using Host Arrays".
You can use host arrays as components of host structures. In the following example, a structure containing arrays is used to INSERT three new entries into the EMP table:
struct { char emp_name[3][10]; int emp_number[3]; int dept_number[3]; } emp_rec; ... strcpy(emp_rec.emp_name[0], "ANQUETIL"); strcpy(emp_rec.emp_name[1], "MERCKX"); strcpy(emp_rec.emp_name[2], "HINAULT"); emp_rec.emp_number[0] = 1964; emp_rec.dept_number[0] = 5; emp_rec.emp_number[1] = 1974; emp_rec.dept_number[1] = 5; emp_rec.emp_number[2] = 1985; emp_rec.dept_number[2] = 5; EXEC SQL INSERT INTO emp (ename, empno, deptno) VALUES (:emp_rec);
You cannot use a C struct as a host variable for a PL/SQL RECORD variable.
You cannot nest host structures. The following example is invalid:
struct { int emp_number; struct { float salary; float commission; } sal_info; /* INVALID */ int dept_number; } emp_record; ... EXEC SQL SELECT empno, sal, comm, deptno INTO :emp_record FROM emp;
Also, you cannot use a C union as a host structure, nor can you nest a union in a structure that is to be used as a host structure.
When you need to use indicator variables, but your host variables are contained in a host structure, you set up a second structure that contains an indicator variable for each host variable in the host structure.
For example, suppose you declare a host structure student_record as follows:
struct { char s_name[32]; int s_id; char grad_date[9]; } student_record;
If you want to use the host structure in a query such as
EXEC SQL SELECT student_name, student_idno, graduation_date INTO :student_record FROM college_enrollment WHERE student_idno = 7200;
and you need to know if the graduation date can be NULL, then you must declare a separate host indicator structure. You declare this as
struct { short s_name_ind; /* indicator variables must be shorts */ short s_id_ind; short grad_date_ind; } student_record_ind;
Reference the indicator structure in the SQL statement in the same way that you reference a host indicator variable:
EXEC SQL SELECT student_name, student_idno, graduation_date INTO :student_record INDICATOR :student_record_ind FROM college_enrollment WHERE student_idno = 7200;
When the query completes, the NULL/NOT NULL status of each selected component is available in the host indicator structure.
Note: This Guide conventionally names indicator variables and indicator structures by appending _ind to the host variable or structure name. However, the names of indicator variables are completely arbitrary. You can adopt a different convention, or use no convention at all.
The demonstration program in this section shows a query that uses an explicit cursor, selecting data into a host structure. This program is available inthe file sample2.pc in your demo directory.
/* * sample2.pc * * This program connects to ORACLE, declares and opens a cursor, * fetches the names, salaries, and commissions of all * salespeople, displays the results, then closes the cursor. */ #include <stdio.h> #include <sqlca.h> #define UNAME_LEN 20 #define PWD_LEN 40 /* * Use the precompiler typedef'ing capability to create * null-terminated strings for the authentication host * variables. (This isn't really necessary--plain char *'s * does work as well. This is just for illustration.) */ typedef char asciiz[PWD_LEN]; EXEC SQL TYPE asciiz IS STRING(PWD_LEN) REFERENCE; asciiz username; asciiz password; struct emp_info { asciiz emp_name; float salary; float commission; }; /* Declare function to handle unrecoverable errors. */ void sql_error(); main() { struct emp_info *emp_rec_ptr; /* Allocate memory for emp_info struct. */ if ((emp_rec_ptr = (struct emp_info *) malloc(sizeof(struct emp_info))) == 0) { fprintf(stderr, "Memory allocation error.\n"); exit(1); } /* Connect to ORACLE. */ strcpy(username, "SCOTT"); strcpy(password, "TIGER"); EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--"); EXEC SQL CONNECT :username IDENTIFIED BY :password; printf("\nConnected to ORACLE as user: %s\n", username); /* Declare the cursor. All static SQL explicit cursors * contain SELECT commands. 'salespeople' is a SQL identifier, * not a (C) host variable. */ EXEC SQL DECLARE salespeople CURSOR FOR SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'SALES%'; /* Open the cursor. */ EXEC SQL OPEN salespeople; /* Get ready to print results. */ printf("\n\nThe company's salespeople are--\n\n"); printf("Salesperson Salary Commission\n"); printf("----------- ------ ----------\n"); /* Loop, fetching all salesperson's statistics. * Cause the program to break the loop when no more * data can be retrieved on the cursor. */ EXEC SQL WHENEVER NOT FOUND DO break; for (;;) { EXEC SQL FETCH salespeople INTO :emp_rec_ptr; printf("%-11s%9.2f%13.2f\n", emp_rec_ptr->emp_name, emp_rec_ptr->salary, emp_rec_ptr->commission); } /* Close the cursor. */ EXEC SQL CLOSE salespeople; printf("\nArrivederci.\n\n"); EXEC SQL COMMIT WORK RELEASE; exit(0); } void sql_error(msg) char *msg; { char err_msg[512]; int buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\n%s\n", msg); /* Call sqlglm() to get the complete text of the * error message. */ buf_len = sizeof (err_msg); sqlglm(err_msg, &buf_len, &msg_len); printf("%.*s\n", msg_len, err_msg); EXEC SQL ROLLBACK RELEASE; exit(1); }
C supports pointers, which "point" to other variables. A pointer holds the address (storage location) of a variable, not its value.
You define pointers as host variables following the normal C practice, as the next example shows:
int *int_ptr; char *char_ptr;
In SQL statements, prefix pointers with a colon, as shown in the following example:
EXEC SQL SELECT intcol INTO :int_ptr FROM ...
Except for pointers to character strings, the size of the referenced value is given by the size of the base type specified in the declaration. For pointers to character strings, the referenced value is assumed to be a null-terminated (also known as 0-terminated) string. Its size is determined at run time by calling the strlen() function. For details, see the section "Handling Character Data" on page 3-50.
You can use pointers to reference the members of a struct. First, declare a pointer host variable, then set the pointer to the address of the desired member, as shown in the example below. The datatypes of the struct member and the pointer variable must be the same. Most compilers will warn you of a mismatch.
struct { int i; char c; } structvar; int *i_ptr; char *c_ptr; ... main() { i_ptr = &structvar.i; c_ptr = &structvar.c; /* Use i_ptr and c_ptr in SQL statements. */ ...
You can use a pointer to a structure as a host variable. The following example
struct EMP_REC { int emp_number; float salary; }; char *name = "HINAULT"; ... struct EMP_REC *sal_rec; sal_rec = (struct EMP_REC *) malloc(sizeof (struct EMP_REC)); ... EXEC SQL SELECT empno, sal INTO :sal_rec FROM emp WHERE ename = :name; printf("Employee number and salary for %s: ", name); printf("%d, %g\n", sal_rec->emp_number, sal_rec->salary);
In the SQL statement, pointers to host structures are referred to in exactly the same way as a host structure. The ``address of" notation (&) is not required; in fact, it is an error to use it.
You can use the VARCHAR pseudotype to declare variable-length character strings. When your program deals with strings that are output from, or input to, VARCHAR2 or LONG columns, you might find it more convenient to use VARCHAR host variables instead of standard C strings. The datatype name VARCHAR can be uppercase or lowercase, but it cannot be mixed case. In this Guide, uppercase is used to emphasize that VARCHAR is not a native C datatype.
Think of a VARCHAR as an extended C type or pre-declared struct. For example, the precompiler expands the VARCHAR declaration
VARCHAR username[20];
into the following struct with array and length members:
struct { unsigned short len; unsigned char arr[20]; } username;
The advantage of using VARCHAR variables is that you can explicitly reference the length member of the VARCHAR structure after a SELECT or FETCH. Oracle puts the length of the selected character string in the length member. You can then use this member to do things such as adding the null (i.e. '\0') terminator
username.arr[username.len] = '\0';
or using the length in a strncpy or printf statement; for example:
printf("Username is %.*s\n", username.len, username.arr);
You specify the maximum length of a VARCHAR variable in its declaration. The length must lie in the range 1..65,533. For example, the following declaration is invalid because no length is specified:
VARCHAR null_string[]; /* invalid */
The length member holds the current length of the value stored in the array member.
You can declare multiple VARCHARs on a single line; for example:
VARCHAR emp_name[ENAME_LEN], dept_loc[DEPT_NAME_LEN];
The length specifier for a VARCHAR can be a #defined macro, or any complex expression that can be resolved to an integer at precompile time.
You can also declare pointers to VARCHAR datatypes. See the section "Handling Character Data" on page 3-50.
Do not attempt to use a typedef statement such as :
typedef VARCHAR buf[64];
This causes errors during the C compilation.
In SQL statements, you reference VARCHAR variables using the struct name prefixed with a colon, as the following example shows:
... int part_number; VARCHAR part_desc[40]; ... main() { ... EXEC SQL SELECT pdesc INTO :part_desc FROM parts WHERE pnum = :part_number; ...
After the query is executed, part_desc.len holds the actual length of the character string retrieved from the database and stored in part_desc.arr.
In C statements, you reference VARCHAR variables using the component names, as the next example shows:
printf("\n\nEnter part description: "); gets(part_desc.arr); /* You must set the length of the string before using the VARCHAR in an INSERT or UPDATE */ part_desc.len = strlen(part_desc.arr);
Oracle automatically sets the length component of a VARCHAR output host variable. If you SELECT or FETCH a NULL into a VARCHAR, the server does not change the length or array members.
Note: If you select a NULL into a VARCHAR host variable, and there is no associated indicator variable, an ORA-01405 error occurs at run time. Avoid this by coding indicator variables with all host variables. (As a temporary fix, use the DBMS=V6 or UNSAFE_NULL=YES precompiler option. See "DBMS" on page 9-14).
If you set the length of a VARCHAR variable to zero before performing an UPDATE or INSERT statement, the column value is set to NULL. If the column has a NOT NULL constraint, Oracle returns an error.
VARCHARs are structures, and most C compilers permit passing of structures to a function by value, and returning structures by copy out from functions. However, in Pro*C/C++ you must pass VARCHARs to functions by reference. The following example shows the correct way to pass a VARCHAR variable to a function:
VARCHAR emp_name[20]; ... emp_name.len = 20; SELECT ename INTO :emp_name FROM emp WHERE empno = 7499; ... print_employee_name(&emp_name); /* pass by pointer */ ... print_employee_name(name) VARCHAR *name; { ... printf("name is %.*s\n", name->len, name->arr); ... }
When the precompiler processes a VARCHAR declaration, the actual length of the array element in the generated structure can be longer than that declared. For example, on a Sun Solaris system, the Pro*C/C++ declaration
VARCHAR my_varchar[12];
is expanded by the precompiler to
struct my_varchar { unsigned short len; unsigned char arr[12]; };
However, the precompiler or the C compiler on this system pads the length of the array component to 14 bytes. This alignment requirement pads the total length of the structure to 16 bytes: 14 for the padded array and 2 bytes for the length.
The sqlvcp() function-part of the SQLLIB runtime library-returns the actual (possibly padded) length of the array member.
You pass the sqlvcp() function the length of the data for a VARCHAR host variable or a VARCHAR pointer host variable, and sqlvcp() returns the total length of the array component of the VARCHAR. The total length includes any padding that might be added by your C compiler.
The syntax of sqlvcp() is
sqlvcp(size_t *datlen, size_t *totlen);
Put the length of the VARCHAR in the first parameter before calling sqlvcp(). When the function returns, the second parameter contains the total length of the array element. Both parameters are pointers to long integers, so must be passed by reference.
The following sample program shows how you can use the sqlvcp() function in a Pro*C/C++ application. (The sample also uses the sqlgls() function, which is described in Chapter 11, "Handling Runtime Errors".) The sample declares a VARCHAR pointer, then uses the sqlvcp() function to determine the size required for the VARCHAR buffer. The program FETCHes employee names from the EMP table and prints them. Finally, the sample uses the sqlgls() function to print out the SQL statement and its function code and length attributes. This program is available on-line as sqlvcp.pc in your demo directory.
/* * The sqlvcp.pc program demonstrates how you can use the * sqlvcp() function to determine the actual size of a * VARCHAR struct. The size is then used as an offset to * increment a pointer that steps through an array of * VARCHARs. * * This program also demonstrates the use of the sqlgls() * function, to get the text of the last SQL statement executed. * sqlgls() is described in the "Error Handling" chapter of * _The Programmer's Guide to the Oracle Pro*C/C++ Precompiler. */ #include <stdio.h> #include <sqlca.h> #include <sqlcpr.h> /* Fake a VARCHAR pointer type. */ struct my_vc_ptr { unsigned short len; unsigned char arr[32767]; }; /* Define a type for the VARCHAR pointer */ typedef struct my_vc_ptr my_vc_ptr; my_vc_ptr *vc_ptr; EXEC SQL BEGIN DECLARE SECTION; VARCHAR *names; int limit; /* for use in FETCH FOR clause */ char *username = "scott/tiger"; EXEC SQL END DECLARE SECTION; void sql_error(); extern void sqlvcp(), sqlgls(); main() { unsigned int vcplen, function_code, padlen, buflen; int i; char stmt_buf[120]; EXEC SQL WHENEVER SQLERROR DO sql_error(); EXEC SQL CONNECT :username; printf("\nConnected.\n"); /* Find number of rows in table. */ EXEC SQL SELECT COUNT(*) INTO :limit FROM emp; /* Declare a cursor for the FETCH statement. */ EXEC SQL DECLARE emp_name_cursor CURSOR FOR SELECT ename FROM emp; EXEC SQL FOR :limit OPEN emp_name_cursor; /* Set the desired DATA length for the VARCHAR. */ vcplen = 10; /* Use SQLVCP to help find the length to malloc. */ sqlvcp(&vcplen, &padlen); printf("Actual array length of VARCHAR is %ld\n", padlen); /* Allocate the names buffer for names. Set the limit variable for the FOR clause. */ names = (VARCHAR *) malloc((sizeof (short) + (int) padlen) * limit); if (names == 0) { printf("Memory allocation error.\n"); exit(1); } /* Set the maximum lengths before the FETCH. * Note the "trick" to get an effective VARCHAR *. */ for (vc_ptr = (my_vc_ptr *) names, i = 0; i < limit; i++) { vc_ptr->len = (short) padlen; vc_ptr = (my_vc_ptr *)((char *) vc_ptr + padlen + sizeof (short)); } /* Execute the FETCH. */ EXEC SQL FOR :limit FETCH emp_name_cursor INTO :names; /* Print the results. */ printf("Employee names--\n"); for (vc_ptr = (my_vc_ptr *) names, i = 0; i < limit; i++) { printf ("%.*s\t(%d)\n", vc_ptr->len, vc_ptr->arr, vc_ptr->len); vc_ptr = (my_vc_ptr *)((char *) vc_ptr + padlen + sizeof (short)); } /* Get statistics about the most recent * SQL statement using SQLGLS. Note that * the most recent statement in this example * is not a FETCH, but rather "SELECT ENAME FROM EMP" * (the cursor). */ buflen = (long) sizeof (stmt_buf); /* The returned value should be 1, indicating no error. */ sqlgls(stmt_buf, &buflen, &function_code); if (buflen != 0) { /* Print out the SQL statement. */ printf("The SQL statement was--\n%.*s\n", buflen, stmt_buf); /* Print the returned length. */ printf("The statement length is %ld\n", buflen); /* Print the attributes. */ printf("The function code is %ld\n", function_code); EXEC SQL COMMIT RELEASE; exit(0); } else { printf("The SQLGLS function returned an error.\n"); EXEC SQL ROLLBACK RELEASE; exit(1); } } void sql_error() { char err_msg[512]; int buf_len, msg_len; EXEC SQL WHENEVER SQLERROR CONTINUE; buf_len = sizeof (err_msg); sqlglm(err_msg, &buf_len, &msg_len); printf("%.*s\n", msg_len, err_msg); EXEC SQL ROLLBACK RELEASE; exit(1); }
This section explains how the Pro*C/C++ Precompiler handles character host variables. There are four host variable character types:
Do not confuse VARCHAR (a host variable data structure supplied by the precompiler) with VARCHAR2 (an Oracle internal datatype for variable-length character strings).
The CHAR_MAP precompiler command line option is available to specify the default mapping of char[n] and char host variables. In Oracle V6, these host variables were mapped to VARCHAR2 by default. Oracle7 and Oracle8 map them to CHARZ. CHARZ implements the ANSI Variable Character format. Strings are fixed-length, blank-padded and 0-terminated. VARCHAR2 values (including NULLs) are always fixed-length and blank-padded. Table 3-6 shows the possible settings of CHAR_MAP:
When you specify DBMS=V6, any option you use for CHAR_MAP, other than VARCHAR2, results in an error. The default mapping is CHAR_MAP=VARCHAR2, which was the case in previous versions of Pro*C/C++.
The option DBMS=V6_CHAR, which Oracle introduced in Pro*C/C++ 2.2 to obtain default mapping of character host variables to VARCHAR2, is being deprecated (will become obsolete). Use CHAR_MAP=VARCHAR2 instead.
Unless you declared a char or char[n] variable otherwise, the inline CHAR_MAP option determines its mapping. The following code fragment illustrates the results of setting this option inline in Pro*C:
char ch_array[5]; strncpy(ch_array, "12345", 5); /* char_map=charz is the default in Oracle7 and Oracle8 */ EXEC ORACLE OPTION (char_map=charz); /* Select retrieves a string "AB" from the database */ SQL SELECT ... INTO :ch_array FROM ... WHERE ... ; /* ch_array == { 'A', 'B', ' ', ' ', '\0' } */ strncpy (ch_array, "12345", 5); EXEC ORACLE OPTION (char_map=string) ; /* Select retrieves a string "AB" from the database */ EXEC SQL SELECT ... INTO :ch_array FROM ... WHERE ... ; /* ch_array == { 'A', 'B', '\0', '4', '5' } */ strcpy( ch_array, "12345", 5); EXEC ORACLE OPTION (char_map=charf); /* Select retrieves a string "AB" from the database */ EXEC SQL SELECT ... INTO :ch_array FROM ... WHERE ... ; /* ch_array == { 'A', 'B', ' ', ' ', ' ' } */
The DBMS and CHAR_MAP options determine how Pro*C/C++ treats data in character arrays and strings. These options allows your program to observe compatibility with ANSI fixed-length strings, or to maintain compatibility with previous releases of Oracle and Pro*C/C++ that use variable-length strings. See Chapter 9, "Running the Pro*C/C++ Precompiler" for a complete description of the DBMS and CHAR_MAP options.
The DBMS option affects character data both on input (from your host variables to the Oracle table) and on output (from an Oracle table to your host variables).
Character Array and the CHAR_MAP Option
The mapping of character arrays can also be set by the CHAR_MAP option independent of the DBMS option. By default, DBMS=V6 uses CHAR_MAP=VARCHAR2 (the only option permitted for V6), while DBMS=V7 or DBMS=V8 both use CHAR_MAP=CHARZ, which can be overridden by specifying either CHAR_MAP=VARCHAR2 or STRING or CHARF.
On input, the DBMS option determines the format that a host variable character array must have in your program. When the CHAR_MAP=VARCHAR2, (or DBMS=V6), host variable character arrays must be blank padded, and should not be 0-terminated. When the DBMS=V7 or V8, character arrays must be null-terminated ('0\').
When the CHAR_MAP option is set to VARCHAR2, or DBMS is set to V6, trailing blanks are stripped up to the first non-blank character before the value is sent to the database. Note that an uninitialized character array can contain null characters. To make sure that the nulls are not inserted into the table, you must blank-pad the character array to its length. For example, if you execute the statements :
char emp_name[10]; ... strcpy(emp_name, "MILLER"); /* WRONG! Note no blank-padding */ EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES (1234, :emp_name, 20);
you will find that the string "MILLER" was inserted as "MILLER\0\0\0\0" (with four null bytes appended to it). This value does not meet the following search condition:
. . . WHERE ename = 'MILLER';
To INSERT the character array when DBMS is set to V6 or CHAR_MAP is set to VARCHAR2, you should execute the statements
strncpy(emp_name, "MILLER ", 10); /* 4 trailing blanks */ EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES (1234, :emp_name, 20);
When DBMS=V7 or V8, input data in a character array must be null-terminated. So, make sure that your data ends with a null.
char emp_name[11]; /* Note: one greater than column size of 10 */ ... strcpy(emp_name, "MILLER"); /* No blank-padding required */ EXEC SQL INSERT INTO emp (empno, ename, deptno) VALUES (1234, :emp_name, 20);
The pointer must address a null-terminated buffer that is large enough to hold the input data. Your program must allocate enough memory to do this.
The following example illustrates all possible combinations of the effects of the CHAR_MAP option settings on the value retrieved from a database into a character array.
Assume a database
TABLE strdbase ( ..., strval VARCHAR2(6));
which contains the following strings in the column strval:
"" -- string of length 0 "AB" -- string of length 2 "KING" -- string of length 4 "QUEEN" -- string of length 5 "MILLER" -- string of length 6
In a Pro*C/C++ program, initialize the 5-character host array str with 'X' characters and use for the retrieval of all the values in column strval:
char str[5] = {'X', 'X', 'X','X', 'X'} ; short str_ind; ... EXEC SQL SELECT strval INTO :str:str_ind WHERE ... ;
with the following results for the array, str, and the indicator variable, str_ind, as CHAR_MAP is set to VARCHAR2, CHARF, CHARZ and STRING:
strval = "" "AB" "KING" "QUEEN" "MILLER" --------------------------------------------------------------- VARCHAR2 " " -1 "AB " 0 "KING " 0 "QUEEN" 0 "MILLE" 6 CHARF "XXXXX" -1 "AB " 0 "KING " 0 "QUEEN" 0 "MILLE" 6 CHARZ " 0" -1 "AB 0" 0 "KING0" 0 "QUEE0" 5 "MILL0" 6 STRING "0XXXX" -1 "AB0XX" 0 "KING0" 0 "QUEE0" 5 "MILL0" 6
where 0 stands for the null character, '\0'.
On output, the DBMS and CHAR_MAP options determines the format that a host variable character array will have in your program. When DBMS=V6 or CHAR_MAP=VARCHAR2, host variable character arrays are blank padded up to the length of the array, but never null-terminated. When DBMS=V7 or V8 (or CHAR_MAP=CHARZ), character arrays are blank padded, then null-terminated in the final position in the array.
Consider the following example of character output:
CREATE TABLE test_char (C_col CHAR(10), V_col VARCHAR2(10)); INSERT INTO test_char VALUES ('MILLER', 'KING');
A precompiler program to select from this table contains the following embedded SQL:
... char name1[10]; char name2[10]; ... EXEC SQL SELECT C_col, V_col INTO :name1, :name2 FROM test_char;
If you precompile the program with DBMS=V6 (or CHAR_MAP=VARCHAR2), name1 will contain:
"MILLER####"
that is, the name "MILLER" followed by 4 blanks, with no null-termination. (Note that if name1 had been declared with a size of 15, there are 9 blanks following the name.)
name2 will contain:
"KING######" /* 6 trailing blanks */
If you precompile the program with DBMS=V7 or V8, name1 will contain:
"MILLER###\0" /* 3 trailing blanks, then a null-terminator */
that is, a string containing the name, blank-padded to the length of the column, followed by a null terminator. name2 will contain:
"KING#####\0"
In summary, if DBMS=V6 or DBMS=V6 (or CHAR_MAP=VARCHAR2), the output from either a CHARACTER column or a VARCHAR2 column is blank-padded to the length of the host variable array. If DBMS=V7 or V8, the output string is always null-terminated.
The DBMS and CHAR_MAP options do not affect the way character data are output to a pointer host variable.
When you output data to a character pointer host variable, the pointer must point to a buffer large enough to hold the output from the table, plus one extra byte to hold a null terminator.
The precompiler runtime environment calls strlen() to determine the size of the output buffer, so make sure that the buffer does not contain any embedded nulls ('\0'). Fill allocated buffers with some value other than '\0', then null-terminate the buffer, before fetching the data.
Note: C pointers can be used in a Pro*C/C++ program that is precompiled with DBMS=V7 or V8 and MODE=ANSI. However, pointers are not legal host variable types in a SQL standard compliant program. The FIPS flagger warns you if you use pointers as host variables.
The following code fragment uses the columns and table defined in the previous section, and shows how to declare and SELECT into character pointer host variables:
... char *p_name1; char *p_name2; ... p_name1 = (char *) malloc(11); p_name2 = (char *) malloc(11); strcpy(p_name1, " "); strcpy(p_name2, "0123456789"); EXEC SQL SELECT C_col, V_col INTO :p_name1, :p_name2 FROM test_char;
When the SELECT statement above is executed with any DBMS or CHAR_MAP setting, the value fetched is:
"MILLER####\0" /* 4 trailing blanks and a null terminator */ "KING######\0" /* 6 blanks and null */
The following example shows how VARCHAR host variables are declared:
VARCHAR emp_name1[10]; /* VARCHAR variable */ VARCHAR *emp_name2; /* pointer to VARCHAR */
When you use a VARCHAR variable as an input host variable, your program need only place the desired string in the array member of the expanded VARCHAR declaration (emp_name1.arr in our example) and set the length member (emp_name1.len). There is no need to blank-pad the array. Exactly emp_name1.len characters are sent to Oracle, counting any blanks and nulls. In the following example, you set emp_name1.len to 8:
strcpy(emp_name1.arr, "VAN HORN"); emp_name1.len = strlen(emp_name1.arr);
When you use a pointer to a VARCHAR as an input host variable, you must allocate enough memory for the expanded VARCHAR declaration. Then, you must place the desired string in the array member and set the length member, as shown in the following example:
emp_name2 = malloc(sizeof(short) + 10) /* len + arr */ strcpy(emp_name2->arr, "MILLER"); emp_name2->len = strlen(emp_name2->arr);
Or, to make emp_name2 point to an existing VARCHAR (emp_name1 in this case), you could code the assignment
emp_name2 = &emp_name1;
then use the VARCHAR pointer in the usual way, as in
EXEC SQL INSERT INTO EMP (EMPNO, ENAME, DEPTNO) VALUES (:emp_number, :emp_name2, :dept_number);
When you use a VARCHAR variable as an output host variable, the program interface sets the length member but does not null-terminate the array member. As with character arrays, your program can null-terminate the arr member of a VARCHAR variable before passing it to a function such as printf() or strlen(). An example follows:
emp_name1.arr[emp_name1.len] = '\0'; printf("%s", emp_name1.arr);
Or, you can use the length member to limit the printing of the string,
as in:
printf("%.*s", emp_name1.len, emp_name1.arr);
An advantage of VARCHAR variables over character arrays is that the length of the value returned by Oracle is available right away. With character arrays, you might need to strip the trailing blanks yourself to get the actual length of the character string.
When you use a pointer to a VARCHAR as an output host variable, the program interface determines the variable's maximum length by checking the length member (emp_name2->len in our example). So, your program must set this member before every fetch. The fetch then sets the length member to the actual number of characters returned, as the following example shows:
emp_name2->len = 10; /* Set maximum length of buffer. */ EXEC SQL SELECT ENAME INTO :emp_name2 WHERE EMPNO = 7934; printf("%d characters returned to emp_name2", emp_name2->len);
At precompile time, a default external datatype is assigned to each host variable. For example, the precompiler assigns the INTEGER external datatype to host variables of type short and int.
At run time, the datatype code of every host variable used in a SQL statement is passed to Oracle. Oracle uses the codes to convert between internal and external datatypes.
Before assigning a SELECTed column (or pseudocolumn) value to an output host variable, Oracle must convert the internal datatype of the source column to the datatype of the host variable. Likewise, before assigning or comparing the value of an input host variable to a column, Oracle must convert the external datatype of the host variable to the internal datatype of the target column.
Conversions between internal and external datatypes follow the usual data conversion rules. For example, you can convert a CHAR value of "1234" to a C short value. You cannot convert a CHAR value of "65543" (number too large) or "10F" (number not decimal) to a C short value. Likewise, you cannot convert a char[n] value that contains any alphabetic characters to a NUMBER value.
Datatype equivalencing lets you control the way Oracle interprets input data, and the way Oracle formats output data. It allows you to override the default external datatypes that the precompiler assigns. On a variable-by-variable basis, you can equivalence supported C host variable datatypes to Oracle external datatypes. You can also equivalence user-defined datatypes to Oracle external datatypes.
By default, the Pro*C/C++ Precompiler assigns a specific external datatype to every host variable. Table 3-7 shows the default assignments:
With the VAR statement, you can override the default assignments by equivalencing host variables to Oracle external datatypes. The syntax you use is
EXEC SQL VAR host_variable IS type_name [ (length) ];
where host_variable is an input or output host variable (or host array) declared earlier, type_name is the name of a valid external datatype, and length is an integer literal specifying a valid length in bytes.
Host variable equivalencing is useful in several ways. For example, suppose you want to SELECT employee names from the EMP table, then pass them to a routine that expects null-terminated strings. You need not explicitly null-terminate the names. Simply equivalence a host variable to the STRING external datatype, as follows:
... char emp_name[11]; EXEC SQL VAR emp_name IS STRING(11);
The length of the ENAME column in the EMP table is 10 characters, so you allot the new emp_name 11 characters to accommodate the null terminator. When you SELECT a value from the ENAME column into emp_name, the program interface null-terminates the value for you.
You can use any of the datatypes listed in the external datatypes table on "Oracle External Datatypes" on page 3-19 except NUMBER (use VARNUM instead):
You can also equivalence user-defined datatypes to Oracle external datatypes. First, define a new datatype structured like the external datatype that suits your needs. Then, equivalence your new datatype to the external datatype using the TYPE statement.
With the TYPE statement, you can assign an Oracle external datatype to a whole class of host variables. The syntax you use is:
EXEC SQL TYPE user_type IS type_name [ (length) ] [REFERENCE];
Suppose you need a variable-length string datatype to hold graphics characters. First, declare a struct with a short length component followed by a 65533-byte data component. Second, use typedef to define a new datatype based on the struct. Then, equivalence your new user-defined datatype to the VARRAW external datatype, as shown in the following example:
struct screen { short len; char buff[4000]; }; typedef struct screen graphics; EXEC SQL TYPE graphics IS VARRAW(4000); graphics crt; - host variable of type graphics ...
You specify a length of 4000 bytes for the new graphics type because that is the maximum length of the data component in your struct. The precompiler allows for the len component (and any padding) when it sends the length to the Oracle server.
You can declare a user-defined type to be a pointer, either explicitly, as a pointer to a scalar or struct type, or implicitly, as an array, and use this type in an EXEC SQL TYPE statement. In this case, you must use the REFERENCE clause at the end of the statement, as shown in the following example:
typedef unsigned char *my_raw; EXEC SQL TYPE my_raw IS VARRAW(4000) REFERENCE; my_raw graphics_buffer; ... graphics_buffer = (my_raw) malloc(4004);
In this example, you allocated additional memory over and above the type length (4000). This is necessary because the precompiler also returns the length (the size of a short), and can add padding after the length due to word alignment restrictions on your system. If you do not know the alignment practices on your system, make sure to allocate sufficient extra bytes for the length and padding (9 should usually be sufficient).
Release 1.6 of the Pro*C/C++ Precompiler introduced a new external datatype named CHARF, which is a fixed-length character string. You can use this new datatype in VAR and TYPE statements to equivalence C datatypes to the fixed-length SQL standard datatype CHAR, regardless of the setting of the DBMS or CHAR_MAP option.
When DBMS=V7 or V8, specifying the external datatype CHARACTER in a VAR or TYPE statement equivalences the C datatype to the fixed-length datatype CHAR (datatype code 96). However, when DBMS=V6 or CHAR_MAP=VARCHAR2, the C datatype is equivalenced to the variable-length datatype VARCHAR2 (code 1).
Now, you can always equivalence C datatypes to the fixed-length SQL standard type CHARACTER by using the CHARF datatype in the VAR or TYPE statement. When you use CHARF, the equivalence is always made to the fixed-length character type, regardless of the setting of the DBMS or CHAR_MAP option.
You can code an EXEC SQL VAR ... or EXEC SQL TYPE ... statement anywhere in your program. These statements are treated as executable statements that change the datatype of any variable affected by them from the point that the TYPE or VAR statement was made to the end of the scope of the variable. If you precompile with MODE=ANSI, you must use Declare Sections. In this case, the TYPE or VAR statement must be in a Declare Section.
The demonstration program in this section shows you how you can use datatype equivalencing in your Pro*C/C++ programs. This program demonstrates the use of type equivalencing using the LONG RAW external datatype. In order to provide a useful example that is portable across different systems, the program inserts binary files into and retrieves them from the database. For example, suppose you have a file called 'hello' in the current directory. You can create this file by compiling the following source code:
#include <stdio.h>intmain(){printf("Hello World!\n");}When this program is run, we get:$helloHello World!Here is some sample output from a run of sample4:$sample4Connected.Do you want to create (or recreate) the EXECUTABLES table (y/n)? yEXECUTABLES table successfully dropped. Now creating new table...EXECUTABLES table created.Sample 4 Menu. Would you like to:(I)nsert a new executable into the database(R)etrieve an executable from the database(L)ist the executables currently stored in the database(Q)uit the programEnter i, r, l, or q: lExecutables currently stored:----------- --------- ------Total: 0Sample 4 Menu. Would you like to:(I)nsert a new executable into the database(R)etrieve an executable from the database(L)ist the executables currently stored in the database(Q)uit the programEnter i, r, l, or q: iEnter the key under which you will insert this executable: helloEnter the filename to insert under key 'hello'.If the file is not in the current directory, enter the fullpath: helloInserting file 'hello' under key 'hello'...Inserted.Sample 4 Menu. Would you like to:(I)nsert a new executable into the database(R)etrieve an executable from the database(L)ist the executables currently stored in the database(Q)uit the programEnter i, r, l, or q: lExecutables currently stored:----------- --------- ------helloTotal: 1Sample 4 Menu. Would you like to:(I)nsert a new executable into the database(R)etrieve an executable from the database(L)ist the executables currently stored in the database(Q)uit the programEnter i, r, l, or q: rEnter the key for the executable you wish to retrieve: helloEnter the file to write the executable stored under key hello into. If youdon't want the file to be in the current directory, enter thefull path: h1Retrieving executable stored under key 'hello' to file 'h1'...Retrieved.Sample 4 Menu. Would you like to:(I)nsert a new executable into the database(R)etrieve an executable from the database(L)ist the executables currently stored in the database(Q)uit the programEnter i, r, l, or q: qWe now have the binary file 'h1' created, and we can run it:$h1Hello World!
This program is available on-line in the file sample4.pc in your demo directory.
/*************************************************************** sample4.pc ***************************************************************/ #include <stdio.h> #include <sys/types.h> #include <sys/file.h> #include <fcntl.h> #include <string.h> #include <sqlca.h> /* Oracle error code for 'table or view does not exist'. */ #define NON_EXISTENT -942 /* This is the maximum size (in bytes) of a file that * can be inserted and retrieved. * If your system cannot allocate this much contiguous * memory, this value might have to be lowered. */ #define MAX_FILE_SIZE 500000 /* This is the definition of the long varraw structure. * Note that the first field, len, is a long instead * of a short. This is because the first 4 * bytes contain the length, not the first 2 bytes. */ typedef struct { long len; char buf[MAX_FILE_SIZE]; } long_varraw; /* Type Equivalence long_varraw to long varraw. * All variables of type long_varraw from this point * on in the file will have external type 95 (long varraw) * associated with them. */ EXEC SQL type long_varraw is long varraw (MAX_FILE_SIZE); /* This program's functions declared. */ void do_connect(); void create_table(); void sql_error(); void list_executables(); void print_menu(); main() { char reply[20], key[20], filename[100]; int ok = 1; /* Connect to the database. */ do_connect(); printf("Do you want to create (or recreate) the EXECUTABLES table (y/n)? "); gets(reply); if ((reply[0] == 'y') || (reply[0] == 'Y')) create_table(); /* Print the menu, and read in the user's selection. */ print_menu(); gets(reply); while (ok) { switch(reply[0]) { case 'I': case 'i': /* User selected insert - get the key and file name. */ printf(" Enter the key under which you will insert this executable: "); gets(key); printf( "Enter the filename to insert under key '%s'.\n", key); printf( "If the file is not in the current directory, enter the full\n"); printf("path: "); gets(filename); insert(key, filename); break; case 'R': case 'r': /* User selected retrieve - get the key and file name. */ printf( "Enter the key for the executable you wish to retrieve: "); gets(key); printf( "Enter the file to write the executable stored under key "); printf("%s into. If you\n", key); printf( "don't want the file to be in the current directory, enter the\n"); printf("full path: "); gets(filename); retrieve(key, filename); break; case 'L': case 'l': /* User selected list - just call the list routine. */ list_executables(); break; case 'Q': case 'q': /* User selected quit - just end the loop. */ ok = 0; break; default: /* Invalid selection. */ printf("Invalid selection.\n"); break; } if (ok) { /* Print the menu again. */ print_menu(); gets(reply); } } EXEC SQL commit work release; } /* Connect to the database. */ void do_connect() { /* Note this declaration: uid is a char * * pointer, so Oracle will do a strlen() on it * at runtime to determine the length. */ char *uid = "scott/tiger"; EXEC SQL whenever sqlerror do sql_error("Connect"); EXEC SQL connect :uid; printf("Connected.\n"); } /* Creates the executables table. */ void create_table() { /* We are going to check for errors ourselves * for this statement. */ EXEC SQL whenever sqlerror continue; EXEC SQL drop table executables; if (sqlca.sqlcode == 0) { printf("EXECUTABLES table successfully dropped. "); printf("Now creating new table...\n"); } else if (sqlca.sqlcode == NON_EXISTENT) { printf("EXECUTABLES table does not exist. "); printf("Now creating new table...\n"); } else sql_error("create_table"); /* Reset error handler. */ EXEC SQL whenever sqlerror do sql_error("create_table"); EXEC SQL create table executables (name varchar2(20), binary long raw); printf("EXECUTABLES table created.\n"); } /* Opens the binary file identified by 'filename' for * reading, and copies it into 'buf'. * 'bufsize' should contain the maximum size of * 'buf'. Returns the actual length of the file read in, * or -1 if there is an error. */ int read_file(filename, buf, bufsize) char *filename, *buf; long bufsize; { /* We will read in the file LOCAL_BUFFERSIZE bytes at a time. */ #define LOCAL_BUFFERSIZE 512 /* Buffer to store each section of the file. */ char local_buffer[LOCAL_BUFFERSIZE]; /* Number of bytes read each time. */ int number_read; /* Total number of bytes read (the size of the file). */ int total_size = 0; /* File descriptor for the input file. */ int in_fd; /* Open the file for reading. */ in_fd = open(filename, O_RDONLY, 0); if (in_fd == -1) return(-1); /* While loop to actually read in the file, * LOCAL_BUFFERSIZE bytes at a time. */ while ((number_read = read(in_fd, local_buffer, LOCAL_BUFFERSIZE)) > 0) { if (total_size + number_read > bufsize) { /* The number of bytes we have read in so far exceeds the buffer * size - close the file and return an error. */ close(in_fd); return(-1); } /* Copy the bytes just read in from the local buffer into the output buffer. */ memcpy(buf+total_size, local_buffer, number_read); /* Increment the total number of bytes read by the number we just read. */ total_size += number_read; } /* Close the file, and return the total file size. */ close(in_fd); return(total_size); } /* Generic error handler. The 'routine' parameter * should contain the name of the routine executing when * the error occurred. This is be specified in the * 'EXEC SQL whenever sqlerror do sql_error()' statement. */ void sql_error(routine) char *routine; { char message_buffer[512]; int buffer_size; int message_length; /* Turn off the call to sql_error() to avoid * a possible infinite loop. */ EXEC SQL WHENEVER SQLERROR CONTINUE; printf("\nOracle error while executing %s!\n", routine); /* Use sqlglm() to get the full text of the error message. */ buffer_size = sizeof(message_buffer); sqlglm(message_buffer, &buffer_size, &message_length); printf("%.*s\n", message_length, message_buffer); EXEC SQL ROLLBACK WORK RELEASE; exit(1); } /* Opens the binary file identified by 'filename' for * writing, and copies the contents of 'buf' into it. * 'bufsize' should contain the size of 'buf'. * Returns the number of bytes written (should be == bufsize), * or -1 if there is an error. */ int write_file(filename, buf, bufsize) char *filename, *buf; long bufsize; { int out_fd; /* File descriptor for the output file. */ int num_written; /* Number of bytes written. */ /* Open the file for writing. This command replaces * any existing version. */ out_fd = creat(filename, 0755); if (out_fd == -1) { /* Can't create the output file - return an error. */ return(-1); } /* Write the contents of buf to the file. */ num_written = write(out_fd, buf, bufsize); /* Close the file, and return the number of bytes written. */ close(out_fd); return(num_written); } /* Inserts the binary file identified by file into the * executables table identified by key. */ int insert(key, file) char *key, *file; { long_varraw lvr; printf("Inserting file '%s' under key '%s'...\n", file, key); lvr.len = read_file(file, lvr.buf, MAX_FILE_SIZE); if (lvr.len == -1) { /* File size is too big for the buffer we have - * exit with an error. */ fprintf(stderr, "\n\nError while reading file '%s':\n", file); fprintf(stderr, "The file you selected to read is too large for the buffer.\n"); fprintf(stderr, "Increase the MAX_FILE_SIZE macro in the source code,\n"); fprintf(stderr, "reprecompile, compile, and link, and try again.\n"); fprintf(stderr, "The current value of MAX_FILE_SIZE is %d bytes.\n", MAX_FILE_SIZE); EXEC SQL rollback work release; exit(1); } EXEC SQL whenever sqlerror do sql_error("insert"); EXEC SQL insert into executables (name, binary) values (:key, :lvr); EXEC SQL commit; printf("Inserted.\n"); } /* Retrieves the executable identified by key into file */ int retrieve(key, file) char *key, *file; { /* Type equivalence key to the string external datatype.*/ EXEC SQL VAR key is string(21); long_varraw lvr; short ind; int num_written; printf("Retrieving executable stored under key '%s' to file '%s'...\n", key, file); EXEC SQL whenever sqlerror do sql_error("retrieve"); EXEC SQL select binary into :lvr :ind from executables where name = :key; num_written = write_file(file, lvr.buf, lvr.len); if (num_written != lvr.len) { /* Error while writing - exit with an error. */ fprintf(stderr, "\n\nError while writing file '%s':\n", file); fprintf(stderr, "Can't create the output file. Check to be sure that you\n"); fprintf(stderr, "have write permissions in the directory into which you\n"); fprintf(stderr, "are writing the file, and that there is enough disk space.\n"); EXEC SQL rollback work release; exit(1); } printf("Retrieved.\n"); } void list_executables() { char key[21]; /* Type equivalence key to the string external * datatype, so we don't have to null-terminate it. */ EXEC SQL VAR key is string(21); EXEC SQL whenever sqlerror do sql_error("list_executables"); EXEC SQL declare key_cursor cursor for select name from executables; EXEC SQL open key_cursor; printf("\nExecutables currently stored:\n"); printf("----------- --------- ------\n"); while (1) { EXEC SQL whenever not found do break; EXEC SQL fetch key_cursor into :key; printf("%s\n", key); } EXEC SQL whenever not found continue; EXEC SQL close key_cursor; printf("\nTotal: %d\n", sqlca.sqlerrd[2]); } /* Prints the menu selections. */ void print_menu() { printf("\nSample 4 Menu. Would you like to:\n"); printf("(I)nsert a new executable into the database\n"); printf("(R)etrieve an executable from the database\n"); printf("(L)ist the executables currently stored in the database\n"); printf("(Q)uit the program\n\n"); printf("Enter i, r, l, or q: "); }
