1 / 23

Programmatic SQL

Programmatic SQL. Shaista Khan CS 157B. Topic. Embedded SQL statements in high-level programming languages. Why Embedded SQL?. SQL standard lacked “computational completeness”: it contained no flow of control commands such as IF, THEN, ELSE, GOTO, DO, or DO…WHILE.

byron
Download Presentation

Programmatic SQL

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Programmatic SQL Shaista Khan CS 157B

  2. Topic Embedded SQL statements in high-level programming languages.

  3. Why Embedded SQL? • SQL standard lacked “computational completeness”: it contained no flow of control commands such as IF, THEN, ELSE, GOTO, DO, or DO…WHILE. • To overcome this and to provide more flexibility, SQL allows statements to be “embedded” in a high-level procedural language.

  4. Two types of Programmatic SQL • Embedded SQL statements • SQL statements are embedded into the program source code and mixed with the host language statements. This allows users to write programs that access the database directly. A special precompiler modifies the source code to replace SQL statements with calls to DBMS routines. The source code can then be compiled and linked in the normal way. • Application Programming Interface (API) • No need for any precompilation. • Provides a cleaner interface and generates more manageable code.

  5. 2 Types of Embedded SQL • Static SQL • A complete embedded SQL statement. • Static SQL statements can be placed into stored procedures and can contain host variables. • Dynamic SQL • With dynamic SQL statements, knowing the complete structure of an SQL statement before building the application is not necessary. Dynamic SQL statements allow run-time input to provide information about the database objects to query.

  6. Simple SQL Statement CREATE TABLE Viewing (propertyNo VARCHAR2(5) NOT NULL, clientNo VARCHAR2(5) NOT NULL, viewDate DATE NOT NULL, comments VARCHAR2(40));

  7. Static SQL /*Program to create the Viewing table*/ #include <stdio.h> #include <stdlib.h> EXEC SQL INCLUDE sqlca; main() { EXEC SQL BEGIN DECLARE SECTION; char *username = “Manager/Manager”; char *connectString = “DreamHome”; EXEC SQL END DECLARE SECTION; /* Connect to database*/ EXEC SQL CONNECT :username USING :connectString; If(sqlca.sqlcode < 0) exit(-1); /* Display message for user and create the table */ printf (“Creating VIEWING table\n”); EXEC SQL CREATE TABLE Viewing (propertyNo VARCHAR2(5) NOT NULL, clientNo VARCHAR2(5) NOT NULL, viewDate DATE NOT NULL, comments VARCHAR2(40)); if (sqlca.sqlcode >= 0) /* Check success */ printf (“Creation successful\n”); else printf (“Creation unsuccessful\n”); /* Commit the transaction and disconnect from the database */ EXEC SQL COMMIT WORK RELEASE; }

  8. Using the SQLCA Data Structure • The SQL Communication Area (SQLCA) is a data structure that traps and reports runtime errors to the Embedded SQL for C applications. • The application checks the error fields and status indicators of the SQLCA data structure to determine the success or failure of an embedded SQL statement.

  9. SQLCA /* Connect to database*/ EXEC SQL CONNECT :username USING :connectString; If(sqlca.sqlcode < 0) exit(-1); /* Display message for user and create the table */ printf (“Creating VIEWING table\n”); EXEC SQL CREATE TABLE Viewing (propertyNo VARCHAR2(5) NOT NULL, clientNo VARCHAR2(5) NOT NULL, viewDate DATE NOT NULL, comments VARCHAR2(40)); if (sqlca.sqlcode >= 0) /* Check success */ • SQLCA.sqlcode • = 0 successfully executed • > 0 statement executed but with an exception • < 0 error occurred – statement did not execute

  10. The WHENEVER statement EXEC SQL WHENEVER <condition> <action> Error Handling SQLERROR SQLWARNING NOT FOUND CONTINUE DO name (args) DO BREAK DO CONTINUE GOTO label STOP EXEC SQL WHENEVER SQLERROR GOTO error1; EXEC SQL WHENEVER SQLWARNING STOP;

  11. The WHENEVER statement EXEC SQL WHENEVER SQLERROR GOTO error1; EXEC SQL INSERT INTO Viewing VALUES (‘CR76’, ‘PA14’, ’12-MAY-2001’, ‘NOT ENOUGH SPACE’); EXEC SQL INSERT INTO Viewing VALUES (‘CR77’, ‘PA14’, ’12-MAY-2001’, ‘QUITE LIKE IT’); -------------HOW PRECOMPILER READS IT------------ EXEC SQL WHENEVER SQLERROR GOTO error1; EXEC SQL INSERT INTO Viewing VALUES (‘CR76’, ‘PA14’, ’12-MAY-2001’, ‘NOT ENOUGH SPACE’); if (sqlca.sqlcode < 0) goto error1; EXEC SQL INSERT INTO Viewing VALUES (‘CR77’, ‘PA14’, ’12-MAY-2001’, ‘QUITE LIKE IT’); if (sqlca.sqlcode < 0) goto error1;

  12. Host Language Variables • Program variable declared in the host language. • Can be used in embedded SQL statements • Can be used within the WHERE clause of SELECT statement • Cannot be used to represent database objects, such as table names or column names.

  13. Host Language Variable • Must be declared to SQL as well as being declared in the syntax of the host language. • All host variables must be declared to SQL in this block, which must appear before any of the variables are used in an embedded SQL statement. EXEC SQL BEGIN DECLARE SECTION; float increment; EXEC SQL END DECLARE SECTION;

  14. Host Language Variables To use a host variable in an embedded SQL statement, the variable name is prefixed by a colon. For example, EXEC SQL UPDATE Staff SET salary = salary + :increment WHERE staffNo = ‘SL12’;

  15. Retrieving data using embedded SQL EXEC SQL SELECT fName, lName, address INTO :firstName, :lastName, :address, FROM PrivateOwner WHERE ownerNo = ‘CO21’;

  16. Difference between Static and Dynamic SQL Static SQL does not allow host variables to be used in place of table names or column names. For example, EXEC SQL BEGIN DECLARE SECTION; char Viewing; EXEC SQL END DECLARE SECTION; EXEC SQL INSERT INTO :Viewing VALUES (‘CR76’, ‘PA14’, ‘5-MAY-2001’, ‘Not enough space’);

  17. List of embedded SQL statements

  18. Statements for Dynamic SQL

  19. Running SQL Commands Any SQL command can be run from within an embedded SQL application. Below are some examples of how to do that. Creating a table: - EXEC SQL CREATE TABLE foo (number integer, ascii char(16)); - EXEC SQL CREATE UNIQUE INDEX num1 ON foo(number); - EXEC SQL COMMIT; Inserting rows: - EXEC SQL INSERT INTO foo (number, ascii) VALUES (9999, 'doodad');

  20. Running SQL Commands Deleting rows: - EXEC SQL DELETE FROM foo WHERE number = 9999; Single-row Select: - EXEC SQL SELECT foo INTO :FooBar FROM table1 WHERE ascii = ‘doodad’;

  21. Running SQL Commands Selecting using Cursors: - EXEC SQL DECLARE foo_bar CURSOR FOR SELECT number, ascii FROM foo ORDER BY ascii; - EXEC SQL OPEN foo bar; - EXEC SQL FETCH foo_bar INTO :FooBar, DooDad; … - EXEC SQL CLOSE foo_bar; Updates: - EXEC SQL UPDATE foo SET ascii = ‘foobar’ WHERE number = 9999;

  22. Bibliography • Connolly, Thomas, Carolyn Begg. “Database Systems: A practical approach to design, implementation, and management”. 3rd ed. 2002. • http://msdn.microsoft.com/library/default.asp?url=/library/enus/esqlforc/ec_6_epr_01_3m03.asp • http://www-db.stanford.edu/~ullman/fcdb/oracle/or-proc.html#sql

More Related