嵌入SQL 编程 ( 40 ).pdf

《嵌入SQL 编程 ( 40 ).pdf》由会员分享，可在线阅读，更多相关《嵌入SQL 编程 ( 40 ).pdf（21页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、112 Embedding SQL in Programming 12 Embedding SQL in Programming languageslanguages12.1 Introduction:using SQL from programs 12.2 Embedded SQL12.2.1 Static and dynamic embedding12.2.2 Cursors12.2.3.ESQL/C12.2.4 Positioned Update12.3 Transactions in application programs12.3.1 Definition12.3.2 Isolati

2、on levels12.4 SQL and Java12.4.1 JDBC12.4.2 SQLJKemper/Eickler:chap.4.19-4.23;Melton:chap.12,13,17-19,Widom,Ullman,Garcia-Molina:chapt.8 Christian UllenboomJava ist auch eine Insel,Kap.20,Galileo Comp.HS/DBS05-15-ProgLang 2UsingUsing SQL SQL fromfrom ProgramsProgramsIntroductionIntroduction SQL is a

3、 data sublanguage Needs a host language Control structures User interface:output formatting,forms Transactions:more than one DB interaction as a unit of work Issues Language mismatch(impedance mismatch)Set oriented operations versus manipulation of individuals How to interconnect program variables a

4、nd e.g attributesin SQL statements?Should an SQL-statement as part of a program be compiled,when?Question:could you imagine a language bringing both worlds together?2HS/DBS05-15-ProgLang 3ThreeThree-tiertier architecturearchitecture(exampleexample)GUI clientWeb browserWeb browserDB clientWeb ServerD

5、B ApplicationDB ApplicationDB-ServerDB-ServerDB-ServerMiddleware layerMiddle tierFile SystemHS/DBS05-15-ProgLang 4UsingUsing SQL SQL fromfrom ProgramsProgramsIntroductionIntroductionOverview of language/DB integration concepts FourthGeneration Languages Module Language-PSM(PL/SQL,PLpgSQL)Standardize

6、d in SQL-99 Interface of standard programming languages Call level interface,proprietary library routines,APIStandardized:SQL CLI Open Database connection(ODBC),Embedded C/Java/.Standardized language extensions Standardized API Java DBC Fourth generation Language Stored Procedures C/Java/Perl/Python

7、,.Component architectures:hiding the details of DB interaction,Enterprise Java Beans(EJB)3HS/DBS05-15-ProgLang 5SQL SQL fromfrom ProgramsPrograms 4.Generation 4.Generation LanguagesLanguages Proprietary Fourth generation language(4GL)Underlying assumption:most application programs are algorithmicall

8、y simple sophisticated output formatting needed it should be difficult for users to switch from one DBS to another Technical conceptClient evolved from simple Terminal to 4GL-Interpreter Open systems movement and HTTP/HTML/Java makes 4GL less importantClientDBSProprietary protocolHS/DBS05-15-ProgLan

9、g 6UsingUsing SQL SQL fromfrom ProgramsProgramsModulesModules Standardization efforts(SQL 89/SQL99)Modules and Embedded SQL SQL Modules Separate parameterized Modules of SQL statements Compiled for a particular language(e.g.COBOL,C,ADA.)Linked to application program (statically?)Disadvantage SQL cod

10、e hidden in application and vice versa Not widely used Superseded by flexible stored procedure concept4HS/DBS05-15-ProgLang 7UsingUsing SQL SQL fromfrom ProgramsProgramsCallCall interfaceinterface Call level interface Language and DBS specific library of procedures to access the DB Example:MySQL C A

11、PI Communication buffer for transfering commands and results API data types likeMYSQLhandle for db connectionsMYSQL_RES structure which represents result set API functionsmysql_real_query()mysql_real_query(MYSQL*mysql,const char*query,unsigned int length)query of length of character string in buffer

12、 and many more.Standard:Open Database Connection(ODBC)Predecessor of Java Database Connection(JDBC),see belowHS/DBS05-15-ProgLang 8SQL Call level interface(SQL/CLI)SQL Call level interface(SQL/CLI)Standardized Interface to C/C+defined by X/OPEN and SQL Accesss group Main advantages DBS-independent A

13、pplication development independent from DBS(as opposed to Embedded SQL precompilerapproach,see below)Easy to connect to multiple DB Microsoft implementation ODBC(=Open Database Connectivity)de facto standard,available not only for MS products5Main cycle of transaction execution withSQL/CLICalls are

