Hochschule für Technik und Wirtschaft Dresden (FH)dbst/material/20070328_125_wloka.pdf ·...

Fachbereich Informatik/MathematikProf. Dr. Wloka 125. Datenbankstammtisch

Hochschule für Technik und Wirtschaft Dresden (FH)


Entwicklung des SQL-Standardvom Hoffnungsträger zum Ideengrab


Gliederung

1 Die Anfänge: SQL:1986, SQL:19892 Die Entwicklung2.1 SQL:19922.2 SQL:19992.3 SQL-Standard und DBMS-Entwicklung3 Der Stand: SQL:20033.1 Überblick SQL:20033.2 Feature und Konformität der DBMS am Beispiel 4 Die Zukunft: SQL:200n4.1 Überblick SQL:200n4.2 neue Feature bei Part2, Part11, Part 14


Hoffnungsträger

• einfach verständliches Datenmodell• Deskriptive Sprache• Mengenorientierte Sprache mächtige Sprache• Nur 30 Befehle

Sprache für Endanwender


Entwicklung SQL:1986 bis SQL:1992

SQL 0:

SQL 1:

SQL 2:

SQL:1986

International edition

1986

1987

1989

1992

1995

1996

SQL:1989

SQL:1992

SQL/CLI (Part 3)

SQL/PSM (Part 4)


Gliederung

1 Die Anfänge: SQL:1986, SQL:19892 Die Entwicklung2.1 SQL:19922.2 SQL:19992.3 SQL-Standard und DBMS-Entwicklung3 Der Stand: SQL:20033.1 Überblick SQL:20033.2 Feature und Konformität der DBMS am Beispiel 4 Die Zukunft: SQL:200n4.1 Überblick SQL:200n4.2 neue Feature bei Part2, Part11, Part14


SQL89ENTRY SQL

DATE- und TIME-Datentypen, Varchar, nationale String-Operatoren, CAST, CASE expressions, CREATE DOMAIN, ALTER TABLE, DROP, CASCADE, JOIN-Typen NATURAL, OUTER und UNION JOIN, SET TRANSACTION options for ISOLATION LEVELUNION, INTERSECTION, Unterabfragen in Ausdrücken und Tab.operationen

INTERMEDIATESQL

Verbindungsmanagement CONNECT, SET CONNECTION and DISCONNECTBIT-Datentyp, vordefinierte Zeichentransformationenabgeleitete Tabellen im FROM-AbschnittUnterabfragen in CHECK-Klauseln, ALTER DOMAIN, ASSERTION constrainttemporäre Tabellen, MATCH FULL und MATCH PARTIAL in referential constraintcharacter set collations and translationsUNION JOIN und CROSS JOINvoll änderbarer Cursors, nichtsensitiver Cursor,

FULL SQL

Niveau‘s von SQL:1992 (Beispiele)


Entwicklung SQL:1999

SQL 3: 1999

2000

2001

SQL:1999

2: Foundation

OLAP Erweiterungen

1: Framework

5: Bindings4: PSM3: CLI

2002

Technische Berichtigungen

SQL/MED (Part 9)

SQL/OLB (Part 10)

SQL/JRT (Part 13)

Technische Berichtigungen


SQL-Standard und DBMS

• Bis 1992 enthielt der Standard minimale Anforderungen, die die gängigen DBMS weitgehend erfüllten

• 1992 erster „kompletter“ Standard DBMS erfüllen die Niveau‘s „intermediate“ und „full“ nur unzureichend /Melt05/

• SQL:1999 nimmt sehr viele neuen Anforderungen auf,nach zu langer Entwicklungszeit nicht der wahre Erfolg, /Melt05/

Standard nicht umfangreich umgesetzt DBMS „hinken“ hinterher

Abweichungen in 2 Richtungen: Befehle werden nur teilweise implementiert oder sind nicht in der standardisierten Form vorhanden.Es wird der Funktionsumfang in inkompatibler Weise erweitert, um sich von der Konkurrenz abzugrenzen bzw. einen Marktvorteil zu erreichen.


Funktionsexplosion beim Standard und bei DBMS

NeueAnforderungen

SQL:1999 Objektorientierung

Internet

Multimedia

Content-management

Data WarehouseOLAP / Data Mining

Information Retrieval

XML

Java

Strukturen in TabellenStrukturen zwischen Tabellen


SQL-Standard und DBMS

Meist ist der Standard den DBMS voraus.Aber in einzelnen Feature sind DBMS der Standardisierungvoraus (dargestellt am Beispiel von SYBASE)- Stored Procedure- Trigger- Benutzerdefinierte Datentypen- ALTER/DROP TABLE- Replikation- Einbindung von XQuery-Ausdrücken in SQL

(mit XMLEXTRACT)


Gliederung

1 Die Anfänge: SQL:1986, SQL:19892 Die Entwicklung2.1 SQL:19922.2 SQL:19992.3 SQL-Standard und DBMS-Entwicklung3 Der Stand: SQL:20033.1 Überblick SQL:20033.2 Feature und Konformität der DBMS am Beispiel 4 Die Zukunft: SQL:200n4.1 Überblick SQL:200n4.2 neue Feature bei Part2, Part11, Part14


SQL:2003

SQL 4 (SQL:2003) endgültig 2005 mit den folgenden 9 Teilen als ISO/IEC 9075-Teile-Nr.:2003 veröffentlicht:/Melt03a/

• Part 1 – SQL/Framework Rahmen• Part 2 – SQL/Foundation Grundlagen• Part 3 – SQL/CLI Call Level Interface• Part 4 – SQL/PSM Persistent Stored Modules• Part 9 – SQL/MED Management of External Data• Part 10 – SQL/OLB Object Language Bindings• Part 11 – SQL/Schemata Schemata• Part 13 – SQL/JRT Java Routines and Types• Part 14 – SQL/XML Extensible Markup Language


SQL:2003

• Wesentliche Struktur-Änderungen– Bindings in Foundation integriert– Information/Definition Schema wurde zu einem separatem

Part (Schemata)– Neuer Part: SQL/XML


„verlorene“ Teile – „Ideengräber“?

• Part 5 – SQL/Bindings– 2003 zurück in Foundation

• Part 6 – SQL/Transaction– 1999 eingestellt

• Part 7 – SQL/Temporal– 2003 zurück gezogen

• Part 8 – SQL/Objects– 1999 zurück in Foundation

• Part 12 – SQL/Replication– Abgebrochen wegen Mängel beim Entwicklungprozess


Überblick SQL:2003 /Dess05/

3: CLI 4: PSM 9: MED 10: OLB 13: JRT 14: XML

1: Framework

(4) PSM

11: Schemata

2: Foundation

Core SQL

(1) Enhanced Date/Time Fac.

(2) Enhanced Integrity Management

(8) Active Databases

(7) Enhanced Objects

(6) Basic Objects (10) OLAPoptionale

Features

verbindliche Features

n: xxx ... ein Part (n) xxx ... ein Package


SQL 2003 :Standard

SQL: 2003 StandardTotal Fetaures : 573

1%

5%

4%

2%

2%

3%9%

0%

74%

Framework (Packages)Foundation (420)CLI (8)PSM (30)MED (25)OLB (9)Schemata (11)JRT (17)XML (53)


Feature von Core SQL:2003

Core SQL:2003 Features• SQL:92 Features

– Alle des SQL:92 Entry-Levels– Einige Intermediate SQL:92 Features– Einige Full SQL:92 Features

• SQL:1999 Features• SQL:2003 hat keine neuen Features zum Core

hinzugefügt


Neue Funktionalitäten von SQL-Foundation(Auswahl)

Überblick• SQL:2003/ Foundation enthält alle Feature von SQL:1999/ Foundation

(mit vielen Korrekturen - SQL:2003 „bugfix-release“) /Melt05/

sowie eine Reihe von neuen Features– SQL:1999 Part 5: SQL/Bindings und Part 8 :SQL/Objects wurden integriert

• Neue Datentypen– BIGINT– MULTISET

• Erweiterungen existenter Datentypen– unbegrenztes ARRAY

• Löschung existenter Datentypen– BIT– BIT VARYING

• Neue Schemaobjekte– Sequenzgeneratoren (inkrementelle Erzeugung von Werten)



Überblick• Verbesserungen existenter Schemaobjekte

– Identity Spalten für Tabellen (Generierung von Schlüsselwerten)– Generierte Spalten für Tabellen

• Werte werden von anderen Attributwerten abgeleitet• Nicht konform zur 3. Normalform

– Umfangreiche Verbesserungen bei CREATE TABLE LIKE– Basistabellendefinition mittels Anfrage (materialisierte Tabellen)– retrospektive Check Constraints– ALTER-Funktionalität für Transformierungen– SQL-invoked Routines mit Rechten des Aufrufenden– Table Functions (tabllenwertige Funktionen)– Dynamische und Schema Statements innerhalb Routinenkörper



Überblick• Neue Funktionen (z. B. zahlreiche OLAP Funktionen“)• Ausdrücke

– Erweiterungen bei skalaren Ausdrücke und Anfrageausdrücken– Multiset-Ausdrücke– Anfragen auf Tabellensamples

• Erweiterung der Routinenaufrufe• Neue Prädikate und DML Statements (z.B. Statement zur Batch-

Verarbeitung von INSERT und UPDATE Statements)• Geschachtelte Savepoints• Verbessertes Diagnose-Management• Verbesserungen bei PREPARE Statement


Neue Funktionalitäten von SQL-Foundation

Sequenzgeneratoren• Sequenzgeneratoren werden primär zur Generierung

von künstlichen Schlüsseln, beim Einfügen neuer Tupel in Tabellen, eingesetzt

• Syntax

• Bei der Wertdefinition sind Restriktionen zu beachten

CREATE SEQUENCE < sequence name > AS <typename>[START WITH <initial value>][INCREMENT BY <increment value >][NO MINVALUE|MINVALUE <minimal value>][NO MAXVALUE|MAXVALUE <maximal value>][NO CYCLE |CYCLE]



Sequenzgeneratoren• Zum automatischen Generieren von sequentiellen Werten genutzt• Zwei Arten:

– Extern: Explizites Schemaobjekt mit CREATE Statement erzeugt– Intern: Implizit mit einer Identity Spalte erzeugt

• Generierung eines weiteren Wertes des Sequenzgenerators

• ALTER Statement zum Ändern der Schrittweite, Maximum-, Minimum-Wert, Zyklus-Option oder eines neuen Basiswertes

• DROP SEQUENCE Statement löscht einen Sequenzgenerator

INSERT INTO Mit (Mitnr,...) VALUES (NEXT VALUE FOR < sequence name >, …)

ALTER SEQUENCE < sequence name > RESTART WITH 500 INCREMENT BY 2



Identity Spalten• Maximal eine Spalte einer Basistabelle kann zu einer

Identity Spalte designiert werden• Werte für eine Identity Spalte werden automatisch

zugewiesen, wenn eine Zeile in die Tabelle eingefügt wird• Ein interner Sequenzgenerator wird mit jeder Identity

Spalte assoziiert (lediglich konzeptionell - dies muss nicht zwingend so implementiert sein)

• Gleiche Optionen wie für Sequenzgenerator – Datentyp, Startwert, Schrittweite, Maximum-Wert, Minimum-Wert

und Zyklus-Option



Identity Spalten• Beispiel

– Identity Spalte nutzt einen internen Sequenzgenerator

