Static Embedded SQL-Introduction to Database Management-Lecture 05 Slides-Computer Science, Slides of Introduction to Database Management Systems

Download Static Embedded SQL-Introduction to Database Management-Lecture 05 Slides-Computer Science and more Slides Introduction to Database Management Systems in PDF only on Docsity!

Static Embedded SQL

David Toman

School of Computer Science University of Waterloo

Introduction to Databases CS

Database Applications

• SQL isn’t sufficient to write general applications.

) connect it with a general-purpose PL!

• Language considerations:

) Library calls (CLI/ODBC)

) Embedded SQL

) Advanced persistent PL (usually OO)

• Client-server:

) SQL runs on the server

) Application runs on the client

Embedded SQL

• SQL Statements are embedded into

a host language (C, C++, FORTRAN,... )

• The application is preprocessed

pure host language program + library calls

• Advantages:

 Preprocessing of (static) parts of queries

 MUCH easier to use

• Disadvantages:

 Needs precompiler

 Needs to be bound to a database

Embedded SQL (cont.)

• Considerations:

) How much can SQL be parameterized?

 How to pass parameters into SQL?

 How to get results?

 Errors?

) Static vs. dynamic SQL statements.

• How much does the DBMS know about an application?

) precompiling: PREP

) binding: BIND

Declarations

• Include SQL communication area:

EXEC SQL INCLUDE SQLCA;

it defines:

) the return code of SQL statements (sqlcode)

) the error messages (if any)

)... you can’t live without it.

• SQL statements inserted using magic words

EXEC SQL

Host Variables

are used to pass values between a SQL and the rest of the program:

• parameters in SQL statements:

communicate single values between

SQL a statement and host language variables

• must be declared within SQL declare section:

EXEC SQL BEGIN DECLARE SECTION;

declarations of variables to be used

in SQL statements go here

EXEC SQL END DECLARE SECTION;

• can be used in the EXEC SQL statements:

) to distinguish them from SQL identifiers

they are preceded by ‘:’ (colon)

Dummy Application (DB2)

#include <stdio.h> #include "util.h"

EXEC SQL INCLUDE SQLCA;

int main(int argc, char (^) *argv[]) { EXEC SQL BEGIN DECLARE SECTION; char db[6] = "cs448"; EXEC SQL END DECLARE SECTION; printf("Sample C program: CONNECT\n" ); EXEC SQL WHENEVER SQLERROR GO TO error; EXEC SQL CONNECT TO :db; printf("Connected to DB2\n"); // do your stuff here EXEC SQL COMMIT; EXEC SQL CONNECT reset; exit(0); error: check_error("My error",&sqlca); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK; EXEC SQL CONNECT reset; exit(1); }

Dummy Application (Oracle)

#include <stdio.h>

EXEC SQL INCLUDE SQLCA;

int main(int argc, char (^) *argv[]) { EXEC SQL BEGIN DECLARE SECTION; char user[6] = "cs448"; char pwd[10]; EXEC SQL END DECLARE SECTION; printf("Sample C program: CONNECT\n" ); strncpy(pwd,getpass("Password: "),10); EXEC SQL WHENEVER SQLERROR GO TO error; EXEC SQL CONNECT :user IDENTIFIED BY :pwd; printf("Connected to Oracle\n"); // do your stuff here EXEC SQL COMMIT RELEASE; exit(0); error: sqlca.sqlerrm.sqlerrmc[sqlca.sqlerrm.sqlerrml] =’\0’; printf("MyError %s\n", sqlca.sqlerrm.sqlerrmc); EXEC SQL WHENEVER SQLERROR CONTINUE; EXEC SQL ROLLBACK RELEASE; exit(1); }

Example of a build (DB2)

bash$ make NAME=sample db2 connect to cs

Database server = DB2/SUN 6.1. SQL authorization ID = DAVID Local database alias = CS

db2 prep sample1.sqc bindfile LINE MESSAGES FOR sample1.sqc

---

SQL0060W The "C" precompiler is in progress. SQL0091W Precompilation or binding was ended with "0" errors and "0" warnings. db2 bind sample1.bnd LINE MESSAGES FOR sample1.bnd

---

SQL0061W The binder is in progress. SQL0091N Binding was ended with "0" errors and "0" warnings. db2 connect reset DB20000I The SQL command completed successfully. cc -I/usr/db2/include -c sample1.c cc -I/usr/db2/include -o sample1 sample1.o util.o -L/usr/db2/lib -R/usr/db2/lib -ldb

Example

bash$ ./sample

Sample C program: CONNECT

Connected to DB

bash$

Simple Application

Write a program that for each publication id supplied as an argument

prints out the title of the publication:

main(int argc, char (^) *argv[]) { ... printf("Connected to DB2\n"); for (i=1; i<argc; i++) { strncpy(pubid,argv[i],8);

EXEC SQL WHENEVER NOT FOUND GO TO nope;

EXEC SQL SELECT title INTO :title FROM publication WHERE pubid = :pubid;

printf("%10s: %s\n",pubid,title); continue; nope: printf("%10s: (^) *** not found (^) *** \n",pubid); }; ... }

Simple Application (cont.)

bash$ ./sample2 ChTo98 nopubid

Sample C program: SAMPLE

Connected to DB

ChTo98: Temporal Logic in Information Systems

nopubid: *** not found ***

) it is important that at most one title is returned for each pubid.

Impedance Mismatch

What if we EXEC SQL a query and it returns more than one tuple?

1 Declare the cursor :

EXEC SQL DECLARE CURSOR

FOR ;

2 Iterate over it:

EXEC SQL OPEN ;

EXEC SQL WHENEVER NOT FOUND GO TO end;

for (;;) {

EXEC SQL FETCH

INTO ;

end:

EXEC SQL CLOSE ;

Application with a Cursor

Write a program that lists all author names and publication titles with

author name matching a pattern given as an argument:

main(int argc, char (^) *argv[]) { ... strncpy(apat,argv[1],8);

EXEC SQL DECLARE author CURSOR FOR SELECT name, title FROM author , wrote, publication WHERE name LIKE :apat AND aid=author AND pubid=publication;

EXEC SQL OPEN author; EXEC SQL WHENEVER NOT FOUND GO TO end; for (;;) { EXEC SQL FETCH author INTO :name, title; printf("%10s -> %20s: %s\n",apat,name,title); }; end: ... }