Embedded SQL - tinman.cs.gsu.edu

1 CHAPTER3 Embedded SQLO racle SQL, introduced in the previous chapter, is not a language that can beused to build sophisticated database applications, but it is a very good languagefor defining the structure of the database and generating ad hoc queries. However,to build applications, the power of a full-fledged high-level programming languageis needed. Embedded SQL provides such an environment to develop applicationprograms. The basic idea behind Embedded SQL is to allow SQL statements ina program written in a high-level programming language such as C or C++.Byembedding SQL statements in a C/C++program, one can now write applicationprograms in C/C++that interact (read and write) with the database.

2 oracle providesa tool, called Pro*C/C++, which allows for applications to be developed in the C orC++language with Embedded SQL statements. The Pro*C/C++preprocessor parsesthe Embedded program and converts all SQL statements to system calls in C/C++andproduces a C/C++program as its output. This C/C++program can be compiled inthe usual manner to produce the executable version of the application program. Thischapter introduces concepts and techniques needed to write successful embeddedSQL programs in oracle using the C or C++language. Most of the concepts areintroduced using the C language.

3 A separate section is devoted to C++ VariablesSince SQL statements are to be Embedded within the C program, there is a needfor a mechanism to pass values between the C program environment and the SQLstatements that communicate with the oracle database server. Special variables,calledhost variables, are defined in the Embedded program for this purpose. These9394 Embedded SQLhost variables are defined between thebegin declare sectionandend declaresectiondirectives of the preprocessor as follows:EXEC SQL begin declare section;intcno;varchar cname[31];varchar street[31];intzip;char phone[13];EXEC SQL end declare section;The data types of the host variables must be compatible with the data types of thecolumns of the tables in the database.

4 Figure shows data types in C that arecompatible with commonly used data types in oracle . Thechardata type in Oracleis mapped to thechardata type in C. Thechar(N)data type in oracle is mappedto an array of characters in C. Notice that the size of the C array is one more than thesize of the character string in oracle . This is due to the fact that C character stringsrequire an additional character to store the end-of-string character (\0). oracle sPro*C preprocessor provides avarchararray data type in C that corresponds to thevarchar(N) oracle data type. Again, the size of the Cvarchararray is one morethan the maximum size of thevarcharstring of oracle .

5 Thevarchararray in C isdeclared asvarchar cname[31];and the Pro*C preprocessor produces the following C code corresponding to theabove declaration:/* varchar cname[31]; */struct {unsigned short len;unsigned char arr[31];} cname;Note that thevarchararray variablecnamehas been transformed into a structure(with the same name) containing two fields:arrandlen. Thearrfield will storethe actual string and thelenfield will store the length of the character string. Whensending avarcharvalue to the database, it is the responsibility of the programmerto make sure that both fields,arrandlen, are assigned proper values.

6 Whenreceiving such a value from the database, both fields are assigned appropriate valuesby the system. Thedatedata type is mapped to a fixed-length (10 characters,corresponding to the defaultdateformat in oracle ) character string in C. Host Variables95 Figure oracle and C data Data TypeC Data Typecharcharchar(N)char array[N+1]varchar(N)varchar array[N+1]datechar array[10]number(6)intnumber(10)long intnumber(6,2)floatnumeric data types are appropriately mapped tosmall int,int,long int,float,ordouble, depending on their precisions in host variables are used in the usual manner within C language constructs;however, when they are used in the Embedded SQL statements, they must bepreceded by a colon (:).

7 Some examples of their usage are shown in the followingcode ("%d", EXEC SQL select cnameinto :cnamefrom customerswhere cno = :cno;scanf("%d%s%s%d%s",&cno, , ,&zip,phone); = strlen( ); = strlen( );EXEC SQL insert into customersvalues (:cno,:cname,:street,:zip,:phone);The select statement in the above example has an additional clause, theintoclause,which is required in Embedded SQL since the results of the SQL statements must bestored someplace. Theselect intostatement can be used only if it is guaranteedthat the query returns exactly one or zero rows. A different technique is used toprocess queries that return more than one row.)

8 This technique, which uses theconcept of acursor, is discussed in Section Note that all occurrences of thehost variables within the Embedded SQL statements are preceded by a colon. Also,thelenfields of allvarchararrays in C are set to the correct lengths before sendingthe host variables to the VariablesAnullvalue in the database does not have a counterpart in the C language environ-ment. To solve the problem of communicatingnullvalues between the C programand oracle , Embedded SQL providesindicator variables, which are special integervariables used to indicate if anullvalue is retrieved from the database or stored inthe database.

9 Consider theorderstable of the mail-order database. The followingis the declaration of the relevant host and indicator variables to access SQL begin declare section;struct {intono;intcno;inteno;char received[12];char shipped[12];} order_rec;struct {shortono_ind;shortcno_ind;shorteno_ind; shortreceived_ind;shortshipped_ind;} order_rec_ind;int onum;EXEC SQL end declare section;The code below reads the details of a particular row from theorderstable into thehost variables declared above and checks to see whether theshippedcolumn is indicated by a value of 1 for the indicator variable.

10 Thedatabase server returns a value of 0 for the indicator variable if the column valueretrieved is ("%d", EXEC SQL select *into :order_rec indicator :order_rec_ind1. The indicator variable is also used for other purposes for example, it is used to indicate the length of astring value that was retrieved from the database and that was truncated to fit the host variable into which itwas SQL Communications Area (sqlca)97from orderswhere ono = :onum;if ( == -1)printf("SHIPPED is Null\n");elseprintf("SHIPPED is not Null\n");To store anullvalue into the database, a value of 1 should be assigned to theindicator variable and the indicator variable should be used in an update or insertstatement.)