CREATE TABLE Mit(

Mitnr INTEGER GENERATED ALWAYS AS IDENTITYSTART WITH 100INCREMENT 1MINVALUE 100NO MAXVALUENO CYCLE,

Name VARCHAR(64), ... )



Generierte Spalten• Beliebige Anzahl von Spalten einer Basistabelle kann

als generierte Spalte designiert werden• Muss mit einem skalaren Ausdruck assoziert werden

– Referenzierte Spalten in solchen Ausdrücken müssen entweder Spalten der Basistabelle oder die generierte Spalte selbst sein

• Falls ein Typ spezifiziert wird muss dieser mit dem Typ des Ausdrucks korrespondieren

• Die Werte der generierten Spalte werden automatisch bei dem Einfügen einer Zeile errechnet und zugewiesen

• Widerspricht der 3. Normalform



Generierte Spalten• Beispiel

CREATE TABLE Mit

(

Mitnr INTEGER,

Gehalt DECIMAL(7,2),

Bonus DECIMAL(7,2),

GesamtEinkommen GENERATED ALWAYS AS ( Gehalt + Bonus ),

Sachbearbeiter GENERATED ALWAYS AS ( CURRENT_USER )

)



Tabellendefinition mittels Anfragen• Tabellen können einfach erstellt werden indem das Ergebnis einer

Anfrage persistent gemacht wird• Eigenschaften der Spalten werden von den Basistabellen der

Anfrage abgeleitet• Die Definition und/oder der Inhalt einer Basistabelle kann mit

einem Anfrageausdruck generiert werden– Falls WITH NO DATA spezifiziert ist, wird eine leere Tabelle erstellt

• "Materialisierte Sicht" - Aktualisierungsmechanismus ist bislang nicht standardisiert

• Syntax

– <predicate> meint hier den Anfrageausdruck

CREATE TABLE <tablename> [(columnnamelist)]AS <predicate> WITH [NO] DATA


Konformität der DBMS zum Standard /Dess05/

Konformität zu SQL:2003• SQL:92 nutzt inkrementelle Ebenen der Konformität

(Entry, Intermediate, Full)– Kein DBMS erreichte Intermediate oder Full /Dess05/, /sql-06/

• SQL:1999 und SQL:2003 bestehen aus einer Vielzahl von kleinen “Features“, welche identifiziert und deren Inhalte präzise spezifiziert sind

• jedes Feature ist entweder als Bestandteil von “Core SQL“oder nicht als Bestandteil von Core SQL spezifiziert

• Ein “non-core feature“ kann als Bestandteil eines der benannten und definierten “Packages“ spezifiziert sein, welche alle auf der Konformität zu Core SQL aufbauen


Konformität der DBMS zum Standard

Konformität zu SQL:2003• Implementierungen können folgende Ebenen erreichen

– Minimum conformance (Core SQL)• Konformität zu bestimmten Features des Part 2 (Foundation) und 11

(Schema)– Conformance to a part (0 oder mehrere)

• Verbindliche Features und zusätzl. Konformitätserfordernisse eines Parts – Conformance to a package (0 oder mehrere)

• Ein Package ist eine Gruppe optionaler Features• Teile und Feature erfordert durch ein Package

– Conformance to optional feature (0 oder mehrere)• Kann Konformität zu anderen Features eines Parts implizieren

• Kein DBMS erreicht Minimum conformance/Dess05/


Part 2 – SQL/Foundation; Data Types


Konformität der neuen Funktionalitäten von SQL-Foundation

Feature ID/ Name DB2UDB v.9.1

Microsoft SQLServer 2005

Oracle10g R2

SybaseASE 15

T071 - BIGINT data type Unterstützt Unterstützt - Unterstützt

F312 - MERGE statement Unterstützt - Partielle StützeUnterstützt

(Teilmenge –Merge joins )

T172 - AS subquery clause in table definition Unterstützt - -

T174 - Identity columns Unterstützt Unterstützt - Unterstützt

T175 - Generated columns Unterstützt Unterstützt Partielle Stütze Unterstützt

T176 - Sequence generatorsupport Unterstützt - (proprietäre

Syntax) Unterstützt

T641 - Multiple columnassignment Unterstützt - - -

T272 - Enhanced savepointmanagement - Unterstützt - Unterstützt

/Dess05/


Konformität bei Core-SQL /IBM06/, /Micr06/, /Orac05/, /Syba06/, [1]

Core SQL Features

141143152141

19 10 20 1824 51

020406080

100120140160180

Oracle 10g DB2 V9 Sybase ASE15 MS SQL Server2005RDBMS

Feat

ures

(164

)

Full Support Partial Support No Support

97.6%98.8%

99.4% 96.9%

2.4% 1.2% 0.6% 3.1%

Full Support = Same semantic and syntaxPartial Support = Semantic/Syntax support with different Syntax/Semantic

No Support = Feature not implemented


Konformität bei SQL-Core

Feature ID

Feature descriptionOracle

10gDB2 V9

Sybase ASE 15

MS SQL 2005

E011 Numeric data typesE011-01 INTEGER and SMALLINT data types

(including all spellings)

E011-02 REAL, DOUBLE PRECISION, and FLOAT data types

E011-03 DECIMAL and NUMERIC data types

E011-04 Arithmetic operators

E011-05 Numeric comparison

E011-06 Implicit casting among the numeric data types

Legend : Fully Supported Not Supported Partially Supported


Konformität bei Core-SQL

Feature ID


10gDB2 V9 Sybase

ASE 15MS

SQL 2005

E021 Character string typesE021-01 CHARACTER data type (including all its spellings)

E021-02 CHARACTER VARYING data type (including all its spellings)

E021-03 Character literals

E021-04 CHARACTER_LENGTH function

E021-05 OCTET_LENGTH function

E021-06 SUBSTRING function

E021-07 Character concatenation

E021-08 UPPER and LOWER functions

E021-09 TRIM function

E021-10 Implicit casting among the fixed and variable length character string types

E021-11 POSITION function

E021-12 Character comparison



Feature ID


10gDB2 V9

Sybase ASE 15

MS SQL 2005

E031 IdentifiersE031-01 Delimited identifiers

E031-02 Lower case identifiers

E031-03 Trailing underscore

E051 Basic query specificationE051-01 SELECT DISTINCT

E051-02 GROUP BY clause

E051-04 GROUP BY can contain columns not in <select list>

E051-05 Select list items can be renamed

E051-06 HAVING clause

E051-07 Qualified * in select list

E051-08 Correlation names in the FROM clause

E051-09 Rename columns in the FROM clause



Feature ID


10gDB2 V9

Sybase ASE 15

MS SQL 2005

E061 Basic predicates and search conditionsE061-01 Comparison predicate

E061-02 BETWEEN predicate

E061-03 IN predicate with list of values

E061-04 LIKE predicate

E061-05 LIKE predicate: ESCAPE clause

E061-06 NULL predicate

E061-07 Quantified comparison predicate

E061-08 EXISTS predicate

E061-09 Subqueries in comparison predicate

E061-11 Subqueries in IN predicate

E061-12 Subqueries in quantified comparison predicate

E061-13 Correlated subqueries

E061-14 Search condition



Feature ID Feature description Oracle 10g DB2 V9Sybase ASE 15

MS SQL 2005

E071 Basic query expressions

E071-01 UNION DISTINCT table operator

E071-02 UNION ALL table operator

E071-03 EXCEPT DISTINCT table operator

E071-05 Columns combined via table operators need not have exactly the same data type.

E071-06 Table operators in subqueries

E081 Basic Privileges

E081-01 SELECT privilege at the table level

E081-02 DELETE privilege

E081-03 INSERT privilege at the table level

E081-04 UPDATE privilege at the table level

E081-05 UPDATE privilege at the column level

E081-06 REFERENCES privilege at the table level

E081-07 REFERENCES privilege at the column level

E081-08 WITH GRANT OPTION

E081-09 USAGE privilege

E081-10 EXECUTE privilege



Feature ID


10gDB2 V9

Sybase ASE 15

MS SQL 2005

E091 Set functionsE091-01 AVG

E091-02 COUNT

E091-03 MAX

E091-04 MIN

E091-05 SUM

E091-06 ALL quantifier

E091-07 DISTINCT quantifier

E101 Basic data manipulationE101-01 INSERT statement

E101-03 Searched UPDATE statement

E101-04 Searched DELETE statement

E111 Single row SELECT statement



Feature ID

Feature descriptionOracle 10g DB2

V9Sybase ASE 15

MS SQL 2005

E121 Basic cursor supportE121-01 DECLARE CURSOR

E121-02 ORDER BY columns need not be in select list

E121-03 Value expressions in ORDER BY clause

E121-04 OPEN statement

E121-06 Positioned UPDATE statement

E121-07 Positioned DELETE statement

E121-08 CLOSE statement

E121-10 FETCH statement: implicit NEXT

E121-17 WITH HOLD cursors



Feature ID


10gDB2 V9 Sybase

ASE 15MS

SQL 2005

E131 Null value support (nulls in lieu of values)E141 Basic integrity constraints

E141-01 NOT NULL constraints

E141-02 UNIQUE constraints of NOT NULL columns

E141-03 PRIMARY KEY constraints

E141-04 Basic FOREIGN KEY constraint with the NO ACTION default for both referential delete action and referential

update action.

E141-06 CHECK constraints

E141-07 Column defaults

E141-08 NOT NULL inferred on PRIMARY KEY

E141-10 Names in a foreign key can be specified in any order

E151 Transaction supportE151-01 COMMIT statement

E151-02 ROLLBACK statement



Feature ID Feature descriptionOracle 10g DB2 V9 Sybase

ASE 15MS SQL

2005

E152 Basic SET TRANSACTION statement

E152-01 SET TRANSACTION statement: ISOLATION LEVEL SERIALIZABLE clause

E152-02 SET TRANSACTION statement: READ ONLY and READ WRITE clauses

E153 Updatable queries with subqueries

E161 SQL comments using leading double minus

E171 SQLSTATE support

E182 Module language

F031 Basic schema manipulation

F031-01 CREATE TABLE statement to create persistent base tables

F031-02 CREATE VIEW statement

F031-03 GRANT statement

F031-04 ALTER TABLE statement: ADD COLUMN clause

F031-13 DROP TABLE statement: RESTRICT clause

F031-16 DROP VIEW statement: RESTRICT clause

F031-19 REVOKE statement: RESTRICT clause



Feature ID Feature descriptionOracle

10gDB2 V9 Sybase

ASE 15MS SQL 2005

F041 Basic joined table

F041-01 Inner join (but not necessarily the INNER keyword)

F041-02 INNER keyword

F041-03 LEFT OUTER JOIN

F041-04 RIGHT OUTER JOIN

F041-05 Outer joins can be nested

F041-07 The inner table in a left or right outer join can also be used in an inner join

F041-08 All comparison operators are supported (rather than just =)

F051 Basic date and time

F051-01 DATE data type (including support of DATE literal)

F051-02 TIME data type (including support of TIME literal)

F051-03 TIMESTAMP data type (including support of TIMESTAMP literal)

F051-04 Comparison predicate on DATE, TIME, and TIMESTAMP data types

F051-05 Explicit CAST between datetime types and character string types

F051-06 CURRENT_DATE

F051-07 LOCALTIME

F051-08 LOCALTIMESTAMP




10gDB2 V9

Sybase ASE 15

