SQL in Obsydian

1
SQL in Obsydian

• Paul Rohrdanz
• Platform Technologies Manager
• Synon
• pgr_at_synon.com

2
Obsydian 3.1Jasper
DAT
A
ACC
ESS
3
What matters aboutData Access?
PARITY
EXPLOITATION
4
ObsydianPlatform Parity

• Features have equivalent implementations on
  different platforms
• Applications can be regenerated for a new
  platform with minimal redesign
• Architectural expertise applies equally to all
  supported platforms

5
ObsydianPlatform Exploitation

• A platforms strengths can be put to work
• Developers knowledge applies to Obsydian use
• Features work the way you would expect

6
DDS and SQL
? DDS is closely integrated with AS/400
? SQL is a cross-platform standard
? Obsydian exploited DDS from its inception
? SQL generation was added at 2.0
? DDS is controlled by IBM
? Every vendor has its own SQL implementation
7
3.1 Features

• Parity
• Descending keys
• Concurrent connections
• Exploitation
• SELECT WHERE statement
• ODBC 3.0 support
• Oracle for NT

8
Descending Keys
DDS
Fetch GT D retrieves C
Fetch Next retrieves B
SQL at 3.0
Fetch GT D retrieves F
Fetch Next retrieves E
SELECT FROM WHERE key gt D ORDER BY key DESC
9
Descending Keys
DDS
Fetch GT D retrieves C
Fetch Next retrieves B
SQL at 3.1
F
. . .
. . .
E
. . .
. . .
D
. . .
. . .
Fetch GT D retrieves C
C
. . .
. . .
Fetch Next retrieves B
B
. . .
. . .
A
. . .
. . .
SELECT FROM WHERE key lt D ORDER BY key DESC
10
Concurrent SQL ConnectionsBefore Jasper
DDS
Obsydian Genned Application
SQL at 3.0
Obsydian Genned Application
11
Concurrent SQL ConnectionsBefore Jasper
DDS
Obsydian Genned Application
Customer
SQL at 3.0
Customer
Order
Order
Obsydian Genned Application
12
Concurrent SQL ConnectionsMultiple Servers
Obsydian Genned Application
13
Concurrent SQL ConnectionsHeterogenous Servers
NT SQL Server Sales Dept.
Customer
Obsydian Genned Application
ODBC Driver
14
Concurrent SQL ConnectionsTo the Same Database
ROWSET 50 FORWARD ONLY
Customer
A
. . .
. . .
B
. . .
. . .
C
. . .
. . .
D
. . .
. . .
E
. . .
. . .
F
. . .
. . .
ROWSET 1 PESSIMISTIC LOCKING
15
Concurrent SQL Triples
16
Concurrent SQL ConnectionsImplementation

• Logical Names are mapped to Physical Names in the
  Registry or .INI file
• APIs for accessing Connection setting
• SetDatabaseConnectionName
• GetDatabaseConnectionName

17
Concurrent SQL ConnectionsBenefits

• Increased flexibility for performance tuning
• Much simpler to develop applications using
  multiple data sources

18
SELECT WHEREParity
Position GT/GE FETCH GT/GE/LT/LE
DDS
SQL
19
SELECT WHEREExploitation
SELECT WHERE ...
status ltgt OPEN
UNIQUE ( SELECT...
date BETWEEN 199802 AND 199804
Product IS NULL
total gt ALL ( SELECT...
state IN ( CA, OR, WA )
MATCH ( SELECT...
name LIKE smith
id ANY ( SELECT...
EXISTS ( SELECT...
20
SELECT WHEREStatement

• AD Statement SELECT WHERE ltgt takes any valid
  WHERE clause as a parameter
• EXTERNAL NAME operator retrieves column name
  dynamically
• Literals, variables and metadata can be used in
  the parameter

21
SELECT WHERETest Panel
22
SELECT WHERELiterals
The literal string is
T1.ArtistClapton
23
SELECT WHEREVariables
Building the WHERE clause
T1.TitleSlowhand
24
SELECT WHEREEXTERNAL NAME usage
EXTERNAL_NAME and FormatMessage are used to
build compound clauses
25
DELETE WHERE Statement
Delete Where takes the same syntax as Select Where
26
SELECT WHEREBenefits

• Better SQL query performance
• Greater flexibility
• Much easier to implement what you intend
• New possibilities for Pattern Libraries

27
ORACLEfor NT

• 40 of NT DBMS Market
• Obsydian needed native Oracle implementation

ODBC

• Oracle Business Alliance Partner Program

28
Oracle for NTRequirements
? Support for Oracle on NT Server only
? Uses the Oracle 8 OCI Library
29
Oracle for NTDesign
30
Oracle for NTData Access Runtime
Runtime DLL
Obsydian Genned Application
Database Layer DLL
OCI DLL
ODBC DLL
31
Oracle for NTChanges Required

• New triples for Tables, Views, Stored Procedures
• Build Manager now supports Oracle Connections
• Environment Manager supports Oracle Settings

32
Oracle for NTNew Triples
33
Oracle for NTBuild Manager
34
Oracle for NTEnvironment Manager
35
Oracle for NTBenefits

• Broadens Obsydian DBMS reach
• Improves performance for Oracle
• Enables exploitation of Oracle-specific features

36
Beyond Jasper

• BLOBs
• Database Functions
• Rowsets as design objects
• OLE DB

37
Obsydian 3.1

• Closer parity between DDS and SQL generation
• Exploitation of SQL functionality and Oracle DBMS
• In RC1 now!