14、embeddedin the applicationprogramSee also JDBC,ESQLsource:IBM DB2 manualHS/DBS05-15-ProgLang 1012.2 12.2 EmbeddedEmbedded SQLSQL Embedded SQL the most important(?)approach Concepts Program consists of native and SQL-likestatements Precompiler compiles it to native code,includes calls to DBSresources

15、 Employs call level interface in most implementations Most popular:Embedded C (Oracle:PRO*C)SQLJ =Embedded JavaESQLNative Language codePrecompilerObjectcodeCompilerLibraryExcecu-tableLinker6HS/DBS05-15-ProgLang 11EmbeddedEmbedded SQL(ESQL)SQL(ESQL)Syntax and Syntax and moremore Well defined type map

16、ping (for different languages)Exception handling (WHENEVER condition action)SQLSTATE,SQLCODE(deprecated)Syntax for embedded SQL statements Binding to host language variables#sqlSELECT m#FROM M WHERE titel=:titleString;.#sql FETCH.INTO:var1 hypothetical syntax,like SQLJ HS/DBS05-15-ProgLang 12ESQLESQ

17、L C/Java embeddingESQL/C SQLJEXEC SQL UPDATE staff SET job=Clerk WHERE job=Mgr;if(SQLCODE 0 printf(Update Error:.);try#sql UPDATE staff SET job=Clerk WHERE job=Mgr;catch(SQLException e)println(Update Error:SQLCODE=+.);7HS/DBS05-15-ProgLang 13ESQL code generationESQL code generationCode generatedbasi

18、cally atcompile time.DBS and DBmust be known beforeruntime in orderto generate executablesfrom:DB2 manualHS/DBS05-15-ProgLang 1412.2.1 ESQL 12.2.1 ESQL StaticStatic/dynamicdynamic embeddingembeddingStatic versus dynamic SQL:Static:all SQL commands are known in advance,SQL-compilation and language bi

19、nding at precompile time Dynamic(i)SQL-String executed by DBS:Operator tree,optimization,code binding.(ii)SQL-String prepared(compiled)at runtime.Performance gain in loops etc.8HS/DBS05-15-ProgLang 1512.2.2 ESQL12.2.2 ESQLCursorsCursorsCursor concept How to process a result set one tuple after the o

20、ther?CURSOR:name of an SQL statement and a handle for processing the result set record by record Cursor is defined,opened at runtime(=SQL-statement is excecuted)and used for FETCHing single result records OPEN cFETCH cDBSBuffers for application program cursorsDBS may determine result set in a lazyor

21、 eager way cDECLARE c.Cursor concept used in most language embeddings of SQL(ESQL-C,PL/SQL,JDBC and more)HS/DBS05-15-ProgLang 16ESQLESQLCursorsCursorsExplicit cursors:Declared and named by the programmer Sometimes implicit cursors for individual SQL statements areused in 4GL Cursor assigns a name to

22、 an SQL statement.Cursor/SQL statement do not bind the result attributes to variables allows to traverse the result set(the active set)row by rowActive setActive setCurrent rowCurrent rowCursor curs7369SMITHTo be or.7566JONESMetropolis7788SCOTTForest Gump7876ADAMSForest Gump7902FORDStar Wars IDeclar

23、e curs for Select c#,lname,m.title from C,R,M where.9HS/DBS05-15-ProgLang 17ESQLESQLCursorsCursors Controlling a cursor:the necessary steps Identify the Identify the active setactive setOPENOPENOPENNoNo Load the Load the current row current row into into variablesvariablesFETCHFETCHFETCH Test for Te

24、st for existing existing rowsrowsEMPTY?EMPTY?Release the Release the active setactive setCLOSECLOSECLOSEYesYes Create a Create a named named SQL areaSQL areaDECLAREDECLAREDECLAREExecutes the queryHS/DBS05-15-ProgLang 18ESQLESQLCursorsCursors OpeningOPENcursor_name;OPENcursor_name;In a compiled langu

25、age environment(e.g.embedded C):bind input variables execute query put(first)results into communication(context)area no exception if result is empty has to be checked when fetching the results positions the cursor before the first row of the result set(1)First steps in an interpreted language(e.g.4G

26、L PL/SQL):allocate context area parse query 10HS/DBS05-15-ProgLang 19ESQLESQLCursorsCursors FetchFETCH curs INTO:x,:nameVar,:titleVar;FETCH curs INTO FETCH curs INTO:x:x,:nameVarnameVar,:,:titleVartitleVar;Cursor scrolling(Declare c SCROLL cursor.in SQL 92):FETCH NEXT|PRIOR|FIRST|LAST|ABSOLUTE|RELAT

27、IVE expressionFROM cursor INTO target-variablesFETCH curs PRIOR INTO:x,:nameVar,:titleVar;FETCH curs FETCH curs PRIOR PRIOR INTO INTO:x:x,:nameVarnameVar,:,:titleVartitleVar;FETCH curs RELATIVE 1 INTO:x,:nameVar,:titleVar;FETCH curs FETCH curs RELATIVE RELATIVE 1 1 INTO INTO:x:x,:nameVarnameVar,:,:t

28、itleVartitleVar;=Single row SELECT does not need a FETCH but result isbound to variables:SELECT a,b FROM.INTO:x,:y WHEREHS/DBS05-15-ProgLang 2012.2.3 ESQL12.2.3 ESQL#include#include /*declare host variables/*declare host variables*/*/char userid12=char userid12=ABELABEL/xyzxyz;char emp_name10;char e

29、mp_name10;intintemp_numberemp_number;intintdept_number;dept_number;char temp32;char temp32;void void sql_errorsql_error();();/*include the SQL/*include the SQL Communications AreCommunications Are*/#include*/#include main()main()emp_numberemp_number=7499;=7499;/*handle errors*/*handle errors*/EXEC S

30、QL EXEC SQL WHENEVER SQLERRORWHENEVER SQLERRORdo do sql_error(Oraclesql_error(Oracle error);error);/*connect to Oracle*/*connect to Oracle*/EXEC SQL EXEC SQL CONNECTCONNECT:useriduserid;printf(Connected.printf(Connected.n n););/*declare a cursor*/*declare a cursor*/EXEC SQL EXEC SQL DECLARE DECLARE

31、emp_cursoremp_cursorCURSOR FOR CURSOR FOR SELECT SELECT enameenameFROM FROM empempWHERE WHERE deptnodeptno=:dept_number;:dept_number;Establish DB connection11HS/DBS05-15-ProgLang 21ESQLESQLExampleExample:EmbeddedEmbedded C Cprintf(Departmentprintf(Department number?);number?);gets(temp);gets(temp);d

32、ept_number=dept_number=atoi(tempatoi(temp););/*open the cursor and identify the active/*open the cursor and identify the active set*/set*/EXEC SQL OPEN EXEC SQL OPEN emp_cursoremp_cursor;/*fetch and process data in a loop/*fetch and process data in a loopexit when no more data*/exit when no more dat

33、a*/EXEC SQL WHENEVER NOT FOUND EXEC SQL WHENEVER NOT FOUND DODO break;break;while(1)while(1)EXEC SQL FETCH EXEC SQL FETCH emp_cursoremp_cursor INTO INTO :emp_nameemp_name;.;.EXEC SQL CLOSE EXEC SQL CLOSE emp_cursoremp_cursor;EXEC SQL COMMIT WORK RELEASE;EXEC SQL COMMIT WORK RELEASE;exit(0);exit(0);C

34、lose cursor before another SQLstatement is executedHS/DBS05-15-ProgLang 22ESQLESQLExceptionException handlinghandling Exception handling void void sql_error(msgsql_error(msg)char*char*msgmsg;char buf500;char buf500;intint buflenbuflen,msglenmsglen;EXEC SQL EXEC SQL WHENEVER SQLERROR CONTINUE;EXEC SQ

35、L ROLLBACK WORK RELEASE;buflenbuflen=sizeofsizeof(bufbuf););sqlglm(bufsqlglm(buf,&,&buflenbuflen,&,&msglenmsglen););printf(%sprintf(%s n n,msgmsg););printfprintf(%*.s(%*.s n,n,msglenmsglen,bufbuf););exit(1);exit(1);12HS/DBS05-15-ProgLang 23ESQLESQLExceptionException handlinghandlingEXEC SQL WHENEVER

36、 SQLERROR GOTO sql_error;.sql_error:EXEC SQL WHENEVER SQLERROR CONTINUE;EXEC SQL ROLLBACK WORK RELEASE;.Without the WHENEVER SQLERROR CONTINUE statement,a ROLLBACK error would invoke the routine again,starting aninfinite loop.HS/DBS05-15-ProgLang 2412.2.4 12.2.4 PositionedPositioned Update Update Up

37、date/Delete statements in general use search predicates to determine the rows to be updatedUpdate M Update M set set price_Dayprice_Day=price_Day+1 where=price_Day+1 where price_Dayprice_Day=1=1 Often useful:step through a set of rows and update some of them?positioned updateDECLARE myCursFOR SELECT

38、 ppd,title FROM MFOR UPDATE ON ppdUPDATE M SET ppd=ppd+1 WHERE CURRENT OFmyCurs/*delete in a/*similar wayA cursor may declared FOR READ ONLY(which basically results in some performance gains)Caveat:Use the capabilities of SQL!It would be stupid to check a predicate on a rowwithin the FETCH loop and

39、then update the row.13HS/DBS05-15-ProgLang 25ESQLESQLCursor Cursor sensitivitysensitivityWhich state has the database during processing?EXEC SQL DECLARE myCursFOR SELECT price_Day,title FROM M FOR UPDATE ON price_DayWHERE price_Day=0UPDATE account SET balance=myVar amountWHERE acc#=:myAcc;Call ATM_p

40、ay_out;ENDIF;COMMIT;SELECT SUM(balance),ownerFROM accountGROUP BY owner;COMMIT;DBS_OUTPUT.PutLine();concurrent execution in independent DB sessionsConflict?Not a big deal in this case,but may be SUM is incorrect.16HS/DBS05-15-ProgLang 31IsolationIsolationWorst case:lost updateLost update:two indepen

41、dent updaters update thesame object.Conflict may result in a wrong value!Updates is lost!Not allowed in any serious multiuser DBST1:progVar read(x);progVar+;write(x progVar)T2:progVar read(x);progVar+;write(x progVar)123456Read of T1 and T2:x=7;Increment by T1:x=8,increment by t2:x=8 Concurrent Exec

42、utionHS/DBS05-15-ProgLang 32Isolation levels:control Isolation levels:control behaviourbehaviour of transactionof transaction No problem at all if only READs How much isolation does a TA need?Application dependent:is it acceptable thatthe balance per customer does not reflectthe correct balances of

43、her account?read/write ratio?What is the conflict probability?Isolation level:The kind of conflicts a program is willing to acceptThe more isolation the less parallelism17HS/DBS05-15-ProgLang 33TransactionsTransactions in in applicationapplication programsprograms Isolation Levels Suppose TA1 decrea

44、ses the prices of some movies in the movie DB by 5%TA2 scrolls through all movies Question:does TA2 see the new values before TA1 commits?READ UNCOMMITTED Yes:updates of TA1 are immediately visible but only if TA2 has isolation level read uncommittedSET TRANSACTION READ ONLY,ISOLATION LEVEL READ UNC

45、OMMITTED Lowest locking overhead,but unpleasant effects may happen (Examples?)READ COMMITTED in PostgresHS/DBS05-15-ProgLang 34Setting isolation levelsSetting isolation levelsSET TRANSACTION ,0n=|ISOLATION LEVEL|DIAGNOSTIC SIZE =READ ONLY|READ WRITE =READ UNCOMMITTED|READ COMMITTED|REPEATABLE READ|S

46、ERIALIZABLEDiagnostic:area for details about exceptions,only for ESQLDifferent default modes:READ UNCOMMITTED?READ ONLYelse READ WRITE18HS/DBS05-15-ProgLang 35TransactionsTransactions in in applicationapplication programsprogramsREAD COMMITTED(cursor stability)No uncommitted update can be seen by an

47、y application But TA might see different states of the same object Conflicts typically solved by locks (2-phase locking)If Read committed but no repeatable read required:read-only transaction need only short read locks?higher parallelismTA2:R(a),x=x+a;.R(b);x:=x+b;.TA1:W(b+10);W(a-10);COMMIT;Value o

48、f program variablel x does not reflect DB statebecauseREAD is not REPEATABLEHS/DBS05-15-ProgLang 36TransactionsTransactionsin in applicationapplication programsprograms Isolation levels(4)REPEATABLE READ all read/write conflicts prevented,reads repeatable Lock synchronization:all locks held until en

49、d of TASERIALIZABLE repeatable read +phantoms avoidedTA2:R(a),x=x+a.R(b),x:=x+b,.TA1:Insert(z);Commit;-TA2:SUM of attribut of relation S,-TA1:inserts a row into S Unpleasant effect:Phantom recordsbut19HS/DBS05-15-ProgLang 37TransactionsTransactionsIsolation levels first statement within TA Be carefu

50、l with default modesSET TRANSACTION READ WRITE;SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;TA has default access mode of last SETi.e.READ ONLY(!)Read uncommitted dangerous:may cause inconsistencies Read committed is the default in some systems(e.g.Oracle)Serializable important for high frequent