MS SQL 2005

F081 UNION and EXCEPT in views

F131 Grouped operations

F131-01 WHERE, GROUP BY, and HAVING clauses supported in queries with grouped views

F131-02 Multiple tables supported in queries with grouped views

F131-03 Set functions supported in queries with grouped views

F131-04 Subqueries with GROUP BY and HAVING clauses and grouped views

F131-05 Single row SELECT with GROUP BY and HAVING clauses and grouped views

F181 Multiple module support

F201 CAST function

F221 Explicit defaults

F261 CASE expression

F261-01 Simple CASE

F261-02 Searched CASE

F261-03 NULLIF

F261-04 COALESCE




10gDB2 V9

Sybase ASE 15

MS SQL 2005

F311 Schema definition statement

F311-01 Create Schema

F311-02 CREATE TABLE for persistentbase tables

F311-03 Create View

F311-04 Create view with option

F311-05 Grant Statement

F471 Scalar subquery values

F481 Expanded NULL predicate

F812 Basic flagging SQL -92

S011 Distinct data types



Feature ID


10gDB2 V9

Sybase ASE 15

MS SQL 2005

T321 Basic SQL-invoked routines

T321-01 User-defined functions with no overloading Syntactic

T321-02 User-defined stored procedures with no overloading Syntactic

T321-03 Function invocation

T321-04 CALL statement

T321-05 RETURN statement

T631 IN predicate with one list element

Legend Fully Supported Not Supported Partially Supported


Part 11 – SQL/Schemata /IBM06/, /Micr06/, /Orac05/, /Syba06/ , [2]

SQL/Schemata - Mandatory Features

0

1010

12

9

1335

31

7

02468

10121416

Oracle 10g DB2 V9 Sybase ASE15 MS SQL Server2005

RDBMS

Feat

ures

(16)


56.2%75%

62.5% 62.5%

• Full Support = Same semantic and syntax• Partial Support = Semantic/Syntax support with different Syntax/Semantic• No Support = Feature not implemented

SQL/Schemata - Mandatory Features

0

9

1211

8

3 23

7

1 35

02468

10121416

Oracle 10g DB2 V9 Sybase ASE15 MS SQL Server 2005

RDBMS

Feat

ures

(16)


56.2%93.75% 81.25%

68.75%43.8%

6.25% 18.75%31.25%


SQL/Schemata – Mandatory Conformance

FeatureID Feature Description Oracle

10gDB2V9

SybaseASE 15

MS SQL 2005

F021 Basic information schema

F021-01 COLUMNS view

F021-02 TABLES view

F021-03 VIEWS view

F021-04 TABLE_CONSTRAINTS view

F021-05 REFERENTIAL_CONSTRAINTS view

F021-06 CHECK_CONSTRAINTS view

F501 Features and conformance views

F501-01 SQL_FEATURES view

F501-02 SQL_SIZING view

F501-03 SQL_LANGUAGES view

S011 Distinct data types

S011-01 USER_DEFINED_TYPES view

T321 Basic SQL-invoked routines

T321-06 ROUTINES view

T321-07 PARAMETERS view

Full Supported Partial Support Not Supported


Part 11 – SQL/Schemata /IBM06/, /Micr06/, /Orac05/, /Syba06/, [2]

SQL/Schemata - Optional Features

3

9

655

2

4 54

1 2 202

46

810

12

Oracle 10g DB2 V9 Sybase ASE15 MS SQL Server 2005

RDBMS

Feat

ures

(12)


66.7%

91.6%

83.3% 83.3%33.3%

8.4% 16.7%16.7%


SQL/Schemata – Optional Features

Feature ID Feature description Oracle

10gDB2V9

SybaseASE 15

MS SQL 2005

F231 Privilege tables

F341 Usage tables

F391 Long identifiers

F502 Enhanced documentation tables

S023 Basic structured types

S024 Enhanced structured types

T011 Timestamp in Information Schema

T175 Generated columns

T176 Sequence generator support

T211 Basic trigger capability

T272 Enhanced savepoint management

T331 Basic Roles

Full Supported Partial Support Not Supported


SQL und Java

SQL-Java• SQLJ Part 0: Embedded SQL in Java• SQLJ Part 1: Routinen in Java

– Implementierung von SQL-Routinen in Java

• SQLJ Part 2: SQL-Typen in Java– Implementierung von strukturierten Datentypen in Java

• SQL:2003 und SQL-JavaSQL:2003 SQLJSQL / OLB (Part 10) SQLJ Part 0

SQLJ Part 1SQL / JRT (Part 13)SQLJ Part 2


SQL/JRT (Java Routines and Types)

User-defined routines

Definierte SQL Aufrufe(SQL:2003)

Definierte Implementierung(SQLJ Part 1)

Stored procedure User-defined function

SQL Routine(SQL / PSM)

External Routine(C, Java, ...)

Java Klassen

( - IN/OUT-Parameter- dynamisches

ResultSet- CALL-Statement )

(Java Klassen implementieren Verhalten von SP und UDF komp. Javacode in DB installiert)

( - Ausschließlich IN-Parameter- definierte Datentypen für Ergebnis- innerhalb beliebiger DML-Befehle)



SQLJ Part 1: SQL-Routinen die Java nutzen• Statische Java Methoden um SP und UDF zu implementieren

– Konvertierung der Parametertypen, Error-/Exception Handler– Stored Procedure: einige Spezifika

• OUT und INOUT Parameter (Return Parameter)– Java Methode deklariert diese als Array

• Zurückgegebene Ergebnismenge(n)– Java Methode deklariert Parameter für zurückgegebene Ergebnismenge

explizit als Array von ResultSet (JDBC) und Array von Iterator (SQLJ)– Mehrere Ergebnismengen können zurückgegeben werden

– Methodenkörper kann JDBC und/oder SQLJ enthalten

CREATE PROCEDURE avgGehalt(IN gruppe VARCHAR(30), OUT gehalt DECIMAL(10,2))public static void durchschnittsGehalt(String gruppe, BigDecimal[] gehalt);

CREATE PROCEDURE rang_mit(region INTEGER) DYNAMIC RESULT SETS 1 ...public void rang_mitarbeiter(int region, ResultSet[] rs);



SQLJ Part 1: SQL-Routinen die Java nutzen• Beispiel: Implementierung von user-defined routines

– Java Klasse implementiert das Verhalten der Prozedur

– Erstellen einer JAR-Datei (Java Archive)

– Installation der SQLJ JAR-Datei in der Datenbank

– Vollständige DDL zur Generierung der Prozedur

