Download O2 ODBC User Manual

O2 ODBC
User Manual
Release 5.0 - April 1998
In for m ation in t h is docu m en t is su bject t o ch an ge with ou t
n ot ice an d sh ou ld n ot be con str u ed as a com m it m en t by
O2 Tech n ology.
Th e soft war e descr ibed in th is docu m en t is deliver ed u n der a
licen se or n on disclosu r e agr eem en t.
Th e soft war e can on ly be u sed or copied in accor dan ce wit h t h e
ter m s of th e agr eem en t . It is again st t h e law t o copy t h is
soft war e t o m agn et ic t ape, disk , or an y ot h er m ediu m for an y
pu r pose ot h er t h an t h e pu r ch aser ’s own u se.
Copyr igh t 1992-1998 O 2 Tech n ology.
All r igh t s r eser ved. No par t of t h is pu blicat ion can be
r epr odu ced, st or ed in a r et r ieval syst em or t r an sm it t ed in an y
for m or by an y m ean s, elect r on ic, m ech an ical, ph ot ocopy
with ou t pr ior wr itt en per m ission of O2 Tech n ology.
O2 , O 2 En gin e API, O2 C, O2 DBAccess, O2 En gin e, O2 Gr aph ,
O2 Kit , O2 Look , O2 St or e, O2 Tools, an d O2 Web ar e r egist er ed
tr adem ar k s of O2 Tech n ology.
SQL an d AIX ar e r egist er ed t r adem ar k s of In t er n at ion al
Bu sin ess Mach in es Cor por at ion .
Su n , Su n OS, an d SOLARIS ar e r egist er ed t r adem ar k s of Su n
Micr osystem s, In c.
X Win dow System is a r egist er ed tr adem ar k of t h e
Massach u set t s In st itu t e of Tech n ology.
Un ix is a r egist er ed t r adem ar k of Un ix Syst em Labor at or ies, In c.
HPUX is a r egist er ed t r adem ar k of H ewlet t -Pack ar d Com pan y.
BOSX is a r egist er ed t r adem ar k of Bu ll S.A.
IRIX is a r egist er ed t r adem ar k of Siem en s Nixdor f, A.G.
NeXTSt ep is a r egister ed t r adem ar k of t h e NeXT Com pu t er , In c.
Pu r ify, Qu an t ify ar e r egist er ed tr adem ar k s of Pu r e Soft war e In c.
Win dows is a r egist er ed t r adem ar k of Micr osoft Cor por ation .
All oth er com pan y or pr odu ct n am es qu ot ed ar e t r adem ar k s or
r egist er ed t r adem ar k s of t h eir r espect ive t r adem ar k h older s.
Who should read this manual
Th is m an u al pr esen t s O2 Tools, a com plet e gr aph ical pr ogr am m in g
en vir on m en t for th e design an d developm en t of O 2 applicat ion s. It
descr ibes t h e br owser s an d edit or s available, as well as h ow to
cu st om ize O2 Tools scr een s.
Oth er docu m en t s available ar e ou t lin ed, click below.
See O2 Documentation set.
1
TABLE O F CONTENT S
T h i s m a n u a l i s d i v i d ed i n t o t h e f o l l o w i n g c h a p t er s :
• 1 - In t r odu ct ion
• 2 - O 2 ODBC In st allat ion
• 3 - O 2 ODBC Over view
• 4 - O 2 SQL
• 5 - O 2 ODBC
• 6 - Pr ogr am m in g an O2 ODBC Ser ver
• 7 - O 2 ODBC Refer en ce
O2ODBC User M an u al
5
TABLE OF CONTENTS
1
Introduction
11
1.1 System overview ......................................................................12
1.2 ODBC.........................................................................................14
1.3 O2 and ODBC............................................................................15
1.4 Manual Overview ......................................................................16
1.5 Background ..............................................................................17
2
O2ODBC Installation
19
2.1 Hardware and Software Requirements ..................................20
2.2 O2 ODBC Distribution Package ..............................................20
2.3 Setting up the O2 ODBC Driver...............................................21
Installing the driver ......................................................................22
Declaring the o2 open-dispatcher ................................................22
Installing the tools ........................................................................22
2.4 Adding, Modifying and Deleting O2 ODBC Data Sources ....23
3
O2 ODBC Overview
25
3.1 O2 ODBC Architecture.............................................................26
Outline of the O2 ODBC driver activity......................................27
Advantages of the O2 ODBC architecture...................................27
3.2 O2 SQL ......................................................................................28
3.3 O2 ODBC Server.......................................................................29
4
O2 SQL
31
4.1 Schema Translation .................................................................32
O2 Schema ...................................................................................32
Relational Schema........................................................................32
6
O2ODBC User Man u al
TABLE OF CONTENTS
Example....................................................................................... 33
Class translation........................................................................... 33
Primary Key Definition ............................................................... 34
Attribute Translation ................................................................... 36
Atomic and Class Attributes........................................................ 36
Tuple Attributes........................................................................... 37
Collection Attributes ................................................................... 37
Inherited Attributes...................................................................... 38
Data Retrieval Methods ............................................................... 38
Customized translation ................................................................ 39
4.2 Query Translation .................................................................... 42
Table creation command ............................................................. 42
View table creation command ..................................................... 44
Table deletion command ............................................................. 45
View deletion command.............................................................. 45
Index creation command ............................................................. 45
Table modification command...................................................... 46
Table Types ................................................................................. 46
Insert commands.......................................................................... 48
Insertion from an associated named collection extent................. 48
Insertion and foreign keys ........................................................... 49
Insertion and computed extents ................................................... 50
Insertion with nested queries ....................................................... 52
Delete commands ........................................................................ 52
Deletion from an associated named collection extent ................. 52
Deletion and foreign keys............................................................ 52
Deletion from a class table with an associated computed extent 53
Update commands ....................................................................... 54
Update and foreign keys .............................................................. 54
O2C procedures ........................................................................... 59
C++ procedures ........................................................................... 59
Linking C++ functions with the “sql” library.............................. 60
Typing restrictions....................................................................... 60
4.3 Development Tools .................................................................. 61
Modifying existing views ............................................................ 61
O2OD BC User Man u al
7
TABLE OF CONTENTS
The SQL catalog.......................................................................... 62
SQL commands ........................................................................... 63
Transaction commands ................................................................ 63
View inspection commands......................................................... 64
View management commands..................................................... 65
5
O2 ODBC
67
5.1 Conformance Levels ............................................................... 68
5.2 O2 Data Sources ...................................................................... 68
5.3 ODBC API Functions ............................................................... 70
5.4 O2 ODBC Tools ........................................................................ 79
6
Programming an O2ODBC Server
83
6.1 Defining the O2 ODBC Server main function ........................ 84
6.2 Compiling your own O2 ODBC server ................................... 85
6.3 Running your own O2 ODBC server ...................................... 87
7
O2 ODBC Reference
89
7.1 The o2_odbc Class .................................................................. 90
banner .......................................................................................... 94
begin ............................................................................................ 95
end ............................................................................................... 99
enroll .......................................................................................... 100
enroll_path ................................................................................. 102
get_option .................................................................................. 103
init.............................................................................................. 104
set............................................................................................... 105
usage .......................................................................................... 106
7.2 The O2 ODBC Commands .................................................... 107
o2odbc_dump_base ................................................................... 108
8
O2ODBC User Man u al
TABLE OF CONTENTS
o2odbc_server............................................................................ 109
o2open_dispatcher ..................................................................... 111
o2sql_export .............................................................................. 113
o2sql_query ............................................................................... 115
A
Syntax for View Customization
117
B
SQLGETINFO Return Values
119
O2OD BC User Man u al
9
TABLE OF CONTENTS
10
O2ODBC User M an u al
1
I ntr oduction
1
Con gr at u lat ion s! You ar e n ow a u ser of O 2 ODBC - t h e st an dar d
in t er face for accessin g dat a in an h et er ogen eou s en vir on m en t of bot h
r elation al an d object dat abase syst em s.
Th e O2 OD BC in t er face h an dles clien t applicat ion r equ est s to a dat abase
an d r etu r n s th e dat abase ser ver ’s r espon se.
Th is in tr odu ct or y ch apt er is divided as follows:
• Syst em over view
• ODBC
• M an u al Over view
O2 ODBC User M an u al
11
1
Introduction
1.1 System overview
h e syst em ar ch it ectu r e of O2 is illu str at ed in Figu r e 1.1.
External
Interfaces
Development Tools
Standard
Dev. Tools
O2 Dev. Tools
OQL
C
O2 C
C++
Java
Database Engine
O2Engine
O2Store
O2ODBC
O2Corba
O2DB
Access
O2Web
Fi gu r e 1 .1 : O2 Sy st em Ar ch i t ect u r e
Th e O 2 system can be viewed as con sist in g of t h r ee com pon en t s. Th e
Database Engine pr ovides all t h e feat u r es of a D at abase syst em an d an
object -or ien t ed syst em . Th is en gin e is accessed wit h Development Tools ,
su ch as var iou s pr ogr am m in g lan gu ages, O 2 developm en t t ools an d an y
st an dar d developm en t tool. Nu m er ou s External Interfaces ar e pr ovided.
All en com passin g, O 2 is a ver sat ile, por t able, dist r ibu t ed,
h igh -per for m an ce dyn am ic object -or ien t ed dat abase syst em .
Database En gin e:
12
• O2 St or e
Th e dat abase m an agem en t syst em pr ovides low level
facilit ies, t h r ou gh O2 Stor e API, t o access an d m an age a
dat abase: disk volu m es, files, r ecor ds, in dices an d
t r an sact ion s.
• O2 En gin e
Th e object dat abase en gin e pr ovides dir ect con t r ol of
sch em as, classes, object s an d t r an sact ion s, t h r ou gh
O2 En gin e API. It pr ovides fu ll t ext in dexin g an d sear ch
capabilit ies wit h O 2 Sear ch an d spat ial in dexin g an d
r et r ieval capabilit ies wit h O 2 Spat ial. It in clu des a
Not ificat ion m an ager for in for m in g oth er clien t s
con n ect ed t o t h e sam e O 2 ser ver t h at an even t h as
occu r r ed, a Ver sion m an ager for h an dlin g m u lt iple
object ver sion s an d a Replicat ion API for syn ch r on izin g
m u lt iple copies of an O2 system .
O2 OD BC User Man u al
System overview :
Pr ogr am m in g Lan gu ages:
O 2 object s m ay be cr eat ed an d m an aged u sin g t h e followin g
pr ogr am m in g lan gu ages, u tilizin g all t h e featu r es available wit h O2
(per sist en ce, collection m an agem en t , t r an sact ion m an agem en t , OQL
qu er ies, et c.)
• C
O2 fu n ct ion s can be in vok ed by C pr ogr am s.
• C++
OD MG com plian t C++ bin din g.
• J ava
OD MG com plian t J ava bin din g.
• O2 C
A power fu l an d elegan t object-or ien t ed fou r t h
gen er at ion lan gu age specialized for easy developm en t
of object dat abase applicat ion s.
• OQL
OD MG st an dar d, easy-t o-u se SQL-lik e object qu er y
lan gu age wit h special feat u r es for dealin g wit h com plex
O2 object s an d m eth ods.
O 2 Developm en t Tools:
• O2 Gr aph
Cr eat e, m odify an d edit an y t ype of object gr aph .
• O2 Look
Design an d develop gr aph ical u ser in ter faces, pr ovides
in t er active m an ipu lation of com plex an d m u ltim edia
object s.
• O2 Kit
Libr ar y of pr edefin ed classes an d m et h ods for fast er
developm en t of u ser applicat ion s.
• O2 Tools
Com plet e gr aph ical pr ogr am m in g en vir on m en t t o
design an d develop O 2 dat abase applicat ion s.
St an dar d Developm en t Tools:
All stan dar d pr ogr am m in g lan gu ages can be u sed wit h st an dar d
en vir on m en t s (e.g. Visu al C++, Su n Spar cwor k s).
Exter n al In t er faces:
• O2 Cor ba
Cr eat e an O2 / Or bix ser ver t o access an O2 dat abase
wit h CORBA.
• O2 DBAccess Con n ect O2 applicat ion s to r elation al dat abases on
r em ot e h ost s an d in vok e SQL st at em en ts.
• O2 OD BC
Con n ect r em ot e ODBC clien t applicat ion s t o O2
dat abases.
O 2 Web Cr eat e an O2 Wor ld Wide Web ser ver t o access an O2 dat abase
t h r ou gh t h e in t er n et n et wor k .
O2 ODBC User Man u al
13
1
Introduction
1.2 ODBC
ODBC (Open Dat abase Con n ect ivit y) is a stan dar d in t er face for
accessin g dat a in an h et er ogen eou s en vir on m en t of r elation al an d
n on -r elat ion al dat abase m an agem en t syst em s. Man y existin g t ools u se
ODBC t o access a dat abase, e.g. Wor d, Excel, D elph i, et c.
An ODBC clien t applicat ion u ses t h e ODBC API t o r equ est an d/ or sen d
dat a t o a dat abase ser ver . Th e ODBC dr iver t r an slat es clien t r equ ests
an d ser ver an swer s in t o a for m at t h at t h e DBMS ser ver an d t h e ODBC
clien t can u n der st an d. Th e ODBC API defin es a set of cor e fu n ct ion s,
t h at cor r espon d to t h e fu n ct ion s in t h e X/ Open an d SQL Access Gr ou p
Call Level In t er face specificat ion , toget h er wit h t wo ext en ded set s of
fu n ct ion alit y. ODBC defin es a st an dar d SQL gr am m ar , wh ich dr iver s
t r an slat e t o th e n ative SQL gr am m ar s u sed by var iou s DBMSs.
14
O2 OD BC User Man u al
O2 and ODBC :
1.3 O2 and ODBC
O 2 OD BC is an ODBC dr iver bu ilt on top of O2 . It allows exist in g ODBC
applicat ion s t o r u n on t op of an O 2 database an d n ew OD BC
applicat ion s t o be defin ed on t op of O 2 t h r ou gh th e ODBC API. O 2
ODBC wor k s on a r elat ion al view of an O 2 base.
St ar t in g fr om an exist in g O2 base, t h e ODBC applicat ion can r et r ieve
in for m ation abou t t h e r elat ion al view der ived for th at base: t ables,
colu m n s, pr im ar y an d for eign k eys, et c.
Th e r elat ion al view der ivat ion is per for m ed by a special t ool an d can be
cu st om ized by t h e u ser t h r ou gh a con figu r at ion file. M an y differ en t
views can be defin ed for a given O2 base. O2 ODBC im plem en t s th e
cor e ODBC API an d som e Level 1 an d Level 2 ext en sion s (e.g. r et r ieve
cat alog an d par am et er in for m at ion ). In addit ion , it su ppor t s t h e cor e
SQL gr am m ar an d par t of t h e ext en ded gr am m ar level (e.g. pr ocedu r e
calls an d lon g dat a).
SQL qu er ies (SELECT-FROM-WHERE) for m u lat ed on t h e r elat ion al view,
an d sen t t h r ou gh O2 OD BC ar e t r an slat ed in t o t h e cor r espon din g OQL
qu er ies. D at a u pdat e SQL com m an ds (INSERT-UPDATE-DELETE) ar e
in t er pr et ed by t h e O 2 OD BC dr iver , wh ich per for m s u pdates on t h e
cor r espon din g O2 dat a t h r ou gh t h e O 2 API in t er face.
SQL cat alog com m an ds (CREATE TABLE, for in stan ce) ar e also
in t er pr et ed by t h e O 2 ODBC dr iver , wh ich u pdat es t h e O2 dat abase
sch em a accor din gly. Tables an d views can be t h er efor e cr eated fr om
scr at ch r at h er th an bein g der ived fr om exist in g O2 classes. Th e two
k in ds of t ables (syst em -der ived an d application -defin ed) can be
com bin ed in an ODBC application .
O2 ODBC User Man u al
15
1
Introduction
1.4 Manual Overview
Th is m an u al is divided in to t h e followin g ch apt er s:
• Ch apt er 1
In t r odu ces O2 OD BC.
• Ch apt er 2
D escr ibes h ow t o in st all O2 OD BC.
• Ch apt er 3
Gives an over view of O2 OD BC.
• Ch apt er 4
D escr ibes h ow O2 sch em as an d O2 dat a ar e t r an slat ed in t o equ ivalen t
SQL en t it ies.
• Ch apt er 5
D escr ibes h ow t o u se t h e O2 ODBC dr iver , it s feat u r es an d lim it at ion s.
• Ch apt er 6
Sh ow h ow pr ogr am m er s can u se t h e o2_Odbc class to bu ild t h eir own
O 2 ODBC ser ver s.
• Ch apt er 7
A r efer en ce gu ide for O 2 ODBC.
Two appen dixes com plet e t h is m an u al:
• Appen dix A
Gives t h e com plet e syn t ax for wr it in g con figu r ation files u sed for view
cu st om izin g by t h e o2sql_export tool.
• Appen dix B
Gives t h e valu es r et u r n ed by t h e SQLGetInfo ODBC API fu n ct ion for all
possible fInfoType in pu t ar gu m en t valu es.
16
O2 OD BC User Man u al
Background :
1.5 Background
We asu m e th e r eader is fam iliar wit h OD BC an d O 2 . Th e followin g
r efer en ces pr ovide essen tial in for m at ion :
• ODBC SDK 2.1 Programmer’s Reference, Micr osoft D evelopm en t
Libr ar y.
• O2 Sy stem version 5.x Ad mistration Manuals , O2 Tech n ology.
• Understanding the new SQL: a complete guide, J . M elt on an d A. R.
Sim on , M or gan Kau fam an n , 1993.
O2 ODBC User Man u al
17
1
18
Introduction
O2 OD BC User Man u al
2
2
O2ODBC Installation
Th is ch apt er addr esses th e in stallat ion of an O2 ODBC dr iver an d
det ails t h e con t en t s of t h e O2 ODBC dist r ibu t ion pack age.
Th e r eader sh ou ld be fam iliar with t h e ODBC en vir on m en t an d r elat ed
con cept s.
O2 ODBC User M an u al
19
2
O2ODBC Installation
2.1 Hardware and Software Requirements
Th e O 2 ODBC dr iver r equ ir es t h e followin g h ar dwar e:
• IBM-com patible PC
• 8 MB RAM r equ ir ed
• Har d Disk Space: 1.5 MB for th e in stallation .
• Th e O2 OD BC dr iver r equ ir es t h e followin g soft war e:
• O2 D BM S
In or der t o access dat a in an O2 dat abase with t h e O2 ODBC dr iver , you
m u st h ave t h e O2 D BM S ver sion 5.x. For in for m ation on soft war e an d
h ar dwar e r equ ir em en t s for t h e O2 DBM S ver sion 5.x, r efer t o th e O2
Syst em Adm in istr at ion M an u als.
• Micr osoft Win dows 95 or Micr osoft Win dows NT
• Net wor k soft war e
A n et wor k is r equ ir ed to con n ect th e plat for m s on wh ich t h e O 2 ODBC
clien t an d O2 ODBC ser ver r eside. For in for m ation on t h e soft war e an d
h ar dwar e r equ ir ed by you r n etwor k , see it s docu m en t at ion .
2.2 O2 ODBC Distribution Package
Th e O 2 ODBC dist r ibu t ion pack age con t ain s t h e followin g:
• Th e Dyn am ic Lin k Libr ar ies (DLL) libo2dri.dll an d libo2com.dll.
• Th e setup.exe pr ogr am .
• Th e o2open_dispatcher pr ogr am .
• Th e o2odbc_dump_base pr ogr am .
• Th e o2odbc_server pr ogr am .
20
O2 OD BC User Man u al
Setting up the O2 ODBC Driver :
• Th e o2sql_export pr ogr am .
• Th e o2sql_query pr ogr am .
• Th e o2_Odbc.hxx in clu de files.
• Th e libsql.so an d libo2odbc_svr.so libr ar ies.
Th ese libr ar ies ar e u sed by t h e differ en t O 2 ODBC r elated t ools an d ar e
n ecessar y t o bu ild a u ser specific O2 ODBC ser ver .
2.3 Setting up the O2 ODBC Driver
Th e in st allat ion pr ocedu r e descr ibed below assu m es t h at you h ave
du m ped t h e con t en t s of t h e O2 ODBC dist r ibu t ion pack age t o t h e disk
of t h e Win dows 95 or Win dows NT st at ion wh er e th e dr iver is t o be
in st alled. Th e st r u ct u r e of th e O2 OD BC dist r ibu tion pack age is th e
followin g:.
o2odbc
include
o2_Odbc.hxx
install
o2driver
libo2com.dll
libo2dri.dll
odbc32
setup.exe
odbcad32.exe
...
lib
libsql.so
libo2odbc_svr.so
bin
o2open_dispatcher
o2odbc_dump_base
o2odbc_server
o2sql_export
o2sql_query
doc
o2odbc_manual.pdf
O2 ODBC User Man u al
21
2
O2ODBC Installation
Installing the driver
Go t o th e su b-dir ect or y o2odbc/install/odbc32 an d r u n t h e pr ogr am
setup.exe. Th is pr ogr am will pr om pt you for con fir m at ion an d t h en
in st all t h e O2 OD BC dr iver by copyin g all th e OD BC com pon en ts n eeded
t o r u n t h e dr iver in t h e syst em dir ect or ies.
At t h e en d of t h e in st allation pr ocess, t h e set u p pr ogr am pr om pt s you t o
declar e n ew dat a sou r ces on in st alled dr iver s. You can declar e O2 dat a
sou r ces at t h is poin t or , if you pr efer , you will be able t o m an age you r
dat a sou r ces u sin g t h e ODBC adm in ist r ator pr ogr am odbcad32.exe
locat ed in t h e sam e dir ect or y.
Declaring the o2 open-dispatcher
Th e declar at ion of t h e o2open_dispatcher is a t wo st eps pr ocess:
• Declar e in t h e O2OPEN_DISPATCHER var iable t h e n am e of th e m ach in e on
wh ich t h e dispatch er is r u n n in g.
On Win dows NT, open t h e con tr ol pan el pr ogr am , ch oose t h e system
icon an d select th e “environment” pan e. You can t h en add t h e n ew
var iable.
On Win dows 95, declar e t h e var iable in you r autoexec.bat file by
addin g t h e followin g lin e: set O2OPEN_DISPATCHER=<machine name>.
• Declar e in t h e ser vices file t h e por t on wh ich t h e dispatch er is r each able.
On Win dows NT, edit t h e file <WINDIR>/system32/drivers/etc/
services an d add t h e followin g lin e: o2opendispatcher <port
number>/tcp.
On Win dows 95, edit t h e file <WINDIR>/services an d add th e followin g
lin e: o2opendispatcher <port number>/tcp.
Installing the tools
On ce th e dr iver an d dispat ch er h ave been declar ed on t h e clien t side,
t h e O 2 ODBC t ools m u st be in st alled on t h e ser ver side, i.e. on t h e
m ach in e wh er e th e O2 dat abase syst em is in st alled. Assu m in g t h e
en vir on m en t var iable O2HOME den otes t h e O 2 in st allat ion dir ect or y, t h e
followin g com pletes t h e in st allat ion of O 2 ODBC:
22
O2 OD BC User Man u al
Adding, Modifying and Deleting O2 ODBC Data
cp o2odbc/include/* $O2HOME/include;
cp o2odbc/bin/* $O2HOME/<platform>/bin;
cp o2odbc/lib/* $O2HOME/<platform>/lib;
2.4 Adding, Modifying and Deleting O2 ODBC
Data Sources
On t h e clien t side, an O2 data sou r ce is added, m odified an d delet ed
u sin g t h e st an dar d OD BC Adm in ist r at or t ool. In th e Dat a Sou r ces
dialog box of t h is t ool, a n ew dat a sou r ce can be added by click in g on
t h e Add bu t t on . Assu m in g t h e O 2 ODBC dr iver h as been alr eady
in st alled, it can be select ed fr om t h e In st alled ODBC D r iver s list t h at is
displayed in t h e Add Dat a Sou r ce dialog box. An O2 ODBC Set u p dialog
box is displayed t o allow t h e opt ion valu es t o be set an d t h e data sou r ce
defin ition t o be com plet ed on t h e clien t side.
M odificat ion an d delet ion of O 2 dat a sou r ces ar e car r ied ou t in an
an alogou s way, by followin g t h e appr opr iat e opt ion s fr om t h e Data
Sou r ces dialog box of t h e ODBC Adm in ist r ator t ool.
On t h e O2 OD BC ser ver side, a dat a sou r ce cor r espon ds t o an O 2 base
on wh ich a view sch em a gen er at ed wit h t h e o2sql_export t ool fr om th e
sch em a of t h e base h as been gen er ated. For m or e det ails on data
sou r ces, see Section 5.2.
O2 ODBC User Man u al
23
2
24
O2ODBC Installation
O2 OD BC User Man u al
3
3
O2 ODBC Overview
Th is ch apter is an in t r odu ction t o t h e m ain O 2 ODBC con cept s. It gives
an over view of t h e dr iver ar ch it ectu r e an d descr ibes it s m ain
com pon en t s.
Th is ch apt er pr ovides an over view of th e O2 ODBC dr iver ar ch it ect u r e
an d t h e way it wor k s.
We assu m e t h e r eader is fam iliar wit h t h e ODBC en vir on m en t an d
r elated con cept s an d wit h O 2 gen er al ar ch itect u r e an d r elated
con cept s.
O2 ODBC User M an u al
25
3
O2 ODBC Overview
3.1 O2 ODBC Architecture
Th e ar ch itect u r e of t h e O2 OD BC pr odu ct is depict ed in t h e Figu r e
below:
Client Windows 95
Client NT
query
SQL
o2open
dispatcher
query
result
server
server
O2 ODBC
O2 ODBC
server
O2 ODBC
server O2
server O2
We iden tify t h e followin g m ain elem en t s in t h is ar ch it ect u r e:
• an O2 ser ver
Th is is t h e st an dar d o2server pr ogr am .
• an O2 OD BC ser ver
Th is is t h e o2odbc_server pr ogr am wh ich is con n ect ed t o an O2
ser ver .
• an O2 Open D ispat ch er dispat ch er
Th is is t h e st an dar d o2open_dispatcher pr ogr am .
26
O2 OD BC User Man u al
O2 ODBC Architecture : Outline of the O2 ODBC
Outline of the O2 ODBC driver activity
An O2 OD BC dr iver wor k s in th e followin g way:
1. An ODBC clien t r equ est s a con n ect ion t o an O 2 ODBC dat a sou r ce.
2. Th e O2 OD BC clien t libr ar y con n ects (t h r ou gh SQLCon n ect or SQLDr iver Con n ect ODBC API fu n ct ion s) t o an O 2 Open Access dispat ch er
r u n n in g on t h e local ar ea n et wor k .
3. Th e O 2 Open Access dispat ch er t ells t h e OD BC clien t wh ich O2 ODBC
ser ver t o con n ect t o.
4. Th e ODBC clien t con n ect s t o th e appr opr iat e O2 ODBC ser ver .
5. On ce t h e con n ect ion h as been establish ed, th e OD BC clien t will u se
t h e ODBC API appr opr iat e fu n ction s (e.g. SQLExecute) t o access
dat a in t h e dat a sou r ce t o wh ich it is con n ect ed.
6. Th e O2 ODBC ser ver pr ocesses t h e clien t r equ est s. It is con n ect ed t o
an O 2 ser ver an d per for m s qu er y t r an slation an d execu t ion .
7. Th e O2 OD BC ser ver r et u r n s dat aqu er y r esu lt dat a t o th e clien t on
dem an d (e.g. SQLBind, SQLFetch, SQLGetData).
Advantages of the O2 ODBC architecture
Th e ar ch it ect u r e of t h e O2 ODBC dr iver pr ovides n u m er ou s feat u r es
t h at en h an ce t h e applicat ion s per for m an ce:
• Mu lt i-t h r eadin g
O2 OD BC allows an applicat ion to u se m u lt iple th r eads in or der t o
con cu r r en tly per for m differ en t t r eat m en t s. Th e m odu le pr ovides
som e syn ch r on izat ion fu n ct ion s t h at allow clien t application developer s t o u se m u lt iple t h r eads in t h e clien t par t wh ile pr ot ect in g t h e
applicat ion fr om for bidden r esou r ces access violat ion .
• Load-balan cin g
Th e dispatch er m odu le is an in depen den t m odu le u sed t o r ou t e con n ect ion s fr om an ODBC clien t to an ODBC ser ver an d t o pr eser ve an
efficien t load-balan cin g (st at ic an d dyn am ic load-balan cin g) am on g
t h e n et wor k . It s r ole is t o m an age a pool of ODBC ser ver s available
t h r ou gh ou t t h e n et wor k t o an swer clien t s r equ est s.
• Flexible deploym en t
O2 OD BC allows t o dist r ibu t e th e applicat ion am on g m u lt iple
m ach in e if n ecessar y, t h u s offer in g an easy way t o su ppor t scalabilit y. Mu lt iple ODBC ser ver s can be r u n on differ en t m ach in es, t h e
u ser load bein g dist r ibu t ed am on g t h ese m ach in es accor din g t o cr it er ia lik e cu r r en t load, m ach in e ch ar act er ist ics, et c. Th is locat ion -in depen den t m odel m ak es it easy t o ch an ge deploym en t sch em es as t h e
O2 ODBC User Man u al
27
3
O2 ODBC Overview
applicat ion gr ows. As dem an d gr ows, oth er O2 ODBC ser ver s can be
added on ot h er m ach in es, an d t h e dem an d can be dist r ibu ted am on g
t h ose ser ver s wit h ou t an y code ch an ges.
In t h e r em ain in g of t h is ch apt er , we give an over view of t h e t wo m ain
com pon en t s of t h e O 2 ODBC dr iver ar ch it ect u r e, n am ely t h e O 2 SQL
com pon en t an d t h e O2 OD BC ser ver .
3.2 O2 SQL
We den ot e by O 2 SQL th e m odu le of t h e O2 OD BC ar ch itect u r e
im plem en t in g t h e sch em a an d qu er y t r an slat ion capabilit ies of t h e
dr iver . Th is m odu le is basically com posed of t h e O2 SQL libr ar y
libsql.so t oget h er wit h t wo developm en t t ools o2sql_export an d
o2sql_query t h at can be u sed in depen den tly of t h e O 2 ODBC dr iver .
Th e O2 SQL libr ar y is u sed by t h e O 2 ODBC dr iver ser ver com pon en t . It
im plem en t s t h e sch em a an d qu er y t r an slat ion ser vices n ecessar y t o
allow O2 dat a t o be accessed t h r ou gh SQL. O2 SQL is bu ilt on t op of
OQL an d t h e O2 En gin e.
Th e o2sql_export tool is u sed t o der ive r elat ion al views fr om O 2
sch em as. Su ch a view m u st be der ived pr ior t o an y access t o O2 wit h
ODBC. Th e act ivat ion of a r elat ion al view on an O 2 base allows su ch
base t o be seen as a r elat ion al dat abase. Object s stor ed in O 2 ar e
per ceived as t u ples in r elat ion al tables (an object can spawn m or e t h an
on e t u ple in m or e th an on e table). It sh ou ld be n ot ed th at su ch t ables
exist on ly vir t u ally in t h e vir t u al dat abase r esu lt in g fr om a view
act ivat ion on an O2 base.
Th e o2sql_query t ool is an in t er active sh ell allowin g SQL com m an ds t o
be execu ted on a vir t u al dat abase. Th is can be a u sefu l t ool for qu ick ly
in spect in g view sch em as an d dat abases an d t u n n in g SQL applicat ion s
r u n n in g on O2 .
Given t h e separ at ion bet ween th e O2 SQL an d t h e O2 ODBC dr iver
im plem en t at ion , it is possible t o see an d qu er y O 2 dat a as r elat ion al
dat a t h r ou gh SQL wit h ou t u sin g an OD BC in ter face. An API fu n ct ion
o2_sql, an alogou s t o t h e st an dar d O2 API fu n ct ion o2_oql can be
u sed t o execu t e SQL qu er ies fr om a given O2 En gin e API pr ogr am .
O2 SQL is det ailed in Ch apt er 4.
28
O2 OD BC User Man u al
O2 ODBC Server : Advantages of the O2 ODBC
3.3 O2 ODBC Server
Th e O2 ODBC ser ver is bu ilt on t op of O2 SQL.
Th e ser ver pr ocesses clien t r equ ests. Th ese r equ est s ar e issu ed t h r ou gh
t h e ODBC API. SQL qu er ies sen t by a clien t t o be execu t ed on a O 2
data sou r ce ar e tr an slat ed by t h e ser ver in t o an equ ivalen t OQL qu er y
an d execu t ed on t h e O2 base t o wh ich t h e clien t is con n ect ed.
A ser ver can h an dle r equ est s of differ en t clien t s wor k in g on differ en t
dat a sou r ces. Befor e pr ocessin g t h e r equ est of a given clien t , t h e ser ver
act ivat es t h e clien t ’s dat a sou r ce, i.e. it act ivat es t h e appr opr iat e view
on t h e O2 base t o wh ich t h e clien t is con n ect ed.
A ser ver u ses t h e qu er y t r an slat ion ser vices im plem en ted in t h e O2 SQL
libr ar y. It per for m s, in addit ion , all t h e ODBC specific pr ocessin g (dat a
con ver sion s, cu r sor m an agem en t , cat alog dat a r et r ieval, et c) n ecessar y
t o r espon d t o ODBC clien t s r equ ests.
Th e O2 ODBC ser ver is det ailed in Ch apt er 5.
O2 ODBC User Man u al
29
3
30
O2 ODBC Overview
O2 OD BC User Man u al
4
4
O2 SQL
Th e O2 SQL libr ar y an d r elat ed t ools ar e defin ed as a separ at e pr odu ct
an d u sed by t h e O 2 ODBC ser ver .
O2 SQL pr ovides t wo m ain ser vices for applicat ion s wan t in g t o access
O2 dat abases t h r ou gh SQL: a sch em a an d a qu er y t r an slat or .
Th is ch apt er pr esen t s h ow t o defin e soph ist icated SQL views of O2 data
in stead of u sin g t h e defau lt view der ivat ion r u les, so as t o adapt th e
r elation al st r u ct u r e t o t h e n eeds of a par t icu lar applicat ion .
To cu st om ize t h e way a r elation al view of an O 2 sch em a is der ived,
Sect ion 4.1 an d, in par t icu lar , Sect ion “View cu st om ization ” pr ovide
u sefu l in for m at ion .
To for m u lat e com plex qu er ies an d u pdat es oper at ion s on O 2 dat a
t h r ou gh t h e SQL in t er face, Sect ion 4.2 gives som e h in t s on h ow t o
per for m an d opt im ize su ch oper at ion s. In par t icu lar , Sect ion "Sch em a
Updat e Com m an ds" sh ou ld be r ead by t h ose wan t in g t o popu lat e an O2
sch em a th r ou gh SQL (wit h t able cr eat ion com m an ds), wh er eas Sect ion
"Dat a Updat e Com m an ds" pr ovides u sefu l in for m at ion for t h ose
wan t in g to cr eat e an d u pdate O 2 dat a t h r ou gh SQL.
All u ser s wish in g t o access O2 dat a th r ou gh SQL sh ou ld r ead Sect ion
4.3.
We assu m e t h e r eader is fam iliar wit h t h e O2 an d r elation al dat a
m odels, as well as wit h SQL r elat ed con cept s in gen er al.
O2 ODBC User M an u al
31
4
O2 SQL
4.1 Schema Translation
An object m odel capt u r es sem an t ics of application dom ain s in a m or e
elabor at e way an d it pr om ot es adh er en ce t o n or m al for m s. Relat ion al
sch em as der ived fr om a r ich er object m odel tend to be in th ir d n or m al
for m . Th e dat abase design er can t h u s ben efit s fr om th e expr essiven ess
an d exten sibilit y, am on g ot h er s, of an object m odel an d st ill im plem en ts
su ch a m odel in t er m s of well n or m alized r elat ion al t ables.
Th e st r u ct u r e of data stor ed in an O2 base is defin ed in t h e sch em a of
t h at base. Th e sch em a con tain s class an d t ype defin ition s, as well as
dat a en tr y poin t s (n am es t h at play t h e r ole of r oot s of per sist en ce).
As SQL qu er ies can be for m u lat ed on r elat ion al dat a on ly, it is n ecessar y
t o pr ovide a relational view of O2 dat a t o be able to qu er y su ch dat a
wit h SQL.
A r elat ion al view sch em a is der ived fr om an O2 sch em a with t h e
o2sql_export t ool. We say t h at t h e O2 sch em a is expor t ed t o SQL.
Wh en a view is der ived, it s defin it ion is k ept by t h e O2 syst em in an
in t er n al st r u ct u r e called t h e SQL cat alog (see “View cr eat ion t ool
o2sql_expor t ” on page 61 ). Man y differ en t view sch em as can be der ived
for a given O2 sch em a. All views der ived for a given O2 sch em a can be
act ivat ed on ever y base in st an ce of t h at sch em a.
4.1.1 SQL View Schema
We r ecall below t h e m ain feat u r es of t h e O2 an d r elat ion al sch em as,
befor e con sider in g t h e t r an slat ion of an in st an ce of t h e for m er in t o on e
of t h e lat t er .
O2 Schema
An O 2 sch em a is com posed of a set of class defin it ion s. A class can
in h er it fr om a n u m ber of classes. To each class a t ype is associat ed.
Valid t ype con st r u ctor s in clu de set , list an d tuple con st r u ctor s, t h at can
be applied r ecu r sively to defin e ar bitr ar ily com plex t ypes fr om class
t ypes (each class defin es a type) an d at om ic t ypes (integer , real , char ,
string, boolean ). Met h ods can be defin ed in a class t o be applied on it s
in st an ces.
Relational Schema
A r elat ion al sch em a is com posed of a set of t able defin it ion s. Each t able
is com posed of a set of colu m n s, each of a given at om ic type. A su bset of
t h e colu m n s of a t able can be declar ed as a pr im ar y k ey ser vin g t o
32
O2 OD BC User Man u al
Schema Translation : Example
u n iqu ely iden tify r ows in t h e t able. For eign k eys can poin t t o pr im ar y
k eys, allowin g r efer en ces t o be establish ed am on g r ows in t ables.
Sin ce dat a ar e n ot st or ed on t h e r elat ion al database, per for m an ce is n ot
an issu e an d we do n ot pay at t en t ion t o t able fr agm en tation (ver t ical
par tit ion in g). Never t h eless, we t r y t o r edu ce th e n u m ber of collect ion
t ables in or der t o sim plify qu er y for m u lat ion . In t h at sen se, we decided
t o u n n est tu ple at t r ibu t es in st ead of gen er at in g collect ion t ables
cor r espon din g to w eak entities in t h e r elat ion al sch em a.
Example
Let u s con sider an exam ple of sch em a t r an slat ion befor e con sider in g
t h e differ en t aspect s of th e sch em a t r an slat ion pr ocess in t u r n .
Th e O2 sch em a document given in Figu r e 4.1 m odels in for m ation abou t
ar t icles, t h eir au t h or s an d r espect ive affiliat ion s. Th e r elat ion al sch em a
obt ain ed fr om t h e O2 sch em a in Figu r e 4.1 is given in Figu r e 4.2.
Each class is m apped t o an h om on ym ou s table. For t able Article,
at t r ibu t e title is a pr im ar y k ey an d date_title is a for eign k ey
poin t in g t o t able Date. At t r ibu t es of t h e n est ed t u ple at t r ibu t e address
in class Author ar e u n n est ed in t h e cor r espon din g t able. Set an d list
at t r ibu t es ar e m apped t o t h e so called collect ion t ables
Article_authors an d Article_sections. Su ch t ables associat e t o
each in st an ce of Article th e cor r espon din g set of au t h or s an d list of
sect ion s r espect ively. For t h e list at t r ibu t e, an addit ion al att r ibu t e pos
r epr esen t s t h e posit ion of elem en t s in t h e list .
An SQL clien t k n owin g t h e r elat ion al sch em a above can for m u lat e
qu er ies on it. Su ch qu er ies ar e t r an slat ed in t o OQL qu er ies t h at ar e
evalu at ed on an O2 base in st an ce of t h e or igin al sch em a.
Class translation
Each O2 class is m apped in t o a so called class table. A colu m n title of
defau lt t ype LONGVARCHAR is defin ed by defau lt in t h e cor r espon din g
t able an d declar ed as a pr im ar y k ey, u n less a differ en t logical k ey is
declar ed for th e class in t h e con figu r at ion file.
A class m u st h ave an associat ed h om on ym ou s n am ed set defin ed in t h e
sch em a an d m odelin g it s ext en t in t h e O 2 base. If, h owever , su ch a
n am ed set is n ot explicit ly defin ed in t h e O2 sch em a, a vir t u al ext en t
(i.e. an OQL qu er y givin g a set of object of th e class as a r esu lt ) m u st be
pr ovided in a con figu r at ion file u sed to der ive t h e view sch em a, as it will
be descr ibed lat er .
O2 ODBC User Man u al
33
4
O2 SQL
.
schema document;
class Article type tuple(
title:string,
authors:set(Author),
sections:list(Section),
date:Date)
end;
class Author type tuple(
name:string,
address:tuple(institute:Institute,email:string))
end;
class Institute type tuple(
name:string,
country:string)
end;
class Section type tuple(
title:string,
contents:string)
end;
class Date type tuple(
day:integer,
month:integer,
year:integer)
end;
name Articles:set(Article);
Fi gu r e 4 .1 : O2 sch em a
document
Primary Key Definition
Wh en m appin g object str u ct u r es t o r elat ion al t u ples, we m u st defin e
pr im ar y k eys so as t o be able t o u n iqu ely iden t ify object s wh en t h ey ar e
qu er ied t h r ou gh SQL in t h eir t u ple for m . In O 2 , object iden t ifier s ar e
n ot available to t h e u ser . Th ey ar e u sed in t er n ally by t h e syst em at t h e
object st or e level an d ar e n ot ext er n alized.
In O2 , each class defin es a m et h od title, in h er ited fr om class Object,
wh ich by defau lt gives t h e n am e of t h e class of t h e object . Th is m eth od
can be r edefin ed in a su bclass as a m et h od or an at t r ibu te t h at gives a
differ en t valu e for each object of t h e class, t h er efor e playin g t h e r ole of
logical id entifier of t h e object .
34
O2 OD BC User Man u al
Schema Translation : Primary Key Definition
.
CREATE SCHEMA document
CREATE TABLE Article( title LONGVARCHAR
date_title LONGVARCHAR PRIMARY KEY ( title )
FOREIGN KEY ( date_title ) REFERENCES Date )
CREATE TABLE Author( title LONGVARCHAR name LONGVARCHAR
address_institute_title LONGVARCHAR
address_email LONGVARCHAR
PRIMARY KEY ( title )
FOREIGN KEY ( address_institute_title )
REFERENCES Institute )
CREATE TABLE Date(
title LONGVARCHAR day INTEGER month INTEGER
year INTEGER PRIMARY KEY ( title ) )
CREATE TABLE Section(
title LONGVARCHAR title LONGVARCHAR ~
contents LONGVARCHAR PRIMARY KEY ( title ) )
CREATE TABLE Institute(
title LONGVARCHAR name LONGVARCHAR
country LONGVARCHAR PRIMARY KEY ( title ) )
CREATE TABLE Article_authors(
Article_title LONGVARCHAR
authors_title LONGVARCHAR
FOREIGN KEY ( Article_title )
REFERENCES Article
FOREIGN KEY ( authors_title )
REFERENCES Author )
CREATE TABLE Article_sections(
Article_title LONGVARCHAR
pos INTEGER
sections_title LONGVARCHAR
PRIMARY KEY ( Article_title, pos )
FOREIGN KEY ( Article_title )
REFERENCES Article
FOREIGN KEY ( sections_title )
REFERENCES Section )
Fi gu r e 4 .2 : Rel a t i on a l sch em a
document
Th e title m et h od or att r ibu t e is t h u s t r an slat ed by defau lt as a
pr im ar y k ey in t h e cor r espon din g t able bu t a differ en t at t r ibu te or
m et h od can be declar ed t o be u sed as a k ey in th e con figu r at ion file.
O2 ODBC User Man u al
35
4
O2 SQL
Rem ar k 1 : It is u p to t h e object sch em a design er to gu ar an t ee t h at t h e
valu e r etu r n ed by su ch a m eth od or at t r ibu te r em ain s con st an t , at least
du r in g an SQL section on t h e object dat abase. Su ch iden t ifier sh ou ld be
com plet ely in depen den t on ch an ges to t h e object valu e an d on ph ysical
locat ion . In pr act ice, we r equ ir e th e valu e of a logical iden t ifier t o depen d
on con st an t at t r ibu t es, i.e. at t r ibu tes t h at ar e n ot lik ely t o ch an ge aft er
object cr eat ion , an d, in ou r fr am ewor k , at t r ibu tes t h at ar e n ot lik ely t o
be u pdat ed by an SQL st at em en t .
Rem ar k 2 : Th e ch oice bet ween an at t r ibu t e or a m et h od k ey is an
im por t an t on e, as SQL qu er ies m at ch in g r ows based on th eir pr im ar y
k ey colu m n s will be m apped in t o OQL qu er ies r et r ievin g object s based
on t h e cor r espon din g k ey at t r ibu t es or m et h ods. If at tr ibu t es ar e u sed
in st ead of m et h ods t o iden t ify object s in O 2 , t h en in dexes on su ch
at t r ibu t es can be defin ed t o opt im ize t h e qu er y evalu at ion .
Rem ar k 3 : Th e m eth od title, or an y oth er m et h od declar ed as a
logical k ey in t h e con figu r at ion file, can r et u r n t h e external id entifier of
t h e object on wh ich it is applied. Th is iden t ifier can be pr ovided by O2
En gin e on u ser ’s r equ est .
Attribute Translation
Att r ibu t es in a t u ple-t yped class r epr esen t r elat ion sh ip am on g object s
an d valu es. Th ese can be on e-t o-on e, on e-t o-m an y or m an y-t o-m an y
r elat ion sh ips. Th e sim pler case, t h at of at om ic an d object at t r ibu t es,
cor r espon d t o on e-to-on e r elat ion sh ips an d ar e dir ect ly tr an slat ed as
colu m n s in t h e cor r espon din g der ived t able. We con sider t h em fir st
befor e look in g at com plex att r ibu t es (t u ple an d collection at t r ibu t es).
Atomic and Class Attributes
Each at t r ibu te h avin g an at om ic or class type in t h e O 2 class becom es a
colu m n in t h e cor r espon din g t able wit h a t ype given by t h e t ype
m appin g defin ed below.
OQL type t
integer
real
char
string
bytes
boolean
class
TSQL(t)
INTEGER
REAL
CHARACTER
LONGVARCHAR
LONGVARBINARY
CHARACTER
LONGVARCHAR
Th e colu m n t ak es it s n am e fr om t h e at t r ibu t e n am e, u n less a r en am in g
is defin ed in th e con figu r at ion file.
36
O2 OD BC User Man u al
Schema Translation : Tuple Attributes
An at t r ibu t e poin t in g t o an ot h er object (at t r ibu t e of class t ype) becom es
a for eign k ey r efer en cin g t h e t able cor r espon din g t o th e class of t h e
poin t ed object .
For at t r ibu t es h avin g a com plex type we con sider t wo possibilit ies:
collection an d t u ple at t r ibu t es.
Tuple Attributes
Tu ple t ypes ar e u n n ested an d h ave t h eir att r ibu t es in cor por ated t o th e
t able cor r espon din g t o t h e type st r u ct u r e wh er e t h e t u ple type occu r s.
Tu ple at t r ibu tes ar e r en am ed befor e bein g m er ged, i.e. th e ou ter tu ple
at t r ibu t e n am e is pr efixed t o each at t r ibu te n am e of t h e n est ed t u ple to
avoid n am in g con flict s. Su ch at tr ibu t es can also be r en am ed by t h e
u ser th r ou gh t h e con figu r at ion file.
M er gin g at t r ibu t e t u ples with t h eir poin t in g object s leads t o r elat ion al
qu er ies t h at ar e easier t o for m u lat e. In addit ion , t u ple att r ibu t es ar e
valu es an d, opposit e t o object at t r ibu t es, can n ot be sh ar ed, an d placin g
t h em in an ext er n al au xiliar y t able wou ld be poin t less in t h at sen se.
Collection Attributes
A collect ion t ype at t r ibu te is t r an slat ed in to a so-called collect ion t able.
Th e type of t h e elem en t s of t h e collect ion ar e t r an slat ed r ecu r sively as
colu m n s in t h e collect ion t able (or ot h er collect ion t ables, for collect ion s
n est ed in collect ion s).
A set at t r ibu te m odels a on e-t o-m an y or a m an y-t o-m an y r elation sh ip. A
on e-t o-m an y r elat ion sh ip can be m er ged wit h a par ticipat in g object (all
object s in t h e set can poin t t o th e com posit e object ). Alt h ou gh t h e
ch oice on h ow t o m ap collect ion at t r ibu t es cou ld h ave been let t o t h e
u ser , we decided to m odel all su ch aggr egation s as dist in ct t ables, for
expedien cy of im plem en tation of t h e qu er y tr an slator .
Th e collect ion t able cor r espon din g to a set at t r ibu te is n am ed accor din g
t o t h e n am e of t h e class wh er e t h e at t r ibu t e is defin ed an d t h e n am e of
t h e at tr ibu t e it self, u n less it is explicit ly r en am ed in t h e con figu r at ion
file. A colu m n class _name + _title is defin ed t o h old t h e logical iden t ifier
of t h e com posit e object (t h e object h oldin g t h e set ). Th e ot h er colu m n s
of t h e collect ion t able ar e der ived based on t h e t ype of elem en t s of th e
set. For a set s of at om ic valu es, a colu m n s wit h t h e cor r espon din g
elem en t type is defin ed t o h old elem en t s of t h e set . For a set s of object s,
an at t r ibu t e s_title is defin ed t o h old t h e logical iden t ifier s of
elem en t s. For a set s of t u ples, each at t r ibu t e a i of t h e t u ple is t r eat ed
r ecu r sively an d m er ged to t h e collection t able as colu m n s_ai, as for
or din ar y t u ple att r ibu t es. For n est ed collect ion s, an ext r a collect ion
t able is der ived r ecu r sively. D efau lt k ey at t r ibu t e n am es for t h e k ey
colu m n s gen er at ed in th e collect ion t ables can be r en am ed th r ou gh t h e
con figu r at ion file as u su al.
O2 ODBC User Man u al
37
4
O2 SQL
List at t r ibu t es ar e also m apped t o collection t ables, as for set at tr ibu t es,
bu t an addit ion al colu m n pos of t ype INTEGER is defin ed t o h old t h e
posit ion of each elem en t in t h e list .
Rem ar k : In t h e ODMG C++ bin din g, collect ion s ar e im por ted as O 2
classes h avin g a collection t ype. In or der t o allow C++ applicat ion s t o
u se t h e O 2 / SQL bin din g, su ch classes will be t r eat ed by th e qu er y
t r an slat or as special classes t o allow a dir ect access t o t h e en capsu lated
collect ion s. For in st an ce, a class K wh ich en capsu lat es an O2 list will be
m apped in t o t wo t ables: TABLE K an d TABLE K_List . TABLE K h olds th e
object it self, wh ile TABLE K_List h olds it s valu e.
Inherited Attributes
Wh at abou t in h er it an ce? We con sider t wo alter n at ives: (1) m er gin g
in h er it ed at t r ibu tes wit h at tr ibu t es defin ed locally in t h e su bclass t o
der ive t h e cor r espon din g r elat ion ; (2) der ivin g a r elat ion with on ly locally
defin ed att r ibu t es plu s a for eign k ey poin t in g t o t h e cor r espon din g t u ple
in th e t able der ived for each su per class. Again , ou r ch oice is dict at ed by
t h e specificit ies of th e pr oblem in h an d: sin ce der ived t ables ar e n ot (at
least in pr in ciple) u sed t o st or e dat a, bu t exist on ly vir t u ally, we decided
t o collapse in h er it ed at t r ibu tes in t h e table der ived fr om a given
su bclass. Th e r esu lt in g tables ar e n ot n or m alized bu t ar e far easier t o
qu er y.
Data Retrieval Methods
In addit ion t o t h e colu m n s der ived t o h old t h e valu es of at t r ibu t es
defin ed in t h e t u ple t ype of a class, colu m n s can be der ived t o h old
valu es r et u r n ed by data retrieval method s . Th e ch oice of wh ich su ch
m et h ods to im por t is left t o t h e u ser , as t h ey m u st be explicitly declar ed
in th e con figu r at ion file.
As far as visibilit y is con cer n ed, on ly r ead an d pu blic at t r ibu t es (an d
m et h ods) sh ou ld be der ived as colu m n s in t h e cor r espon din g t able, in
or der to pr eser ve dat a en capsu lat ion an d r u les ou t u n au th or ized access
t h r ou gh t h e r elat ion al in t er face.
4.1.2 View Customization
Th e r elat ion al sch em a in Figu r e 4.2 r esu lts fr om a so-called default
mod e t r an slat ion .
In t h e defau lt m ode, t ables an d colu m n s ar e n am ed fr om th eir
cor r espon din g class an d at t r ibu t e n am es an d t h e exist en ce of a defau lt
logical title m et h od or at t r ibu t e is assu m ed for ever y class. In
38
O2 OD BC User Man u al
Schema Translation : Customized translation
addition , for qu er y t r an slat ion pu r poses, for each class C, th e system
assu m es t h e exist en ce of an h om on ym ou s n am ed set C m odellin g t h e
ext en t of t h e class.
Customized translation
In a customized mod e, t h e sch em a tr an slat or tak es in t o accou n t som e
u ser -su pplied in for m ation u sed for t h e gen er at ion of th e view sch em a
(an d con sequ en t ly for t h e t r an slat ion of SQL qu er ies in t o OQL).
View cu st om izat ion in clu des t h e followin g possibilit ies:
• h idin g of classes
By defau lt , ever y class defin ed in t h e O2 sch em a is der ived as a table
in t h e view sch em a, u n less it is h idden in t h e con figu r at ion file u sed
t o der ive t h e view.
• h idin g of at t r ibu t es
By defau lt , ever y att r ibu t e of a t u ple typed class is der ived as colu m n
(or possibly as a collect ion t able if it is a collection at t r ibu t e) in t h e
view sch em a, u n less it is h idden in t h e con figu r at ion file.
• r en am in g of classes
If a class r en am in g is n ot specified in t h e con figu r ation file, a t able
t ak es t h e sam e n am e as t h e class fr om wh ich it is der ived.
• r en am in g of colu m n s
If an at t r ibu t e r en am in g is n ot specified in t h e con figu r at ion file, a
colu m n t ak es th e sam e n am e as th e at t r ibu t e fr om wh ich it is
der ived.
• vir t u al class ext en t s
If a n am ed set is n ot explicit ly defin ed in t h e O 2 sch em a, a vir t u al
ext en t (i.e. an OQL qu er y givin g a set of object of t h e class as a r esu lt )
can be pr ovided in t h e con figu r at ion file. Th is is n ot m an dat or y,
u n less t h e t able is t o be u sed in t h e FROM clau se of a given SQL
qu er y.
• expor t of dat a r etr ieval m et h ods as colu m n s
D at a r et r ieval m et h ods ar e m eth ods wit h ou t in pu t par am eter an d
wit h an ou tpu t par am et er . Su ch m et h ods can be t r an slat ed in t o
t able colu m n s as if t h ey wer e at tr ibu t es.
• defin it ion of alt er n ative logical k eys
O2 ODBC User Man u al
39
4
O2 SQL
By defau lt, t h e title m et h od or at t r ibu te is expor t ed as a pr im ar y
k ey, bu t an alt er n at ive at t r ibu t e an d/ or m et h od can be declar ed as a
logical k ey for a given class in t h e con figu r at ion file.
• data u pdat e au t h or izat ion
Th e con figu r at ion file can also be u sed t o au t h or ize dat a u pdat es on
t ables gen er at ed fr om O2 classes. By defau lt, su ch u pdates (i.e.
in ser t ion , u pdat es an d deletion s) ar e n ot au t h or ized.
• st or ed pr ocedu r es
O 2 C an d C++ fu n ct ion s can be declar ed as stor ed pr ocedu r es in t h e
con figu r at ion file to be called t h r ou gh t h e SQL in t er face.
• r edefin it ion of collection t ables
Th e defau lt n am in g r u les u sed in t h e der ivat ion of collect ion t ables
fr om collect ion att r ibu t es can be r edefin ed to allow differ en t table
an d colu m n n am es t o be u sed.
A view sch em a can be cu stom ized t h r ou gh a con figu r at ion file pr ovided
t o th e sch em a expor t tool at view cr eation or u pdat e. Appen dix A gives
t h e com plete syn t ax u sed t o specify con figu r at ion files.
Rem ar k : Hidin g an d r en am in g of classes an d at t r ibu t es t oget h er wit h
t h e select ive im por t in g of m eth ods allow t h e en t ir e object str u ct u r e t o be
cu st om ized t o m eet t h e n eeds of a given SQL applicat ion . Man y differ en t
views of t h e sam e sch em a can be defin ed, allowin g differ en t vir tu al
dat abases t o be der ived fr om a given O 2 base.
Ex am p l e 4 .2 .1 Th e con figu r at ion file sh own in Figu r e 4.3 is specified
u sin g t h e syn t ax given in Appen dix A.
Att r ibu t es th at ar e n ot h idden n or r edefin ed ar e expor ted u sin g t h e
defau lt t r an slat ion r u les. Met h ods declar ed in a method clau se ar e
expor ted as vir t u al at t r ibu t es (e.g. m eth od year in class Article is
expor ted as annee in t h e cor r espon din g t able). Th e n am e of expor t ed
m et h ods is r edefin ed t h r ou gh t h e redefine clau se. Th e r esu ltin g view
sch em a is given in Figu r e 4.4 below.
40
O2 OD BC User Man u al
Schema Translation : Customized translation
.
view schema french_document from document;
hide Section, Date, Institute;
stored procedure articles_from_author;
export class Article
hide
sections, date;
redefine
title as titre,
year as annee;
method
year;
extent
"Articles";
with insert,update,delete;
end;
export class Author as Auteur
hide address.institute;
redefine
name as nom,
address_email as adresse_eletronique;
extent
"select x
from Articles a,
a->authors x";
end;
export collection Article.authors as Auteurs
redefine
authors_title as auteur,
Article.Article_title as article;
end;
Fi gu r e 4 .3 : Con f i gu r a t i on f i l e f or sch em a t r a n sl a t i on
O2 ODBC User Man u al
41
4
O2 SQL
CREATE SCHEMA french_document
CREATE TABLE Article(
titre LONGVARCHAR
annee INTEGER
PRIMARY KEY ( title ) )
CREATE TABLE Auteur(
title LONGVARCHAR nom LONGVARCHAR
adresse_eletronique LONGVARCHAR
PRIMARY KEY ( title ) )
CREATE TABLE Auteurs(
article LONGVARCHAR auteur LONGVARCHAR
FOREIGN KEY (article) REFERENCES Article
FOREIGN KEY (auteur) REFERENCES Auteur )
Fi gu r e 4 .4 : Rel a t i on a l sch em a
french_document
4.2 Query Translation
4.2.1 Schema Update Commands
In t h is sect ion , we con sider t h e t r an slat ion of sch em a u pdat e SQL
com m an ds in t o O 2 sch em a u pdat e com m an ds.
Sch em a u pdat e SQL com m an ds ar e com m an ds for t able, view an d in dex
cr eat ion , delet ion an d m odificat ion . Th e t r an slat ion of su ch com m an ds
cor r espon ds to an u pdat e of t h e u n der lyin g O2 sch em a.
Table creation command
Cu r r en t ly, a sim ple tr an slat ion m ech an ism is u sed for gen er at in g, for
each n ewly cr eat ed t able, a cor r espon din g class in t h e O2 sch em a wit h
t h e sam e at t r ibu tes an d u sin g a defau lt t ype m appin g. Sim ple r efer en ce
at t r ibu t es, i.e. on e-t o-on e r elat ion sh ips, ar e gen er at ed in t h e th e O2
class by t ak in g pr im ar y an d for eign k ey defin it ion s in t o accou n t , as th e
in for m at ion pr ovided in t h e t able cr eat ion com m an d is n ot en ou gh for
allowin g t h e syst em t o in fer on e-t o-m an y or m an y-to-m an y
r elat ion sh ips.
42
O2 OD BC User Man u al
Query Translation : Table creation command
Ex am p l e 4 .3 .1 Let u s con sider t h e cr eation of t ables Proceedings an d
Proceedings_articles in sch em a document:
CREATE TABLE Proceedings(
title LONGVARCHAR,
editor LONGVARCHAR,
date LONGVARCHAR
PRIMARY KEY ( title ),
FOREIGN KEY ( date )
REFERENCES Date )
CREATE TABLE Proceedings_articles(
proceedings LONGVARCHAR,
pos INTEGER NOT NULL,
article LONGVARCHAR
PRIMARY KEY ( proceedings, pos ),
FOREIGN KEY ( proceedings
REFERENCES Proceedings,
FOREIGN KEY ( article )
REFERENCES Article )
Th e two table cr eation com m an ds above ar e t r an slat ed in t o t h e
followin g class an d n am e cr eat ion com m an ds in t h e O2 sch em a:
class SQL_Proceedings type tuple(
title:string,
editor:string,
date:Date)
end;
class SQL_Proceedings_articles type tuple(
proceedings:SQL_Proceedings,
pos:integer,
article:Article);
name SQL_Proceedings:unique set(SQL_Proceedings);
name SQL_Proceedings_articles:set(
SQL_Proceedings_articles);
Th e n am e of t h e O2 class gen er at ed is defin ed as follows: th e pr efix
SQL_ is added t o t h e t able n am e. Also, for each cr eated t able, a n am e
(t h e sam e iden tifier as for t h e class n am e is u sed) is cr eat ed t o m odel
t h e t able exten t. Each t im e an in ser t ion is per for m ed in su ch a t able, an
object of t h e cor r espon din g class is cr eat ed an d in ser t ed in t o t h e
cor r espon din g n am ed collect ion .
O2 ODBC User Man u al
43
4
O2 SQL
D efin it ion s pr efixed with SQL_ in an O 2 sch em a sh ou ld n ot be m odified
t h r ou gh O2 bu t on ly t h r ou gh t h e SQL in t er face. Ch an ges t o class
SQL_Proceedings, for in stan ce, sh ou ld be per for m ed on ly in dir ect ly
t h r ou gh t h e ALTER TABLE com m an d. Modificat ion s t o su ch classes an d
n am es can be n ever t h eless per for m ed (i.e. t h e O 2 system does n ot
pr even t t h em ) at t h e r isk of m ak in g t h e SQL cat alog in con sist en t .
Refer en ces t o a class (in a h ide or expor t clau se) wh ose n am e is pr efixed
by SQL_ ar e n ot t ak en in t o accou n t in t h e con figu r at ion file. In ot h er
wor ds, t h e con figu r at ion file can n ot be u sed t o cu st om ize classes
gen er at ed via a CREATE TABLE com m an d, as t h ese classes ar e n ot
expor ted in th e sam e way as O 2 classes ar e expor t ed.
Con st r ain t s associat ed t o colu m n defin it ion s (e.g. NOT NULLX, DEFAULT,
CHECK, et c) ar e au t om at ically ch eck ed at in ser t ion s an d u pdat es.
Rem ar k : NULL valu es ar e n ot su ppor t ed. All class an d collect ion t ables
h ave t h e NOT NULL con st r ain t added syst em at ically to all colu m n s. In
addit ion , t h e n u ll pr edicate (IS NULL) always evalu at es t o false
(r espect ively, IS NOT NULL always evalu at es t o t r u e).
View table creation command
SQL view t able defin it ion s ar e r ecor ded in t h e SQL cat alog. At qu er y
t r an slat ion t im e r efer en ces t o a view t able ar e r eplaced by t h e qu er y
u sed in t h e view t able defin it ion .
Ex am p l e 4 .3 .2 Let u s con sider t h e followin g view t able defin ed in
sch em a document:
CREATE VIEW Recent_Articles(
title LONGVARCHAR, year INTEGER ) AS
SELECT art.title, dat.year
FROM Article art
Date dat
WHERE art.date_title = dat.title
AND dat.year > 90
Now let u s con sider t h e followin g qu er y on t h is view an d it s
cor r espon din g OQL qu er y:
44
O2 OD BC User Man u al
Query Translation : Table deletion command
SQL query:
OQL query:
SELECT art.title
select struct(title:art.title)
FROM Recent_Article art
from
WHERE art.year = 95
(select tuple(title:art.title,
year:a.date.year)
from Articles art
where art.date.year > 90) art
where art.year = 95
An optim ized ver sion of t h e OQL qu er y above t h at elim in at es t h e n est ed
select ion will be gen er at ed by t h e OQL qu er y in ter pr et er at r u n t im e.
Table deletion command
Wh en a t able is dr opped t h r ou gh t h e DROP TABLE com m an d, t h e
cor r espon din g class an d n am e ar e both delet ed fr om t h e O 2 sch em a.
A t able can n ot be dr opped if t h er e ar e in dexes defin ed on it or if ot h er
t ables r efer en ce it t h r ou gh a for eign k ey.
View deletion command
Wh en a view t able is dr opped t h r ou gh th e DROP VIEW com m an d, t h e
cor r espon din g view defin it ion is r em oved fr om t h e SQL cat alog.
Rem ar k : Th e option s CASCADE an d RESTRICT in t h e DROP TABLE an d
DROP VIEW com m an ds ar e n ot su ppor t ed.
Index creation command
An SQL in dex is t r an slat ed in t o an equ ivalen t O2 in dex or set of
in dexes.
Ex am p l e 4 .3 .3 Let u s con sider t h e followin g in dex cr eated on t able
Proceedings defin ed above:
CREATE INDEX i1 ON Proceedings( title );
Th e in dex cr eation com m an d above is t r an slated in t o th e followin g O2
in dex cr eation com m an d in t h e O2 base:
O2 ODBC User Man u al
45
4
O2 SQL
index SQL_Proceedings on title;
Table modification command
Th e ALTER TABLE com m an d can be u sed t o add colu m n s t o an exist in g
t able.
Ex am p l e 4 .3 .4 Let u s con sider t h e followin g m odificat ion of t able
Proceedings defin ed above:
ALTER TABLE Proceedings ADD code INTEGER;
Th e com m an d above is t r an slat ed in to t h e followin g O2 class u pdat e
com m an d in th e O2 sch em a:
attribute code:integer in class SQL_Proceedings;
Table Types
We dist in gu ish fou r differ en t types of t ables in a view sch em a:
• User Tabl es
Th ese ar e defin ed t h r ou gh t h e SQL CREATE TABLE com m an d.
• Vi ew Tabl es
Th ese ar e defin ed t h r ou gh t h e SQL CREATE VIEW com m an d.
• Cl ass Tabl es
Th ese ar e gen er at ed by t h e expor t of an exist in g O 2 class t h r ou gh
t h e o2sql_export tool.
• Col l ect i on Tabl es
Th ese ar e gen er at ed by t h e expor t of an O 2 collect ion att r ibu t e
t h r ou gh t h e o2sql_export t ool.
We assu m e t h at u ser t ables belon g t o th e SQL applicat ion an d t h er efor e
all oper at ion s on t h em ar e allowed (delet ion , in dex cr eation ,
m odification ), wh er eas class tables belon g t o O2 , so t h at m odificat ion s
t o th em ar e allowed on ly t h r ou gh t h e con figu r at ion file. Th e com plet e
list of r estr ict ion s associat ed t o each t ype of table is given below.
• SQL com m an d DROP TABLE
On ly u ser an d view t ables can be dr opped via t h e DROP TABLE com m an d. Class an d collection t ables can be dr opped in dir ect ly via t h e
hide com m an d in t h e con figu r at ion file.
46
O2 OD BC User Man u al
Query Translation : Table Types
• SQL com m an d ALTER TABLE
On ly u ser t ables can be m odified wit h t h e ALTER TABLE com m an d.
Alt h ou gh t h e syn t ax defin ed for t h e cor e level ODBC SQL does n ot
allow con st r ain t s t o be associat ed t o a colu m n added via ALTER
TABLE, t h e con st r ain ts NOT NULL an d DEFAULT ar e au tom at ically
associat ed t o n ewly added colu m n s. Th e defau lt valu e is t h e cor r espon din g O 2 defau lt valu e for t h e at t r ibu t e gen er at ed for t h e colu m n . For in st an ce, n u m er ic colu m n s h ave a zer o defau lt valu e,
wh er eas ch ar acter colu m n s h ave t h e em pt y str in g as defau lt valu e.
Class an d collection t ables can be m odified in dir ectly by m odyfyin g
t h e cor r espon din g O2 dat a t ypes in t h e O2 sch em a. For in st an ce,
addin g an att r ibu t e t o an expor t ed O 2 class en tails t h e addit ion of a
n ew colu m n t o th e cor r espon din g t able, u n less t h e n ew att r ibu t e is
h idden in t h e con figu r ation file an d on e r er u n s o2sql_export t o
u pdat e t h e view sch em e defin it ion .
• SQL com m an d CREATE INDEX an d DROP INDEX
In dexes can be cr eated on u ser t ables on ly. Th is is t r an slat ed as t h e
cr eat ion of an in dex on t h e cor r espon din g syst em gen er at ed n am ed
collection . In dexes on collect ion s u sed as class t able ext en t s
(declar ed in t h e con figu r ation file t h r ou gh t h e clau se extent) can be
defin ed dir ect ly in O 2 . On ly in dexes cr eated t h r ou gh t h e SQL com m an d CREATE INDEX can be dr opped via DROP INDEX.
4.2.2 Data Update Commands
Th er e ar e t h r ee t ypes of u pdat e oper at ion s: r ow in ser t ion , r ow deletion
an d r ow m odificat ion . Wit h SQL, an u pdat e oper at ion is issu ed on a
t able an d is per for m ed on a set of tu ples (r ows) wh ich ar e select ed
t h r ou gh a qu er y.
OQL does n ot dispose of a set of u pdat e com m an ds an alogou s t o t h ose
of SQL. In O2 , u pdat es t o object s can be per for m ed t h r ou gh applicat ion
pr ogr am s or by callin g m et h ods or fu n ct ion s fr om an OQL qu er y.
In t h e cu r r en t ver sion , u pdat es t o u ser t ables ar e per for m ed by t h e SQL
en gin e in a gen er ic way, so t h at n o extr a O 2 C fu n ct ion s or m et h ods
n eed t o be defin ed. User t ables can t h u s be fr eely u pdat ed.
Class t ables can be u pdat ed on ly if an u pdat e clau se is declar ed for t h e
cor r espon din g class in t h e con figu r at ion file an d a n u m ber of con dit ion s
h old. For in st an ce, if a colu m n in a t able is der ived fr om a m eth od, t h en
t h is colu m n can n ot be u pdat ed. Also, t h e abilit y t o in ser t or r em ove
r ows in / fr om a t able will depen d on th e n atu r e of t h e cor r espon din g
t able ext en t declar ed in t h e con figu r at ion file. If it is a n am ed collect ion ,
in ser t ion s/ delet ion s can be str aigh t for war dly m apped in t o
cor r espon din g O2 in ser tion / delet ion oper ation s, bu t if a class ext en t is
O2 ODBC User Man u al
47
4
O2 SQL
given by a selection qu er y, for in stan ce, t h en in ser t ion s can be
per for m ed on ly t h r ou gh st or ed pr ocedu r es, t h ese pr ocedu r es
cor r espon din g t o u ser -defin ed O2 C or C++ im por t ed fu n ct ion s (see
Sect ion 4.2.5).
View t ables can n ot be u pdat ed an d collect ion tables can be u pdat ed
on ly in dir ect ly t h r ou gh stor ed pr ocedu r es defin ed t o t h at en d.
Insert commands
Th e in ser t ion of tu ples in to t ables is t r an slated as t h e cr eat ion an d
in itializat ion of t h e cor r espon din g O2 object s an d t h e at tach m en t of
su ch objects t o t h e r oot of per sist en ce m odelin g t h e t able ext en t in th e
O 2 base.
No r est r iction is im posed on t h e in ser t ion of r ows in t o u ser t ables.
To be able t o in ser t r ows in t o a given class t able th r ou gh t h e INSERT
com m an d, h owever , an insert clause m u st be declar ed in t h e
con figu r at ion file for t h e cor r espon din g class.
Insertion from an associated named collection extent
Th e ext en t of class Article declar ed in t h e con figu r at ion file
cor r espon ds to a n am ed collect ion defin ed in t h e or igin al O2 sch em a.
Th e SQL in ser t ion is au tom at ically t r an slat ed as an in ser t ion of t h e
n ewly cr eat ed object in to t h e cor r espon din g O2 class ext en t . Th e
followin g in ser t clau se m u st be declar ed in t h e con figu r ation file t o t ell
t h e syst em t h at in ser t ion s ar e allowed on t able ARTICLE: .
export class Article
...
extent "Articles";
with insert;
end;
Let u s con sider t h e followin g INSERT SQL com m an d:
INSERT INTO Article (title,date_title)
VALUES (’New Article’,’12/10/1995’)
Wh en t h e SQL com m an d above is issu ed, t h e SQL en gin e in ser t s a
n ewly cr eat ed object in to t h e cor r espon din g u ser defin ed class ext en t
aft er in it ializin g th e cor r espon din g at t r ibu t es. Object at tr ibu t es ar e
in itialized wit h th e colu m n valu es given in t h e in ser t ion com m an d.
48
O2 OD BC User Man u al
Query Translation : Insertion and foreign keys
Insertion and foreign keys
Wh en on e in ser t s a r ow con t ain in g a for eign k ey valu e in t o a t able, t h e
cor r espon din g r ow m u st exist in t h e r efer en ced t able, ot h er wise a
r efer en t ial in t egr it y con st r ain t is violat ed an d t h e in ser t ion is r efu sed. If
in ser t ion s ar e allowed in th e r efer en ced t able, t h en t h e r efer r ed r ow
m u st be in ser t ed befor e t h e r efer r in g r ow is in ser t ed. Fin ally, if dir ect
in ser t ion s in t o t h e r efer en ced t able ar e n ot allowed, th en in ser t ion s can
be ach ieved in dir ect ly, t h r ou gh a u ser -defin ed st or ed pr ocedu r e.
In t h e exam ple above, t h e in ser t ed r ow con t ain s t h e for eign k ey
date_title, th at poin t s t o an en t r y in t able Date. If t h e cor r espon din g
dat e alr eady exists in t h e Date t able, th e in ser tion of t h e ar t icle will be
per for m ed an d t h e n ewly cr eat ed Article object will poin t t o t h e
cor r espon din g Date object . If t h e r efer en ced dat e does n ot exist , t h e
in ser t ion will be r efu sed by t h e u pdate en gin e.
If, h owever , t h e u ser wan t s a n ew date wit h t h e cor r espon din g k ey to be
in ser t ed wh en ever it does n ot alr eady exist , t h e followin g O 2 C fu n ct ion
can be defin ed an d declar ed as a st or ed pr ocedu r e in t h e con figu r at ion
file to be called th r ou gh t h e SQL in t er face. .
function body
Insert_Article(title:string,date_title:string):integer
{
o2 Article obj = new Article;
o2 Date obj_date;
obj->sql_update_title(title);
obj_date = select_Date(date_title);
if (obj_date==nil) {
obj_date= new Date(0,0,0);
obj_date->to_date(tuple(mode:’a’,
s_date:date_title));
}
obj->sql_update_date(obj_date);
Articles += set(obj);
return(0);
};
In t h e exam ple above, we assu m e t h at t h e followin g m et h od com pu t es
t h e logical k ey of an in st an ce of class Date:
method body title:string in class Date {
return(self->to_string(tuple(mode:’a’)));
};
O2 ODBC User Man u al
49
4
O2 SQL
Assu m in g t h at t h e fu n ct ion Insert_Article is declar ed as a st or ed
pr ocedu r e, in ser t ion s in t o t able Article can be per for m ed th r ou gh t h e
SQL in t er face wit h t h e followin g SQL com m an d:
CALL Insert_Article(’A1’,’12/10/1995’)
Insertion and computed extents
For class t ables with an associat ed com pu t ed ext en t , t h e com plex
sem an t ics of an in ser t ion in t o su ch a table can be en capsu lat ed in t o a
u ser -defin ed fu n ct ion to be called by t h e u ser as a st or ed pr ocedu r e.
In ou r exam ple, an au t h or depen ds, as a dat e, on an ar t icle t o exist in
t h e dat abase, as it becom es per sist en t t h r ou gh t h e pat h leadin g fr om
t h e r oot Articles t o t h e at tr ibu t e authors. Bu t , con t r ar y t o a dat e,
h owever , an au t h or is n ot dir ectly poin t ed t o by an ar t icle. In st ead, it is
r elat ed t o m or e ar t icles t h r ou gh t h e collect ion t able Article_authors.
An en t r y in su ch t able can be in ser t ed on ly if th e ar t icle an d t h e au t h or
bein g r elat ed alr eady exist , as t h ey ar e poin t ed t o by it s for eign k eys.
Rem ar k : Class an d collect ion tables can be u pdat ed th r ou gh a st or ed
pr ocedu r e call even if an u pdate clau se is n ot declar ed for t h em in t h e
con figu r at ion file.
Let u s con sider an ot h er exam ple. Th e ext en t of t able Author is given by
a com plex OQL qu er y r at h er t h an by a n am ed collect ion an d in ser t ion s
can t h er efor e n ot be per for m ed dir ect ly by t h e u pdat e en gin e in t o t h is
t able. In st ead, in or der t o allow n ew au t h or s t o be in ser t ed in t h e
dat abase, a fu n ct ion per for m in g th e in ser t ion m u st be declar ed as a
st or ed pr ocedu r e in th e con figu r ation file.
Wh en a r ow is in ser t ed in t o t able Author, th e cor r espon din g n ew object
cr eat ed m u st be at tach ed to t h e att r ibu t e authors of a given Article.
Th is im plies t h at an ar t icle m u st be pr ovided if on e wan t s t o in ser t a
n ew au th or in t h e dat abase.
Given t h e con sider at ion s above, t h e followin g fu n ct ion can be defin ed t o
be called as a st or ed pr ocedu r e an d per for m t h e in ser t ion of a r ow in to
t able Author:
50
O2 OD BC User Man u al
Query Translation : Insertion and computed extents
.
function body Insert_Author_of_Article(
(name:string,
address_institute_title:string,
address_email:string,
Article_title:string):integer {
o2 Author obj = new Author;
o2 Article a;
obj->set_name(name);
obj->set_address_institute(select_Institute(
address_institute_title));
obj->set_address_email(address_email);
a = select_Article(Article_title);
if (a!=nil) {
a->authors += set(obj);
return 1;
}
return 0;
};
method body set_name(name:string) in class Author {
self->name = name;
};
method body
set_address_institute(address_institute:Institute)
in class Author {
self->address.institute = address_institute;
};
method body set_address_email(email:string) in class
Author {
In t h e exam ple, th e Insert_Author_of_Article pr ocedu r e per for m s
t h e in ser t ion of a n ew r ow in to Author an d of a n ew r ow in t o
Article_Author t h at r elat es t h e ar t icle iden t ified by Article_title
t o t h e n ewly in ser ted au t h or .
Given t h e above, t h e in ser t ion of a n ew au t h or can be per for m ed
t h r ou gh t h e followin g SQL com m an d:
CALL Insert_Author_of_Article("John Smith",
"ICS","[email protected]","A1");
M an y differ en t st or ed pr ocedu r es can be declar ed by t h e u ser for t h e
differ en t path s leadin g fr om a r oot of per sist en ce t o t h e in st an ces of a
given class.
O2 ODBC User Man u al
51
4
O2 SQL
Insertion w ith nested queries
If a qu er y is specified in t h e body of an INSERT com m an d, t h is qu er y is
t r an slat ed t o it s equ ivalen t OQL qu er y, wh ich m u st in t u r n r et u r n a set
of t u ples of at om ic at t r ibu tes cor r espon din g t o th e att r ibu t es specified in
t h e column-identifier list . Th e u pdat e en gin e it er at es on t h e r esu lt of
t h is n est ed qu er ies t o per for m t h e in ser tion of t h e cor r espon din g r ows.
Delete commands
Th e delet ion of tu ples fr om a table m u st be t r an slat ed as t h e
discon n ect ion of t h e cor r espon din g O 2 object s fr om t h e r oot (s) of
per sist en ce t o wh ich t h ey ar e at t ach ed in t h e O2 base.
No r est r iction is im posed on t h e delet ion of r ows fr om u ser t ables.
To be able t o delet e r ows fr om a given class t able t h r ou gh t h e DELETE
com m an d, h owever , a d elete clause m u st be declar ed in th e
con figu r at ion file for t h e cor r espon din g class.
Deletion from an associated named collection extent
Th e SQL delet ion fr om table ARTICLE can be au t om at ically t r an slat ed as
t h e r em oval of th e cor r espon din g object fr om th e n am ed collect ion
ext en t . Th e followin g delete clau se m u st be declar ed in th e con figu r at ion
file t o t ell t h e syst em t h at delet ion s fr om table ARTICLE ar e allowed:.
export class Article
...
extent "Articles";
with delete;
end;
Let u s con sider t h e followin g DELETE com m an d:
DELETE FROM Article WHERE title = ‘Old Article’
Th e SQL en gin e will fir st select all object s cor r espon din g t o th e r ows t o
be deleted an d t h en delet e t h em fr om t h e class ext en t .
Deletion and foreign keys
Cascadin g delet ion s can be im plem en t ed th r ou gh st or ed pr ocedu r es, as
for in ser t ion s.
In t h e exam ple above, t h e delet ed r ow con t ain s t h e for eign k ey
date_title, t h at poin t s t o an en t r y in t able Date. Su ppose t h at on e
52
O2 OD BC User Man u al
Query Translation : Deletion from a class table with
wan t s t h e poin t ed dat e t o be delet ed fr om t h e cor r espon din g table
wh en ever a poin t in g ar t icle is delet ed. As a r ow in t able Date exist s on ly
as lon g as at least on e r ow in t able Article poin t s t o it , th en t h e
r em oval of t h e last poin t in g ar t icle fr om t h e database wou ld
au t om at ically en t ail th e r em oval of t h e poin t ed dat e. If h owever , t h e
poin t ed object s can be r each ed t h r ou gh an ot h er pat h fr om a given
per sist en ce r oot , t h en t h e cascadin g deletion can be per for m ed t h r ou gh
st or ed pr ocedu r es.
In addition , if th e class associat ed t o t h e poin t ed t able h as n o ext en t
clau se associat ed t o it , or if t h e associate ext en t expr ession is com pu t ed
r at h er t h an given by a n am ed collect ion , th en t h e cascadin g delet ion is
per for m ed by defau lt .
Su pposin g th at a n am ed set Dates is defin ed in t h e O2 sch em a an d
declar ed as t h e exten t of class Date, t h en t h e delet ion of an ar t icle
wou ld n ot en t ail t h e delet ion of th e poin ted date. Th e cascadin g r em oval
cou ld be ach ieved by explicitly per for m in g t h e r em oval of t h e poin t ed
dat e fr om th e n am ed collect ion Dates in t h e fu n ct ion im plem en t in g a
st or ed pr ocedu r e u sed t o r em ove ar t icles fr om t h e dat abase.
Deletion from a class table w ith an associated computed
extent
Let u s n ow con sider t h e delet ion of a r ow fr om th e t able AUTHOR. Su ch
delet ion s can n ot be au t om at ically per for m ed by th e syst em an d a delet e
clau se sh ou ld n ot be associat ed to t h e class Author. In st ead, t h e
followin g st or ed pr ocedu r e can be u sed: .
function body
Delete_Author_of_Article(author_title:string,
Article_title:string):integer {
select_Article(Article_title)->authors -=
set(select_Author(author_title));
return 0;
};
Th e Delete_Author_of_Article pr ocedu r e per for m s t h e delet ion of
t h e AUTHOR r ow cor r espon din g to t h e k ey passed as par am et er an d of
t h e cor r espon din g en t r y in t able Article_Author th at r elat es t h e
ar t icle iden t ified by article_title t o t h e delet ed au t h or .
Th e delet ion of a given au t h or can be per for m ed t h r ou gh t h e followin g
SQL com m an d:
CALL Delete_Author_of_Article("John Smith","A1");
O2 ODBC User Man u al
53
4
O2 SQL
As for in ser t ion , m an y differ en t st or ed pr ocedu r es can be declar ed by
t h e u ser for t h e differ en t path s leadin g fr om a r oot of per sist en ce t o t h e
in st an ces of a given class.
Update commands
Th e u pdat e of t u ples in a class t able m u st be t r an slat ed as t h e u pdate of
t h e cor r espon din g O2 object s in t h e O2 base.
No r est r iction is im posed on t h e u pdat eof r ows fr om u ser tables.
To be able t o u pdate r ows in a given class t able, an update clause m u st
be declar ed in t h e con figu r at ion file for t h e cor r espon din g class, as
illu str at ed below:.
export class Article
...
extent "Articles";
with update;
end;
Let u s con sider t h e followin g UPDATE com m an d:
UPDATE Articles SET title = ‘New Article’ WHERE title =
‘Old Article’
Th e SQL en gin e will fir st select all object s cor r espon din g t o th e r ows t o
be u pdat ed an d th en u pdat e t h eir at t r ibu t es with t h e n ew
cor r espon din g colu m n valu es.
Update and foreign keys
Th e u pdat e of for eign k ey colu m n s is sim ilar t o t h e in ser t ion of n ew r ows
wit h for eign k ey colu m n s. Wh en on e u pdat es a for eign k ey r ow colu m n ,
t h e cor r espon din g r ow m u st exist in t h e r efer en ced t able, ot h er wise a
r efer en tial in t egr it y con st r ain t is violat ed an d t h e u pdat e is r efu sed. If
in ser t ion s ar e allowed in t h e r efer en ced table, t h en t h e r efer r ed r ow
m u st h ave been in ser t ed befor e t h e r efer r in g r ow is u pdat ed. If h owever ,
it is n ot possible t o explicit ly in ser t a r ow in t o t h e r efer en ced t able, t h en
an in ser t ion in t o t h is t able can be ach ieved t h r ou gh a cascad ing upd ate
of t h e r efer en cin g t able im plem en ted by a st or ed pr ocedu r e.
To con clu de t h is sect ion , we r ecall t h at t h e u se of st or ed pr ocedu r es can
be gen er alized to over com e t h e lim it at ion s on u pdat e oper at ion s on
class an d collect ion tables.
54
O2 OD BC User Man u al
Query Translation : Update and foreign keys
4.2.3 Data Retrieval Commands
D at a r et r ieval com m an ds cor r espon d t o t h e SELECT-FROM-WHERE SQL
qu er ies.
Th e qu er y t r an slat or bu ilds u p on t h e im plem en t at ion of t h e OQL qu er y
in t er pr et er . St ar t in g fr om t h e OQL ver sion 1.2 in ter pr et er , t h e or igin al
OQL qu er y par ser was r eplaced by an SQL par ser bu ildin g an OQL
qu er y tr ee. Th e con st r u ct ion of an OQL syn t ax t r ee fr om a given SQL
qu er y is based on in for m at ion collected by t h e sch em a t r an slat ion an d
k ept in t h e SQL cat alog. Th e gen er at ed OQL tr ee is fu r t h er optim ized by
applyin g stan dar d optim izat ion t ech n iqu es.
Qu er y t r an slat ion is t h u s in t egr at ed t o cu r r en t OQL in t er pr et at ion an d
is per for m ed in t wo m ain st eps:
1. Th e text of an SQL qu er y is par sed an d t h e abst r act syn tax t r ee of
t h e equ ivalen t OQL qu er y is con str u ct ed, based on in for m ation collect ed by t h e sch em a t r an slat or .
1. Th e r ewr it t en qu er y (i.e. it s cor r espon din g syn t ax tr ee) is passed t o
t h e n ext ph ases of OQL qu er y in t er pr et at ion : qu er y gr aph con str u ct ion , opt im izat ion an d evalu at ion .
As th e syn tax of t h e OQL lan gu age ver sion 1.2 is ver y close t o t h at of
SQL, t h e OQL t r ee con st r u ct ion is str aigh t for war d. Th is fir st st ep
pr odu ces t h e syn t ax t r ee of an OQL qu er y t h at is alr eady sem an t ically
equ ivalen t to t h e or igin al SQL qu er y, i.e. t h is tr ee can be u sed by t h e
OQL in t er pr et er as it is in t h e su bsequ en t ph ases of t h e st an dar d OQL
qu er y pr ocessin g, n am ely gr aph con st r u ct ion , opt im izat ion an d
evalu at ion , to pr odu ce t h e expected r esu lt w ithout no further
intervention of the query translator . In ot h er wor ds, t h e fir st st ep
capt u r es t h e sem an t ics of t h e SQL qu er y in t o an equ ivalen t OQL qu er y
alr eady an d th e secon d st ep is st an dar d OQL en gin e act ivit y.
D u r in g t h e con st r u ct ion of t h e t r ee, t h e t r an slat or sear ch es well defin ed
access pat t er n s t o per for m qu er y r ewr it in g. Wh en a given pat t er n is
m atch ed, an action on t h e cor r espon din g su bt r ee is per for m ed. Th is
act ion en t ails t h e r eplacem en t of on e or m or e su bt r ees by ot h er
equ ivalen t su bt r ees as well as th e in clu sion of n ew var iables in th e
cor r espon din g from clau se. Th e in t u it ion is t h at t h e n ew su btr ees ar e
sem an t ically equ ivalen t t o t h e on es t h ey r eplace, bu t th e cor r espon din g
su bqu er ies can be evalu ated in a m or e efficien t way.
Th e der ivat ion of a qu er y gr aph fr om t h e syn t ax t r ee is per for m ed as for
or din ar y OQL qu er ies. In par t icu lar , st an dar d opt im izat ion t ech n iqu es
ar e applied as for or din ar y OQL qu er y gr aph s.
Rem ar k : On ly t h e per cen t ch ar acter (m atch in g zer o or m or e of an y
ch ar act er ) is su ppor t ed in pat t er n valu es u sed in t h e LIKE pr edicat e
an d in par am et er s t o som e API fu n ct ion s (e.g. szTableName par am eter
O2 ODBC User Man u al
55
4
O2 SQL
of SQLTables). Th e u n der scor e ch ar act er (m at ch in g on e ch ar act er ) is
n ot su ppor t ed.
Below, we give som e exam ples of qu er y t r an slat ion t o illu st r at e t h e
pr ocess. Th e exam ples ar e based on t h e view sch em a given in Figu r e
4.2.
Ex am p l e 4 .3 .5 Ret r ieve t h e n am e of all ar t icles:
SQL query:
OQL query:
SELECT title
select struct(title:x0.title)
FROM Article
from
Articles x0
Ex am p l e 4 .3 .6 Retr ieve th e n am e of all ar ticles u sin g a colu m n alias:
SQL query:
OQL query:
SELECT title AS article_name
select
struct(article_name:x0.title)
FROM Article
from Articles x0
Ex am p l e 4 .3 .7 Retr ieve all colu m n s of all r ows of t able Ar t icle:
SQL query:
OQL query:
SELECT *
select
struct(title:x0.title,date_title:x0.date.title)
FROM Article
from Articles x0
Ex am p l e 4 .3 .8 Retr ieve th e n am es of all au t h or s:
56
SQL query:
OQL query:
SELECT name
select struct(name:x1.name)
FROM Author
from Articles x0,x0.authors x1
O2 OD BC User Man u al
Query Translation : Update and foreign keys
Ex am p l e 4 .3 .9 Ret r ieve t h e n am es of all au t h or s of an ar t icle wh ose
t itle is “Th e Ar t icle”:
SQL query:
OQL query:
SELECT name
select struct(name:x1.name)
FROM Author
from Articles x0,x0.authors x1
WHERE title IN
where x0.title == "The Article"
(SELECT Author_title
FROM Article_authors
WHERE Article_title IN
(SELECT title
FROM Article
WHERE title = "The Article"))
Ex am p l e 4 .3 .1 0 Ret r ieve t h e n am e of all au t h or s of ar ticle "Article
1":
SQL query:
OQL query:
SELECT y.name
select struct(name:x1.name)
FROM Article x,Author y,
from Articles x0,
Article_authors z
x0.authors x1
WHERE x.title = "Article 1"
where x0.title == "Article 1" and
AND x.title = z.Article_title
x1 in x0.authors
AND y.title = z.authors_title
Ex am p l e 4 .3 .1 1 Ret r ieve t h e elect r on ic addr esses of all au t h or s:
SQL query:
OQL query:
SELECT address_email
select
struct(address_email:x0.address.email)
FROM Author
from Articles x0,x0.authors x1
O2 ODBC User Man u al
57
4
O2 SQL
Ex am p l e 4 .3 .1 2 Ret r ieve t h e institute_title of au t h or "Author 1":
SQL query:
OQL query:
SELECT address_institute_title AS i
struct(i:a.address.institute.title)
select
FROM Author
from Articles x0,
x0.authors x1
WHERE name = "Author 1"
where x1.name == "Author 1"
Ex am p l e 4 .3 .1 3 Retr ieve t h e n am e of all au t h or s h avin g wr it t en at least
t wo differ en t ar t icles:
SQL query:
OQL query:
SELECT name
select struct(name:x1.name)
FROM Author,
from Articles x0,
Article_authors
x0.authors x1
WHERE title = Authors_title AND group by name
GROUP BY name
having count(partition) > 1
HAVING COUNT(*) > 1
4.2.4 Granting privileges
Pr ivileges ar e defin ed t h r ou gh t h e GRANT an d REVOKE com m an ds. A
pr ivilege defin it ion is, t oget h er wit h table an d view defin it ion com m an ds,
a basic r elat ion al sch em a elem en t .
Wh en a gr an t com m an d is passed as an or din ar y SQL st at em en t
t h r ou gh t h e SQL in t er face, t h e system r ecor ds in for m at ion abou t
gr an ted u pdat e pr ivileges. In th e cu r r en t ver sion , h owever , u pdat e
pr ivileges ar e n ot ch eck ed by t h e syst em .
Rem ar k : Th e k eywor d USER r epr esen t s t h e con st an t st r in g "USER"
in st ead of th e n am e of th e cu r r en t u ser .
58
O2 OD BC User Man u al
Query Translation : O2C procedures
4.2.5 Stored Procedures
St or ed pr ocedu r es ar e declar ed in th e con figu r ation file, as illu st r at ed
below:
stored procedure Insert_Article
"This will insert a tuple into table Article and a tuple
into table Date if the referenced date does not exist.",
C++:Process_Complex_Update
"This will perform something by calling a C++ imported
function.";
For each pr ocedu r e, a t ext m ay be associat ed t o it , in addit ion t o t h e
pr ocedu r e n am e. Th is allows a br ief descr ipt ion of th e sem an t ics of t h e
pr ocedu r e to be st or ed in t h e SQL cat alog an d t o be r et r ieved wh en t h e
st or ed pr ocedu r es ar e in spect ed t h r ou gh th e o2sql_query t ool or
t h r ou gh t h e ODBC API fu n ct ion SQLStoredProcedure.
O2C procedures
By defau lt , st or ed pr ocedu r es cor r espon d t o an O2 C fu n ct ion wit h t h e
sam e n am e defin ed in t h e O 2 sch em a.
Th e followin g is a call t o t h e pr ocedu r e Insert_Article declar ed above:
CALL Insert_Article(’A1’,’12/10/1995’)
Su ch call is st r aigh tfor war dly tr an slated in t o t h e followin g OQL qu er y:
Insert_Article(’A1’,’12/10/1995’)
C++ procedures
If t h e pr efix “C++:” is added to th e pr ocedu r e n am e, t h en t h e pr ocedu r e
will cor r espon d t o an im por t ed C++ m em ber fu n ct ion of t h e im por t ed
class SQLStoredProcedureHandler.
C++ pr ocedu r es allow fu n ct ion s defin ed by a C++ appplicat ion t o be
called t h r ou gh th e SQL in t er face in st ead of u sin g O2 C fu n ct ion s.
To be able t o call C++ fu n ction s, t h e applicat ion m u st per for m t h e
followin g st eps:
• t o im por t a C++ class n am ed SQLStoredProcedureHandler in t o th e O2
sch em a. Th is class sh ou ld be defin ed by t h e u ser t o gr ou p all fu n ct ion s
th at ar e to be called as st or ed pr ocedu r es t h r ou gh t h e SQL in t er face.
O2 ODBC User Man u al
59
4
O2 SQL
• to defin e t h e r oot of per sist en ce SQLStoredProcedureHandler in t h e O2
sch em a wit h t ype SQLStoredProcedureHandler.
• to cr eat e an in st an ce of class SQLStoredProcedureHandler an d at t ach
it t o th e r oot of per sisten ce SQLStoredProcedureHandler.
Let u s con sider a call to t h e pr ocedu r e Process_Complex_Update (we
assu m e t h at t h is pr ocedu r e t ak es n o par am eter ):
CALL Process_Complex_Update()
Su ch call is t r an slat ed in t o t h e followin g OQL qu er y:
SQLStoredProcedureHandler->Process_Complex_Update()
Th e st an dar d o2odbc_server pr ogr am (see com m an d “o2odbc_ser ver ”,
page 7-109) is able t o au t om at ically execu t e O2 C fu n ct ion s declar ed as
st or ed pr ocedu r es.
Linking C++ functions w ith the “ sql” library
St or ed pr ocedu r es im plem en t ed by C++ fu n ct ion s can n ot be execu t e
t h r ou gh t h e o2sql_query sh ell n or t h r ou gh t h e o2odbc_server
pr ogr am . Th is is so becau se t h e libr ar y con t ain in g t h e im plem en t at ion
of su ch fu n ct ion s is n ot lin k ed t o o2sql_query n or t o t h e
o2odbc_server.
A C++ application wish in g t o call C++ fu n ct ion s as st or ed pr ocedu r es
t h r ou gh t h e SQL in t er face m u st t h en be lin k ed at least wit h t h e sql, t h e
oql an d t h e o2cppruntime libr ar ies (as well as ot h er O2 an d gen er al
pu r pose libr ar ies n ecessar y t o bu ild t h e applicat ion ). Th is is det ailed in
Ch apt er 6.
Typing restrictions
Th e followin g con dit ion s m u st h old on t h e O2 C an d C++ im por t ed
fu n ct ion s declar ed as st or ed pr ocedu r es:
• in pu t ar gu m en t s, if an y, m u st h ave an at om ic t ype;
• th e r esu lt t ype, if th e fu n ct ion r et u r n s a r esu lt , m u st be of on e of t h e followin g:
• an at om ic t ype;
Th e pr ocedu r e h as an ou t pu t par am et er an d m u st be called wit h t h e
syn t ax ? = call proc-name(arg1,..,argn), i.e. t h e r esu lt can be
r et r ieved as an ou t pu t par am et er .
60
O2 OD BC User Man u al
Development Tools : Modifying existing views
• a collect ion of t u ples of at om ic t ype at t r ibu t es;
Th e pr ocedu r e r et u r n s a r esu lt set as a select-from-where qu er y.
• a class wh ose t ype is a collect ion of t u ples of at om ic t ype at t r ibu t es;
Th e pr ocedu r e r et u r n s a r esu lt set , as in t h e pr eviou s case.
Th e con dit ion s above ar e ch eck ed by th e o2sql_export t ool wh en t h e
con figu r at ion file is loaded an d an er r or is r epor t ed an d t h e view
gen er at ion abor t if t h ey do n ot h old on all declar ed pr ocedu r es.
4.3 Development Tools
4.3.1
View creation tool o2sql_export
Th e o2sql_export t ool allows view sch em as t o be cr eat ed an d
m odified. It t ak es t wo m an dator y ar gu m en t s: a sch em a n am e an d a
view n am e, t h r ou gh ar gu m en t s -schema an d -view r espect ively.
An option al con figu r at ion file can be pr ovided t h r ou gh t h e ar gu m en t
-config. All classes in t h e in pu t sch em a, if an y, ar e expor t ed in to t h e
view associat ed to t h is sch em a as r elat ion al t ables, u n less t h ey ar e
h idden in t h e con figu r ation file.
If n o con figu r at ion file is given , a defau lt t r an slat ion is per for m ed (n o
h idin g n or r en am in g of classes an d/ or at tr ibu t es t ak e place).
Th e com plet e u sage of o2sql_export is given in Ch apt er 7.
A view sch em a gen er at ed wit h o2sql_export can be in spect ed at an y
t im e with t h e t ool o2sql_query or th r ou gh t h e OD BC API, by callin g t h e
appr opr iate cat alog fu n ction s (e.g. SQLTables, SQLColumns, et c).
Rem ar k : A view can be cr eated on an em pt y O2 sch em a. Th is sch em a
can be fu r t h er popu lat ed t h r ou gh CREATE TABLE com m an ds.
Modifying existing view s
Views gen er ated wit h t h e o2sql_export t ool can be fu r t h er delet ed an d
u pdat ed. Updat e is per for m ed t h r ou gh t h e o2sql_export t ool, i.e.
r u n n in g o2sql_export on an exist in g view allows t h e view t o be
m odified. Th is will be u su ally per for m ed t o associat e a n ew
con figu r at ion file to an exist in g view (ch an gin g h idin gs, r edefin it ion s,
O2 ODBC User Man u al
61
4
O2 SQL
st or ed pr ocedu r e declar ation s, et c). Th e tool pr om pt s t h e u ser for
con fir m at ion of th e view u pdate.
Th e delet ion of a view sch em a can be per for m ed t h r ou gh t h e
o2sql_query t ool, as it will be descr ibed in t h e sequ el.
The SQL catalog
O 2 k eeps an SQL catalog as par t of it s syst em cat alog. An en t r y in t h is
SQL cat alog is associat ed t o each view sch em a cr eat ed wit h t h e
o2sql_export t ool.
SQL u ser defin it ion s su ch as view t ables an d in t egr ity con st r ain t s
associat ed t o u ser tables ar e k ept in in t er n al st r u ct u r es of t h e SQL
cat alog. Th e in for m at ion pr ovided in t h e con figu r at ion file is also st or ed
in th e SQL cat alog.
Th e SQL cat alog is t h u s accessed wh en a view sch em a is cr eat ed,
u pdat ed or deleted an d it is au tom at ically u pdat ed wh en SQL
oper at ion s u pdat in g t h e view sch em a (e.g. CREATE TABLE, CREATE
INDEX) ar e per for m ed on t h e database.
Th e SQL cat alog can be in spect ed t h r ou gh a n u m ber of display
fu n ct ion s, wh ich ar e det ailed below.
Each O2 sch em a k eeps a list of SQL catalog st r u ct u r es, on e per view
sch em a cr eat ed on it . En t r ies in t h e SQL cat alog ar e r em oved wh en t h e
cor r espon din g view sch em as or t h e O2 sch em a ar e delet ed.
4.3.2 SQL shell tool o2sql_query
Th is t ool allows views t o be activat ed, delet ed an d in spect ed. It is an
in t er act ive sh ell allowin g SQL com m an ds an d som e special
m ain t en an ce com m an ds t o be execu t ed on an act ivat ed view.
Th er e is n o m an dat or y ar gu m en t , bu t if a base an d a view ar e pr ovided
as ar gu m en t s, t h e view is act ivat ed on t h at base wh en t h e sh ell is
lau n ch ed. Ot h er wise, a view can be act ivat ed on ce t h e sh ell h as been
lau n ch ed with t h e com m an d set view schema t h at will be descr ibed
below.
Th e m ain u ses of t h e o2sql_query t ool ar e:
• To qu ick ly t est som e qu er ies on t h e dat abase befor e wr it in g an O2 /
ODBC com plet e application . It act u ally u ses t h e o2_sql fu n ct ion in
or der t o evalu at e t h e SQL qu er ies su bm it t ed by t h e u ser t h r ou gh th e
st an dar d in pu t .
62
O2 OD BC User Man u al
Development Tools : SQL commands
• Th e delet ion of exist in g view sch em as th r ou gh th e delete view schema
com m an d.
• Th e in spect ion of t h e SQL cat alog t h r ou gh a n u m ber of display com m an ds: display config file, display view schema, display
tables, et c. Th is can be par t icu lar ly u sefu l for tu n n in g u p con figu r at ion
files an d t h e r esu lt in g view sch em as so as t o adapt t h em to t h e n eeds of
a given applicat ion .
Th e com plet e u sage of o2sql_query is given in Ch apt er 7.
If an ou t pu t file is specified th r ou gh ar gu m en t -output t h en t h e r esu lt
of SQL selection qu er ies an d of gen er al view in spect ion com m an ds is
du m ped in t o t h is file.
On ce t h e sh ell is lau n ch ed, t h e followin g pr om pt is displayed:
TYPE YOUR QUERY ENDED BY ’;’:
D iffer en t k in ds of com m an ds can be su bm it t ed t o t h e sh ell. Th ese ar e
con sider ed in t u r n .
SQL commands
St an dar d SQL com m an ds u sin g t h e syn t ax defin ed for t h e cor e ODBC
SQL level in t h e appen dix C of t h e ODBC SDK Pr ogr am m er ’s Refer en ce;
Th ese ar e st an dar d SQL com m an ds wh ich in clu de “Dat a Ret r ieval
Com m an ds” on page 55, “D at a Updat e Com m an ds” on page 47,
“Sch em a Updat e Com m an ds” on page 42 an d “St or ed Pr ocedu r es” on
page 59.
Rem ar k : SQL com m an ds wit h in pu t an d/ or ou t pu t par am et er s can n ot
be su bm itt ed t o th e o2sql_query sh ell.
Transaction commands
As far as t r an sact ion s ar e con cer n ed, t h e defau lt beh avior of t h e
o2sql_query t ool is sim ilar t o t h at of t h e o2shell t ool. In ot h er wor ds,
wh en t h e sh ell is lau n ch ed, a t r an sact ion is im plicitly st ar t ed. At an y
m om en t t h e followin g t r an sact ion com m an ds can be execu t ed:
• commit work
Th is will com m it all u pdat es t o dat a an d t o t h e cu r r en t ly act ive view
sch em a by per for m in g a com m it on O 2 .
• rollback work
O2 ODBC User Man u al
63
4
O2 SQL
Th is will per for m an abor t on O 2 an d r ollback all m odificat ion to
dat a an d t o t h e cu r r en t ly act ive sch em a.
If t h e u ser qu its t h e tool (by t ypin g “;”) wit h ou t com m it in g or abor t in g,
t h en a com m it is im plicit ly per for m ed.
Alt er n at ively, t h e u ser can r u n t h e sh ell in an au t o-com m it m ode (option
auto_commit). In t h is case, a com m it is au t om atically per for m ed after
each com m an d is execu ted. Wh en r u n n in g in au t o-com m it m ode, t h e
t r an sact ion com m an ds descr ibed above ar e n ot allowed.
View inspection commands
Th e followin g view in spect ion com m an ds ar e available:
• display view schemas;
Th is will list t h e n am es of th e differ en t views defin ed on t h e cu r r en t ly
act ive base.
• display view schema;
Th is will display all t h e defin ition s (t ables, in dexes an d pr ocedu r es)
in th e cu r r en tly active view sch em a.
• display tables;
Th is will display all t ables in t h e cu r r en t ly act ive view sch em a. Th ese
ar e th e u ser , class, collect ion an d view t ables.
• display table <table-name>;
Th is will display t h e defin it ion of t h e t able <table-name> in t h e cu r r en tly active view sch em a.
• display procedures;
Th is will list t h e n am es of all st or ed pr ocedu r es declar ed in t h e con figu r at ion file for t h e cu r r en t ly act ive view sch em a.
• display procedure <proc-name>;
Th is will display th e defin it ion of th e st or ed pr ocedu r e <proc-name>
in th e cu r r en tly active view sch em a.
• display indexes;
Th is will list t h e n am es of all in dexes cr eat ed t h r ou gh th e SQL
CREATE INDEX com m an d.
• display index <index-name>;
64
O2 OD BC User Man u al
Development Tools : View management commands
Th is will display t h e defin it ion of t h e in dex <index-name> in t h e cu r r en t ly act ive view sch em a.
• display config file;
Th is will display t h e con t en t s of th e con fig file u sed t o der ive t h e cu r r en t ly act ive view t h r ou gh t h e o2sql_export t ool, if an y. Th e con figu r at ion file u sed t o der ive a view is n eeded on ly at view cr eat ion t im e.
If a ch an ge t o t h e view n eeds t o be per for m ed by edit in g an exist in g
con figu r at ion file, t h e con ten t s of t h e file u sed t o der ive th e view can
be r et r ieved t h r ou gh t h e display config file com m an d an d
du m ped t o a file to be edit ed. Th is r eleases u ser s fr om k eepin g back u ps of con figu r at ion files on th eir disk .
View management commands
• view act ivat ion com m an d;
set view schema (<base-name>,<view-name>);
This will activate the view schema <view-name> on base
<base-name>.
• view delet ion com m an d;
delete view schema (<schema-name>,<view-name>);
Th is will delet e t h e view sch em a <view-name> on defin ed on sch em a
<schema-name>. Th e view m u st n ot be t h e cu r r en t ly act ive view.
O2 ODBC User Man u al
65
4
66
O2 SQL
O2 OD BC User Man u al
5
5
O2 ODBC
Th is ch apt er descr ibes h ow t o u se t h e O 2 OD BC dr iver an d wr it e or u se
OD BC applicat ion th at access O2 data sou r ces.
Sect ion 5.2 descr ibes var iou s dat a sou r ces an d det ails h ow an ODBC
clien t applicat ion can con n ect t o an O 2 dat a sou r ce. Section 5.3 gives
in for m at ion on t h e OD BC API fu n ct ion s im plem en t ed by t h e O2 OD BC
dr iver .
In or der to r ead t h is ch apt er , it is assu m ed you ar e fam iliar wit h t h e
OD BC en vir on m en t an d r elat ed con cept s.
O2 ODBC User M an u al
67
5
O2 ODBC
5.1 Conformance Levels
Th e O 2 ODBC dr iver h as th e followin g con for m an ce levels:
• API Con for m an ce Level: Level 1
• SQL Con for m an ce Level: Core
Not e: Th e O2 ODBC dr iver also su ppor ts som e of th e fu n ction s in t h e
level 2 API con for m an ce level an d par t of th e gr am m ar in t h e exten ded
SQL con for m an ce level.
5.2 O2 Data Sources
An O 2 dat a sou r ce is defin ed by:
• an O2 syst em
• an O2 dat abase
• opt ion ally, an OD MG C++ applicat ion t o wh ich t h e clien t m u st con n ect
• a qu er y k in d m ode (cu r r en t ly on ly SQL is su ppor t ed)
• an SQL view for SQL k in d con n ect ion s,
•
5.2.1 Connection to Data Sources
Con n ection t o a dat a sou r ce is per for m ed by SQLConnect or
SQLDriverConnect calls.
Wh en t h e fu n ct ion SQLConnect is u sed, in for m at ion in t h e ODBC.ini
file (or r egistr y in for m at ion ) is u sed t o per for m t h e con n ect ion , wh er eas
wit h SQLDriverConnect, a con n ect ion st r in g (or pr om pted in for m ation )
is u sed.
68
O2 OD BC User Man u al
O2 Data Sources :
5.2.2 Configuring Data Sources with ODBC.ini
An O 2 dat a sou r ce specification sect ion in t h e ODBC.ini file will
in t r odu ce 4specific k eywor ds : System, Database, Application, an d
View. It s for m at is given below: .
[data-source-name]
Driver=driver-DLL-path
System=system-name
Database=database-name
[Application=application-name]
View=SQL-view-name
5.2.3 Connection String
A con n ect ion st r in g u sed by SQLDriverConnect an d by t h e
o2odbc_dump_base tool (Sect ion 5.4.4 ) h as t h e followin g syn t ax:
connection-string : := empty-string [" ; " ] | list-ofattributes [" ; " ]
list-of-attributes
list-of-attributes
: := attribute | attribute " ; "
attribute : := DRIVER " = { " attribute-value " } " |
attribute-keyword " = " attribute-value | specificattribute
attribute-keyword : := DSN | UID
| PWD
specific-attribute: := SYSTEM " = " attribute-value
| DATABASE " = "
attribute-value | APPLICATION " =
" attribute-value | VIEW " = " attribute-value
attribute-value : := character-string
Th e DSN k eywor d is t h e on ly k eywor d n ecessar y t o con n ect t o a dat a
sou r ce fr om a Win dows 95/ NT clien t , as in for m at ion abou t th e O2
syst em , base an d view ar e par t of th e dat a sou r ce defin it ion .
Wh en u sin g t h e O 2 ODBC clien t libr ar y to con n ect t o an O2 ODBC
ser ver wit h ou t passin g t h r ou gh an OD BC Dr iver Man ager , h owever , t h e
con n ection st r in g for t h e O 2 ODBC dr iver m u st u se t h e k eywor ds:
O2 ODBC User Man u al
69
5
O2 ODBC
Keyword
SYSTEM
BASE
APPLICATION
VIEW
Description
The name of the O2 system.
The name of the O2 base.
The name of a C++ application (optional).
The name of the SQL view.
5.3 ODBC API Functions
All Cor e an d Level 1 API ODBC fu n ct ion s ar e su ppor ted. Som e fu n ction s
in level 2 ar e also su ppor ted. Th e list of all fu n ct ion s im plem en t ed by
t h e O 2 ODBC D r iver can be r et r ieved with t h e SQLGetFunction ODB
API fu n ction .
Th e m ain r est r ict ion s in t h e API con cer n t h e exten ded cu r sor s (scr olls,
u pdat es), an d position ed u pdat e or delet e statem en t s, wh ich ar e n ot
su ppor t ed.
Th e Level 2 fu n ct ion s im plem en t ed by t h e O2 OD BC dr iver ar e:
• SQLNumParams
• SQLNativeSql
• SQLExtendedFetch
• SQLForeignKeys
• SQLPrimaryKeys
• SQLProcedures
Th e fu n ct ion s im plem en t ed by t h e O2 ODBC dr iver ar e gr ou ped by t ype
of task in t h e sequ el. Specificit ies of t h e O2 ODBC dr iver r egar din g som e
of t h ese fu n ction s ar e given wh en ever n ecessar y.
70
O2 OD BC User Man u al
ODBC API Functions :
5.3.1 Connecting to a data source
•SQLAllocConnect
•SQLAllocEnv
•SQLConnect
•SQLDriverConnect
5.3.2 Obtaining information about a driver and a data
source
•SQLGetInfo
Appen dix B gives t h e valu es r et u r n ed by t h e SQLGetInfo ODBC API
fu n ct ion for all possible fInfoType in pu t ar gu m en t valu es.
•SQLGetTypeInfo
5.3.3 Setting and retrieving driver options
•SQLSetConnectOption
Th is fu n ct ion sets a con n ect ion st at em en t opt ion . No specific dr iver
opt ion s h ave been defin ed. Th e con n ect ion opt ion s t h at can be set wit h
t h is fu n ct ion ar e:
—SQL_AUTOCOMMIT
Th is opt ion defin es t h e t r an sact ion m ode. To set th is option valu e,
t h e con n ect ion m u st n ot be open ed, ot h er wise t h e dr iver r et u r n s
SQL_ERROR.
Th e two possible valu es for t h is opt ion ar e:
—SQL_AUTOCOMMIT_ON
If t h e valu e is set t o SQL_AUTOCOMMIT_ON (au t o-com m it m ode), t h e
dr iver com m it s each st at em en t im m ediat ely after it h as been execu t ed. Th is is th e defau lt valu e (accor din gly, an o2odbc_server is
lau n ch ed by defau lt in au t o-com m it m ode).
O2 ODBC User Man u al
71
5
O2 ODBC
—SQL_AUTOCOMMIT_OFF
If t h e valu e is set t o SQL_AUTOCOMMIT_OFF (m an u al-com m it m ode), it
is u p t o t h e application t o explicit ly com m it or r oll back t r an sact ion s
wit h SQLTransact.
Rem ar k : An O2 ODBC ser ver r u n n in g on m an u al m ode m u st be
declar ed t o t h e dispat ch er to allow t h e con n ect ion t o t h e dat a sou r ce
t o be per for m ed wh en opt ion SQL_AUTOCOMMIT is set t o
SQL_AUTOCOMMIT_OFF.
—SQL_ACCESS_MODE
Th is opt ion defin es t h e access m ode:
—SQL_MODE_READ_WRITE
Th is is t h e defau lt m ode.
—SQL_MODE_READ_ONLY
Th is valu e is su ppor t ed bu t n ot u sed in t h is cu r r en t dr iver ver sion .
—SQL_TXN_ISOLATION
Set s t h e t r an sact ion isolation level. If a t r an sact ion is open , t h e dr iver
r et u r n s SQL_ERROR.
SQL_TXN_SERIALIZABLE (ser ializable t r an sact ion s plu s lock in g) is
t h e defau lt an d t h e on ly valid opt ion valu e in t h e cu r r en t O 2 ODBC
dr iver ver sion .
—SQL_ODBC_CURSORS
Th is is r elat ive t o SQLExtendedFetch calls. To set t h is option valu e,
t h e con n ect ion m u st n ot be open ed, ot h er wise t h e dr iver r et u r n s
SQL_ERROR.
Th e valu e SQL_CUR_USE_ODBC m ean s t h at t h e dr iver m an ager will
u se t h e M icr osoft OD BC cu r sor libr ar y for cu r sor scr ollin g. Cu r r en tly, t h is is t h e on ly valid opt ion valu e. Th e applicat ion m u st set
t h is opt ion to SQL_CUR_USE_ODBC if it wan t s t o u se SQLExtendedFetch.
Th e followin g opt ion s ar e n ot su ppor ted:
—SQL_PACKET_SIZE
—SQL_QUIET_MODE
—SQL_CURRENT_QUALIFIER
—SQL_OPT_TRACE
72
O2 OD BC User Man u al
ODBC API Functions :
—SQL_OPT_TRACEFILE
•SQLGetConnectOption
—SQLSetStmtOption
Set s a st at em en t opt ion valu e. Th e st at em en t opt ion s th at can be set
wit h t h is fu n ction ar e:
—SQL_ASYNC_ENABLE
Th e two possible valu es ar e:
—SQL_ASYNC_ENABLE_ON
Th e followin g fu n ction s can be execu t ed asyn ch r on ou sly :
SQLGetTypeInfo, SQLPutData, SQLParamData, SQLExecDirect,
SQLPrepare, SQLExecute, SQLFetch, SQLGetData,
SQLNumResultCols, SQLDescribeCol, SQLColAttributes an d all
cat alog fu n ction s.
—SQL_ASYNC_ENABLE_OFF
D isable asyn ch r on ou s fu n ct ion execu t ion s.
Ch an gin g th is opt ion valu e is allowed at an y tim e, becau se n o
asyn ch r on ou s fu n ct ion s can be st ill execu t in g for t h is statem en t . Th is
in du ces im m ediate effect for su bsequ en t calls. Th e defau lt valu e is
SQL_ASYNC_ENABLE_OFF.
—SQL_NOSCAN
Scan n in g or n ot SQL st r in g for escape clau ses. Escape clau ses ar e
allowed on ly in SQL statem en t st r in gs for ext en ded OD BC pr ocedu r e
calls. Th e t wo possible valu es ar e:
—SQL_NOSCAN_OFF
Th e dr iver will scan SQL str in gs for escape clau se.
—SQL_NOSCAN_ON
Th e dr iver does n ot scan an d sen ds dir ect ly t h e st at em en t t o t h e data
sou r ce.
Ch an gin g t h is valu e will t ak es effect for t h e n ext calls to SQLExecDirect or SQLPrepare. Th e defau lt valu e is SQL_NOSCAN_OFF.
—SQL_MAX_LENGTH
Th is gives t h e m axim u m am ou n t of dat a r etu r n ed by t h e dr iver for a
ch ar act er or bin ar y colu m n . If t h e valu e is 0, t h e dr iver at tem pt s t o
r et u r n all available data. For an y oth er valu e gr eat er th an 254 byt es,
O2 ODBC User Man u al
73
5
O2 ODBC
if t h e len gt h of available dat a is gr eat er t h an SQL_MAX_LENGTH, dat a
r et r ieved wit h SQLFetch or SQLGetData ar e t r u n cat ed wit h ou t er r or
or war n in g m essages.
In t h e cu r r en t ver sion , th e on ly valid valu e is t h e defau lt on e, i.e. 0,
m ean in g all available dat a is r et r ieved wh en ever possible.
—SQL_QUERY_TIMEOUT
Nu m ber of secon ds t o wait for an SQL st at em en t t o execu t e befor e
r et u r n in g to th e applicat ion . If t h e valu e is 0, t h e t im e-ou t is disabled
(n o t im e ou t ). If t h e valu e exceeds t h e m axim u m t im e-ou t in t h e dat a
sou r ce, 600 secon ds, or is sm aller t h an t h e m in im u m , 60 secon ds,
t h e dr iver su bst itu t es t h at valu e by th is m axim u m or m in im u m valu e
an d r et u r n s SQL_SUCCESS_WITH_INFO.
Ch an gin g t h e valu e is allowed an y t im e an d is t ak en in t o accou n t for
su bsequ en t execu t ion s. Th e defau lt valu e is 0 (n o t im e ou t ).
—SQL_ROWSET_SIZE
D efin es th e n u m ber of r ows r et u r n ed by an SQLExtendedFetch. An y
valu e is su ppor t ed. Ch an gin g th is valu e is allowed even if cu r sor s ar e
open ed, specially bet ween t wo SQLExtendedFetch. Th e valu e will
t ak e effect for t h e n ext SQLExtendedFetch calls. Th e defau lt valu e is
1.
—SQL_MAX_ROWS
Th is defin es t h e m axim u m n u m ber of r ows t o r et u r n to t h e applicat ion for a SELECT st at em en t . If t h e valu e is 0, t h e dr iver r et u r n s all
r ows. An y an ot h er valu e is allowed. Th e defau lt valu e is 0 m ean in g all
r ows.
—SQL_BIND_TYPE
Two t ypes of valu e defin e t h e bin d type to be u sed by SQLExtendedFetch. Th e defau lt an d on ly possible valu e is SQL_BIND_BY_COLUMN.
—SQL_RETRIEVE_DATA
Two valu es for r et r ievin g dat a in SQLExtendedFetch calls:
—SQL_RD_ON
In SQLExtendedFetch calls, dat a ar e r et r ieved.
—SQL_RD_OFF
SQLExtendedFetch posit ion s th e cu r sor to th e specified locat ion bu t
dat a ar e n ot r et r ieved. For exam ple, th is opt ion valu e allows an applicat ion t o call SQLExtendedFetch on ly t o ver ify exist en ce of r ows an d
ch eck global er r or s.
Ch an gin g t h is valu e is allowed even if cu r sor s ar e open ed, especially
bet ween t wo calls t o SQLExtendedFetch. Th e n ew valu e tak es effect
74
O2 OD BC User Man u al
ODBC API Functions :
for th e n ext SQLExtendedFetch calls. Th e defau lt valu e is
SQL_RD_ON.
—SQL_CONCURRENCY
Specifies t h e cu r sor con cu r r en cy. To set t h is valu e, t h e cu r sor m u st
n ot be open ed an d th e st at em en t n ot pr epar ed. Th e defau lt an d on ly
valu e su ppor t ed by t h e O2 OD BC dr iver is SQL_CONCUR_READ_ONLY,
m ean in g th at t h e cu r sor is r ead-on ly an d n o u pdates ar e allowed. If
an ot h er valu e is specified, t h e dr iver su bstit u t es th is valu e by th e
defau lt on e an d r et u r n s SQL_SUCCESS_WITH_INFO.
—SQL_CURSOR_TYPE
Specifies t h e cu r sor t ype. To set th is valu e t h e cu r sor m u st n ot be
open ed an d t h e st at em en t n ot pr epar ed. Th e defau lt an d on ly valu e
su ppor ted by t h e O2 ODBC dr iver is SQL_CURSOR_FORWARD_ONLY,
m ean in g th at t h e cu r sor on ly scr olls for war d. If an ot h er valu e is
specified, th e dr iver su bst it u t es t h is valu e by t h e defau lt on e an d
r et u r n s SQL_SUCCESS_WITH_INFO.
Th e followin g opt ion s ar e n ot su ppor t ed:
—SQL_KEYSET_SIZE
—SQL_SIMULATE_CURSOR
—SQL_USE_BOOKMARKS.
•SQLGetStmtOption
Besides t h e opt ion s u sed wit h SQLSetStmtOption, for wh ich t h e dr iver
r et u r n s t h e cu r r en t set tin g, an oth er opt ion can be r et r ieved:
—SQL_ROW_NUMBER
Th is allows t h e n u m ber of t h e cu r r en t r ow in t h e r esu lt set t o be
r et r ieved. If t h e cu r r en t r ow can n ot be det er m in ed or if t h er e is n o
cu r r en t r ow, t h e dr iver r et u r n s 0. To get t h is opt ion valu e, a cu r sor
m u st be open ed an d n ot position ed befor e or aft er t h e r esu lt set .
5.3.4 Preparing SQL requests
•SQLAllocStmt
•SQLNativeSql
O2 ODBC User Man u al
75
5
O2 ODBC
•SQLPrepare
•SQLBindParameter
•SQLGetCursorName
Cu r sor n am es ar e u sed by position ed u pdat e or delet e st at em en ts. Even
if t h ose st at em en t s ar e n ot su ppor ted by t h e O2 ODBC dr iver , t h e
fu n ct ion s SQLSetCursorName an d SQLGetCursorName ar e
im plem en t ed.
•SQLSetCursorName
5.3.5 Submitting requests
•SQLExecute
•SQLExecDirect
•SQLNumParams
•SQLParamData
•SQLPutData
5.3.6 Retrieving results and information about results
•SQLRowCount
•SQLNumResultCols
•SQLDescribeCol
•SQLColAttributes
•SQLBindCol
•SQLFetch
76
O2 OD BC User Man u al
ODBC API Functions :
•SQLExtendedFetch
•SQLGetData
5.3.7 Catalog functions
Th e followin g r est r ict ion s apply t o cat alog fu n ct ion s:
• r esu lt sets ar e n ot or der ed (e.g. by t able n am e for SQLTable);
• on ly t h e per cen t ch ar act er (m at ch in g zer o or m or e of an y ch ar act er ) is
su ppor t ed in sear ch pat t er s;
• t able qu alifier s an d own er s ar e n ot su ppor t ed.
•SQLColumns
•SQLForeignKeys
•SQLPrimaryKeys
•SQLProcedures
Ret u r n s t h e list of pr ocedu r e n am es an d ch ar act er ist ics for a specific
dat a sou r ce. Th ese ar e t h e pr ocedu r es declar ed in t h e con figu r at ion file
u sed t o der ive th e view associated t o th e dat a sou r ce.
•SQLSpecialColumns
•SQLStatistics
On ly st at ist ics givin g t h e n u m ber of r ows of a t able will be per for m ed.
For in dexes in for m ation , n o data will be r et u r n ed in t h e r esu lt set .
If t h e ar gu m en t fAccuracy is SQL_ENSURE, t h e n u m ber of r ows in t h e
t able is u n con dition ally r et r ieved wh ich m ean s t h at a COUNT r equ est is
per for m ed on t h e t able in t h e O2 dat a sou r ce. If fAccuracy is
SQL_QUICK, t h is n u m ber is on ly r et r ieved if it is r eadily available fr om
t h e ser ver .
•SQLTables
O2 ODBC User Man u al
77
5
O2 ODBC
If t h e ar gu m en t szTableType is % an d t h e ar gu m en t szTableName is
an em pt y st r in g, t h e r esu lt set con t ain s t h e list of valid t able t ypes for
t h e dat a sou r ce (all oth er s colu m n s con t ain NULL). Valid table t ypes ar e:
O2 CLASS TABLE, O2 COLLECTION TABLE, USER TABLE, VIEW TABLE.
For m or e details on t h e differ en t t ypes of tables, see Ch apt er 4, “Sch em a
Updat e Com m an ds” on page 42.
If a qu alifier or own er is specified, SQL_ERROR is r et u r n ed.
5.3.8 Terminating a statement
•SQLFreeStmt
•SQLCancel
•SQLTransact
If t h e con n ection is in au t o-com m it m ode, an O2 t r an sact ion is st ar t ed
each t im e an SQL st at em en t t h at can be con tain ed wit h in a t r an sact ion
is execu t ed again st t h e cu r r en t dat a sou r ce. Th e dr iver validat es t h is
t r an sact ion aft er each execu t ion .
Execu t in g a SELECT st at em en t will im ply, for t h e O2 data sou r ce,
st ar t in g an O2 t r an saction , pr ocessin g, open in g a scan on th e r esu lt
an d validat in g t h e tr an sact ion . For t h e O2 OD BC clien t , a cu r sor is
open ed.
An SQLFreeStmt wit h SQL_CLOSE opt ion valu e will close, for th e O2
dat a sou r ce, t h e scan an d delet e pen din g r esu lt s, an d, for t h e O2 OD BC
clien t , close th e cu r sor an d delet e pen din g r esu lt s.
In m an u al-com m it m ode, each t im e an SQL statem en t t h at can be
con tain ed wit h in a t r an sact ion is su bm it t ed t o t h e O2 dat a sou r ce an O2
t r an sact ion is st ar t ed on ly if n o t r an saction is alr eady open . All
st at em en ts associated t o a con n ect ion sh ar e t h e sam e tr an sact ion
space. In or der to com m it or r ollback a t r an sact ion , t h e applicat ion
m u st call SQLTransact wit h t h e appr opr iat e par am et er .
Execu t in g a SELECT st at em en t will im ply, for t h e O2 data sou r ce,
pr ocessin g an d open in g a scan on t h e r esu lt, an d, for t h e O2 OD BC
clien t , open in g a cu r sor . An SQLFreeStmt wit h SQL_CLOSE opt ion valu e
will, for t h e O 2 dat a sou r ce, close t h e scan an d delet e pen din g r esu lt s,
an d, for th e O2 ODBC clien t, close t h e cu r sor an d delet e pen din g
r esu lts.
78
O2 OD BC User Man u al
O2 ODBC Tools :
Wh en SQLTransact is called, with t h e on ly valid option
SQL_CB_DELETE, it com m it s or r ollback s all th e pr eviou sly su bm it ted
r equ est s wit h in th e t r an sact ion . For t h e O 2 dat a sou r ce, all open ed
scan s ar e closed, all pen din g r esu lt s an d all access plan s (i.e. O2
h an dles) ar e delet ed. For t h e O 2 ODBC clien t , cu r sor s an d pen din g
r esu lt s ar e delet ed for all t h e associat ed st at em en t s.
5.3.9 Terminating a connection
•SQLDisconnect
•SQLFreeConnect
•SQLFreeEnv
5.3.10 General information
•SQLError
•SQLGetFunctions
Th e ar gu m en t fFunction is SQL_API_ALL_FUNCTIONS or a defin ed
valu e iden t ifyin g t h e ODBC fu n ct ion of in t er est. Th e ou t pu t ar gu m en t
pfExists is an ar r ay of 100 elem en ts or a sin gle UWORD. Valu es ar e set
t o TRUE if t h e fu n ct ion is su ppor t ed, FALSE ot h er wise.
SQLGetFunctions will r et u r n FALSE for th e followin g level 2 fu n ct ion s
on ly: SQLBrowseConnect, SQLParamOptions, SQLSetPos,
SQLSetScrollOptions, SQLDescribeParam, SQLMoreResults,
SQLProcedureColumns, SQLColumnPriviliges an d
SQLTablePrivileges.
5.4 O2 ODBC Tools
A n u m ber of t ools is available for O2 ODBC developer s. Th ese ar e
pr ogr am s t h at sh ou ld be fou n d in th e bin/<platform> su bdir ect or y of
t h e O2 in st allat ion dir ect or y.
O2 ODBC User Man u al
79
5
O2 ODBC
5.4.1 o2sql_export
As descr ibed in Sect ion 5.3, an O2 dat a sou r ce cor r espon ds t o an O2
base on wh ich a view h as been activat ed. To be able t o con n ect t o a
dat a sou r ce, an O2 base m u st exist an d a view on t h e sch em a of t h at
base m u st h ave been pr eviou sly cr eated.
Th e o2sql_export t ool is t h e view cr eat ion t ool. It s feat u r es an d
com plet e u sage ar e descr ibed in Sect ion 4.3.1 an d Sect ion 5.4.1
r espect ively.
5.4.2 o2sql_query
Th e o2sql_query is an au xilair y t ool u sed for view sch em a
m an agem en t. It can be ver y u sefu l for vir t u al sch em a design er s as it
allows qu ick in spection of vir tu al sch em as an d databases. In par t icu lar ,
it can be u sed t o delet e view sch em as fr om t h e SQL cat alog an d t o
r et r ieve t h e con t en t s of a con figu r ation file u sed t o der ive a given view
in t o a file. Th is file can be t h u s m odified an d t h e view r e-gen er at ed.
It s feat u r es an d com plet e u sage ar e descr ibed in Sect ion 4.3.2 an d
com m an d “o2sql_qu er y”, page 7-115.
5.4.3 o2odbc_server
An O 2 ODBC ser ver s pr ocess O2 ODBC clien t r equ est s.
Wh en st ar t ed, o2odbc_server establish es a con n ect ion wit h an O2
Open D ispat ch er (o2open_dispatcher) wh ich m u st alr eady be r u n n in g
an d est ablish es also a con n ection wit h a n am ed O2 dat abase syst em
t h r ou gh an o2server, wh ich m u st also alr eady be r u n n in g.
An O2 OD BC ser ver loads view in for m at ion fr om t h e SQL cat alog st or ed
in an O 2 syst em for a given dat a sou r ce so as t o be able t o per for m
qu er y t r an slation s. It also u pdat es t h e SQL cat alog wh en ever sch em a
u pdat e com m an ds (t able, view an d in dex cr eat ion , m odificat ion an d
dest r u ct ion ) ar e execu ted on t h e data sou r ce. Fin ally, it per for m s all th e
ODBC specific act ivit y (dat a con ver sion s, cu r sor m an agem en t , et c)
in volved in t h e pr ocessin g of clien t s r equ est s.
An O2 ODBC ser ver can r u n in two m odes, n am ely th e au to-com m it an d
m an u al m odes. In th e au t o-com m it m ode, an im plicit com m it is
per for m ed after t h e execu t ion of each SQL st at em en t . In m an u al, m ode,
80
O2 OD BC User Man u al
O2 ODBC Tools :
com m it s an d/ or r ollback s m u st be explicit ly per for m ed by t h e
applicat ion t h r ou gh t h e ODBC API fu n ct ion SQLTransact.
Th e com plet e u sage of t h e o2odbc_server pr ogr am is given in
com m an d “o2odbc_ser ver ”, page 7-109.
5.4.4 o2odbc_dump_base
Th e wh ole con t en t s of an O2 dat a sou r ce, i.e. of t h e vir t u al r elat ion al
dat abase cor r espon din g t o t h e application of a view on an O2 base, can
be logically du m ped in t o an ASCII file wit h t h e o2odbc_dump_base
pr ogr am . Th e logical du m p of a vir tu al dat abase con sists of all t able
cr eat ion an d r ow in ser tion SQL com m an ds. Th e gen er at ed ASCII file can
be given as in pu t to a pr ogr am th at sen ds each com m an d t o execu t ion
on a given dat abase. Th is allows th e con t en t s of a du m ped database t o
be loaded elsewh er e.
In par ticu lar , th e gen er ated ou t pu t file can be given as in pu t t o t h e
o2sql_query tool t o du plicat e t h e con ten ts of a vir t u al dat abase in t o
an ot h er base. Th is allows an O2 base (or th e par t of an O 2 base t h at is
expor t ed as a vir tu al database) t o be m at er ialized as a r elat ion al
dat abase.
Th e com plet e u sage of t h e o2odbc_dump_base pr ogr am is given in
com m an d “o2odbc_du m p_base”, page 7-108.
5.4.5 o2open_dispatcher
An O2 Open Dispatch er r egister s all O2 ODBC ser ver s r u n n in g on a LAN
an d is qu er ied t o get t h e addr ess of a ser ver able t o an swer t o an O 2
ODBC clien t r equ ests.
A ser ver is ch osen by t h e dispat ch er accor din g t o a h eu r ist ics an d based
on con n ect ion opt ion s set by th e clien t. A scor e is com pu t ed for each
ser ver r u n n in g an d t h e ser ver wit h th e best scor e is r etu r n ed t o t h e
clien t .
Th e com plet e u sage of t h e o2open_dispatcher pr ogr am , in clu din g
m or e det ails on t h e h eu r ist ics u sed by t h e dispat ch er t o ch oose a ser ver
for a given clien t , is given in com m an d “o2open _dispat ch er ”, page 7111.
O2 ODBC User Man u al
81
5
82
O2 ODBC
O2 OD BC User Man u al
6
Programming an
O2ODBC Server
6
Pr ogr am m er s can u se t h e o2_Odbc class t o bu ild t h eir own O2 OD BC
ser ver s.
Th is ch apt er descr ibes h ow to in t egr ate you r C++ applicat ion wit h an
O2 ODBC ser ver so as t o be able t o access in st an ces of t h e im por t ed C++
classes st or ed in an O 2 database as r elat ion al dat a an d, in par t icu lar , t o
execu t e C++ fu n ct ion s as st or ed pr ocedu r es t h r ou gh t h e OD BC
in t er face.
We assu m e t h e r eader is fam iliar wit h t h e ODMG C++ Bin din g an d with
t h e con cept of stor ed pr ocedu r e in SQL an d ODBC.
To im plem en t you r own O2 OD BC ser ver you bu ild an OD MG C++
applicat ion u sin g t h e followin g:
• u ser classes
• ODM G C++ libr ar ies
• O2 ODBC libr ar ies
Th e followin g sect ion s det ail t h e differ en t st eps in volved in t h e
con st r u ct ion of an O2 ODBC ser ver .
O2 ODBC User M an u al
83
6
Programming an O2ODBC Server
6.1 Defining the O2 ODBC Server main function
You m u st bu ild an O 2 ODBC ser ver execu t able fr om a m ain fu n ct ion
an d application files. Th e m ain fu n ct ion u ses t h e o2_Odbc class.
Th e gen er al st r u ct u r e of a main fu n ct ion u sed in t h e con st r u ct ion of an
O 2 ODBC ser ver is t h e followin g:
• Cr eat es an o2_Odbc class object .
• Set s t h e t h e ser ver opt ion s an d par am et er s.
• In it ializes t h e O 2 ODBC ser ver (begin).
• St ar ts t h e ser ver loop (loop).
• Fin ish es (end).
An exam ple of a main fu n ct ion is given below. .
int main(int argc, char** argv)
{
short error=0;
o2_Odbc *o2odbcServer = new o2_Odbc();
o2odbcServer->set_sysdir(getenv("O2HOME"));
o2odbcServer->set_conffile(".o2rc");
o2odbcServer->set_confvar("O2OPTIONS");
o2odbcServer->set_enroll(enroll_func);
o2odbcServer->set_check(check_func);
error = o2odbcServer->begin(argc, argv);
if (error) {
return(1);
}
error = o2odbcServer->init();
if (error) {
return(1);
}
o2odbcServer->loop();
o2odbcServer->end();
delete o2odbcServer;
return (0);
}
On ce th e ser ver is star t ed, it con n ect s to an O2 ser ver fir st (fu n ction
begin) an d th en t o an O 2 Open Dispat ch er (fu n ct ion init). It t h en wait s
84
O2 OD BC User Man u al
Compiling your own O2 ODBC server :
for r equ est s sen t by O2 ODBC clien t s (fu n ction loop). Fu n ct ion s begin,
init, loop an d end of class o2_Odbc ar e defin ed in t h e o2odbc_svr
libr ar y.
For a given applicat ion , a specific con figu r at ion can be defin ed in t h e
main fu n ction by applyin g t h e appr opr iat e set fu n ct ion s to t h e in st an ce
of o2_Odbc.
Th e fu ll r efer en t ial in for m at ion on t h e o2_Odbc class is given in Sect ion
7.1.
6.2 Compiling your own O2 ODBC server
An O 2 ODBC ser ver is bu ilt as an ODM G C++ applicat ion with t h e h elp
of t h e o2makegen t ool. A con figu r ation file is u sed to bu ild t h e m ak efile
u sed in t h e con st r u ct ion of an O 2 ODBC ser ver . Th e exam ple below
illu st r at es su ch a con figu r at ion file..
O2Home= $O2HOME
O2System= $O2SYSTEM
O2Server= $O2SERVER
O2Schema= o2odbc_cpp
+UseOql
+UseConfirmClasses
ImpFiles= Person.hxx SQLStoredProcedureHandler.hxx
[Person.hxx]ImpClasses= Person
[SQLStoredProcedureHandler.hxx]ImpClasses=
SQLStoredProcedureHandler
+[SQLStoredProcedureHandler.hxx]
[SQLStoredProcedureHandler]
ImpAllPublicMemberFunc
ImpSet= Person
ProgramLibDir= $O2HOME/lib
ProgramLib= o2odbc_svr sql oql o2cppruntime o2runtime
o2api o2util
o2store o2common
Sources= Person.cc SQLStoredProcedureHandler.cc main.cc
ProgramObjs= main.o Person.o
SQLStoredProcedureHandler.o $O2HOME/obj/o2odbc_load.o
ProgramName= my_o2odbc_server
O2 ODBC User Man u al
85
6
Programming an O2ODBC Server
In t h e exam ple aboce, Person is an applicat ion class defin ed in file
Person.hxx. It s m em ber fu n ct ion s ar e defin ed in file Person.cc. Class
SQLStoredProcedureHandler gr ou ps all C++ fu n ct ion s t h at ar e
declar ed as st or ed pr ocedu r es in t h e con figu r at ion file u sed by t h e
o2sql_export t ool to cr eat e t h e r elation al view of t h e O2 sch em a. Th e
im plem en t at ion of su ch fu n ct ion s is pr ovided in file
SQLStoredProcedureHandler.cc. Th e main.cc file con t ain s t h e
defin it ion of t h e main fu n ct ion , as illu str at ed above.
Th e execu t able my_o2odbc_server (clau se ProgramName) is gen er at ed
by im por t in g th e Person an d SQLStoredProcedureHandler classes
in t o O2 , com pilin g t h e cor r espon din g sou r ce files an d lin k in g t h e
r espect ive object files wit h th e libr ar ies declar ed in t h e ProgramLib
clau se.
.
O2
O2 ODBC
libr ar ies
OD MG C++
libr ar ies
cr eat e sch em a S;
u ser s classes
u ser s
im por t ed classes
cr eat e base B;
o2im por t
n am e N: ...
m ain .cc
O 2 OD BC ser ver
execu t able
Fi gu r e 4 .2 : Com p on en t s of a n O2 ODBC a p p l i ca t i on
For m or e det ails, r efer t o t h e ODM G C++ an d o2makegen u ser an d
r efer en ce m an u als.
86
O2 OD BC User Man u al
Running your own O2 ODBC server :
6.3 Running your own O2 ODBC server
Given a C++ applicat ion , th e followin g st eps sh ou ld be per for m ed t o r u n
an O2 ODBC ser ver t h at can access in st an ces of C++ applicat ion classes
t h r ou gh SQL an d lau n ch C++ fu n ction s as stor ed pr ocedu r es:
• In it ialize an O2 syst em an d r u n t h e O 2 ser ver .
Th is is ach ieved th r ou gh t h e appr opr iate o2dba_in it an d o2ser ver
pr ogr am s. For m or e in for m at ion r efer t o t h e O 2 Syst em Adm in ist r at ion Man u als.
• Cr eat e an O2 sch em a.
Th is can be ach ieved t h r ou gh t h e appr opr iate adm in istr at ion t ools
(e.g. o2dsa). Refer t o t h e O2 Syst em Adm in ist r at ion M an u als.
• Im por t t h e C++ classes in to O2 .
Aft er sch em a cr eat ion , you m u st im por t t h e classes an d m em ber
fu n ct ion s of you r application . Th is is ach ieved th r ou gh t h e appr opr iat e t ools (e.g. o2makegen). Refer to t h e ODM G C++ Bin din g Refer en ce
M an u al an d User ’s Gu ide.
• Im por t t h e class SQLStoredProcedureHandler.
Th is class is th e en t r y poin t allowin g C++ fu n ct ion s t o be called as
st or ed pr ocedu r es t h r ou gh t h e SQL in t er face.
• Cr eat e per sist en t r oot s.
Per sist en t r oot s m u st be defin ed t o st or e in st an ce of th e C++ applicat ion classes. Su ch r oots can be u sed as an en t r y poin t in t h e dat abase by th e SQL en gin e, if t h ey ar e declar ed as ext en t s in t h e
con figu r at ion file u sed by t h e o2sql_export tool.
• Cr eat e t h e O2 ODBC ser ver .
As det ailed above, t h e cr eat ion of an O2 OD BC ser ver in volves t h e
defin ition of a m ain fu n ct ion t h at , t oget h er wit h t h e applicat ion files,
is u sed t o bu ild an execu table t h at is lin k ed t o t h e appr opr iat e libr ar ies.
• Popu lat e t h e dat abase.
An applicat ion m u st load dat a in th e database befor e qu er yin g it . Th e
dat abase can be popu lat ed by th e C++ application or t h r ou gh th e
SQL in t er face, with t h e appr opr iat e r ow in ser t ion SQL com m an d or
by callin g u ser defin ed C++ fu n ct ion s declar ed as st or ed pr ocedu r es.
• Ru n t h e ser ver .
O2 ODBC User Man u al
87
6
Programming an O2ODBC Server
A u ser -defin ed O2 ODBC ser ver wor k s as a st an dar d O2 OD BC
ser ver , bu t as it is lin k ed t o som e u ser -defin ed classes, it is able t o
r u n C++ fu n ction s defin ed in class SQLStoredProcedureHandler
an d declar ed in a view gen er at ion con figu r at ion file as stor ed pr ocedu r es.
88
O2 OD BC User Man u al
7
O2 ODBC Reference
7
Th is ch apt er det ails t h e o2_Odbc class an d all O2 ODBC com m an ds. It
is divided in t o t h e followin g sect ion s:
• Th e o2_odbc Class.
Th is class is u sed by an applicat ion to star t an O2 OD BC ser ver an d
begin t h e ser ver loop.
• Th e O2 OD BC Com m an ds.
Th is section pr ovides t h e O 2 ODBC syst em com m an ds.
O 2 ODBC User M an u al
89
7
O2 ODBC Reference
7.1 The o2_odbc Class
Th is sect ion pr esen t s t h e o2_Odbc class
an d descr ibes t h e followin g m em ber fu n ct ion s:
•
•
•
•
•
•
•
•
•
90
banner
begin
end
enroll
enroll_path
get_option
init
set...
usage
O 2 ODBC User M an u al
The o2_odbc Class :
.
class o2_Odbc {
public:
enum OptionType {
NoValue, OptionalValue, MandatoryValue };
enum OptionMode{
Append=0,
// string value are appended to old ones
Replace=1,
// string value replace old one
Add=2
// string value are added in the list found
};
o2_Odbc();
~o2_Odbc();
static void interruptFunc(int signal);
int begin(int argc, register char *argv[]);
int begin(int argc, register char *argv[],
const char *sysdir, const char
*systemname, const char *servername,
const char *dispatchername, int verbose);
int begin(int argc, register char *argv[],
const char *sysdir, const char *systemname,
const char *servername, const char
*dispatchername, const char *conf_file,
const char *conf_var, void (*enroll_func)(),
void (*check_func)(), int verbose);
int begin(int argc, register char *argv[],
const char *sysdir, const char *systemname,
const char *servername, const char
*dispatchername, const char *conf_file,
const char *conf_var, void (*enroll_func)(),
void (*check_func)(), const char *swapdir,
char * const *libpath, char * const *libname,
int commitfrequency, const char * commitmode,
const char *application, int verbose);
O2 OD BC User Man u al
91
7
O2 ODBC Reference
.
int init();
int end();
int loop();
void set_systemname(const char *systemname);
void set_servername(const char *servername);
void set_sysdir(const char *sysdir);
void set_swapdir(const char *swapdir);
void set_dispatchername(const char *dispatchername);
void set_commitFrequency(const char *commitfrequency);
void set_commitFrequency(int commitfrequency);
void set_commitMode(const char *commitmode);
void set_verbose(int verbose);
void set_libpath(char * const *libpath);
void set_libname(char * const *libname);
void set_application(const char *appli);
void set_conffile(const char *conf_file);
void set_confvar(const char *conf_var);
void set_enroll(void (*enroll_function)());
void set_check(void (*check_function)());
void set_default_env();
static void default_enroll_func();
static void default_check_func();
static int usage();
static int banner();
static int enroll(const char * const name,
const char * const confname,
const char * const optname,
char *dflt,
const OptionType t,
const char * const desc,
const OptionMode mode=Replace);
92
O 2 ODBC User M an u al
The o2_odbc Class :
.
static int enroll(const char * const name,
const char * const confname,
const char * const optname,
long dflt,
const OptionType t,
const char * const desc,
const OptionMode mode=Replace);
static int enroll(const char * const name,
const char * const confname,
const char * const optname,
char dflt,
const OptionType t,
const char * const desc,
const OptionMode mode=Replace);
static int enroll(const char * const name,
const char * const confname,
const char * const optname,
double dflt,
const OptionType t,
const char * const desc,
const OptionMode mode=Replace);
static int enroll_path(const char *path);
static int get_option(const char *name,
char *&value,
int ind = -1);
static int get_option(const char *name,
long &value,
int ind = -1);
static int get_option(const char *name,
double &value,
int ind = -1);
static int get_option(const char *name,
char &value,
int ind = -1);
};
O2 OD BC User Man u al
93
7
O2 ODBC Reference
banner
94
Summary
Displays t h e ver sion n u m ber of O 2 .
Syntax
static int o2_odbc::banner();
Arguments
Non e.
Description
Displays t h e ver sion n u m ber of O 2 on t h e st an dar d ou t pu t .
Returns
0 if su ccessfu l.
-1 if t h er e was an in t er n al er r or .
O 2 ODBC User M an u al
The o2_odbc Class : begin
begin
Summary
St ar t s u p a con n ect ion t o an O2 dat abase.
Syntax
int begin (
int argc, register char * argv[]);
int begin (
int argc, register char * argv[],
const char *systemname,
const char *servername,
const char *sysdir,
int verbose);
int begin (
int argc, register char * argv[],
const char *conf_file,
const char *conf_var,
void (*enroll_func) (),
void (*check_func) (),
const char *systemname,
const char *servername,
const char *sysdir,
int verbose);
int begin (
int argc, register char * argv[],
const char *conf_file,
const char *conf_var,
void (*enroll_func) (),
void (*check_func) (),
const char *systemname,
const char *servername,
const char *sysdir,
const char *swapdir,
const * const *libpath,
const * const *libname,
int verbose);
Arguments
argc
Nu m ber of ar gu m en t s of t h e C++ execu t able.
argv
List of ar gu m en t s of t h e C++ execu t able.
systemname
Nam e of database syst em . Th is in for m at ion is
m an dat or y. It can be given as a par am eter or by callin g
o2_odbc::set_systemname befor e begin n in g t h e
session .
It can also be set by o2_odbc::set_default_env, in
wh ich case it is fou n d in t h e par am et er -system of you r
execu t able, in t h e O2OPTIONS en vir on m en t var iable (see
t h e conf_var ar gu m en t), or in t h e O2 option file .o2rc
(see t h e conf_file ar gu m en t ). See t h e O2 Sy stem
Administration Guid e for fu r th er det ails.
O2 OD BC User Man u al
95
7
O2 ODBC Reference
servername
Nam e of m ach in e on wh ich t h e O2 ser ver is r u n n in g. It
can be given as a par am et er or by callin g
o2_odbc::set_servername befor e begin n in g th e
session .
It can also be set by o2_odbc::set_default_env, in
wh ich case it is fou n d in t h e par am et er -server of you r
execu t able, in t h e O2OPTIONS en vir on m en t var iable (see
t h e conf_var ar gu m en t ), or in th e O2 opt ion file .o2rc
(see t h e conf_file ar gu m en t ). See t h e O2
Ad ministration Guide for fu r th er details.
sysdir
Pat h t o t h e dir ector y wh er e O2 is in st alled. Th is
in for m ation is m an dat or y. It n ot given , th e valu e fou n d
in th e en vir on m en t var iable O2HOME is u sed.
swapdir
Pat h t o a dir ect or y wh er e a swap file can be cr eated if O2
n eeds it . It can be NULL, in wh ich case t h e swap
dir ect or y in t h e O2 dir ect or y is u sed (See t h e O2 Sy stem
Ad ministration Guide).
libpath
A NULL-t er m in at ed ar r ay of ch ar acter st r in gs, wh er e
each st r in g gives a dir ect or y pat h . O2 sear ch es t h ese
dir ect or ies for libr ar ies n am ed in libname if dyn am ic
lin k in g is n eeded. It m ay be NULL.
libname
A NULL-t er m in at ed ar r ay of ch ar acter st r in gs, each
specifyin g a libr ar y n am e t o u se wh en lin k in g an d
loadin g fu n ction s dyn am ically. It m ay be NULL.
conf_file
Nam e of t h e file wh er e t h e O2 option s m an ager can fin d
t h e valu e for t h e en r olled opt ion s (see t h e enroll an d
enroll_path fu n ct ion s). If 0, conf_file tak es t h e
defau lt valu e .o2rc.
conf_var
Nam e of t h e en vir on m en t var iable wh er e t h e O2 opt ion s
m an ager can fin d t h e valu e for t h e en r olled opt ion s (see
t h e enroll an d enroll_path fu n ct ion s). If 0, conf_file
t ak es th e defau lt valu e O2OPTIONS.
enroll_func Poin t er t o a C fu n ct ion of t ype
static void (*func) (). Th is fu n ct ion m u st con t ain
code for r egist er in g option s.
96
check_func
Poin t er t o a C fu n ct ion of t ype
static void (*func) (). Th is fu n ct ion m u st con t ain
code for r et r ievin g an d ver ifyin g option valu es.
verbose
An in t eger specifyin g t h e session as a ver bose session .
O 2 ODBC User M an u al
The o2_odbc Class : begin
Description
St ar t s u p t h e con n ection t o t h e dat abase aft er an alyzin g t h e opt ion s.
Th is m em ber fu n ct ion allows you t o u se t h e sam e power fu l opt ion
m ech an ism th at is u sed by all th e t ools of t h e O2 en vir on m en t . Th is
opt ion m ech an ism is explain ed in detail in t h e O2 Sy stem
Ad ministration Guide.
Th e O2 opt ion s m ech an ism allows you t o defin e opt ion s fr om t h e
followin g sou r ces:
• Con figu r at ion file.
• En vir on m en t var iables.
• Com m an d lin e.
For a given opt ion , a valu e r etr ieved fr om a con figu r at ion file can be
over loaded by a valu e defin ed as an en vir on m en t var iable, wh ich in
t u r n can be over loaded by a valu e defin ed at t h e com m an d lin e.
Usin g t h e O2 opt ion s m ech an ism h as t h e followin g advan t ages:
• Sim ple m an agem en t of r u n t im e opt ion s.
• A coh er en t set of option s for all O2 applicat ion s an d t ools.
Usin g t h e O2 opt ion s m ech an ism is n ot m an dat or y. Th e m ost sim ple
way to u se th e O2 opt ion s m ech an ism is t o u se t h e m em ber fu n ction
o2_odbc::set _defau lt _en v befor e callin g o2_odbc::begin .
void o2_odbc::set_default_env()
Th is fu n ct ion allows you r C++ pr ogr am t o u se th e st an dar d O 2
con figu r at ion file (.o2rc), t h e st an dar d O2 en vir on m en t var iable
opt ion s (O2OPTIONS), an d t h e st an dar d O2 com m an d opt ion s:
-syst em , wh ich defin es t h e O2 syst em n am e,
-ser ver , wh ich defin es t h e n am e of t h e O2 ser ver h ost ,
-h elp, wh ich displays t h e h elp t ext for t h e pr ogr am , an d
-ver bose, wh ich en ables t h e ver bose m ode.
Customizing the options
You can add you r own opt ion s. For exam ple, you can r et r ieve O 2 C
par am eter s u sin g n ew opt ion s. To do th is, you m u st u se t h e
o2_odbc::enroll an d o2_odbc::get_option m em ber fu n ct ion s.
Th e o2_odbc::enroll fu n ction allows you t o r egist er th e opt ion s,
an d t h e o2_odbc::get_option fu n ct ion allows you t o r etr ieve th e
valu e of t h e opt ion s.
You m u st wr ite t h e followin g t wo fu n ction s:
O2 OD BC User Man u al
97
7
O2 ODBC Reference
• A r egist er fu n ct ion t h at con t ain s a call t o o2_odbc::enroll
fu n ct ion s, wh ich r egist er each of you r option s.
• A ch eck fu n ction t h at con t ain s a call to o2_odbc::get_option
fu n ct ion s, wh ich r etr ieve th e valu e of t h e r egist er ed opt ion s.
Th ese t wo fu n ction s can be r egist er ed u sin g t h e o2_odbc:begin
m em ber fu n ct ion (enroll_function an d check_function
par am et er s) or explicitly, befor e callin g o2_odbc::begin, u sin g t h e
followin g m em ber fu n ct ion s:
void o2_odbc::set_enroll(void (*enroll_function) ())
void o2_odbc::set_check(void (*check_function) ())
Th e opt ion s for t h e syst em n am e an d t h e ser ver n am e ar e m an dat or y.
Th ese t wo opt ion s ar e r egist er ed by t h e followin g code, wh ich you
m u st add to you r r egist er fu n ct ion :
session->enroll( "system_name", "system", "system"
(char *)NULL,
MandatoryValue,
"o2 system name to connect to",
Replace);
session->enroll( "system_name", "server", "server"
(char *)NULL,
MandatoryValue,
"machine on which o2 server is running",
Replace);
Aft er r egister in g t h ese m an dat or y opt ion s, you can r egist er you r own
opt ion s.
Returns
98
0 if t h e con n ection was car r ied ou t su ccessfu lly. If n ot , an er r or code is
given .
O 2 ODBC User M an u al
The o2_odbc Class : end
end
Summary
En ds an O2 session .
Syntax
int o2_odbc::end();
Arguments
Non e.
Description
En ds an O2 session an d t h e con n ect ion t o t h e O 2 ser ver . A com m it is
car r ied ou t au t om at ically.
Returns
Zer o if su ccessfu l, a n on -zer o valu e ot h er wise.
O2 OD BC User Man u al
99
7
O2 ODBC Reference
enroll
Summary
Regist er s an opt ion t o be r ecogn ized by t h e O2 opt ion s m an ager .
Syntax
static int o2_odbc::enroll (const char * const name,
const char * const confname,
const char * const optname,
char *dflt,
const OptionType t,
const char * const desc,
const OptionMode mode=Replace);
static int o2_odbc::enroll (const char * const name,
const char * const confname,
const char * const optname,
long dflt,
const OptionType t,
const char * const desc,
const OptionMode mode=Replace);
static int o2_odbc::enroll (const char * const name,
const char * const confname,
const char * const optname,
char dflt,
const OptionType t,
const char * const desc,
const OptionMode mode=Replace);
static int o2_odbc::enroll (const char * const name,
const char * const confname,
const char * const optname,
double dflt,
const OptionType t,
const char * const desc,
const OptionMode mode=Replace);
Arguments
100
name
A st r in g t h at in dicat es t h e n am e of t h e option . Th is n am e
is u sed for r et r ievin g th e valu e of t h e opt ion .
confname
A str in g th at in dicat es u n der wh ich n am e t h e valu e of
t h is opt ion can be given in a con figu r ation file.
optname
A str in g th at in dicat es u n der wh ich n am e t h e valu e of
t h is opt ion can be given in th e en vir on m en t var iable or
at th e com m an d lin e.
dflt
Th e defau lt valu e of t h e option . Th is valu e is r et r ieved if
t h e en d u ser does n ot give a valu e is given t o t h e opt ion .
O 2 ODBC User M an u al
The o2_odbc Class : enroll
Description
t
A valu e t ak en fr om t h e Opt ion Type en u m er at ion :
NoValue.
Th e opt ion r epr esen t s a boolean
valu e. If t h er e ar e valu es t h er e will be
an er r or du r in g par sin g of th e
opt ion s.
OptionalValue Th e opt ion can h ave an associat ed
valu e.
MandatoryValue Th e opt ion r epr esen t s a valu e. If t h is
valu e is n ot in dicat ed t h er e will be an
er r or du r in g par sin g of th e opt ion s.
desc
A st r in g descr ibin g t h e opt ion . Th is str in g is displayed
wh en t h e usage fu n ct ion is called or wh en a par sin g
er r or is det ect ed.
mode
A valu e t ak en fr om t h e Opt ion Mode en u m er at ion .
Add
Each t im e a valu e for t h e
opt ion is fou n d, th e n ew
valu e is added t o t h e
ar r ay of valu es. Valu es
can be r et r ieved by t h e
get fu n ction
u sin g t h e in dex
ar gu m en t .
Replace
Each t im e a valu e for t h e
opt ion is fou n d t h e old
valu e is r eplaced with a
n ew valu e. On ly on e valu e
can be r et r ieved.
Append
Each t im e a valu e for t h e
opt ion is fou n d, t h is valu e
is appen ded to t h e
cu r r en t valu e. On ly on e
valu e can be r etr ieved.
Th ese m em ber fu n ct ion allow you to r egister n ew opt ion s on t h e O 2
opt ion s m an ager .
Th ese fu n ct ion ar e r egist er ed by th e begin m em ber fu n ct ion .
Each of t h ese fu n ct ion s allow you t o en r oll on e option . Th er e is on e
fu n ct ion for each t ype of opt ion .
Returns
1 if su ccessfu l.
0 if th e opt ion cou ld n ot be en r olled.
-1 if t h er e was an in t er n al er r or in t h e opt ion m an ager .
O2 OD BC User Man u al
101
7
O2 ODBC Reference
enroll_path
Summary
Allows you t o r egist er h ier ar ch ical opt ion s.
Syntax
static int o2_odbc::enroll_path (const char * path);
Description
Th is m em ber fu n ct ion allows you t o r egist er h ier ar ch ical opt ion s.
H ier ar ch ical opt ion s ar e descr ibed as a pat h , i.e., an or der ed list of
opt ion s su ch as:
system.base.loadname
Th e h ier ar ch ical opt ion s on ly wor k in a con figu r at ion file su ch as
.o2rc.
Returns
102
0 if su ccessfu l.
-1 if t h er e was an in t er n al er r or .
O 2 ODBC User M an u al
The o2_odbc Class : get_option
get_option
Summary
Retr ieves t h e valu e of an opt ion .
Syntax
static int o2_odbc::get_option (
const char *name,
char *&value,
int ind = -1);
static int o2_odbc::get_option (
const char *name,
long &value,
int ind = -1);
static int o2_odbc::get_option (
const char *name,
double &value,
int ind = -1);
static int o2_odbc::get_option (
const char *name,
char &value,
int ind = -1);
Arguments
Description
name
A st r in g th at in dicat es t h e in t er n al n am e of t h e opt ion as
defin ed in t h e cor r espon din g o2_odbc::enroll m em ber
fu n ct ion .
value
Th is ar gu m en t poin t s t o t h e r etu r n ed valu e.
ind
An in dex t h at is u sed if t h e u ser en t er s an opt ion sever al
t im es. If you h ave r egist er ed th e opt ion with t h e r eplace
or appen d m ode, you sh ou ld set t h is ar gu m en t t o -1. If
t h e in dex is -1, t h e last valu e en ter ed by t h e en d-u ser is
r et u r n ed. If t h e in dex is >= 0, t h e in dex-t h valu e is
r et u r n ed. If t h e in dex is t oo lar ge, t h e r et u r n ed valu e is
NULL.
Th is m em ber fu n ction allows you t o r et r ieve t h e valu e of th e r egister ed
opt ion s. Th is fu n ct ion sh ou ld on ly be called for opt ion s t h at ar e
r egist er ed.
Th is fu n ct ion is in t en ded to be u sed in t h e ch eck fu n ct ion, wh ich can
be r egist er ed by t h e o2_odbc::begin m em ber fu n ct ion .
Returns
0 if su ccessfu l.
-1 if t h e opt ion can n ot be r et r ieved (i.e., t h e opt ion is n ot r egist er ed).
O2 OD BC User Man u al
103
7
O2 ODBC Reference
init
104
Summary
St ar ts u p a con n ection t o an o2open_dispatcher.
Syntax
int o2_odbc::init();
Arguments
Non e.
Description
Th is fu n ct ion con n ect s t h e ser ver t o t h e dispat ch er .
Returns
Zer o if t h e oper at ion was su ccessfu l. Else a n on -zer o valu e.
O 2 ODBC User M an u al
The o2_odbc Class : set...
set...
Summary
Set s t h e var iou s session par am et er s.
Syntax
void o2_odbc::set_default_env();
void o2_odbc::set_enroll();
void o2_odbc::set_libname(char **);
void o2_odbc::set_libpath(char **);
void o2_odbc::set_servername(char *);
void o2_odbc::set_swapdir(char *);
void o2_odbc::set_sysdir(char *);
void o2_odbc::set_systemname(char *);
Description
Explicit ly set var iou s session par am eter s befor e begin n in g t h e session
wit h o2_odbc::begin(argc, argv, mode);.
set_default_env(); allows you r C++ pr ogr am t o u se t h e stan dar d O2
con figu r at ion file (.o2rc), t h e st an dar d O2 en vir on m en t var iable opt ion s
(O2OPTIONS), an d th e stan dar d O2 com m an d opt ion s:
-syst em , wh ich defin es t h e O2 syst em n am e,
-ser ver , wh ich defin es t h e n am e of t h e O2 ser ver h ost ,
-h elp, wh ich displays t h e h elp t ext for t h e pr ogr am , an d
-ver bose, wh ich en ables t h e ver bose m ode.
Returns
Not h in g.
Note
Refer t o o2_odbc::begin() for addit ion al in for m ation .
O2 OD BC User Man u al
105
7
O2 ODBC Reference
usage
106
Summary
Displays a descr ipt ion of t h e r egist er ed opt ion s.
Syntax
static void o2_odbc::usage ();
Description
Th is m em ber fu n ct ion displays a u sage descr ipt ion of t h e r egister ed
opt ion s. All valid opt ion s ar e displayed wit h t h e con t en t s of t h e desc
ar gu m en t of t h e o2_odbc::enroll m em ber fu n ct ion .
Returns
0 if su ccessfu l.
-1 if t h er e was an in t er n al er r or .
O 2 ODBC User M an u al
The O2 ODBC Commands : usage
7.2 The O2 ODBC Commands
Th e com m an ds ou t lin ed in t h is sect ion sh ou ld be fou n d in t h e
bin / <platfor m > su bdir ector y of t h e O2 in st allat ion dir ector y. Th ese
com m an ds ar e:
• o2odbc_dump_base
• o2odbc_server
• o2open_dispatcher
• o2sql_export
• o2sql_query
O2 OD BC User Man u al
107
7
O2 ODBC Reference
o2odbc_dump_base
Summary
Gen er at es a logical du m p of an O2 dat a sou r ce in a given ASCII file.
Syntax
o2odbc_dump_base
connection_string
output_file
Mandatory arguments
• connection_string
Th is ar gu m en t m u st be defin ed as specified in Section 5.2.3. It is u sed
by t h e o2odbc_dump_base pr ogr am to con n ect t o a given O2 ODBC dat a
sou r ce.
• output_file Th is is t h e n am e of t h e file wh er e t h e SQL com m an ds ar e
du m ped in t o.
Optional arguments
Non e.
Description
Th e logical du m p of a vir t u al dat abase con sist s of all t able cr eat ion an d
r ow in ser t ion SQL com m an ds. Com m an ds ar e t er m in at ed by ";".
Th e gen er at ed ASCII file can be given as in pu t t o a pr ogr am t h at sen ds
each com m an d to execu tion on a given dat abase. Th is allows t h e
con ten ts of a du m ped dat abase t o be loaded elsewh er e.
A dispat ch er an d a ser ver m u st be r u n n in g, as t h e o2odbc_dump_base
t ool is an O2 OD BC clien t . In addit ion , t h e OD BC ser ver u sed by t h e
o2odbc_dump_base t ool m u st be r u n n in g in m an u al m ode.
Environment variables
Non e.
Files
See also
108
An ou t pu t file is gen er at ed. If a file wit h t h e sam e n am e alr eady exist s, it
is over wr itt en .
o2odbc_server, o2open_dispatcher
O 2 ODBC User M an u al
The O2 ODBC Commands : o2odbc_server
o2odbc_server
Summary
St ar t s an O 2 ODBC ser ver .
Syntax
o2odbc_server
[-system
[-server
[-dispatcher
[-commit_mode
[-verbose]
system_name]
server_host]
dispatcher_host]
commit_mode]
Mandatory arguments
Non e.
Optional arguments
D efau lt ar gu m en t s (lik e -system or -server ar gu m en t s) ar e accept ed
accor din g t h e gen er al opt ion m ech an ism of O2 (see t h e Sy stem
Ad ministration Reference Manual ).
• -system system_name
Specifies t h e O 2 syst em n am e.
• -server server_host
Specifies t h e O2 ser ver h ost n am e. Th is m u st be t h e n am e of a m ach in e
on t h e n et wor k .
• -dispatcher dispatcher_host
Specifies t h e O 2 Open Access dispat ch er h ost . Th is m u st be t h e n am e of
a m ach in e on t h e n et wor k .
• -commit_mode commit_mode
Specifies th e com m it m ode on wh ich t h e ser ver will r u n . Possible valu es
ar e auto (for au t o-com m it m ode) an d manual (for m an u al m ode). If n ot
specified, th e au t o-com m it m ode is set by defau lt.
• -verbose
D isplays addit ion al in for m at ion abou t th e o2odbc_server activit y, i.e.
sets t h e ver bose m ode on .
O2 OD BC User Man u al
109
7
Description
O2 ODBC Reference
Th is com m an d st ar t s a n ew O 2 ODBC ser ver on a m ach in e. An O2 OD BC
ser ver pr ocesses O2 OD BC clien t r equ est s.
Wh en st ar t ed, o2odbc_server establish es a con n ect ion wit h an
O 2 Open D ispat ch er (o2open_dispatcher) wh ich m u st alr eady be
r u n n in g an d est ablish es also a con n ect ion wit h a n am ed O2 dat abase
syst em t h r ou gh an o2server, wh ich m u st also alr eady be r u n n in g.
Environment variables
• O2HOME
Specifies t h e in st allat ion dir ect or y of O 2 . Th is var iable is m an dat or y.
Files
Th e file /etc/services (Un ix) or
$WINDIR\system32\drivers\etc\services (Win dows NT) con t ain s
t h e dispat ch er h ost n am e an d por t n u m ber .
See also
o2open_dispatcher, o2server
110
O 2 ODBC User M an u al
The O2 ODBC Commands : o2open_dispatcher
o2open_dispatcher
Summary
St ar t s an O 2 Open Access dispatch er .
Syntax
o2open_dispatcher
[-verbose]
Mandatory arguments
Non e.
Optional arguments
• -verbose
D isplays addit ion al in for m at ion abou t th e o2open_dispatcher
act ivity, i.e. set s t h e ver bose m ode on .
Description
Th is com m an d st ar t s a n ew O 2 Open Access dispat ch er on a m ach in e.
An O 2 Open Dispat ch er r egist er s all O 2 ODBC ser ver s r u n n in g on a LAN
an d is qu er ied t o get t h e addr ess of a ser ver able t o an swer t o an
O 2 ODBC clien t r equ ests.
A ser ver is ch osen by t h e dispat ch er accor din g t o a h eu r ist ics an d based
on con n ect ion opt ion s set by th e clien t. A scor e is com pu t ed for each
ser ver r u n n in g an d t h e ser ver wit h th e best scor e is r etu r n ed t o t h e
clien t .
Th e followin g elem en t s en t er in t h e com pu t at ion of t h e scor e:
• a ser ver is r u n n in g on t h e sam e h ost as t h e clien t
• a ser ver is alr eady con n ect ed t o t h e dat abase t o wh ich t h e clien t
wan t s to con n ect
• th e cu r r en t load of each ser ver (t h e n u m ber of con n ect ed clien t s)
• th e valu e of th e SQL_AUTOCOMMIT con n ect ion opt ion (specified by
t h e clien t wit h th e SQLSetConnectOption or t h e defau lt valu e
SQL_AUTOCOMMIT_ON)
O2 OD BC User Man u al
111
7
O2 ODBC Reference
Environment variables
Non e.
Files
Th e file /etc/services (Un ix) or
$WINDIR\system32\drivers\etc\services (Win dows NT) con t ain s
t h e dispat ch er h ost n am e an d por t n u m ber .
See also
o2odbc_server, o2server, o2odbc_dump_base
112
O 2 ODBC User M an u al
The O2 ODBC Commands : o2sql_export
o2sql_export
Summary
View sch em a gen er at ion pr ogr am .
Syntax
o2sql_export
[-system system_name]
[-server server_host]
-schema schema_name
-view
view_name
[-config config_file]
[-output output_file]
[-verbose]
Mandatory arguments
• -schema schema_name
Th is is th e n am e of a sch em a for wh ich t h e view view_name is t o be
der ived.
• -view view_name
Th is is th e n am e of th e view to be der ived for sch em a schema_name.
M an y differ en t views can be der ived for t h e sam e sch em a.
Optional arguments
D efau lt ar gu m en t s (lik e -system or -server ar gu m en t s) ar e accept ed
accor din g t h e gen er al opt ion m ech an ism of O2 (see t h e Sy stem
Ad ministration Reference Manual ).
• -system system_name
Specifies t h e O 2 syst em n am e.
• -server server_host
O2 OD BC User Man u al
113
7
O2 ODBC Reference
Specifies t h e O2 ser ver h ost n am e. Th is m u st be t h e n am e of a m ach in e
on t h e n et wor k .
• -config config_file
Specifies a con figu r at ion t o be u sed wh en expor t in g t h e O2 sch em a as a
r elat ion al sch em a.
• -output output_file
If an ou tpu t file is specified t h r ou gh ar gu m en t -output t h en t h e
gen er at ed view sch em a defin ition is du m ped in t o t h is file.
• -verbose
D isplays addit ion al in for m at ion abou t t h e o2sql_export act ivit y, i.e.
sets t h e ver bose m ode on .
Description
Th e o2sql_export tool allows view sch em as t o be cr eat ed an d m odified.
Wh en st ar t ed, it est ablish es a con n ect ion wit h a n am ed O 2 dat abase
syst em t h r ou gh an o2server, wh ich m u st alr eady be r u n n in g.
Environment variables
• O2HOME
Specifies t h e in st allat ion dir ect or y of O 2 . Th is var iable is m an dat or y.
Files
An ou t pu t file is gen er ated if t h e opt ion -output is specified. If a file
wit h t h e sam e n am e alr eady exist s, it is over wr it t en .
An in pu t con figu r at ion file is u sed if t h e opt ion -config is specified. If
t h e file can n ot be open ed, an er r or is r epor t ed an d t h e pr ogr am abor ts.
See also
o2server, o2sql_query, o2odbc_server
114
O 2 ODBC User M an u al
The O2 ODBC Commands : o2sql_query
o2sql_query
Summary
SQL in t er active sh ell.
Syntax
o2sql_query
[-system system_name]
[-server server_host]
-base
base_name
-view
view_name
[-output output_file]
[-auto_commit]
[-verbose]
Mandatory arguments
Non e.
Optional arguments
D efau lt ar gu m en t s (lik e -system or -server ar gu m en t s) ar e accept ed
accor din g t h e gen er al opt ion m ech an ism of O2 (see t h e Sy stem
Ad ministration Reference Manual ).
• -system system_name
Specifies t h e O 2 syst em n am e.
• -server server_host
Specifies t h e O2 ser ver h ost n am e. Th is m u st be t h e n am e of a m ach in e
on t h e n et wor k .
• -base base_name
Th e n am e of a base on wh ich th e view view_name is t o be act ivat ed.
• -view view_name
Th e n am e of t h e view t o be act ivat ed on base base_name. Th e view
m u st h ave been pr eviou sly der ived wit h t h e o2sql_export t ool for t h e
sch em a fr om wh ich t h e base base_name is an in stan ce.
• -output output_file
O2 OD BC User Man u al
115
7
O2 ODBC Reference
If an ou tpu t file is specified t h r ou gh ar gu m en t -output t h en t h e
gen er at ed view sch em a defin ition is du m ped in t o t h is file.
• -auto_commit
Specifies t h at t h e sh ell m u st r u n in au t o-com m it m ode, i.e. a com m it
will be au t om at ically per for m ed aft er t h e execu t ion of each SQL
st at em en t. Th e defau lt m ode is t h e m an u al m ode, wh er eby com m it s
an d/ or r ollback s m u st be explicit ly execu t ed wit h t h e appr opr iat e sh ell
com m an ds (commit work an d rollback work).
• -verbose
D isplays addit ion al in for m at ion abou t t h e o2sql_query act ivity, i.e.
sets t h e ver bose m ode on .
Description
Th e o2sql_query tool allows view sch em as t o be act ivat ed on a given
dat abase. On ce a view is act ivat ed on an O2 base, SQL com m an ds can
be execu t ed on t h e r esu lt in g vir t u al dat abase. Th e view sch em a can also
be in spect ed t h r ou gh specific sh ell com m an ds (see Sect ion [Ref:
o2sql qu er y ] ) for m or e det ails).
Wh en st ar t ed, o2sql_query establish es a con n ect ion wit h a n am ed
O 2 dat abase syst em t h r ou gh an o2server, wh ich m u st alr eady be
r u n n in g.
Environment variables
• O2HOME
Specifies t h e in st allat ion dir ect or y of O 2 . Th is var iable is m an dat or y.
116
Files
An ou tpu t file is gen er at ed if t h e opt ion -output is specified. If a file
wit h t h e sam e n am e alr eady exist s, it is over wr it t en .
See also
o2server, o2sql_export
O 2 ODBC User M an u al
A
A
Synta x f or Vi ew
C us t o m i zat i o n
Th e syn t ax for view cu st om izat ion t h r ou gh a con figu r at ion file is given
below in EBNF for m at . Reser ved wor ds ar e “quoted” an d n on t er m in al
sym bols ar e given in italics . Th e sym bol | r epr esen t s a ch oice (a
disju n ct ion ), br ack ets ({ an d }) r epr esen t zer o or m an y occu r r en ces an d
squ ar e br ack et s ([ an d ]) r epr esen t zer o or on e occu r r en ce.
Th e n on -t er m in al query _expression cor r espon ds t o a valid qu oted OQL
expr ession , wh er eas schema_name, class_name, proc_name,
table_name, collection_name, column_name, method _name an d
attribute_name cor r espon d t o valid O2 iden t ifier s.
Th e n on -t er m in al proc_d escription cor r espon ds t o a qu ot ed st r in g an d is
in t en ded t o allow a sh or t t ext descr ibin g t h e sem an t ics of t h e pr ocedu r e
t o be at t ach ed t o th e pr ocedu r e declar at ion in t h e SQL cat alog.
O2 ODBC User M an u al
117
A
Syntax for View Customization
schema ::= “view schema” schema_name “from” schema_name “;”
[hide_command] [proc_command] [export_list]
hide_command ::= “hide” class_name_list “;”
proc_command ::= “stored procedure” proc_list“;”
export_list ::= export_command { “;”export_command }
export_command ::= export_class_command
| export_collection_command
export_class_command ::= “export class” class_name [“as” table_name]
[“define key” attribute_name]“;”
[“hide” attribute_name_list]“;”
[“redefine” virtual_attribute_list]“;”
[“method” method_name_list]“;”
[“extent” query_expression]“;”
[“with” data_update_clause_list]“;”
“end”
export_collection_command ::= “export collection” collection_name “in class” class_name
[“as” table_name]
[“redefine” virtual_attribute_list]
“end”
class_name_list ::= class_name { “,” class_name }
proc_list ::= proc { “,” proc }
proc ::= proc_lang proc_name [proc_description]
proc_lang ::= “C++:” | “O2C:” \mid
virtual_attribute_list ::= virtual_attribute { “,” virtual_attribute }
virtual_attribute ::= path “as” column_name
attribute_name_list ::= attribute_name { “,” attribute_name }
method_name_list ::= virtual_method { “,” virtual_method }
virtual_method ::= method_name “as” column_name
data_update_clause_list ::= data_update_clause { “,” data_update_clause }
data_update_clause ::= “insert”
|
“update”
|
“delete”
collection_name ::= class_name { “.” path }
path ::= attribute_name { “.” attribute_name }
118
O2 OD BC User Man u al
B
B
SQL GETINF O Return
Va l ues
We list below t h e C lan gu age #define’s for t h e fInfoType ar gu m en t
an d t h e cor r espon din g valu es r et u r n ed by t h e OD BC API fu n ct ion
SQLGetInfo.
O2 ODBC User M an u al
119
B
SQLGETINFO Return Values
fInfoType
SQL_ACTIVE_CONNECTIONS
SQL_ACTIVE_STATEMENTS
SQL_DATA_SOURCE_NAME
SQL_DRIVER_HDBC
SQL_DRIVER_HENV
SQL_DRIVER_HSTMT
SQL_DRIVER_NAME
SQL_DRIVER_VER
SQL_FETCH_DIRECTION
SQL_ODBC_API_CONFORMANE
SQL_ODBC_VER
SQL_ROW_UPDATES
SQL_ODBC_SAG_CLI_CONFORMANCE
SQL_SERVER_NAME
SQL_SEARCH_PATTERN_ESCAPE
SQL_ODBC_SQL_CONFORMANCE
SQL_DBMS_NAME
SQL_DBMS_VER
SQL_ACCESSIBLE_TABLES
SQL_ACCESSIBLE_PROCEDURES
SQL_PROCEDURES
SQL_CONCAT_NULL_BEHAVIOR
SQL_CURSOR_COMMIT_BEHAVIOR
SQL_CURSOR_ROLLBACK_BEHAVIOR
SQL_DATA_SOURCE_READ_ONLY
SQL_DEFAULT_TXN_ISOLATION
SQL_EXPRESSIONS_IN_ORDERBY
SQL_IDENTIFIER_CASE
SQL_IDENTIFIER_QUOTE_CHAR
SQL_MAX_COLUMN_NAME_LEN
SQL_MAX_CURSOR_NAME_LEN
SQL_MAX_OWNER_NAME_LEN
SQL_MAX_PROCEDURE_NAME_LEN
SQL_MAX_QUALIFIER_NAME_LEN
SQL_MAX_TABLE_NAME_LEN
SQL_MULT_RESULT_SETS
SQL_MULTIPLE_ACTIVE_TXN
SQL_OUTER_JOINS
SQL_OWNER_TERM
SQL_PROCEDURE_TERM
SQL_QUALIFIER_NAME_SEPARATOR
120
Returned Value
64
64
a long pointer to DSN
Handled by the driver manager
Handled by the driver manager
Handled by the driver manager
a long pointer to "O2 Technology Driver"
a long pointer to "02.01.0000"
SQL_FD_FETCH_NEXT
SQL_OAC_LEVEL1
a long pointer to "02.10"
a long pointer to "N"
SQL_OSCC_COMPLIANT
a long pointer to ""
a long pointer to ""
SQL_OSC_CORE
a long pointer to "O2 Technology"
a long pointer to "05.00.0000"
a long pointer to "Y"
a long pointer to "Y"
a long pointer to "Y"
0
SQL_CB_DELETE
SQL_CB_DELETE
a long pointer to "N"
SQL_TXN_SERIALIZABLE
a long pointer to "N"
SQL_IC_SENSITIVE
a long pointer to "\"{"}
0
18
0
0
0
0
a long pointer to "N"
a long pointer to "Y"
a long pointer to "N"
a long pointer to ""
a long pointer to "stored procedure"
a long pointer to ""
O2 OD BC User Man u al
fInfoType
SQL_QUALIFIER_TERM
SQL_SCROLL_CONCURRENCY
SQL_SCROLL_OPTIONS
SQL_TABLE_TERM
SQL_TXN_CAPABLE
SQL_USER_NAME
SQL_CONVERT_FUNCTIONS
SQL_NUMERIC_FUNCTIONS
SQL_STRING_FUNCTIONS
SQL_SYSTEM_FUNCTIONS
SQL_TIMEDATE_FUNCTIONS
SQL_CONVERT_BIGINT
SQL_CONVERT_BINARY
SQL_CONVERT_BIT
SQL_CONVERT_CHAR
SQL_CONVERT_DATE
SQL_CONVERT_DECIMAL
SQL_CONVERT_DOUBLE
SQL_CONVERT_FLOAT
SQL_CONVERT_INTEGER
SQL_CONVERT_LONGVARCHAR
SQL_CONVERT_NUMERIC
SQL_CONVERT_REAL
SQL_CONVERT_SMALLINT
SQL_CONVERT_TIME
SQL_CONVERT_TIMESTAMP
SQL_CONVERT_TINYINT
SQL_CONVERT_VARBINARY
SQL_CONVERT_VARCHAR
SQL_CONVERT_LONGVARBINARY
SQL_TXN_ISOLATION_OPTION
SQL_ODBC_SQL_OPT_IEF
Returned Value
a long pointer to "database"
SQL_SCCO_READ_ONLY
SQL_SO_FORWARD_ONLY
a long pointer to "O2 name"
SQL_TC_ALL
a long pointer to ""
0
SQL_FN_NUM_ABS | SQL_FN_NUM_MOD
SQL_FN_STR_CONCAT | SQL_FN_STR_LENGTH
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
SQL_TXN_SERIALIZABLE
a long pointer to "N"
O2 ODBC User Man u al
121
B
122
SQLGETINFO Return Values
O2 OD BC User Man u al