DB2 for Linux on iSeries: Implementation Guide

72 DB2 UDB for Linux on iSeries: Implementation Guide

Example 3-3 Embedded SQL code translated by the precompiler

/*

EXEC SQL CONNECT TO :dbname USER :user USING :password;

*/

{

sqlastrt(sqla_program_id, &sqla_rtinfo, &sqlca);

sqlaaloc(2,3,1,0L);

{

struct sqla_setdata_list sql_setdlist[3];

sql_setdlist[0].sqltype = 460; sql_setdlist[0].sqllen = 10;

sql_setdlist[0].sqldata = (void*)dbname;

sql_setdlist[0].sqlind = 0L;

sql_setdlist[1].sqltype = 460; sql_setdlist[1].sqllen = 10;

sql_setdlist[1].sqldata = (void*)user;

sql_setdlist[1].sqlind = 0L;

sql_setdlist[2].sqltype = 460; sql_setdlist[2].sqllen = 10;

sql_setdlist[2].sqldata = (void*)password;

sql_setdlist[2].sqlind = 0L;

sqlasetdata(2,0,3,sql_setdlist,NULL,0L);

}

sqlacall((unsigned short)29,5,2,0,0L);

if (sqlca.sqlcode < 0)

{

sqlastop(0L);

goto error;

}

sqlastop(0L);

}

3.7.2 Compile and run

Figure 3-30 shows the procedure to make a program use embedded SQL.

Embedded SQL programs need to be precompiled by a DB2 tool (db2 prep

command) before compiled by a C compiler. The extension of an embedded SQL

program is .sqc. The precompiler first analyzes the .sqc file and converts the

EXEC SQL statements, which shows the line is the DB2 embedded SQL command

to the related C source codes, which includes DB2 internal APIs. The

precompiler makes a C source file in which the extension is .c. We have to

compile the .c file using a C compiler to make the application. At the precompile

time, the precompiler checks the SQL statement with actually connecting the

database and creates some objects called SQL package or bind file. The SQL

package is an object stored in the database which has all the information needed

to execute the SQL statements of the program. The SQL package is created and

stored in the database at the precompilation time. Instead, you can create the

bind file as a local file. Using the bind file, you can delay the creation of the SQL

package until the file is

bound to a database by using the db2 bind command.

Chapter 3. Connectivity with DB2 UDB for iSeries 73

Figure 3-30 Embedded SQL program compiling process

We create the SQL package directly in this scenario and do not use bind file.

To precompile the testESQL.sqc, the DB2 CLP and db2 prep command is used.

Since the connection to the database is necessary when precompiling the

program, you have to connect to the OS400 database first. Then we can

precompile. Figure 3-31 shows this.

Source file

testESQL.sqc

DB2

Precompiler

SQL

Package

DB2 database

bind file

Modified

source file

testESQL.c

C compiler

Executable

program

testESQL

db2 prep

db2 bind

(DB access)

gcc

74 DB2 UDB for Linux on iSeries: Implementation Guide

Figure 3-31 Precompile the embedded SQL program

The parameter collection SAMPLEDB01 specifies the schema (OS/400 library) in

which the SQL package is created. If the collection parameter is not specified,

the default schema of the user (DBDEMO) is assumed.

The precompiler creates the file testESQL.c. We, then compile the file using the

gcc compiler to create the final product of the process. The compile parameters

are the same as for the CLI program. Figure 3-32 shows the compile and run

steps.

Figure 3-32 Compile and run the embedded SQL program

db2inst1@linux02:~/redbook> db2 connect to OS400 user DBDEMO using PASSWORD

Database Connection Information

Database server = OS/400 5.2.0

SQL authorization ID = DBDEMO

Local database alias = OS400

db2inst1@linux02:~/redbook> db2 prep testESQL.sqc collection SAMPLEDB01

LINE MESSAGES FOR testESQL.sqc

------ --------------------------------------------------------------------

SQL0060W The "C" precompiler is in progress.

SQL0091W Precompilation or binding was ended with "0"

errors and "0" warnings.

db2inst1@linux02:~/redbook>db2 terminate

DB20000I The TERMINATE command completed successfully.

db2inst1@linux02:~/redbook>

db2inst1@linux02:~/redbook> gcc -o testESQL -I/home/db2inst1/sqllib/include

-L/home/db2inst1/sqllib/lib -ldb2 ./testESQL.c

db2inst1@linux02:~/redbook> ./testESQL OS400 DBDEMO PASSWORD

Embedded SQL sample

Connected.

290 Quill 84 Mgr 10 19818

300 Davis 84 Sales 5 15454

310 Graham 66 Sales 13 21000

320 Gonzales 66 Sales 4 16858

330 Burke 66 Clerk 1 10988

340 Edwards 84 Sales 7 17844

350 Gafney 84 Clerk 5 13030

Got to end

db2inst1@linux02:~/redbook>