public class MitProzedur{public static void avgGehalt(String beruf, Integer gehalt) throws SQLException{

#sql {SELECT AVG(Gehalt) INTO :gehalt FROM Mit WHERE Beruf = :beruf};}}

JAVA_HOME="/usr/java/jdk1.5.0_01" export JAVA_HOME jar -cf /home/user/classes/Mit.jar MitProzedur.class META-INF

CREATE PROCEDURE avgGehalt(IN beruf VARCHAR(30), OUT gehalt NUMBER(10))EXTERNAL NAME ‘Mit_jar:MitProzedur.avgGehalt‘LANGUAGE JAVAPARAMETER STYLE JAVA

CALL SQLJ.INSTALL_JAR(‘file:/home/user/classes/Mit.jar‘, ‘Mit_jar‘);



SQLJ Part 2: SQL Typen die Java nutzen• Nutzung von Java Klassen um SQL-Typen zu definieren

– Referenz zur strukturierten / nativen SQL-Typen (BLOBs)– Für die Definition von Spalten und SQL typed tables (SDT) verwendet

• Mapping des Objektstatus und -verhaltens– Java Methode wird zur SQL Methode des SQL Typs– Java Methoden sind innerhalb von SQL-Statements aufrufbar

• Automatisches Mapping zum Java Objekt beim Auslesen des Cursors und beim Methodenaufruf– Java Serialisierung– JDBC 2.0 SQLData Interface

• Einschließlich Handhabung des USAGE Privileges des SQL Typs• Enthält die Nutzung von Prozeduren zum Installieren, Entfernen und

Ersetzen von SQLJ JAR Dateien


5.3.3.4 SQL/JRT (Java Routines and Types)

SQLJ Part 2: SQL Typen die Java nutzen• Beispiel: Java Klasse

public class Adresse implements Serializable, SQLData{

public String Nr;public String Strasse;public String Ort;public String Plz;public static String Land = “D“;

public String ausgabeAdresse() {...};public void aendereAdresse(String adr) {... //Segmentierung & Feldänderungen …} public void readSQL(SQLInput in, String type) {...};public void writeSQL(SQLOutput out) {...};

}


5.3.3.4 SQL/JRT (Java Routines and Types)

SQLJ Part 2: SQL Typen die Java nutzen• Beispiel: SQL DDL

CREATE TYPE Adresse EXTERNAL NAME ‘adresse_jar:Adresse‘ LANGUAGE JAVA(

Nr VARCHAR(3) EXTERNAL NAME ‘Nr‘,Strasse VARCHAR(30) EXTERNAL NAME ‘Strasse‘,Stadt VARCHAR(30) EXTERNAL NAME ‘Ort‘,PLZ VARCHAR(30) EXTERNAL NAME ‘Plz‘,STATIC METHOD Land() RETURNS VARCHAR(30)

EXTERNAL VARIABLE NAME ‘Land‘,METHOD ausgabeAdresse() RETURNS VARCHAR(100)

EXTERNAL NAME ‘ausgabeAdresse‘,METHOD aendereAdresse(VARCHAR(100)) RETURNS Adresse

SELF AS RESULT EXTERNAL NAME ‘aendereAdresse‘)


Konformität bei SQL/JRT /Melt03b/, /IBM06/, /Micr06/, /Orac05/, /Syba06/, /Syba05/, [3]

JRT Features

1

1312

9

14 2 3 1

15

3402468

1012141618

Oracle 10g DB 2 V9 Sybase ASE15 MS SQL Server2005

RDBMS

Feat

ures

(17)


76.4%

23.6%

82.3%

17.7%

94.1%

5.9%

11.7%

88.3%


Minimale Konformität

At least one of

Feature ID Feature description Oracle 10g DB2 V9 Sybase ASE 15

MS SQL 2005

J621 External Java Routines

J541 SERIALIZABLE

J551 SQLDATA

At least one of

J511 Commands

J531 Deployment JDBC

Supported Partial Support Not Supported - Unknown


Optionale Konformität

Feature ID Feature description Oracle 10g DB2 V9 Sybase ASE 15

MS SQL 2005

J521 JDBC data types Type 4 Type 4

J551 SQLDATA

J561 JAR privileges

J571 NEW operator Using EJB

J581 Output parameters

J591 Overloading

J601 SQL-Java paths

J611 References

J622 External Java types

J631 Java signatures

J641 Static fields

J651 SQL/JRT Information Schema

J652 SQL/JRT Usage tables



Part 14 : SQL/XML - Hauptbestandeile

- Neuer Datentyp XML- Operationen auf dem Datentyp XML, aber keine

Abfragemöglichkeiten XQUERY (SQL:2007)- Abbildungsverfahren SQL XML- Abbildung von SQL-Konzepten auf XML

- Zeichensätzen und Namen- vordefinierten Datentypen und Namen- benutzerdefinierten Datentypen und Namen- SQL-Tabelle- SQL-Schema und Katalog


XML vs SQL /Schö07/

XML SQLHierarchische Struktur Flache Struktur

Loses Schema,Kein Schema notwendig

Festes Schema

case-senstive Namen case-insensitive Namen

42 BasisdatentypenVerschiedene Typkonzepte

20 BasisdatentypenVerschiedene Typkonzepte

Strikte Unicode-Basierung Verschiedene Codierungen

geordnet Ungeordnet möglich

XQuery: Suche in Bäumen von XDM-Knoten

SQL: Suche in Mengen von Tupeln


SQL/XML Datentyp und Operationen

XML PARSE

XMLCONCAT(XML->XML)

XMLSERIALIZEXMLELEMENTXMLFORESTXMLCOMMENTXMLDOCUMENTXMLAGG


SQL/XML Operationen /Schö07/

• XMLDOCUMENT erzeugt aus einem Wert vom Typ XML einen XML- Dokumentknoten

• XMLPARSE wandelt SQL-Werte in einen XML-Wert• XMLELEMENT erzeugt aus SQL-Werten einen XML-

Elementknoten vom Typ XML• XMLFOREST erzeugt aus einer Tabelle eine Folge von

XML-Elementknoten• XMLCOMMENT erzeugt einen XML-Kommentarknoten• XMLAGG aggregiert Ergebnisse über mehrere Zeilen


SQL/XML Operationen /Schö07/

• XMLSERIALIZE wandelt einen XML-Wert in einen SQL-String um

• XMLCONCAT verkettet Werte vom Typ XML(XML->XML)


Konformität bei SQL/XML /IBM06/, /Micr06/, /Orac05/, /Syba06/ , [4]

XML Features

25

36

9 9 102 2 6

26

15

3834

06

1218243036424854


RDBMS

Feat

ures

(53)


50.9%71.7%

28.3%

71.7%

35.8%

64.2%49.1%

28.3%


Minimale Konformität

Feature ID Feature description Oracle 10g

DB2 V9

Sybase ASE

15

MS SQL 2005

X010 XML type XMLType XML

X031 XMLElement Extract Query

X032 XMLForest Extract Value

X034 XMLAgg Value

X070 XMLSerialize: CONTENT option Query

X071 XMLSerialize: DOCUMENT option Query

X100 Host language support for XML: CONTENT option

X101 Host language support for XML: DOCUMENT option





DB2 V9

Sybase ASE 15

MS SQL 2005

X011 Arrays of XML type varray

X012 Multisets of XML type

X013 Distinct types of XML

X014 Attributes of XML type

X015 Fields of XML type

X016 Persistent XML values Text XML

X020 XML Concatenation

X033 XMLRoot

X035 XMLAgg: ORDER BY option Value

X041 Basic table mapping: null absent

X042 Basic table mapping: null as nil

X043 Basic table mapping: table as forest Value





DB2 V9

Sybase ASE 15

MS SQL 2005

X044 Basic table mapping: table as element Query

X045 Basic table mapping: with target namespace

X046 Basic table mapping: data mapping

X047 Basic table mapping: metadata mapping -

X048 Basic table mapping: base64 encoding of binary strings

X049 Basic table mapping: hex encoding of binary strings -

X051 Advanced table mapping: null absent

X052 Advanced table mapping: null as nil

X053 Advanced table mapping: table as forest

X054 Advanced table mapping: table as element

X055 Advanced table mapping: with target namespace




Feature ID Feature description Oracle 10g DB2 V9 Sybase

ASE 15

MS SQL 2005

X056 Advanced table mapping: data mapping - - -

X057 Advanced table mapping: metadata mapping - - -

X058 Advanced table mapping: base64 encoding of binary strings - -

X059 Advanced table mapping:hex encoding of binary strings DB2 XML

X060 XMLParse: CONTENT option

X061 XMLParse: DOCUMENT option

X062 XMLParse: explicit WHITESPACE option

X080 Namespaces in XML publishing

X081 Query-level XML namespace declarations

X082 XML namespace declarations in DML

X083 XML namespace declarations in DDL





DB2 V9

Sybase ASE 15

MS SQL 2005

X084 XML namespace declarationsin compound statements

X090 XML document predicate Fragment

X110 Host language support for XML: VARCHAR mapping

X111 Host language support for XML: CLOB mapping

X120 XML parameters in SQL routines

X121 XML parameters in external routines

X131 Query-level XMLBINARY clause - - - -

X132 XMLBINARY clause in DML - - - -

X133 XMLBINARY clause in DDL - - - -

X134 XMLBINARY clause in compound statements - - - -

X135 XMLBINARY clause in subqueries - - - -



Stufen der praktischen Umsetzung von SQL/XML

• (Shreddern von XML-Dokumenten in Tabellenspalten)• (Abspeicherung von XML-Dokumenten in LOB-Spalten)• XML-Datentyp und XML-Operationen als erweiterte DB-

Server-Funktionalität(z. B. Oracle 10g, Sybase ASE 15, MS SQL 2005)

• Hybridserver(z. B. DB2 V9)• Native XML-Server(z. B. Tamino, Natrix, Xindice,

Infonyte)


DB2 Version 9 - der Hybridserver

/Seub06/


Gliederung

1 Die Anfänge: SQL:19862 Die Entwicklung2.1 SQL:19922.2 SQL:19992.3 SQL-Standard und DBMS-Entwicklung3 Der Stand: SQL:20033.1 Überblick SQL:20033.2 Feature und Konformität der DBMS am Beispiel 4 Die Zukunft: SQL:200n4.1 Überblick SQL:200n4.2 neue Feature bei Part2, Part11, Part 14


Zeitliche Entwicklung des SQL-StandardParts SQL 1992 SQL 1999 SQL 2003 SQL 2007

Part 1 - SQL/Framework Completed Completed Completed

Part 2 - SQL/Foundation

Completed(includes bindings

and schema information tables)

Completed

Completed(SQL/Bindings merged in

schema information tables separated out)

Completed (Minor

changes in syntax)

SQL/OLAP Processed as an amendment to SQL/Foundation

Part 3 –SQL/CLI

Completed in '95 as an expansion Completed Completed Completed

Part 4 –SQL/PSM

Completed in '96 as an expansion

Completed (stored routines and call statement moved to

SQL/Foundation)Completed

Completed(Revised server module definition & syntax change)

Part 5 - SQL/Bindings

Specification for embedding SQL in programming

languages moved to a separate part.

SQL/Bindings merged back into SQL/Foundation

Part 6 - SQL/Transaction project canceled

Part 7 - SQL/Temporal withdrawn


Zeitliche Entwicklung des SQL-Standard

Parts SQL 1992 SQL 1999 SQL 2003 SQL 2007

Part 9 -SQL/MED

ISO version based on SQL'99 in process Revision completed

Revision Completed( with minor & Major Technical Problems)

Part 10 -SQL/OLB

completed in '98 as an ANSI only

standard

ISO version based on SQL'99 completed Revision completed Revision Completed

Part 11 - SQL/Schemata

Schema information tables extracted from SQL'99

completedCompleted

Part 12 - SQL/Replication

Became a project in 2000, with goal of defining syntax and semantics to support

definition of replication schemes and rules, including rules for resolution of update

conflicts.

Dropped due to lack of progress.

Part 13 -SQL/JRT

completed in '99 as an ANSI only

standardRevision completed Completed

Part 14 -SQL/XML Completed

Expansion completed in 2006. Revision coordinated with

other parts for 2007.


Entwicklung des Umfanges

Part SQL 1992 Pages SQL 1999 Pages SQL 2003 Pages SQL 2007 Pages

Part 1 - SQL/ Framework 85 81 84

Part 2 - SQL Foundation 628 1,147 1,267 1,328

Part 3 - SQL/CLI 236 421 405 390

Part 4 - SQL/PSM 256 170 184 190

Part 5 - SQL/ Bindings 261

Part 9 - SQL/MED 498 486

Part 10 - SQL/OLB 405 396

Part 11 - SQL/ Schemata 296 294

Part 13 - SQL/JRT 204 200

Part 14 - SQL/XML 266 428

Total 1,120 2,084 3,606 3,796


SQL:200n

• Struktur und wesentliche „alte“ Inhalte bleiben gleich• Umfangreiche Entwicklungen bei SQL/XML• Änderungen bei SQL/Foundation• Geringe Änerungen bei SQL/Schema


Überblick SQL:200n

3: CLI 4: PSM 9: MED 10: OLB 13: JRT 14: XML

1: Framework

(4) PSM

11: Schemata

2: Foundation

Core SQL

(1) Enhanced Date/Time Fac.

(2) Enhanced Integrity Management

(8) Active Databases

(7) Enhanced Objects

(6) Basic Objects (10) OLAPoptionale

Features

verbindliche Features

n: xxx ... ein Part (n) xxx ... ein Package


SQL 200n :Standard

SQL : 200n StandardTotal Features : 674

0%

65%1%

4%

4%

1%

2%

3%

20%Framework (Packages)

Foundation (440)

CLI (8)

PSM (30)

MED (25)

OLB (9)

Schemata (11)

JRT (17)

XML (134)


Neue Features in SQL:200n Standard

• Änderungen in– Part 2 SQL/Foundation– Part 11 SQL/Schemata– Part 14 SQL/XML

• Keine Änderungen– Part 1 SQL/Framework– Part 3 SQL/CLI– Part 4 SQL/PSM– Part 9 SQL/MED– Part 10 SQL/OLB– Part 13 SQL/JRT


Neue Feature von SQL:200n

• BINARY data type• Regular expression support• FIRST n (LIMIT TO n, TOP n, …)?• IEEE 754 “floating decimal” data type ?• Materialized views ?• Ermöglichung von Bulk inserts ?• Unterstützung der “history” (stripped-down temporal) ?• Unterstützung von streaming data ? /Melt05/


Part 2 SQL/Foundation – neue Features(22) /Melt06a/

No Feature Id Feature Name

1 B035 Non-extended descriptor names

2 F122 Enhanced diagnostics management

3 F123 All diagnostics

4 F394 Optional normal form specification

5 F403 Partitioned join tables

6 F762 CURRENT_CATALOG

7 F690 Collation Support

8 F763 CURRENT_SCHEMA

9 F841 LIKE_REGEX predicate

10 F842 OCCURENCES_REGEX function

11 F843 POSITION_REGEX function


Part 2 SQL/Foundation – neue Features(22)

No Feature Id Feature Name12 F844 SUBSTRING_REGEX function

13 F845 TRANSLATE_REGEX function

14 F846 Octet support in regular expression operators

15 F847 Nonconstant regular expressions

16 T021 BINARY and VARBINARY data types

17 T022 Advanced BINARY and VARBINARY data types

18 T024 Compound binary literals

19 T024 Spaces in binary literals

20 T043 Multiplier T

21 T044 Multiplier P

22 T101 Enhanced nullability determination


Part 2 SQL/Foundation – entfernte Features

No Feature Id Feature Name

1 F691 Collation and translation

2 F696 Additional translation documentation


Part 11 SQL/Schemata

• Dropped - Mandatory features – F501-03 Sql_languages View

• Added Features– 12 Features are added from SQL/Foundation– Features like

• Basic Reference type• Basic array support• Basic multiset support• Collation support


SQL/Schemata Conformance – New Features(12) /Melt06b/

SQL: 200n - SQL/Schemata

0 3 2 2

8

57 6

4 4 3 4

02468

10


DBMS

Feat

ures

(12)


66.7%

33.3%66.7%

33.3%

75%

25%66.7%

33.3%


SQL:200n – SQL/Schemata (New Features)

Feature ID Feature DescriptionOracle

10gDB2V9

SybaseASE15

MS SQL Server 2005

F251 Domain support - -

F521 Assertions -

F651 Catalog name qualifiers -

F690 Collation support

F695 Translation support -

F696 Additional translation documentation

- - - -

S041 Basic reference types

S081 Subtables - - -

S091 Basic array support -

S241 Transform functions

S271 Basic multiset support

T332 Basic data type attributes

Legend Supported Partial Support Not Supported - Unspecified


Part 14 SQL/XML

• ISO/IEC 9074-14:2003– Mappings and Publishing Functions

• ISO/IEC 9075-14:2005 (“almost completed”)• – Adds XQuery, including Data Model, Validation• ISO/IEC 9075-14:2007 (planned)• – Full-Text?• – Update XML?• – weiteres? /Melt05/


Part 14 SQL/XML

• Mandatory Features– Modified from SQL/XML optional features

• Optional Features– 83 New features are added– Features like XMLPI, XMLComment are updated– 2 features are dropped

• X033 - XMLRoot• X062 - XMLParse: explicit WHITESPACE option


Part 14 SQL/XML SQL/XML:2003 plus

• XQuery Datenmodel zugrunde gelegtSQL-Basisdatentyp „XML“ basiert jetzt auf dem XQuery-Datenmodell,in Tabellen sind als XML-Werte beliebige XQuery-Sequenzen zulässig(vorher XML-Info-Set-basiertes Datenmodell )

• Modifier zum XML-TypDOCUMENTCONTENTSEQUENCEUNTYPEDANYXMLSCHEMA



• Prädikate zum XML-TypIS [NOT] CONTENT evaluiert, ob ein XML-Wert [k]einDokumentknoten istIS [NOT] DOCUMENT evaluiert, ob ein XML-Wert [k]einwohlgeformtes Dokument istXMLEXISTS prüft, ob ein XQuery-Ausdruck ein nicht leeres Ergebnis liefertIS [NOT] VALID prüft, ob ein XML-Wert [un]gültig in bezug auf ein gegebenes Schema ist



• Integration von XQuery in SQLXQuery-Anfragen können in SQL-Anfragen eingebettet werden(mittels SQL-Operation XMLQUERY)XQUERY wertet einen XQuerry-Ausdruck aus

• XMLTABLE wandelt ein XQuery-Ergebnis in eine SQL-Tabelle um

• XMLVALIDATE validiert einen XML-Wert gegen ein Schema

• XMLPI erzeugt eine Verarbeitungsanweisung• XMLTEXT erzeugt einen Textknoten• Casting zwischen XML-Typen und SQL-Typen


SQL/XML Conformance – New Features(83) /Melt06c/

SQL: 200n - SQL/XML

14

27

9 3

2834

39 3741

22

3543

0

10

20

30

40

50


DBMS

New

Fea

ture

s(83

)


73.5%50.6%

57.8% 48.2%49.4%

26.5%

42.2%51.8%


SQL:200n – SQL/XML (New Features)

Feature ID Feature Name Oracle

10g DB2 V9 SybaseASE15

MS SQLServer2005

X025 XMLCast

X030 XMLDocument

X036 XMLComment

X037 XMLPI

X038 XMLText

X040 Basic table mapping

X050 Advanced table mapping

X065 XMLParse: BLOB input and CONTENT option

X066 XMLParse: BLOB input and DOCUMENT option

X072 XMLSerialize: Character string serialization

X073 XMLSerialize: BLOB serialization and CONTENT option

X074 XMLSerialize: BLOB serialization and DOCUMENT option

X075 XMLSerialize: BLOB serialization

X076 XMLSerialize: VERSION -

X077 XMLSerialize: explicit ENCODING option -




10gDB2 V9

Sybase ASE15

MS SQL Server 2005

X078 XMLSerialize: explicit XML declaration -

X085 Predefined namespace prefixes

X086 XML namespaces declaration in XMLTable -

X091 XML content predicate

X096 XMLExists

X112 Host language support for XML: BLOB mapping

X113 Host language support for XML: STRIP WHITESPACE option

X114 Host language support for XML: PRESERVE WHITESPACE option

X141 IS VALID predicate: data-driven case

X142 IS VALID predicate: ACCORDING TO clause

X143 IS VALID predicate: ELEMENT clause

X144 IS VALID predicate: schema location

X145 IS VALID predicate outside check constraints

X151 IS VALID predicate with DOCUMENT option




10gDB2 V9

Sybase ASE15

MS SQL Server 2005

X152 IS VALID predicate with CONTENT option

X153 IS VALID predicate with SEQUENCE option -

X155 IS VALID predicate: NAMESPACE without ELEMENT clause -

X157 IS VALID predicate: NO NAMESPACE with ELEMENT clause -

X160 Basic Information Schema for registered XML Schemas

X161 Advanced Information Schema for registered XML Schemas -

X170 XML null handling options -

X171 NIL ON NO CONTENT option - -

X181 XML(DOCUMENT(UNTYPED)) type - -

X182 XML(DOCUMENT(ANY)) type - -

X190 XML(SEQUENCE) type - -

X191 XML(DOCUMENT(XMLSCHEMA)) type - - -

X192 XML(CONTENT(XMLSCHEMA)) type - - -




10gDB2 V9

Sybase ASE15

MS SQL Server 2005

X200 XMLQuery

X201 XMLQuery: RETURNING CONTENT

X202 XMLQuery: RETURNING SEQUENCE -

X203 XMLQuery: passing a context item -

X204 XMLQuery: initializing an XQuery variable

X205 XMLQuery: EMPTY ON EMPTY option - - - -

X206 XMLQuery: NULL ON EMPTY option - - - -

X211 XML 1.1 support - - -

X221 XML passing mechanism BY VALUE

X222 XML passing mechanism BY REF

X231 XML(CONTENT(UNTYPED)) type - -

X232 XML(CONTENT(ANY)) type - -

X241 RETURNING CONTENT in XML publishing -





10gDB2 V9

Sybase ASE15

MS SQL Server 2005

X242 RETURNING SEQUENCE in XML publishing -

X251 Persistent XML values of XML(DOCUMENT(UNTYPED))type -

X252 Persistent XML values of XML(DOCUMENT(ANY)) type -

X253 Persistent XML values of XML(CONTENT(UNTYPED)) type -

X254 Persistent XML values of XML(CONTENT(ANY)) type -

X255 Persistent XML values of XML(SEQUENCE) type - -

X256 Persistent XML values of XML(DOCUMENT(XMLSCHEMA)) type

X257 Persistent XML values of XML(CONTENT(XMLSCHEMA)) type

X260 XML type: ELEMENT clause

X261 XML type: NAMESPACE without ELEMENT clause -

X263 XML type: NO NAMESPACE with ELEMENT clause -

X264 XML type: schema location -

X271 XMLValidate: data-driven case




10gDB2 V9

Sybase ASE15

MS SQL Server 2005

X272 XMLValidate: ACCORDING TO clause

X273 XMLValidate: ELEMENT clause

X274 XMLValidate: schema location

X281 XMLValidate: with DOCUMENT option -

X282 XMLValidate with CONTENT option -

X283 XMLValidate with SEQUENCE option - -

X284 XMLValidate NAMESPACE without ELEMENT clause -

X286 XMLValidate: NO NAMESPACE with ELEMENT clause - - -

X300 XMLTable





10gDB2 V9

Sybase ASE15

MS SQL Server 2005

X301 XMLTable: derived column list option

X302 XMLTable: ordinality column option

X303 XMLTable: column default option

X304 XMLTable: passing a context item

X305 XMLTable: initializing an XQuery variable

X400 Name and identifier mapping [X290]



Vergleich– SQL :2003 vs SQL: 2007 StandardSQL Standard

Parts Mandatory Features Optional Features NewFeatures Total Features

Added Dropped Added Dropped (2007) SQL:2003 SQL:2007

Part 1 –SQL/Framework - - - - - 7 Packages 7 Packages

Part 2 –SQL/Foundation - - 22 2 22 420 440

Part 3 –SQL/CLI - - - - - 8 8

Part 4 –SQL/PSM - - - - - 30 30

Part 9 –SQL/MED - - - - - 25 25

Part 10 –SQL/OLB - - - - - 9 9

Part 11 –SQL/Schemata - 1 – from

Foundation12 – from

Foundation - 12 – from Foundation 28 39

Part 13 –SQL/JRT - - - - - 17 17

Part 14–SQL/XML Features modified 83 2 83 53 134

Total - - 105 4 117 573 674


Quellen

/Dess01/ Deßloch, S. SQL-Norm und Java Daten. Datenbank-Spektrum 1,25-32 (2000)/Dess05/ SQL-Standardisierung und Umsetzung

Deutsche Informatik-Akademie Datenbank Tutorientage (DIA DBTT), Karlsruhe 2005/Gray04/ Proceedings ADBIS`04, Eighth East-European Conference an Advances in Databases and

Information Systems, 22-25.9, 2004, Budapest, Hungary S. 3/IBM06/ IBM, SQL Reference Volume1, IBM corporation, Dec 5 2006/Melt03/ Melton, J.: ISO ANSI WORKING DRAFT, 2003, ISO + ANSI/Melt03a/ Melton, J.: Information Technology-Database Languages, SQL/Framework,

ISO/IEC 9075-01:2003, 2003/Melt03b/ Jim Melton, Information Technology-Database Languages;, SQL/Foundation,

ISO/IEC 9075-2:2003, 2003/Melt03c/ Melton, J.: Information Technology-Database Languages; SQL/JRT,

ISO/IEC 9075-13:2003, 2003/Melt03d/ Melton, J.: Information Technology-Database Languages; SQL/XML,

ISO/IEC 9075-2:2003, 2003/Melt04/ Melton, J.; Eisenberg, A.; Kulkarni, K.; Michels, J.-E.; Zemke, F.:

“SQL:2003 Has Been Published”, SIGMOD Record, Vol. 33, No. 1, March 2004/Melt05/ Melton, J.: SQL:2007, XQuery, SQL/XML and the Semantic Web Oracle Cooperation 2005/Melt06a/ Jim Melton: SQL/Foundation, Information technology – Database languages,

ISO/IEC 9075-2:200x (E), 2006./Melt06b/ Jim Melton: SQL/Schemata, Information technology – Database languages,

ISO/IEC 9075-11:200x (E), 2006.


Quellen

/Melt06c/ Jim Melton: SQL/XML, Information technology – Database languages, ISO/IEC 9075-14:200x (E), 2006.

/Micr06/ Microsoft Corporation, SQL Server 2005, www.microsoft.com, July 2006

/Müll06/ Sequenzcursor-basierte Verarbeitung von XML-Werten in SQL:2007-AnfrageergebnissenLehrstuhl für Datenbanken und Informationssysteme Friedrich-Schiller-Universität Jena

/Orac05/ Oracle corporation, Oracle Database SQL Reference, Oracle Database, December 2005

/Türk03a/ Türker, C.: SQL: 1999 & SQL: 2003, dpunkt.verlag 2003/Türk03b/ Türker, C.:“XML und Datenbanken, Universität Zürich, 2003

http://www.dbs.ethz.ch/~xml/Seub06/ Seubert, H.: DB2 Version 9 – the Viper Release

Vortrag 122. Datenbankstammtisch an der HTW Dresden/Schö07/ Schöning, H.:SQL/XML-die Brücke zwischen zwei Welten

Deutsche Informatik-Akademie Datenbank Tutorientage (DIA DBTT): Aachen 2007

/Schü05/ Schüller,G.:“SQL2003 – Neue Konzepte, Universität Bonn, 2005

/SQL-06/ http://www.sql-und-xml.de/sql-tutorial/sql-sprache-historische-entwicklung.html/Syba06/ Sybase, Adaptive Server Enterprise Reference Manual, March 2006/Syba05/ Sybase, Java in Adaptive Server Enterprise, Sybase Inc, July 2005

http://www.dbs.ethz.ch/~xml

http://www.sql-und-xml.de/sql-tutorial/sql-sprache-historische-entwicklung.html


Referenzen

[1] Implementation for SQL/Foundation[2] Implementation for SQL/Schemata[3] Implementation for SQL/JRT[4] Implementation for SQL/XML


Danksagungen

• Herrn Böhm und Mr. Kaliappan für die Materialsammlung und –aufbereitung für den Vortrag, Frau Dilz für die Einladungen

• Frau Hamel, Frau Grosch und Frau Walther für die organisatorische Unterstützung bei 125 Veranstaltungen

• Sybase, Microsoft, IBM, Intersystems, SQL GmbH, RobotronDatenbanksoftware für die finanzielle Unterstützung

• Meiner Frau Erika für das langjährige Verständnis für den Datenbankstammtisch


Vielen Dank für Ihre Aufmerksamkeit


Appendix


Sample Database – Tables (Oracle 10g)Table: Employee

Table: Taskemp


Sample Database – Table (Oracle 10g) Table: Task

Table: Car

Table: Department


Sample Database – Tables (DB2 9)Table: Employee

Table: Taskemp

Table: Task


Sample Database – Tables (DB2 9)Table: Car

Table: Department


Sample Databases – Tables (MS SQL Server 2005)Table: Employee

Table: Taskemp

Table: Task


Sample Databases – Tables (MS SQL Server 2005)

Table: Car

Table: Department


Sample Databases – Tables (Sybase ASE15)Table: Employee

Table: Taskemp


Sample Databases – Tables (Sybase ASE15)Table: Task

Table: Car

Table: Department


Implementation Comparison among RDBMS

Oracle 10gDB2

MS SQL ServerSybase ASE


Predefined Datatypes - Feature ID E011, E021

• Oracle 10gcreate table Employee(Empno number not null primary key ,Surname char(15) not null, Firstname char(10) not null, City char(10) not null, Age number not null ,Job char(20) not null, Phone number not null, Departno char(5));

• DB2create table dbo.employee ( empno integer not null primary key, surname char (15) not null , firstname char (10) not null , city char (10) not null , age integer not null , job char (20) not null , phone integer not null , departno char (5)) ;

• MS SQL 2005Create table Employee(Empno int not null primary key, Surname char(15) not null ,Firstnamechar(10) not null,City char(10) not null,Age int not null, Job char(20) not null,Phone int not null,Departno char(5));

• Sybase ASE15Create table Employee(Empno int not null primary key,Surname char(15) not null ,Firstnamechar(10) not null,City char(10) not null,Age int not null, Job char(20) not null,Phone int not null,Departno char(5));


User Defined Data Type – Distinct Types

• Oracle 10g– create or replace type pc as object (name char(11));– create table pclist (pc_id number(10),pc_nam pc);– insert into pclist values(1,PC('HCL'));– insert into pclist values(2,PC('SIECOM'));

• DB2 V9– create distinct type pc as character(11) with comparisons– create table pclist(pc_id numeric,pc_nam pc)– insert into pclist values(1,PC('HCL'))– insert into pclist values(2,PC('SIECOM'))– select * from pclist

• MS SQL Server 2005– Not available in MS SQL Server 2005. Only sp_addtype is specified

• Sybase ASE15– Not available in Sybase ASE15. Only sp_addtype is specified


User Defined Data Type – Structured Type

• Oracle 10g

create type car_detail as object ( carno varchar2(15), cartype varchar2(20), surname varchar2(15),firstname varchar2(15), colour varchar2(10) )

• DB 2 V9

create type car_detail as (carno varchar(15), cartype varchar(20), surname varchar(15), firstname varchar(15), colour varchar(10)) mode db2sql


Constructed Type – Reference Type

• Oracle 10g– create table employee_ref( empid number(5),name varchar(30),car ref car_detail)

• DB2 V9– create table employee_ref( empid integer, name varchar(30), car ref(car_detail))

• MS SQL Server2005– Not available in MS SQL Server 2005.

• Sybase ASE15– Not available in Sybase ASE15


Constructed Type – Composite Type

• Oracle 10g – Array Type– create type Car_Brand as varray(5) of varchar2(25)– create table Car_Type(Surname varchar2(25), Car_Brand_Name Car_Brand)– desc Car_type

– insert into car_type values('Hase',Car_Brand('BMW','Ford','Mitsu'))– insert into car_type values('Grafie',Car_Brand('Ford','Trabant','Opel','Skoda'))– select C.surname,B.* from Car_type C, TABLE(C.Car_Brand_Name)


Constructed Type – Composite Type

• Oracle 10g – Multiset TypeNested table-example

– create table Car_Details(Carnointeger,Cartype varchar2(25),Colour varchar2(15))

– create type CarInfo as Table of varchar2(64)– create table Per_Details(empno

integer,surname varchar2(15),Dept varchar2(10), Veh carinfo) nested table vehstore as car_tab

– desc Per_details

– insert into Per_Detailsvalues(101,'Hase','A3',carinfo('FE-E 123','BMW', 'Schwarz'))

– insert into Per_Detailsvalues(102,'Igel','A1',carinfo('DD-DD 123','Ford','Blau'))

– SELECT P.empno,P.surname,P.dept,C.* FROM per_details P, Table(P.veh) C;


Basic DML Functions (Feature ID E101, E111, E131, E153)

• Oracle 10g– insert into Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing',3419,'A3');– delete from Employee where empno=105;– update task set taskno=(select taskno from taskemp where empno=101) where

task.leader='Hase‘– select taskno, taskname, leader from task

• DB2– insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing',3419,'A3');– delete from dbo.Employee where empno=105;– update dbo.task task set empno=(select empno from dbo.employee where surname='Hase') where

leader='Hase


Basic DML Functions (Feature ID E101, E111, E131, E153)

• MS SQL 2005– insert into Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing',3419,'A3');– delete from Employee where empno=105;– update task set taskno=(select taskno from taskemp where empno=101) where task.leader='Hase'

– select * from task

• Sybase ASE15– insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing',3419,'A3');– delete from dbo.Employee where empno=105;– update dbo.task task set taskno=(select taskno from dbo.taskemp where empno='Hase') where

leader='Hase'


Query Specifications (Feature ID E051, E071, E091)

• Oracle 10g– select empno, taskno, max(targeteffort)from taskemp group by empno, taskno;– select distinct taskno from taskemp intersect select distinct taskno from task;

• DB2 V9– SELECT Empno, Taskno, Max(targeteffort)FROM dbo.Taskemp GROUP BY Empno,

Taskno; – select distinct task.empno, taskemp.taskno from dbo.task as task, dbo.taskemp as

taskemp;

• MS SQL Server 2005– select empno, taskno, max(targeteffort) effort from taskemp group by empno, taskno; – select distinct taskno from taskemp intersect select distinct taskno from task;

• Sybase ASE15– select empno, taskno, max(targeteffort) effort from taskemp group by empno, taskno;– select distinct Task.taskno, Taskemp.Empno from dbo.task as Task, dbo.Taskemp as

Taskemp


Basic Predicates and Privileges (Feature ID E061, T631, F481, E081)

• Oracle 10g– select * from Employee where Surname Like '%s%'– select * from Employee where Departno is NULL – select taskno,empno from taskemp where empno in(select empno from employee where

departno = 'a3')– grant insert, delete on task to scott– revoke insert,delete on Task from scott

• DB2 V9– select * from dbo.employee where Surname like '%s%'– select * from dbo.Employee where Departno is NULL– select taskno,empno from dbo.taskemp where empno in(select empno from dbo.employee

where departno = 'a3')– grant select,insert on table dbo.employee to user shangar– revoke select,insert on table dbo.employee from user shangar


Basic Predicates and Privileges (Feature ID E061, T631, F481, E081)

• MS SQL Server 2005– select * from dbo.employee where Surname like '%s%'– select * from dbo.Employee where Departno is NULL– select taskno,empno from dbo.taskemp where empno in(select empno from dbo.employee

where departno = 'a3')– revoke select,insert on employee to guest– GRANT select,insert on employee to guest

• Sybase ASE15 – select * from dbo.Employee where Surname like '%s%'– select * from dbo.Employee where Departno is NULL– select Taskno,Empno from dbo.Taskemp where Empno IN(SELECT Empno FROM

dbo.Employee WHERE Departno = 'A3')– revoke select,insert on employee to guest– GRANT select,insert on employee to guest


Integrity Constraints (Feature ID E141)

• Oracle 10g– alter table employee add constraint uq_empno unique (empno);– alter table employee add constraint age_ch check (age between 18 and 70)

• DB 2 V9– alter table dbo.employee add constraint uq_empno unique (empno);– alter table dbo.employee add constraint age_ch check (age between 18 and 70)

• MS SQL Server 2005– alter table dbo.employee add constraint uq_empno unique (empno);– alter table dbo.employee add constraint age_ch check (age between 18 and 70)

• Sybase ASE15– alter table dbo.employee add constraint uq_empno unique (empno);– alter table dbo.employee add constraint age_ch check (age between 18 and 70)


Transaction Support (Feature ID E151, E152)

• Oracle 10g– commit;– insert into Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')– rollback;– set transaction read only name ‘db1’

• DB2 V9– commit transaction– insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')– rollback transaction– set transaction isolation level serializable

• MS SQL 2005– Begin tran t1– insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')– commit tran t1


Transaction Support (Feature ID E151, E152)

• Sybase ASE15

– First Possibility• Commit• insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')• Rollback

– Second Possibility• Begin tran t1• insert into dbo.Employee values(105,'Uhu','Uhu','Ast',50,'Dipl.-Ing.',3419,'A3')• commit tran t1


Basic Joined Table (Feature ID F041)

• Oracle 10g– select Employee.empno, Employee.Surname, TaskEmp.Taskno from Employee left join

TaskEmp on Employee.empno = TaskEmp.empno– select Employee.empno, Employee.Surname, TaskEmp.Taskno from Employee right outer

join TaskEmp on Employee.empno=TaskEmp.empno

• DB2– select employee.empno, employee.surname, taskemp.taskno from dbo.employee as

employee, dbo.taskemp as taskemp where employee.empno = taskemp.empno– Select Employee.Empno, Employee.Surname, Taskemp.Taskno From Dbo.Employee As

Employee Right Outer Join Dbo.Taskemp As Taskemp On Employee.Empno = Taskemp.Empno


Basic Joined Table (Feature ID F041)

• MS SQL Server– select Employee.empno, Employee.Surname, TaskEmp.Taskno from Employee left join

TaskEmp on Employee.empno=TaskEmp.empno– select Employee.empno, Employee.Surname, TaskEmp.Taskno from Employee right outer

join TaskEmp on Employee.empno=TaskEmp.empno

• Sybase ASE 15– select Employee.Empno, Employee.Surname, Taskemp.Taskno from dbo.Employee as

Employee, dbo.Taskemp as Taskemp where Employee.Empno = Taskemp.Empno– Select Employee.Empno, Employee.Surname, Taskemp.Taskno From dbo.Employee As

Employee Right Outer Join dbo.Taskemp As Taskemp On Employee.Empno = Taskemp.Empno


View and Grouped Operations (Feature ID F311-03, F131)

• Oracle 10g– create or replace view distinct_view as select distinct * from employee– select empno,count(*) as Available_Entry from taskemp group by empno having count(*)>1

• DB2 V9– create view distinct_view as select distinct * from dbo.employee– select empno,count(*) as Available_Entry from dbo.taskemp group by empno having

count(*)>1

• MS SQL Server 2005– create view distinct_view as select distinct * from dbo.employee– select empno,count(*) as Available_Entry from dbo.taskemp group by empno having

count(*)>1

• Sybase ASE15– create view distinct_view as select distinct * from dbo.Employee– select Empno,count(*) as Available_Entry from dbo.Taskemp group by Empno having

count(*)>1


CAST Function (Feature ID F201, F221)

• Oracle 10g– select cast(Surname as varchar(5)) from Employee– insert into car values(‘A5’,’SoftwareIII’,’Anna’,DEFAULT)

• DB2 V9– select cast(Surname AS CHAR(5) ) from dbo.Employee– insert into dbo.car values(‘A5’,’SoftwareIII’,’Anna’,DEFAULT)

• MS SQL 2005– select cast(Surname AS CHAR(5) ) from dbo.Employee– insert into dbo.car values(‘A5’,’SoftwareIII’,’Anna’,DEFAULT)

• Sybase ASE 15– select cast(Surname AS CHAR(5) ) from Employee– insert into car values(‘A5’,’SoftwareIII’,’Anna’,DEFAULT)


CASE expression (Feature ID F261)

• Oracle 10g

select Empno,Taskno, (case actualeffortwhen .2 then 'Min Effort' when .55 then 'Med Effort'when .6 then 'Max Effort'end) EffortStatus from Taskemp

• DB 2 V9

select Empno,Taskno, (case actualeffortwhen .2 then 'Min Effort' when .55 then 'Med Effort' when .6 then 'Max Effort'end) EffortStatus from dbo.Taskemp


CASE expression (Feature ID F261)

• MS SQL Server 2005

select empno, effortstatus = case targeteffortwhen 0.20 then 'min effort' when 0.55then 'med effort' when 0.60 then 'max effort' else 'no effort'end, taskno from dbo.taskemp

• Sybase ASE15

select Empno,Taskno, "Effort Status" = casewhen Targeteffort = 0.20 then "Min Effort"when Targeteffort = 0.55 then "Med Effort"when Targeteffort = 0.60 then "Max Effort"end from Taskemp


SQL/Information and Definition Schemas (SCHEMATA)


Basic Information Schema

• Columns view– It Identifies the privileges on columns of tables defined in the catalog that are

available to or granted by a given user or role.• Tables View

– It Identifies the tables defined in the catalog that are accessible to a given user or role.

• Views view– It Identifies the viewed tables defined in the catalog that are accessible to a

given user or role.• Table_constraints view

– It Identifies the table constraints defined on tables in the catalog that are accessible to a given user or role.

• Referential_constraints view– It Identifies the referential constraints defined on tables in the catalog that are

accessible to a given user or role.• Check_constraints view

– It Identifies the check constraints defined in the catalog that are owned by a given user or role.


SQL Routines and types using Java Programming Language (SQL/JRT)


JRT Features in Oracle 10g

– Basic manipulation• Load java class/jar file into database • loadjava c:/Example.jar

– Java Routines• Supports through JDBC funtions

– JDBCimport java.sql.*;import java.io.*;public class QueryExample {public static void main (String[] args) throws SQLException, IOException {//Load and register Oracle driverDriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());//Establish a connectionConnection conn = DriverManager.getConnection("jdbc:oracle:thin:@training:1521:Oracle", "oratest", "oratest"); // or DSN name} }


• Java using SQL – Example– Assume that the executable for the following Java class is stored in the Oracle [ODJU03]

databaseJava code looks like

public class Fibonacci {public static int fib (int n) {if (n == 1 || n == 2)return 1; elsereturn fib(n - 1) + fib(n - 2); }}

– create or replace function fib (n number) return number as language java name ’fibonacci.fib(int) return int’;

sql> variable n numbersql> variable f numbersql> execute :n := 7;PL/SQL procedure successfully completed.sql> call fib(:n) into :f;Call completed.


Output look like SQL> PRINT fF----------13



• CALL proceduresJava code look like

import java.sql.*;import oracle.jdbc.driver.*;public class Deleter {public static void main (String[] args) throws SQLException{ Connection conn = new OracleDriver().defaultConnection();String sql = "DELETE FROM " + args[0];if (args.length > 1) sql += " WHERE " + args[1];try {Statement stmt = conn.createStatement();stmt.executeUpdate(sql);stmt.close();}catch (SQLException e) {System.err.println(e.getMessage());} }}

PL/SQL looks likecreate or replace package body pkg asprocedure delete_rows (table_name varchar2, condition

varchar2) as language javaname 'deleter.main(java.lang.string[])'; end;sql> call pkg.delete_rows('emp', 'sal > 1500');


JRT Features in DB2

• JDBC

Java code looks likeImport java.sql.*;Public Class Example{ Public static void main(String arg[])throws Exception{ Class.forName(“com.ibm.db2.jdbc.app.DB2Driver”);Connection conn = DriverManager.getConnection

(“jdbc:db2:test”;”db2admin”,”db2admin”); }}

• JAR Administration– Install

CALL sqlj.install_jar( 'file:/home/bob/bobsjar.jar', 'MYJAR' )– Replace

CALL sqlj.replace_jar( 'file:/home/bob/bobsnewjar.jar', 'MYJAR' )– Remove

CALL sqlj.remove_jar( 'MYJAR' )


JRT Features in DB2

• Java RoutinesJava program look like

public static double product( double in1, double in2 ) throws SQLException{ return in1 * in2; }

SQL Procedural Language looks likecreate function product( double in1, double in2 )returns doublelanguage javaparameter style javano sqlfenced threadsafedeterministic returns null on null inputno external actionexternal name 'Examplejar:udfclass.product'


JRT Features in DB2

• Stored ProceduresJava code looks like

public static void javastp( int inparm, int[] outparm, ResultSet[] rs ) throws SQLException{ Connection con = DriverManager.getConnection( "jdbc:default:connection" );

PreparedStatement stmt = null;String sql = SELECT phone FROM Employee WHERE index = ?";//Prepare the query with the value of index stmt = con.prepareStatement( sql ); stmt.setInt( 1, inparm );//Execute query and set output parmrs[0] = stmt.executeQuery(); outparm[0] = inparm + 1;//Close open resourcesif (stmt != null) stmt.close(); if (con != null) con.close(); return; }

SQL Procedural Language looks likecreate procedure javaproc( in in1 int, out out1 int )

language javaparameter style java dynamic result sets 1fenced threadsafeexternal name 'myjar:stpclass.javastp'


JRT Features in Sybase ASE15

• Basic Manipulations – Load java class/jar file into database [SASE05]

Query : instjava –f “c:/Home_Address.jar”– View the description about installed java classes in database

Query: sp_helpjava ‘jar’ ,Home_Address, detail– To remove the installed java package

Query: Remove java package “Home_Address.jar”– Table creation – java type as column

Query: Alter table Employee add column Address Home_Address

Insertiondeclare @A Addressselect @A = new Address('123 Main Street', '99123')insert into Employee (Home_Address) values(@A) where Empno=102declare @A1 Address,select @A1=new Address('321 Second Street', '99123')update Employee set Address=new Address(@A1) where Empno=102



• Java Routines• Pre Defined Methods in “Example.java”: getNumber(), getName(), getStreet()• declare @street varchar(15)• select @street=Example.getStreet(street) from Employee where Empno=102

• Data Type Mapping• Auto Conversion

– Select Example.getNumber(Address) from Employee where Empno=102• Runtime ConversionJava program looks like

Public class Example1 implements java.io.Serializable{ public static int Empno(int I) { return I ;}public static String Surname(String S) { return S;}

}Transact SQL looks like

declare @I Empdeclare @S Nameselect @I = Example1.Empno(@I)select @S=Example1.Surname(@S)



• JDBC Java code looks like

import java.sql.*; // JDBCpublic class JDBCExamples{{ public static void main(String args[]) {try{ String server = args[0];String action = args[1].toLowerCase();Connection con = connecter(server);} catch (Exception e) {System.out.println("\n Exception: ");e.printStackTrace(); } }



• Connector classpublic static Connection connecter(String server)throws Exception, SQLException, ClassNotFoundException {String forName="", url="";if (server=="default") { // server connection to current serverforName = "sybase.asejdbc.ASEDriver";url = "jdbc:default:connection";} else if (server!="default") { //client connection to serverforName= "com.sybase.jdbc.SybDriver";url = "jdbc:sybase:Tds:"+ server; }String user = "sa", password = "";Class.forName(forName); // Load the driverConnection con = DriverManager.getConnection(url, user, password); return con; } // Get a connection



• Stored ProceduresCreate proc inoutproc @id int, @name varchar(20), @job output, @phone output as

select @job=job_1,@phone=phone_1 where empno=@idCode snippet

Callable statement cs=con.prepareCall(“{call inoutproc(?,?,?,?)}”);

cs.setInt(1,102); cs.setString(2,”Uhu”)cs.registeroutparameter(3,java.sql.Types.varchar)cs.registeroutparameter(4,java.sql.Types.object)

int res=cs.executeupdate();String Job=cs.getString(3); String Phone=cs.getString(4);System.out.println(“Job:” + Job); System.out.println(“Phone: ” + Phone);



• SQLJ Functions

– SQLJ User Defined functionsCreate function SQLJ_Fc()

language javaparameter style javaexternal name ‘SQLJExample.fibo’

– SQLJ Stored ProcedureCreate procedure SQLJ_Pr()

Language javaParameter style javaExternal name ‘SQLJExample_Proc.Fibo’


SQL/JRT in MS SQL Server

• JDBC– MS SQL Server supports Type 4 JDBC Driver. – Based on native implementation.

• Other Features– MS SQL Server doesnot support all other features


SQL/XML


Implementation comparison with DBMS

• XML Type

– Oracle 10g• alter table employee add Resume xmltype

– DB2 V9• alter table dbo.employee add Resume xml

– MS SQL Server 2005• alter table dbo.employee add Resume xml


XML Element

• Oracle 10gselect xmlelement (name "Employee",

xmlelement (name "Empno", empno), xmlelement (name "Empname", Surname), xmlelement (name "Job", job) ) as Employee_Details from Employee where

empno=102• DB2 V9

select xmlelement (name "Employee", xmlelement (name "Empno", empno), xmlelement (name "Empname", Surname), xmlelement (name "Job", job) ) as Employee_Details from dbo.Employee

where empno=102• MS SQL Server 2005, Sybase ASE15

Not Supported

– Results<?xml version="1.0" encoding="UTF-16" ?>

<Employee><Empno>102</Empno> <Empname>Igel</Empname><Job>Industriekaufmann</Job> </Employee>


XML Forest with Attribute and child Elements

• Oracle 10g

select xmlelement("emp", xmlattributes(e.firstname ||' '|| e.surname as "name"), xmlforest(e.job, e.departno as "department")) as "employee" from employee e where Surname like ‘%e%’ where Firstname like '%e%'

• DB2 V9

select xmlelement(name "emp", xmlattributes(firstname ||' '|| surname as "name"), xmlforest(job, departno as "department")) as “employee” from dbo.employee where Firstname like '%e%‘

• MS SQL Server 2005, Sybase ASE15

Not Supported

• Results

<?xml version="1.0" encoding="UTF-16" ?><emp name="Diebische Elster"><JOB>Dipl.-Math.</JOB><department/> </emp><emp name="Stachel Igel"><JOB>Industriekaufmann</JOB><department>A1

</department> </emp> <emp name="Reinecke Fuchs"><JOB>EDV-Facharbeiter</JOB></emp>


XML Aggregate

• Oracle 10gSELECT XMLElement("Employee Detail", XMLAgg(XMLElement("Employee",e.job||'

'||e.surname)ORDER BY e.departno)) AS "Dept_list" from employee e

• Results<Employee Detail><Employee>Industriekaufmann Igel</Employee><Employee>Dip.-Ing. Hase</Employee><Employee>Dipl.-Ing Uhu</Employee><Employee>EDV-Facharbeiter Fuchs</Employee><Employee>Dipl.-Math. Elster</Employee>

</Employee Detail>


XML Aggregate

• DB2 V9SELECT XMLElement(name "EmployeeDetail", XMLAgg(XMLElement(name

"Employee",e.job||' '||e.surname)ORDER BY e.departno)) AS "Dept_list" from dbo.employee e

• Results<?xml version="1.0" encoding="UTF-16" ?><EmployeeDetail><Employee>EDV-Facharbeiter Fuchs</Employee><Employee>Dipl.-Math. Elster</Employee><Employee>Industriekaufmann Igel</Employee><Employee>Dip.-Ing. Hase</Employee><Employee>Dipl.-Ing Uhu</Employee>

</EmployeeDetail>


XML Aggregate

• MS SQL Server 2005– Not Supported

• Sybase ASE15– Not Supported


XML Serialize • Oracle 10g

select xmlserialize(content xmlelement(name "emp_exempt", xmlattributes(firstname,surname as "lastname")) as clob) as "result"from employee where firstname='reinecke'

• Results<Emp_Exemptfirstname="Reinecke" Lastname="Fuchs"></Emp_Exempt>

• DB2 V9select xmlserialize(content xmlelement(name "emp_exempt",xmlattributes(firstname,surname as "lastname")) as clob) as "result"from dbo.employee where firstname='reinecke'

• Results<Emp_Exemptfirstname="Reinecke" Lastname="Fuchs"></Emp_Exempt>

• MS SQL Server 2005, Sybase ASE15– Not Supported


Optional Conformance - XMLConcat• Oracle 10g

select xmlconcat(xmlelement("firstname", e.firstname), xmlelement("surname", e.surname)) as "result" from employee e;

• Results<Firstname>Hoppel</Firstname><Surname>Hase</Surname><Firstname>Stachel</Firstname><Surname>Igel</Surname><Firstname>Reinecke</Firstname><Surname>Fuchs</Surname><Firstname>Diebische</Firstname><Surname>Elster</Surname><Firstname>Uhu</Firstname><Surname>Uhu</Surname>

• DB 2V9select xmlconcat(xmlelement("firstname",firstname),

xmlelement("surname", surname)) as "result" from dbo.employee• Results

<?xml version="1.0" encoding="UTF-16" ?><Firstname>Hoppel</Firstname><Surname>Hase</Surname><Firstname>Stachel</Firstname><Surname>Igel</Surname><Firstname>Reinecke</Firstname><Surname>Fuchs</Surname><Firstname>Diebische</Firstname><Surname>Elster</Surname><Firstname>Uhu</Firstname><Surname>Uhu</Surname>


Optional Conformance - XMLConcat

• MS SQL Server 2005– Not Supported

• Sybase ASE15– Not Supported


XML Comment

• Oracle 10gselect xmlcomment('this is a comment') as cmnt from dual;

• DB2 V9XMLComment (‘This is a comment')

• MS SQL Server 2005, Sybase ASE15– Not Supported.


XML Root

• Oracle 10gselect xmlroot(xmltype('<empno>101</empno>'), version '1.0', standalone

yes) as xmlroot from dual;• Results

<?xml version="1.0" standalone="yes"?> <Empno>101</Empno>

• DB2V9, MS SQL Server 2005, Sybase ASE15– Not Supported


XML Parse

• Oracle 10gselect xmlparse(content ' <task no="32"> <surname>igel</surname>

<actualeffort>0.10</actualeffort> <targeteffort>0.20</targeteffort></task>' wellformed) as task from dual;

• Results<Task No="32"> <Surname>Igel</Surname> <Actualeffort>0.10</Actualeffort> <Targeteffort>0.20</Targeteffort> </Task>

• DB2 V9select xmlparse ( document '<task no="32"> <surname>igel</surname>

<actualeffort>0.10</actualeffort> <targeteffort>0.20</targeteffort></task>') as task from sysibm.sysdummy1

• Results<?xml version="1.0" encoding="UTF-16" ?><Task No="32"> <Surname>Igel</Surname><Actualeffort>0.10</Actualeffort><Targeteffort>0.20</Targeteffort></Task>


XML Parse

• MS SQL Server 2005 – Not Supported

• Sybase ASE15update Employee set Image_type = xmlparse(Resume) where Empno=106


XML Namespaces

• Oracle 10gNot Supported

• DB2 V9select (xmlelement (name "name", xmlnamespaces (default 'http://sample.default.nspace.com'),xmlattributes ('male' as "gender"),xmlforest ('steve' as "firstname",'ferrington' as "lastname"))) from sysibm.sysdummy1

– Results<?xml version="1.0" encoding="UTF-16" ?><Name xmlns="http://sample.default.nspace.com" gender="MALE"><FirstName>Steve</FirstName><LastName>Ferrington</LastName></Name>

• MS SQL Server 2005Supports through external program interface(Visual Basic, Visual C++) by means of

“XmlSchemaCollection” class.• Sybase ASE15

Not Supported


Sybase ASE15 : XML Special Features

• XML Insertinsert into Employee values(106,'Martin','Luther','FRT',36,'Dip.-

Ing',5354,'A6',"<?xml version='1.0' standalone = 'no'?> <resume title='software'> <student name='Prabhu'>

– Results <Experience>Two Years in Software Programing</Experience><Area> <First>RDBMS</First><Second>Java</Second></Area> </student> <student name='Garten'> <Experience>Two Years in RDBMS</Experience><Area><First>Sybase</First><Second>Oracle</Second> </Area></student></resume>")

• XML Extractselect xmlextract('/resume/student/Experience',Resume)from Employee where

empno=106– Results

<Experience>Two Years in Software Programing</Experience><Experience>Two Years in RDBMS</Experience>


Sybase ASE15 : XML Special Features

• XML Testselect Empno, Surname from Employee where '//Area[First="RDBMS"]' xmltestResume;

• XML Parsealter table Employee add Image_type image null;update Employee set Image_type = xmlparse(Resume) where Empno=106

• XML Representationselect Empno, xmlrepresentation(Image_type) as status from Employee where empno=106

• XML Validate create table xmltype(xml_t text null);insert into xmltype values (xmlvalidate( '<employee_name>John Doe </employee_name> ', option 'dtdvalidate=yes'))


MS SQL Server 2005: XML Special Features

• XML DML Support – Insert

insert into Employee values(107,'Peter','Silie','FRT',45,'Dipl-Ing',5343,'A5','<Root> <JobDescription JobID="31" JobName="Report Generator"> <Features> </Features> </JobDescription> </Root>');

select Empno,Surname,Firstname,Resume from Employee where Empno=107

• Insert Using Modify()

UPDATE Employee SET Resume.modify('insert <Experience>3 years in Teaching</Experience> as first into (/Root/JobDescription/Features)[1]') where Empno=107;

Select Empno,Surname,Resume from Employee where Empno=107


MS SQL Server 2005: XML Special Features

• Delete

UPDATE Employee SET Resume.modify('delete /Root/JobDescription/ Features/*[1]') where empno=107;

Select Empno,Resume from Employee where Empno=107;

• Replace Value of (XML DML)

UPDATE Employee SET Resume.modify(' replace value of (/Root/JobDescription/@JobName)[1] with "Statistik" ') where Empno=107;

Select Empno,Resume from Employee where Empno=107;


MS SQL Server 2005 - XML Data Type Methods

• Query() Method select Resume.query('/Root/JobDescription/Features') from Employee as Label where

empno=107;• Value() Method

select Resume.value('(/Root/JobDescription/@JobID)[1]','int')as JOBID from Employee where empno=107

• Exist() Methodselect Resume.exist('/Root/JobDescription/Features/Experience') as Exist_Check from

Employee where empno=107 or empno=103• Nodes() Method

DECLARE @emp xml SET @emp='<Root>

<emp id="109"><name>Nass</name></emp><emp id="110"><name>Uhr</name></emp><emp id="111"><name>Dorant</name></emp>

<empl ids="121"></empl> </Root>'

select c.query('.') as result from @emp.nodes('/root/emp') employee(c)


MS SQL Server 2005 - XML Schemacreate xml schema collection employee_schema as N'<?xml version="1.0" encoding="UTF-16"?>

<xsd:schema targetNamespace="http://schemas.microsoft.com/ sqlserver/2004/07/adventure-works/ProductModelManuInstructions"

xmlns ="http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/ProductModelManuInstructions" elementFormDefault="qualified" attributeFormDefault="unqualified"

xmlns:xsd="http://www.w3.org/2001/XMLSchema" ><xsd:complexType name="Employee" mixed="true" >

<xsd:sequence minOccurs="0" maxOccurs="unbounded" > <xsd:element name="Empid" type="xsd:integer"/><xsd:element name="Surname" type="xsd:string" /><xsd:element name="Firstname" type="xsd:string" /><xsd:element name="Department" type="xsd:string" /><xsd:element name="Job" type="xsd:string" /></xsd:sequence> </xsd:complexType>

<xsd:element name="root"> <xsd:complexType mixed="true"><xsd:sequence><xsd:element name="Car" minOccurs="1" maxOccurs="unbounded"><xsd:complexType mixed="true"> <xsd:sequence>

<xsd:element name="Empl" type="Employee" minOccurs="1" maxOccurs="unbounded" /> </xsd:sequence>

<xsd:attribute name="Empid" type="xsd:integer" use="required"/><xsd:attribute name="Surname" type="xsd:string" use="optional"/><xsd:attribute name="City" type="xsd:string" use="optional"/>

</xsd:complexType> </xsd:element></xsd:sequence> </xsd:complexType>

</xsd:element> </xsd:schema>' ;


MS SQL Server 2005 - XML Schema

• select * from sys.xml_schema_collections

• select name from sys.xml_schema_namespaces

Hochschule für Technik und Wirtschaft Dresden (FH)dbst/material/20070328_125_wloka.pdf ·...

Documents

Transcript of Hochschule für Technik und Wirtschaft Dresden (FH)dbst/material/20070328_125_wloka.pdf ·...