Embedded SQL SQL Is Not for All SQL is

Embedded SQL SQL Is Not for All • SQL is capable in manipulating relational data • SQL is not good for many other tasks – Control structures: loops, conditional branches, … – Advanced data structures: trees, arrays, … – Not good for general problem solving • How to solve a general problem involving relational data? – Use a general programming language (e.g., C++/Java) in general but use SQL for relational data access – How to embed SQL into a general programming language? CMPT 354: Database I -- Embedded SQL 2 Integrating SQL and Programming? • Typical procedural programming languages – C/C++, Java – Programs = data structures + algorithms – Structured data variables: records, arrays – Procedural semantics: loop, control structures • SQL – Sets: query results are sets, unordered in general – No control structure in general – Hiding data structures and algorithms CMPT 354: Database I -- Embedded SQL 3 Programming Environment Database management system program ? SQL files database storage CMPT 354: Database I -- Embedded SQL 4 SQL and Hosting Language Database management system program embedded SQL files database storage CMPT 354: Database I -- Embedded SQL 5 Embedding SQL Host language + Embedded SQL Host language + Function calls preprocessor Pre-compile Host-language compiler SQL library Object-code program CMPT 354: Database I -- Embedded SQL 6 SQL/Host Language Statement? • How can a preprocessor tell whether a statement is for SQL or for the host language? – Keywords EXEC SQL in front of the statement • How can an SQL statement use a variable in the host language? – Insert a new customer whose information is input by a user char customer_name[20], customer_street[30], customer_city[30], SQLSTATE[6]; … //host language stuff EXEC SQL BEGIN DECLARE SECTION; char customer_name[20], customer_street[30], customer_city[30]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; – Declare a host variable in SQL before using it in SQL statements CMPT 354: Database I -- Embedded SQL 7 Embedded SQL • SQL can be embedded in many host languages such as C/C++, Java, and COBOL • EXEC SQL statement is used to identify embedded SQL request to the preprocessor – The basic form follows that of the System R embedding of SQL into PL/I EXEC SQL END_EXEC – May vary by language (for example, the Java embedding uses # SQL { …. }; ) CMPT 354: Database I -- Embedded SQL 8 Using Shared Variables EXEC SQL BEGIN DECLARE SECTION; char customer_name[20], customer_street[30], customer_city[30]; char SQLSTATE[6]; EXEC SQL END DECLARE SECTION; customer_name=get_customer_name(); customer_street=get_customer_street(); customer_city=get_customer_city(); EXEC SQL INSERT INTO customer(customer_name, customer_street, customer_city) VALUES (:customer_name, :customer_street, :customer_city); • The shared variables are prefixed by a colon in SQL statements – They appear without the colon in host language statements CMPT 354: Database I -- Embedded SQL 9 Status of SQL Statement Execution • How can we know whether an update succeeds or not? – SQLSTATE connects the host language program with the SQL execution system • SQLSTATE: an array of five characters – Each time a function of the SQL library is called, a code is put in SQLSTATE – ‘00000’: no error condition occurred – ‘02000’: a tuple requested is not found CMPT 354: Database I -- Embedded SQL 10 Communication in Between Database management system Data program SQL SQLSTATE files database storage CMPT 354: Database I -- Embedded SQL 11 How to Use SQLSTATE • An easy way is to treat it as a string • Special considerations for host languages, e.g., C/C++ – A string in C/C++ needs an end-of-string character ‘\0’ – Declare char SQLSTATE[6]; – Set SQLSTATE[5]=‘\0’; – Use strcmp in comparison CMPT 354: Database I -- Embedded SQL 12 Storing Results into Variables • If an SQL statement returns only one tuple, the attribute values can be stored into variables in the host language • Use keyword INTO in the SELECT clause EXEC SQL SELECT SUM(assets) INTO :total_assets FROM branch WHERE branch_city = ‘Brooklyn’; CMPT 354: Database I -- Embedded SQL 13 Handling a Set of Tuples • Generally, an SQL statement may return a set of tuples • Cursor – Conceptually, put all tuples returned by a query into a file – Fetch the tuples from the virtual file – After use, the file should be closed and released CMPT 354: Database I -- Embedded SQL 14 Example • From within a host language, find the names and cities of customers with more than the variable amount dollars in some account EXEC SQL declare c cursor for select customer_name, customer_city from depositor, customer, account where depositor.customer_name = customer.customer_name and depositor account_number = account.account_number and account.balance > :amount END_EXEC • Declared, but not materialized yet CMPT 354: Database I -- Embedded SQL 15 Executing a Cursor • The open statement causes the query to be evaluated EXEC SQL open c END_EXEC • The fetch statement causes the values of one tuple in the query result to be placed on the host language variables EXEC SQL fetch c into :cn, :cc END_EXEC – Can be called repeatedly to get all tuples CMPT 354: Database I -- Embedded SQL 16 Executing a Cursor (2) • SQLSTATE set to ‘02000’ to indicate no more data is available • The close statement causes the database system to delete the temporary relation that holds the result of the query EXEC SQL close c END_EXEC • Details vary with language – The Java embedding defines Java iterators to step through result tuples CMPT 354: Database I -- Embedded SQL 17 Updates Through Cursors • To update tuples fetched by cursor, declare the cursor for update declare c cursor for select * from account where branch_name = ‘Perryridge’ for update • To update tuple at the current balance of cursor c update account set balance = balance + 100 where current of c CMPT 354: Database I -- Embedded SQL 18 Concurrent Changes • Two users may change tuples in a database concurrently – Process 1: remove account of balance < 1000 – Process 2: increase the balance by 10% – Whether an account of balance 950 would be removed depends on which process touches the tuple first • Keyword INSENSITIVE makes a cursor not affected by other concurrent updates EXEC SQL DECLARE accountCursor INSENSITIVE CURSOR FOR SELECT balance FROM account; – More about concurrency control in CMPT 454 CMPT 354: Database I -- Embedded SQL 19 READ ONY Cursors • If a cursor is used only for read operations, it can be concurrent with any other processes EXEC SQL DECLARE readAccCursor CURSOR READ_ONLY FOR SELECT balance FROM account; • Different database systems may have slightly different syntax CMPT 354: Database I -- Embedded SQL 20 Scrolling Cursors • Random access versus serial access – Serial access: fetch the next one, cannot jump to other tuples – Random access: fetch any tuple in a cursor • Scrolling cursor: a cursor for random access EXEC SQL DECLARE accCursor SCROLL CURSOR FOR account; • Options in FECTCH – – – – NEXT or PRIOR FIRST or LAST RELATIVE k (RELATIVE -1 = PRIOR) ABSOLUTE k (ABSOLUTE -1 = LAST) 21 CMPT 354: Database I -- Embedded SQL Dynamic SQL • Allow programs to construct and submit SQL queries at run time char * sqlprog = “update account set balance = balance * 1.05 where account_number = ?” EXEC SQL prepare dynprog from :sqlprog; char account [10] = “A-101”; EXEC SQL execute dynprog using :account; • “?” is a place holder for a value that is provided when the SQL program is executed CMPT 354: Database I -- Embedded SQL 22 Summary • SQL is good for accessing relational data, but not for other general problem solving tasks – Embedding SQL into a host general programming language is useful in practice • Embedded SQL • Cursors CMPT 354: Database I -- Embedded SQL 23 To-Do-List • Understand how to embed SQL into C# • Understand cursors in SQL Server 2005 CMPT 354: Database I -- Embedded SQL 24