Table of Content
Introduction..............................................................................................................................................5
The CONNECT MariaDB handler........................................................................................................7
-.-&(&/01!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
2,&(&/!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
3410)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
&,**,5&(&/6/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
/)*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
&*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
7*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&41**)4*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&,/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
8/4*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
/9(:3/;<!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
/9(:/!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
/9(:3=;/!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
/9(:/9!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
/9(:</!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
/9(:8>2(!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
/9(:8(&?!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
8@/(84*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
>22,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
>1,4*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
84*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
Inward and Outward Tables................................................................................................................19
-/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
@,-)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
-/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
@,-)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
Relational Table Types..........................................................................................................................21
?1.1)1)1.1-11*1)-!4
*1-..!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
81!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
?*/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
;!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
?**,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
,)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&*11./)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
A**./)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
83B/)/4*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
3*.4,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! #
8/)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
/)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0(&/)/4*0,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&30?//)/4*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
?/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
NoSQL Table Types...............................................................................................................................39
1;>1,;(3/C1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
B?2/)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
&,B?2)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
>1,B*1-B?2)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
=,&1.)4814!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
D*1B?2)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$#
?*1B?28!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
?E,21.?*01!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
3**.=/?2/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
F3/)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
/F*3*.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
=,.>2201!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
=,&1.)4814!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
F3&,/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
,)-F3.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
F31!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
@/)@,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'$
<,3,F3;*1.&!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
&;>8*1F3/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
F3>18.1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'#
/5F64*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
>1,.1G1>8.1,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#"
&,/)1F3!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&,G1.1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
.&1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
3*.4,F3)(,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
;,F3.?,8!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
/)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
&4!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
;-4!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
External Table Types.............................................................................................................................89
(7/)3*.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
8&/)/4*@11,/)1.8?3!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
&(&/8&/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
@11,1*.-1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
&;>8*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
3,183!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"#
&,83!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!""
8&&,.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
H@3&&31-!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
F8&/)/4*@11,/)1.8?3!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
&*,.381)!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
3,I.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&(&/F8&/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
&,F8&!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
;@11F8&/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*1-F8&/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
F8&1*.11!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
=,>>88/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
(7,F8&11!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
?</)/4*@11,&1.?,8!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&(&/?</)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
?<3*.*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&;>8*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
31.?</)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
?93C2/)/4*@11,?43C2J?8/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
&13*.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
7,.?93C2/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&;>8*1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
3,1?43C23!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&(,21!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! $
&(&/?93C211(8(;@/(8!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! $
;B9/)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Virtual Table Types.............................................................................................................................127
B&2K/)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! #
>1,3*&1-B&2!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! "
B&2)1)11*.-1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
&&>;K/)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
0//)/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
$
8.,)!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
/2K/)/4*/)21!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!"
(7!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
>1,/2?93C24*1,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
;47,*7I1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
,1.11!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
3*506/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
0)4*50;6!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$$
8;/4*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
D-1?,1/)/4*5D?6!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$#
?@&@11/)/4*5?@&6!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$"
OEM Type: Implemented in an External LIB.................................................................................151
Catalog Tables......................................................................................................................................152
&,/)11L!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
Virtual and Special Columns..............................................................................................................158
Indexing................................................................................................................................................160
37,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
=,71!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
7.**,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
E7,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
;7,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
847,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
07,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
Partitioning and Sharding...................................................................................................................164
,!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'$
-/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
/),!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'
7,-/),!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'"
3,-/),!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!'"
&21!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
Using CONNECT.................................................................................................................................173
.!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
&/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
8*/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
@/)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
>*8..)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#$
*,.?8)1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#$
(7*,.?8!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
&1-!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
&31.&(&/=!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
34!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#'
Appendix A...........................................................................................................................................178
(7*1!G1!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!#
Appendix B...........................................................................................................................................181
Appendix C...........................................................................................................................................184
&*,F1>813*)4!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!$
Index......................................................................................................................................................187
Introduction
&(&/1G1-59@3(693,(,*1-41
-.1!),1-1?84.)1*1
-)11**1-.)111,
411 , 3C2 .1! 1 1 . I1 1 .
1..-,.1..!@-11M
5N1L,,-)11,1*-1,,
1 . 1711 1 ;8?3,1 .. . .1 1
11!1*111*11)E.)-1*1411.1
1*-)117*11-41--.171,
.1!/,1411*11.14*4*
(/271.*111)11L..
)111 , 1 .1 1 *1! (/2 1E1
G)11I).111*4.,1.
*1I,)1! /1),148@1-
1.11,,,711..!6
/11)-)4;)3
!D1)11E-1?(8?,.
(7 8 ), 1 8?3 )1 1 - 1 )1
8)1341!@317111)-4*1
?(83C2)4.,.,)1.-78D,8D**1)
*&!
=-11-1--1.-1*156!871,
--*))F3B?2.11-;(3/I1!
/1-1.&(&/)1F3B?2&30)1)1
.1;(3/I1!
/11*7-41,?811**33C2J?(8
1 ) 1 1 *11)4 1 ) 14 1 1**
**1.1-&(&/*,1,,*171**.
?(83C21**-.-4!
/.1.&(&/
! .3C2,,711!
! ()-**1.474*1.1113C2!
! 3C2I4.1.F3B?2=/?2.11,F1>81!
$! 3C2).;(3/I1I1*,.1!
! 3C2-4*?<11,?,811)1!
'! ;JD117.1.141.1!
#! 8117118&F8&?43C2?,8@!
! 411.71!
"! 1H-D=(;(11-***!
! 3**.1*1!
!7.H))1!
! 3**1*,)41)H.1)41)H)1),)1,!
! 3**.?;;.3(2(&/>8@/(8(2(/(!
$! 1)E47,!
! &7*7I111!
'! 1@-1-,8D&OO!
;)3 011837.8.?,
?43C2!=141.)17*8 ?43C23C23)1
,1!
'
/1E1?8.,.11**.?(83C2-
.*713C2147.,)156)11,&(&/
,!
<,?814117)11*-.1.1
1,.3C2,,.*,)111,**1!
#
The CONNECT MariaDB handler
/ *1 1)1 ?8 - &(&/ *, ,1 4
7*1.1,!
/1-)1?8117?(8!/11)4
.,)1)1..4*1*.1.1.7.
8?3*111(7?,88&F8&.
.7*8;D??@&)1
/1 1**1)*,?81 *1.,51*6
111;D82(83(;08!
Version Maturit
y
Distributed with Remark
!! , ?8! 3)*)4!'
!'! <@ ?8 ! !
!
!'! 3 &1?<)4*!
!'!$ <@ ?811 8*!
!'!
!
<@ ?811 ?<)4.?8!
!!$!
!'! <@ 3 3**.;(3/I1!
?4.1!'!11*.15)6-*.1-?<
)4*)111-)4*4,41!=-11
)1*1 . . 4711!@1 . .1 )1 &(&/
14)4*14*1..4*,-1-1
11-4*!/1-)-**)!
New Feature of the new CONNECT Version
/111*11)4)F3B?2&3011-;(3/I1!
31.1,3C21*!
/&(&/ 1 )) E4*,.1 1, 3/@22
3@?(!
/)1)ha_connect.dllha_connect.so, ha_connect.so.0, ha_connect.so.0.0.0)11
)* 1?8 *,4-1 4-1,
1)41)!
System Variables
&(&/.1-141)1
Name Type C-Type Defaul
t
Description
7 111 1 &11
4*: 111 9(3 /(B/10@;&=@;411E*
:1L 111 , $ 0@;&=@;1L-./(B/
7:* ,) ().**,.7.1
-E:1L 111 '$? 3L.-E
1:*. 111 @>/ >1,*4..>8@/(J8(2(/(
7:. 111 ;71.I1
:*1 111 (),cond_push.&(&/
G1:,*:1L
111 , ?7 ) . -1 . F3 ,,,
.1
G1: 111 1, PQ ;*1.F31
G:* ,) 1, >22 F0?)4
11:* ,) 1, >22 F11*
G:-** 111 1, 3 F-**.-**1JF).
connect_xtrace
/1))11*1.&(&/7!11)1
Name Value Description
YES
MORE
INDEX
MEM-
ORY
SUBAL-
LOC
!"#$%
QUERY & '( )
STMT '
HAN-
DLER
'**'+,'-.
BLOCK / '**'+,'-"0
MONGO / 1,!-
&1,)1)411.1
!""
#""$%&
'""$%()
*+,#-""$%&
./""$%()
'!01""
3:7.1*,1.1,11!
11?81)7-HH1*)1&(&/
*1111,11
!
connect_work_size
/:-E:1L)*1,,41)H1*-,
-4),)1.1)H.1!11$"$$7*1
*4141L!1)1*.-.-3(/
!.1*.1),4.11L.-E
1))1.1)1!
1271111H!,.!
"
connect_exact_info
/1)1-&(&/,17)
.I1! 1 )4. )11 . E 4 , .
),),)1.)11*4.11)!
)1-711.1-I4,*.-1
*)!
)11,1-)7*-**1!
&/)11.5&(&/6)11?43C2111*.4,
5,R&(&/6! / 1 ) 7 *1 1*.
&(&/!
Table options:
/111.)*1)1*.-,,&(&/)1!3
1 ?8 *1 ) 1 . 1*.&(&/ ,! 1
&(&/ *1 4)4*141 *114!
*11)1*./:23/1,1)-)4*-
4**4!
/111*)*1.11.
Table Option Type Description
ENGINE 3, ?1)1*.1&(&/!
TABLE_TYPE 3, /7)4*83B&30?/B?2F3
8 0(& 8& F8& ?< ?93C2 /2 ;B9 B&2
&&>; 0/ A 0; 8; D? ?@& (?!8.1
83?93C2;B9*,*1!
FILE_NAME 3, /.*.)4*1)1.1!&)
)14!.1*.
11-).11!
XFILE_NAME 3, /.*)1.)7.1!&))1
4!8.1.!
TABNAME 3, /,).8&F8& ?<?93C2;B9
,)1*.B?2)1!
TABLE_LIST 3, /1*1)H)1./2)!
DBNAME 3, / , )1 . 8& F8& ?< ?93C2
, ;B9 )1 )1! / )1 * 1
11E-1536!
DATA_CHARSET 3, /117.1!
SEP_CHAR 3, 3*.1.1*.&30B&2)!
@111*.4F*1*.F3)1!
QCHAR 3, 3*.11.I,1.1.&30
).1.8&JF8&)!
SRCDEF 3, /1..)8&F8&
?43C2@1)40/)!
COLIST 3, /1.&&>;)1S*G.?<)1!
FILTER 3, /.7)!&4?<)14!
MODULE 3, /* . 822 1 )*,
11.H1(?)4*!
SUBTYPE 3, /1)4*.(?)4*!
CATFUNC 3, /,.1)4,)!
Table Option Type Description
OPTION_LIST 3, >11*.4*144.!
CONNECTION 3, 3*.1 . 8& F8& ?< ?93C2
)1!
HTTP 3, /=//..;(3/I1!
URI 3, />;.;(3/I1!
MAPPED 3*.1-5.**,611).!
HUGE / 1*.4 ) . ) , <!
?93C2)*11)41!
COMPRESS ) ..1,HL**11!8.1!
ZIPPED /.).11JL**1L*.1!
SPLIT /.0(&)-11*.1!
READONLY /..1).1!
SEPINDEX D7111*.1!
BLOCK_SIZE ) /).-1)E..)1 )1!
8&)11;-31L*!F8&
)11.1L!0;)11)1L
).-1!
LRECL ) /.1L.)4.!
AVG_ROW_LENGTH ) & ) 1*. * &(&/ 1 1L .
)),!
MULTIPLE ) >11*.4*.)1!
HEADER ) @**1&300(&=/?2.1!1,*1
)4*!
QUOTED ) /.I,1&30).1!
ENDING ) ( . ,! 8. . >7J27 .
D-1!
*11*./:23/*1,14711
2344 3&' 3()'-3&0 3()02
/*11)-1.1,1T7TR
*11)-1TR7T.1,!
1
344 & $-%3 5-4) '6
34 7- %8
/1.1.*1TT4*T)T-1T/@2(T=/?2
T)RU*,R T),R4-!/4 111
1)4I1,1!
Column options:
Column Option Type Description
FLAG )
@,-1,*1)4*!
FIELD_LENGTH )
3.,.8@/(1!
MAX_DIST* )
?7).111!
Column Option Type Description
DISTRIB* ( 5165165161,
DATE_FORMAT 3, /.,-11.!
FIELD_FORMA
T
3, /..1)4*1!
SPECIAL 3, /.3(&@211!
V/1*11.)E7,!
Index options:
Index Option Type Description
DYNAM 371546!
MAPPED >17.**,!
Note 1: ))*1111,),,!
Note 2&,&(&/))1.11...1
1*. &;(@/( /@2( 1 5-6 )! . . 1 71 -)
** )4 1)1I 3(;/ 2@8 1 )4 5@3 1 16 .
&;(@/(K/@2(!>E&30,&(&/14*1)1)1
471,.1.1.1)4**1!=-..1
1*. . - ., tabname.tabtype - ) 4
5-6)!
Note 38**,&(&/)1-18;1!-)11
*14&(&/).)111*,.7
.1!>18(2(/(/;>&@/(1!/1147.1.-)1
18;E.?8,1!
&(&/44).1U1.1.1!//@2(:/9(
*1*.14*..)!/))4*11*
1.-,)
Type Description
DOS /)11.1!/..).)41
*1..1,1*.4*14.1
1))-!-11.7.-1*.7
..1-1),.),!
FIX /7.,E83)-.7,1!
BIN 4. -1*.*11 -1
.7..1-1.7,!
VEC 4.,L1-1,*14
1*1*.1I.!
DBF* ,@3(.!
CSV*$ 5& 3* 016 . - ) , 1
11*)41*..,
FMT -11H1.
1./1.11*..!
INI , . . L ., .1 1 )4 4
**1!
XML*$ ,B?2=/?2.!
Type Description
JSON*$ ,F3.!
ZIP /),,.).L*.!
ODBC* /) 7 . ** 11) 8& 78&!
7*.8?3.(71*1!
JDBC* /)11F8&!
MONGO* /))1?,811?,8F8
?,8&8@
!
MYSQL* /)111,?43C2@E(8(;@/(8,!
PROXY* @))1)71,1!
TBL* @11,. )1 1 )E?(;<( , 1 .
?43@?)1
VIR* 0),41*1!
XCOL* @))1)71,1-.1
,1*1!
OCCUR* @))1)71,111.
)G),1),*4!
PIVOT* >15*61*4.71,)-!
DIR 0)1.1E>75168356!
WMI* D-1?,1)1*4,.,.
D? *!/14*)1,). 1..
)-*,141D-14
MAC 0),.)-E1D-1
4
OEM* /).4.14)4&(&/)-1111
*)478D-&OO182232)4!
Catalog Tables
)4*1E-TV&(&/1)4L1
.!/1)1.5,6)1*41*.
1)-1*.4,.-)4
1)4&(&/-,)!
DE-TS.)1)4;(3/I4!
/14*1-111)7*!
?44*1E11-***.1!/1-4&(&/1**1
411.4*1!=-8&F8&?93C21)14
4*11**)4&(&/! 11 &(&/ E11
11**4*-1*11)!
/4*141**)4&(&/4
Type name Description Used for
TYPE_STRING A1, 7
TYPE_INT $)41, ,
TYPE_SHORT )41, 1
TYPE_TINY )4, 4
>1,?,8&81)4-*,?8.1!
Type name Description Used for
TYPE_BIGINT )41, ),,,
TYPE_DOUBLE )41.,* ).
TYPE_DECIM )
TYPE_DATE $)41, 1*4
TYPE_STRING
/14*1*1-1,4E-1&=@;0@;&=@;)481111,1)4
*,1!&1,17,)1,1.
.7),*,..!
/8@/@:&=@;3(/*1)11*.4111.!
E?8-H)41111L*11
).)41).1!
3,-&(&/1!'!$.,)1*.1!=-1E
11-)1)1.)11*1LH,.E
..)-)E!=-1)1-74*)
111).41**,.1.LH,!
TYPE_INT
//(<(;4*1,$H)41int.&,,,,.–
2,147,483,6482,147,483,647.1,4*04,294,967,295.1,4*!
TYPE_SHORT
/ 3=;/ 4*1, H)41 short integer .&,,
,,.–32,76832,767.1,4*065,535.1,4*!
TYPE_TINY
//94*11,,H)41char.&,,,,
.–128127 .1,4*0255.1,4*!1)4*1/9(:/91
1*111.14,11!
TYPE_BIGINT
/</4*11,,H)41long long.&,,,,
. -9,223,372,036,854,775,808 9,223,372,036,854,775,807 . 1, 4* . 0
18,446,744,073,709,551,615 .1,4*!
1)1,.1*1)4*!)1*1)47
.1)1-1-)1*1)4)4.10(&
)141)4*11*,*.!
/lengthprecision1*.1*1,.).-1
1.7.14!111*.,.)4*1!
TYPE_DOUBLE
/ 8>2( 4*1*1&,, double 4*.,H*)*1
-)41!2E.,1,)1*1)4*
1.7.1*.)4*1.)4.1!
/length1*.1*1,.).-11.7
.14!/scale-1precision1).,1-7.1!)4
)4*10(&11**4!/lengthscale1*.111
*.,).1.4*1.)!
$
TYPE_DECIM
/8(&?@24*1*1-?43C28&11>?(;>?(;&
8(&?@2-7).,1*11.4
),,11!/,&(&/1*1.
)!1
colname4&9':-/;
/1.1colname1)1,precision.$scale.'!3**1,1
**)4
44()960/7<=:;
/*1.-)1,5H '!#$6!/-411
.)*1)4*!/length.1*.1*1,.)
.-111)4&(&/. precision scale
1!/1,1precision*1.scale1.**1.11
1,.11,!.7.)1)1,G1.
..-length.).)111&30.1*1,
1,!
114*41)4&(&/.1.8&F8&
?93C2)4*1&(&/1*1*1)41.!
/11-41.&(&/)1)7!
DATE Data type
4 *11)4 &(&/1 1,$H)41 ,!/
1*1F4"# 1>/&!@J
1*1)4).11*11).,F4
"#J!8J1).,F4"#*1)4
,).11!
&(&/11.December 13, 20:45:52, 1901January 18, 19:14:07, 2038!
@,.)*1)&=@;/(<(;4*18@/(
4*11*11**1!8@/(&(&/141
..-,.4441!
Date Format in Text Tables
4J111,$H)41,!7)14*83B
&30?/811.11.1,,41
)111,*1,!11.4.-41
. .1.,11-1.,.1)
1117*-411!
111.1141)-4*114!
/1.111-.*3(2(&/11.*
3(;/>8@/(11!=--1E*1*14*
1.?43C2*1)1*.!D,)
.1111,8@/(:;?@/*..
1
4%9
&(9'=;-
$%>4 '!>& "??"####-
$4&4&>4 <>& @&&;
4 AA%3 AB
444%()9A4-0!'06''6'0-'7@1!@!!;
C>&4%
/1I41
Name Bday Btime
&
HH
/1. 3(;/ 11)1*.1,1?43C21471
11*414?43C2*1!.1**44-4
11*11.!=1-)
A4-''"'0"0!'0-!1@1!D
Note/(28:2(</=*711)1?43C21471-1*.4,.
,)-*11.*4*1!.1*..,1
..1117I.,.1
.!)7*)111
1*.!=-.)4*-)83B11)G1.
.,-.!
@ &(&/ . 1, 111 . 11 . 1 *1 * * .
..1.!/1-),L4**
.1,!8.1-)*)414
**11,!/4.)4.-,,*1.1
Element Description
99 /1-,1.41""'-)1W"'W!
9999 /.41""')1W"'W)1*415""'6!
?? /-H,)!
??? /H))!
???? /.!
88 /-H,4!
888 /H-E4))!
8888 /.-E4!
/-H, H $H.!
/-H,!
11 /-H,1!
/H@?J?))1@?11W@W!
/-H@?J?))1@?11W@?W!
Usage Notes:
H/11,4)47.1,1,1,I1
)I1.-)),1!E1)I!
H/.1,1 H..5656.-156
.1 $H.-1!
H/W??WW88WWWWWW11W1)1*.--1!,!W??W
W?WE,..*)*,,LH,1*
$
.-H
1!
$
=**11*.41*4,,1.
.,-).!*1*.-3I1U
*1*.>*1I1!
'
H.. 1188888884.-E11E***
,!*4.-E1,
1*4!
H/*1-41110(&)1!
Handling dates that are out of the range of supported CONNECT dates
. 4 - E ) , . 1 1 1 ), )
&(&/1 E 4 &=@;0@;&=@; 1 1 ?8
.!@.1**11,1-?81--E
1.4-1!.141)1-)1*41,?8
.!
NULL handling
&(&/11.11)*1!&4114
8&F8&?<?93C2B?2F3)4*1!F3?<
B?2 4*1 1 - E4 1 11, 1 -
1*,171-B?2F3?<!
.)1111.-1!11
))111E11!/1.1U1
)E.M
@1*118@/(1-8@/(:;?@/1*.!@4,
.),1>22!
&(&/114!D,&;(@/(/@2(1.
1/>22)EL1-)111!-14-)
11>221!111*1*)4*11
LH,1,.74*1L.4*1!*1
14-),L1>224.11)!
1
'94-9'!;;4
44'()9!-E;-9'-;-90-8;-9)-FFF;
C>&'84)
/1I4*1
a b
>22 L
>22 MMM
.1-1,1>22.)!=-.-
7I4
C>&'8 !
/1-)1>221I3C2-1!
-11-**1-1
'94)-9'!;);4
44'()9!-E;-9'-;-90-8;-9)-FFF;
/11*1-,14,
#
Level Code Message
D
,
$ &NN)
1*)4*1.-!2111
C>&'84)
C>&'8 !
/.1I41-11>22!/1I4*1
a b
L
MMM
1-1>221-1*)4!
Unsigned numeric types
/41**)4&(&/11!!..74*1/93=;/
/(<(;</
Data type conversion
&(&/.4*411!/111
--,-1111.*1!/11*.
)1. 4* 8&F8& ?93C2;B9 ?43C2 )1 1 )4
1 4*1 1** )4 &(&/! /4 - *11)
&(&/4*1!
?43C24*11
MySQL Types CONNECT
Type
Remark
,, /9(:/ $)41,
1, /9(:3=;/ )41,
4, /9(:/9 )4,
/9(:3/;< 3,!,1-1**!
). /9(:8>2( )41.,*
/9(:8(&? 2,*1*11
4*1 /9(:8@/( 8.)1,4
),,, /9(:</ )41,
1 /9(:3/;< 11)
@74*1 /9(:3/;<
/9(:(;;;
8*,connect_type_conv
341)!3)-!
4*1 /9(:(;;; 1**1*!
(>?,.1,.,1.!3(/
,1,11-1*!
1./(B/1,*11,connect_type_conv
connect_conv_size141)1!..connect_type_conv1
NO 1!/9(:(;;;11,51**611,!
YES 8. / 1 4 /9(:3/;< 1*,
10@;&=@;nn),.connect_conv_size!
FORCE @18&))1/9(:3/;<!
SKIP 1! D 1 * 814 ,
&(&/)1-1*11,!
Noteconnect_type_convconnect_conv_size 111)111!!'!
Note214)4./9(:4*1&(&/!
=-;&(*)1*..))1,73X*
1**18&21!
D8&3C24*11
SQL Types Connect Type Remark
3C2:&=@;3C2:0@;&=@; /9(:3/;<
3C2:2<0@;&=@; /9(:3/;< R)1connect_conv_size
3C2:>?(;&3C2:8(&?@2 /9(:8(&?
3C2:/(<(; /9(:/
3C2:3?@22/ /9(:3=;/
3C2:/9/3C2:/ /9(:/9
3C2:2@/3C2:;(@2
3C2:8>2(
/9(:8>2(
3C2:8@/(/?( /9(:8@/( R!
3C2:/(;0@2 /9(:3/;< RO1M1O
3C2:/?(3/@? /9(:8@/( R"O1M1O
3C2:</ /9(:</
3C2:<>8 /9(:3/;< 2R'!
3C2:@;9
3C2:0@;@;93C2:2<H
0@;@;9
/9(:3/;<
R)1connect_conv_size
'
4*1 /9(:(;;; 1**!
F8&4*11
JDBC Types Connect Type Remark
&=@;0@;&=@; /9(:3/;<
2<0@;&=@; /9(:3/;< R)1connect_conv_size
#
>?(;&8(&?@2
0@;@;9
/9(:8(&?
/(<(; /9(:/
3?@22/ /9(:3=;/
/9// /9(:/9
2@/;(@28>2( /9(:8>2(
8@/( /9(:8@/( R!
/?( /9(:8@/( RO1M1O
/?(3/@? /9(:8@/( R"O1M1O
</ /9(:</
.1,)41411*.1,1connect_conv_size!
'
4..connect_type_conv 1;&(!/11)41!
#
.1,)41411*.1,1connect_conv_size!
"
JDBC Types Connect Type Remark
>>8
1*.,3C2
/9(:3/;<
/9(:(;;;
R'
.connect_type_conv R
4*1 /9(:(;;; 1**!
Note/connect_type_conv3X*1**18&F8&)1!
Inward and Outward Tables
/-),1..)1&(&/)1!
/)15-6-.11*.&;(@/(/@2(11,
2(:@?(*!
14)&(&/*1?(8?,.(78!/11
WW&(&/)1%5-)16%)1),.1)
*)4**1*.8?3!
/.1W*1W1).7*)41*.111
3(;/>8@/(8(2(/(!1E.1&;(@/(8;@2/(;
1.1!
-)1)71,.17)1!D4**4
1*1**.7)1**1!
Altering Outward tables
D@2/(;/@2(111G1.1).,4-,,
!@2/(;)11.4.1.4*111?@(8=><(
;(@829)-7-.4,.1*1)11
*1112@<11).4)-,!
&,,)4*-@2/(;.E111!411*11)
*).4G171,-,.!
/1,&(&/),1,)**11.)-,
&(&/)1))1-1.711)1,))
,1)),**)-)1!=-)
-.H)1)1-1.1711!
&,**,711*)11.4)!=-1.
1.-@2/(;/@2(11.1!
.1,1*).*4)1!
Note>1,-)1I12(*,!
@1*4*..&(&/)15-6)1!/4.H)1)1-1.1
1*.&;(@/(/@2(12(:@?(*!
/.-))14-.tabn.typ-
tabn1)typ1)4*.-1!D4-
1,&;(@/(/@2(Y3(2(&/Y1*4.14
)**)4.11!
/4)E)1.1,,1E-&(&/)11-
)1**!.141)H4)1)!(41
4)1.1,**1-112(*,!
Altering Inward tables
,E-)1&(&/)1711*.-417.1
1,WH*W,%,1,*4)!/11-4-7,
11*.@2/(;/@2(1,,1)5H*6
1)711!
&,-)-)1,@2/(;/@2(11*.4,-
.-)4*111-41-,).
,-)!=-411,)
&(&/-)!
Relational Table Types
/..&(&/1,?8)4)1.411
.1 8?31 )1 1* 56 )1! ? . )1 *414
*1)4.1&(&/,L14....11))-)
.11)1)14(?)1!
?1. 1)1 )1 .1 -11*1 ) -!4
*1-..
?1.)1*11)4&(&/G1*83>B.1,4,
1)1E11*,-,)!/11*1.
&;(@/(/@2(117*1!8*,**1)1
4711.1111)4&(&/)*414)4&(&/
11.&;(@/(/@2(Y3(2(&/YJ3(;/11!
/.path/name1,)42(:@?(*!.1*J-)
)14,)!;?.!
>111*.4..)4*113/@2(!
Multiple File Tables
/-4*1.*.)1!/.11*)1-*1
11*.!&(&/*)11)1!
/ 1 1 )1 1*. 1 5*6!@ multiple . ) 1 1 *414
1.1.14*1.G1!/1.1*111I4
,*11.I41111.).1-,!
/11,*11.1,...11111,1,.1
..*111)E4*1,1)!*1
1.111I3>*U0(&*)11**
)4&(&/!/ .1 *1 1, . multiple * . &;(@/( /@2(
1.)!
?*)11*.)4*?>2/2(Rn-E.1
*).!/1)1K)1!
/)1..114!/2(:@?(*1
*11C).*J1.4!
/2(:@?( ,1..1*J1.)
.1!/1.)1,8;)!
$ 2E*R)1,,).1.41)H.1!
/FILEID1*1)1-1I4*,)4.,.
1,1,*,.1E*)!
Note?*-1*.B?2)1!/11111! !
Record Format
/11**1).1)4*,141*J*.1!1
fixed.)4*1B80(&1variable.830&/?/1F3
)1!
.7)11J*1)4)E.2&X:3A(-1!/111
).J1)1)E7,!
3,-&(&/1!'!'2&X:3A(*1)1*..)
)1!/.1)E7,.1)4&(&/,11L
)41.)E.2&X:3A(-1!/1)11)EJ1)E7,
))1!1)1&(&/7-)..1!
File Mapping
.H)1)1.1)1L*11,),4D-1Z
1..>B27)41,I.5.**,6-.1*111
.-44!?**,11*.-,)1, ?@(8R9(3
*!/11**4)1)4141J.1B?2!
Big File tables
1.1)41*J*.1.*,1411L
1 <71L)41.1!1)4*1&(&/
-.1, <*),1!/1
B0(&4*1!/1*J*.1,-),.11*.4
*) ') #.)!-&(&/4
11)1, <1!
Compressed file Tables
&(&/E*111)1-1.1*11!/41**
*11.1,L).!A*L).11**..4!/)4*1
)*1183B&30?/!/1111E1*1
.1-,*11,!
311**4*11)1
&*11)17)!
>**1**!
>1&?;(33*1*.4*11)
1 *11
2 &*11,L).!
3 ?.*11)E1.2&X:3A(1),)E7,
Zipped file Tables
&-E).1*111L*.1!
/1*.*11-,)1)1L*.1
Table Option Type Description
ZIPPED ;I!/)11!
ENTRY* 3, /**.L*41)1
-)!.1*.14.1-
)1*,mulentries*1,!
MULENTRIES* / . 1 1 * . )! . 1*.
.1 false . entry * 11*.!. entry
*11*..1 true 141
-1false.1!
APPEND* >1-,-L**)11)-
LOAD* 3, >1-,-L**)11)-
Note*1E-TV1)1*.*1!
Examples of use2N11**14&30..-4-))4
$
&3
optional column definition
4 %3 AB>4& @"?"&3%(
3 '
.&30.1A.&;(@/(/@2()1
&3E43
optional column definition
4 %3 AB>4& @"?"&3%E43
3 'E433 '344 % &3(
/file_name*1.L*.!/entry*1.41L*
.!.144.1L*.1*)!
.)1.1.111*!1* !1!1)
-)
&3&)
optional column definition
4 %3 AB>4& @"?"&3C(
3 '&)43 '
.1.1L**1IL*.)1
&3E&)
optional column definition
4 %3 AB>4& @"?"&3E43
3 'E433 '344 % &3C(
=entry*1*.11L*.1!.4.1E
entry*))*mulentries1)1*.1!
.)11L*.111*.1.*)1
E&3&)
optional column definition
4 %3 AB>4& @"?"&3CE43
3 '&)43 'E433 %
344 % &3%(
=,entry*1114.1)11L*.1)
.E!@1.multiple*1*).11L*
.11)H1!
/1*1))414*11.)4*1-,!
*)1*111**4.&30)1!
&,) ) )4, catfunc=columns! /1) 1 1-
.1.*)1!?*1)1.1.1-)1
..1)4!
/1.1*1111
! A**)14!>8@/(8(2(/(1**!=-3(;/11**
1*.-4-E,)1!
! /1.1*114!?4*)141-,.1!
! 4.4*1).4,).1!/1183B
&30?/F3B?2)4*11-14*1)1111B&2
&&>;0/!
*L)4 7,)E7, 1*11) .) 4*11**,! =-
**1*11)!/11-)1-41*11!
,111**!3-*)*,!
Creating new zipped tables
/)1)11471,L*.1!=-11*11)EL*
..71,.)!/--41)EL*.
Insert method:
3(;/ )1E)..)4*1)11171B?2
F3-;(//91!=-*.1*E,L*1
1** , 4 71, L* 4! /1 1 - 7, 3(;/
111-))-*71,1!&(&/
*171,)4-,111!/.4-41)1
>1,4 3(;/ 1E-)!/11*11)4.1
)11**41.-E,11!
?E,)..)!/1))47,54
4C>&6)41*.4,5C
>&6)1!
?E,) . .-1 .)1 1 5 4>46
1!
/-471,L*.1*.45**R9(36*1!D1,
111@2/(;1*.4I*1.1
E)&)9
A4>>491;)-
9'/;);
4 AA%3 AB
>4& A@"?"G"&)&E43 ' 0!E433 '
344 % )&'
44E)&)C>&)&'
E)&)344 % )&0-33 #
44E)&)C>&)&0
E)&)344 % )&1-33 #
44E)&)C>&)&1
E)&)344 % )&C-33 #
C>&E)&)
/1@2/(;11*41!
File zipping method
/1)1EL*...-,)!**1)
4*1,B?2F3!11*.&;(@/(/@2(1-2@8*!
7*
H,BID9
+,(9:;)-
+(9'7;)-
A5G(97;)-
GA(9'0;)-
(90';);
4 AA%3 H>4& @"H&"3E43E433 '
344 % (4- @"H&"(0&
'
D7,11serv2.xml.-)L**1perso.zip!/41)
1*.1-11*1).L*4.1
714!
11*11)H1).1.1
A,$E8449
497;+-
4%9'/;+-
)9'<-/;+JG?G,J @!-
)9'<-/;+JG?G,J @'-
3349/;+-
90;+
;K AA$#D LG @"L"844E43
IDD? ',A '!!!
D @"L"4%CM-&)4 #-3% !
=.1 1*.-- 1 ?>2(/;(3 *1 1)1*.!
=-(/;9*1)1*.41-)..1!
ZIP Table Type
@A)4*11)!11.1)1*4.
)L*.!1
E434>09
>(907/;)-
&34E44)> '-
)4E44)> 0-
&4)> 1-
4&)> :;
4 %3 ID>4& @"?"L"44E43
/1-1*4*111L*111L*11*,
.11L*.!&1U11flag1-
.!
/).4*83B)17.1!D.1*1
.7..1 .),,.!(7*11.1..1
1..7,!.1.14,,4*.)1 83!1
,.dept.dat.E
!1'<NK=!!'0$O")
!!0',N<====A5*A3P)
!1'.5,,:!7/=G&444
0:70D+K5ND1':'/?BD,Q(3&
9.))1-
3&9
)&9:;)-
49'7;)> 7-
497;)> 0!-
>)49'0;)> 0/-
&900;)> 1<;
4 AA%3 ?>4& 3
=.,**11..1.111!...1.
11*..1.*1.1..1!
#
/lrecl**1171L.1)4.1.
,1)1*.7*-1,.711.
,1!
Note@1*11.1,,11>/H.11*.4,1R>/
-114)*1-1)41!>E4*1L?8
*11).1 11L)41.,
*11 ..1 . . )41! . .,J
1*.4-))4)..1*)41
71L)41..1*,1!>/H11
1..144.-1I,)41)*1!D,
-.41.1)44),7).1..
..1.7.!.1.471,.1..11)1*.
,-711-!
@, . *1 1 -41 7 ) . 4 .4 1
1*,4*1,.,**11!
311number.)department)4.4*
1!/1-.4-14HH1 1
... )HH1-1-11*4!
.1.1.7,)1).1,4*B!1
).boys.txt
L$07"!'"'.</!0"!/"0!'!
5%$!="!/"'.<=!'"!:"0!!<
KL'!"!<"'.<'!0"!/"0!'!
&A400"''"'.=.'!"'!"0!!=
L&?'1"!7"'..0':"'0"0!!.
$4$''"!."'.</'!"!0"0!!<
9.11
%9
&9'0;)-
4%9'0;)-
4)>& ??""####-
4)>& ??""####> 1/;
4 AA%3 GH>4& % :<
=1flag*1-1*.)1.11*)-
7*.1!/..11)4.,.,offset.
*,.!=-..1..1.*1.
))4.!.7.1lrecl*1*41,.
,,1!1)4,.1. )41
D-1&;2)4>B!..1*.*,141
(8<*-)1*.-**!
1)1..1,1)1*.4-4)11
.,,..1E-)4&(&/!8.,
,,,..1!
/1)11*41
name city birth hired
F 1 "'HH H'H
=4 1 "#H'H# H$H
<,
3F1 "HH H'H
3 &, "#"HH #HH
F1 81 "" HH "H H$
1 "'H"H H H
D*11).7.1)*.4,)11.1
-.7)1-))1!1.),-)4
B )1*11)4)E1. 2&X:3A(11,.11*J*
*17!/)E1L.1.1*.&/)1!
)1.4,.)EJ-)1))41*.4,2&X:3A(*!
&(&/11.,1L.)E!
Note 114).171,1.!=-.
1.1,flag*..-,.Jlrecl *-)
1*.!
Note 23.1(E&/;2OA7@*)),L1
.7)1.,1*..71L!/11*
1*(!1..,B)xtab.foo.dat4
E-.71L4,-44111,11
.!1.7,R #R$
@.E,2;(&2.1*.1*.14,
7()4
344 > '
.141*.41*4&1!@1**11
)4*1*)1!
Note 3/-..11,1*.!1.
1
)&491;)-
/.-.1)41!/111..1.7..1
1*.!.1,11*..1?43C2.4*,!
Specifying the Field Format
3 .1 1*.. . .1! 1 *1)1
J.1).-,L1!/-1.11-,1
-,.)1*.&;(@/(/@2(.!/147..
.1*.1
G4>& I?cd
/**1..
Z /.1,L1
N *711.
D 3*.11*1c
d /).1.1*1
211--E1.-,7*!D.))1.xfmt.txt,
,.1. 1
"
>&9
')9'0-1;)-
0)9'0-1;)>4>& :-
1)9'0-0;)>4>& 1-
:)9'0-1;)>4>& I?--
7)9'0-1;)>4>& I1-
/)9'0-7;)>4>& I7-
=49'0;)>4>& 1-
<&49'0;)>4>& 1;
4 AA%3 GH>4& >&
44>&()9:7/=!7/-:7/=!7/-:7/=!7/-:7/=!7/-601:7/<-
1':'7.-:7/=-:7/=;
C>&>&
/.1-11*41
COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8
$'#!
'
$'#!' $'#!' $'#!' H
$'!
!$" $'# $'#
/).11*4..11*11,
.4**!.1,11,.11*.4
1!
?1,1.4!/11.))11.)-1
11
>&9
'9'0;)-
09'0;)-
19'0;)-
:9'0;)-
79'0;)-
/9'0;)-
=9'0;)-
<9'0;);
4 AA%3 GH>4& >&
C>&>&
/1*11*4.11-1.74..!-
-1111!
Column Row 1 Comment (all numeric fields are written right justified)
COL1 :7/=!7/
.-111!
COL2 :7/=!7/!
/.T$.1-$1!
COL3 :7/=!/!
*!/1,11!=-
1-1)1.*1!
COL4 !!!!:7/=-!7/
A 2, L1 1 *1 /
1*1E1(*1
COL5 6!!01:7/<!!
A?11,,L11!
COL6 !!!!!!1':'7.
A2,L1*1!
COL7 :7/=!!!
*!/1,11!
COL8 :7/=!!!
3!@41-),!
Note 1 4 1, ) *1 .,H* )1 ?8 1
*1.4*1!/*11)
1).1..11.11**..
)7*!
@). 4* 8 1*414@3(0..1)44*1E
@3(B)17!!/1.1EB4*.-*.7,,
11..1),.11.)!
1 8 .1 1? ). *
1*1*11)))171, 8 .-,,
1*.1
)4 AA%3 ?$G>4& )>
/1-&(&/1418(3&;(3=D&;(@/(/@2(1
4.41*1-@2/(;/@2(!
/1.111*.-4.8)1!81
..)51.6,4E1!*).
1.1E.1.1!/..471
-1**8)Ktabdbf
)9C;>&>
)9C;>&>8'
/4,..1.1.1,,).*411.
).1,,).1.!
/1 >8@/( 3(;/ 8(2(/( )1-8 )1!/ 8(2(/(
E1111**11)E*1.!/3(;/.11
**-4)11.).1,-1!
Note8)1,11., ')41!
Conversion of dBASE Data Types
&(&/144*1111!
Symbo
l
DBF Type CONNECT Type Description
B 41, /9(:3/;< ,1*1,!8/)E)!
C & /9(:3/;< @ (? *, 1 H * -
)E1-..!
D 8 /9(:8@/( )41 H 1 1 1, .
9999??88!
N /9(:/
/9(:</
/9(:8>2(
) 1 1 1, , G1.
*-)E1-..!
L 2, /9(:3/;< )4HL7 -1/!
M ?1, /9(:3/;< ,1*1,!8/)E)!
@ /1* 1** )41H-,1.1.1.!
1).411JJ$#&!
I 2, 1** $ )41! 2.1 ) 1 1,
,!
+ @ 1** 312,
F /9(:8>2( ) 1 1 1, , G1.
*-)E1-..!
O 8) 1** )41H1111)!
G 2( /9(:3/;< ,1*1,!8/)E)!
N4*&(&/1/9(:8>2(.11
/9(:</.,1,1/9(:/!
MBG4*1&(&/G118/)!
Reading Soft Deleted Lines of a DBF table
1*11)11)4,,.)!/111*.)4*
;(@8?8(E1
03!/11.*!
1;1,1.1!
2;41.1!
7*1.tabdbf)4
>344 ,& '
/)E1*.4;(@8?8(R!
@).4*1*414)4.--1,..7,
!
D.1..7..1,1-B)1!3*.)1
1141,*.*1111
17*111!
1I1..)1*)4111&;J2)11
*11)!1lrecl*1)1*.,4!
Note >E . 83 B 4*1 - . .1 1 , .
*1.!1.1
)&497;)-
/.-.1$11L.)4,!/111
..1.7..11*.!/..7.1*1.
111,.,*1)17.!
Type Conversion in BIN Tables
=1*1)-4*..*)4.
Column type File default format
&n /7.n1!
8 ,$)41
n ,$)41
3n 3, )41
/4 &4
31)*41.2&;2)-.!
Column type File default format
,n 2,,)41
8)nd 8).,*)41
=-4*114..-).!/11
...11*4*1)4&(&/
"
!
).144.)1)4)1.4)4,),H
H*1
!@11 83 B )144-1
.111!
/11-41*11)1*.4..-11*4*
.1, field_format* &;(@/(/@2(1!=)
..1.)1
Field_format Internal representation
[n]{L|B|H}
[n]
n)41)4)),1*1!
C &11,n)41
I ,$)41
D 8).)41
S 3, )41
T /4,)4
G ,,)41
F R ;.,*)$)41
X >1....4*
@..17* .1 H 1*.
!TB1I
1*.4,..!T&1*.n1-11*.-
4*!H.1).)41..11*1
- 11*.1!=- 4.1!/ G I S T .1
* )1 4 1* 1** 4*1 4 ) 1** .
11!
=17*.)!/.411**1)
AAAAAAAAAAGGGG
=*111&41,)4131,)41
.))41!/.1.!
/)))4
9
>449:;)>4>& A-
&9'!;)-
4)>4>& -
497;)>4>& 0-
%)9.-0;)>)!!!>4>& G-
349:;)>4>& 0;
4 AA%3 $O4E 7>4&
3*.4,H*1.)4111.11)E1
)1*)1,),1-1).!
"
?1.1)1)1&(&/1**14*17*2@/!
/.*11).)1*.)41,(8@
*1T2T*1!
)1*.-)4.111,.!
/...11.,),,.
4.)4..11141*.4.*E)4.
-,!.,111*.4 ending*1*.4 lrecl*
,,-!/)).)4
44()
977!!-,A5$?-'.<!6!'607-1=<.-:1<!7!-1'<;-
9'01-B,-'.716!<6'!-01:7/-1:!!/<-0'7<;-
91'01-G-0!!06!=601-<<<->)-1'<;
4*1.1114*..4*!
/I4
C>&
;1
fig name birth id salary dept
@;&=@28 "HH
#" $!
20(; "HH
$' $!'
H#H
!
Numeric fields alignment
)4.1.1,),$H)4)1*L
*.1*111!/1)./:23/-5,6*
5*E6,,R1.!
!"!#$%
Warning @ 1, 1 ) 4* * **1! /1 . . 1 1*.
&(&/4)1**.11!
/)1.4*0(&)4.1111*,*.H1
I4 -E1! &(&/ ,L1 1E 1 1 . 1 . 1
)1**11,1-1.)1!/1,L1-I4
1114.-1.*)411).1E!
&14-H)1)4*4.1E-1,J
)-!
&(&/*1-,0(&.1-11G!
Integral Vector Formats
1.10(&.1...1.-)4
.1!@1)*41.)
1*.!.11*?@B:;D3Rm-m11.71L
).-1.)1)1*.))11-1!/11
*41*.--)1!1153*6
*)1*.
)-)1.1I4
.)..-)4E.!1,-1.4,
1)!
/32/*1)4.-?@B:;D11*.!
$
Differences between vector formats
/1.11*..1!/,.*1)1*.
,!-)1-1*.1I11)*L!
1.I.1,--))-)4*1
!=-1L.))..1L)1.
11*.!1)E*,7).-1
).-1)!/11*.4*=Rn-,
)!.R-)*),,...R -)1*.
-4*T!)E.R-)*..!/111*
)1)1,1111--1),,..!.
1*.*-. .1)4*!
53*6.-1*.1.111111.
1-)1.I411,111!1
(9
4)-
9'!;);
4 AA%3 BA>4& ((
/1)1*)4.-1.1('((0(!
)1*2&X:3A(=n11.)E,-,U-
..)E1.I1L.?@B:;D3=m *141
)*.n!
2E . )1 1 1 1,*. 4 1*
)-4*1.),1.1,).!
=-..1).0(&)1!
Header Option
/1**1 0(& )11*!1.1L*1 ?@B:;D3
&(&/E--4171.!8*,.=(@8(;
*1.11)*),,...
.1*.fn!)E!/1.=(@8(;*
8.1 .1)1.-)1!
/1),,..!
/11*.!
$ /1..!
/ )1-,-.1)4**-!/
E1111,..1-1),,..!
Note 0(&), ..1*.&(&/),J11
*!/1.1*))-11,..)41,!
!&
?41.1.-),.11!/1*1.
E-1&30&3*0)11.11*)41*!4
.1*1))1*.)4 3(:&=@; *14.
11H!
.&30..111.11*.4,=(@8(;R*-1E*
.1,!-,..1*4114
-!
1,.-,people.csv.
&44
R4R'="!7"!'1
R)R'0"!<"!10
91*,))4
339
&9'0;)-
4)>& ??""##-
4&490;);
4 AA%3 AB>4& 33(
'3 P) '
&30)1flag*1E..1,..
.1!/11),1*4...J
.)1*.4,411.&30.!1
339
&9'0;)-
4&490;)> 1-
4)> 0>& ??""##;
4 AA%3 AB>4& 33(
'3 P) '
11
C>&33
-1*4)1
name children birth
@)
HH#
) HH
?4**1*&30 .1,1.1I*)1.7
11*!1.11*.4*+? n*.
I,J*A5, c1*.4-11I,-1W)4.!
C,-1,I1 1 ) 1*.1 *A5, !-,.1-) I
*,.I,-1%)4.,I,
/.1)-I1I11!-,.1-)I
4.41*),-I,!.4
I,-))!
47.1-)-)-I17*.1!/111
1.!
@.1-)-)-I17*.1!
$ @.1-)-)-I1,.1!
1-1-4111.4)41**1,1*11!
Note 1.4C&=@;*11*.C>/(8*-.!
Note 2&30)1-11*1)1*.43 S!
'
Note 3D,)71,&30.4&(&/4L.
E1*!=-11)411...1
8@/(.1-),L11)-),11,.1!
Note 4/ (28:;?@/*)1-&30)111,
.83B)1!=-.1*11.1*
11..1*.1-)),1-11
.1IC,QR !
Note 5I1.,1I1!
Note 6 D 4 1 1 . &(&/ ,!
/.2;(&2*1)1*.!/11**1-1,
=(@8(;*1!
Bad record error processing
&30.1.H.1!D1**1*11)1-11,
11
$>&41>4:>>)%
D4E-4.11.1).
*11,1576*.&;(@/(/@2(1.1
344 & '!!
/1-11,)1..1-,1!91?7
),).-,14.1,,1!
@45*6**E*1.1-).
1,.1..!.5*611*.-576.
1-)*!
FMT type
?/)1.1.1.171.*.&30.1!&(&/
1**11.1*,11..1*11
,L)*.1 1 ,L)1!/1.1 )41*.
**&(&/4!
?/ )1 1) 1 &30 )11*.4,4*1 ?/!
1*1)1.1*.!
Column Format Specification of FMT tables
/*..11*.1(28:;?@/*!@1*7*1
DA9'7;)>4>& TTT-
)7*..11.1TTT!)E
),,.1.is1,.!,)E1)1*..1!
/147,.*.1.&scanf.!
=-&(&/11*.1*.-4!1.1,41
*)..U111)1,.*1
1*,!;,11)4.11.1
&30.1!
/..1..*1
#
! @1*.-1,).!
! @E.),,.-1[!
! /.1*..1.!
$! @E..-1[[.*.1!
! @1*.-1.1[-11!
7*E,.funny.txt
'01:7-$,,?-U0!!7!!.'1
7/-D,6?-U:07/V'<!!.
1:7-,+A+A5-U/='.!!!07
9E)fmtsample-$18@?(8(3@2@;91,&
/)1.1
GD9
?97;)>4>& TTT-
A9'/;)>4>& -TTWT-
?D9:;)>4>& -UTTT-
,#?)9'0-0;)>4>& TT>T;
4 AA%3 G>4& >)%
Field 11,[-,)E1!
Field 211*..)4*)E1*)E11)-
1,I1!/,I1*...-)4[
E!/11*.1TW,E*41I1
!/,E[1.-)4*..1,I
.-1!
Field 311*)41)*)4*1,!
Field 41*)4141)4)E11 )-*
*[.!
/1)-)1*41
ID NAME DEPNO SALARY
$
(;/;@8 "!
' ;/H8(2?//( $ ' "!
$ /;>&?>&=( '# "!
Optional Fields
/),L.41)1H,!1.
11,.)!=-471,.1
.-1.!
211**1.1*,7*.)
'01:7-$,,?-U0!!7!!.'1
7/-D,6?-UV'<!!.
1:7--U/='.!!!07
/1-1*411,115.7.4.?/3@?2(6!/1
* 1 1 1*, .1 1 ) 1*. 1 5*6! )
7*.1 1 1 1*4!/ 1*.4 1
*.1))4T&1.1T!@1111
)
GD9
?97;)>4>& TTT-
A9'/;)>4>& -TTWT&-
?D9:;>4>& -UTTT&-
,#?)9'0-0;>4>& TT>T;
4 AA%3 G>4& >)%
)111))4T&-11E**,
,I.. -1..1.),,.
.!
/)11
ID NAME DEPN
O
SALARY
$ (;/;@8 "!
' ;/H8(2?//( >22 "!
$ >22 '# "!
?11,.1*)41.1))E1.1,1
..1.1!
Note 1:
1.11*.)-I1I1),,.11))
1*&;(@/(K/@2(1147!
Note 2:
&11*,1)1-*.*,.
*.1,.).)E1-1)-41
*.1,.)1,)E1)111!/1
114.*.1!
Note 3:
1. 141.1)H1,1*,.
1*.11144*!11**1,)1-
,1(/D-11),11)
$497;)>4>& TTT-
$X497;>4>& TTT-
/.11*.1I,.[11)1.)
,))*)4WHW4!3*.4,.1*..
11.[1)1,L-11!21
.-),)4.-),.
.1*..1H!
Bad record error processing
D....*11)1-11,11
$>&41>4:>>)%
/11-.*1...1.
1)-,41*.!D4E-4.11.
"
1).*11,1576*.&;(@/(/@2(1
.1
344 & '!!
/1-11,)1..1-,1!91?7
),).-,14.1,,1!
@45*6**E*1.1-).
1,.1..!.5*611*.-576.
1-)*!
Fields containing a formatted Date
@1*1 1 .1 ,.! 11 -.11)
1*.
! /.,.1*!
! /.1*!
! /.,*.*11..14*1L!
7*11**1--),1.,11
'/7.'0'71'66'="L)"0!!'@!!@!'@'16!:!!6RK")8"&&5D"''R1!0
/)11)E1
X$D9
D9'7;)>4>& TTT-
?4&)>4>& 66TTT6!:!!
>& ??""####@@&&@>4 0!-
G9'0<;)>4>& 6RKTTT-
5D)9:-0;)>4>& 5D"TT>TR-
$497;)>4>& TTT;
4 AA%3 G :!!
>4& @SSSSOSSX&3
Note 1= >4 0! -1114)1.1L.11
4"!/ :!!-111*.)1.1.
E,1L)4.1!
Note 2/.)1*.1N@""O"X&3N!
Note 3?/)144!
$
NoSQL Table Types
/4)1.1.).*1!
&(&/F3H&<B?21=/?2.1!
/-411...-?43C21,3C21!,
)11.1*..F3B?2))41*..1
&(&/411F3B?2.1)*)4**1
11).1)1--.1)!
/111...-?81-41-)4-411.
-?43C21,3C2-F34*!
Files Retrieved Using REST Queries
3,-&(&/1!'!F3B?2*11)4&30.1)
11-.;(3/I1-,I4,1)1!=-)11.1
1,?1.&1)**11E*E,1)41)1.
?8!/,1.1114*?8.1!
11*E,17**1JJ,)!J1.J**11E!/,1
E*1?817*?8!/11
)?81-&(&/,1.)!
NoteD-1*8),1,1)*)-cpprest
)4&(&/-1, ;(3/!.1-1 1 1!11
*&(&/-013.1,&<;2)4
**!*-4cpprestsdk-111.-)J?8!
Note.4-11.-)41)1.?8*1
1,1&(&/111*11)1(?1
7*@**7!
Creating Tables using REST
/11*.4=//.-)4>;.I1&;(@/(
/@2(1!1.I4,F3
A,$8)9
44490;+-
&90:;+-
)&9'/;+-
&4907;+-
(907/;?G++-
390';+-
849'1;+-
&3%(907/;?G++
;K AA?G+A5, )><
$#D LG )M
5D 3@""M3%34&+, ")
@1.1F3)1141*11),4&(&/E
..1)44L,F3.!=4G1
A,$8)
K AA?G+A5, )><
$#D LG )M
5D 3@""M3%34&+, ")
$
1)14!-).-)4
I4))43(2(&/1!/11,.1411-4I4
-.1,1!14-1.41-,.
G1)1.-1*.4,=//*!
&
&(&/ 1**1)1*1)4B?2 .1!1)11 *J*
.1.*,1411)*1,*11,..1,
1*L )4! &4 - 1 1411 1** )7 * . <?(
.-E ) - 1 I <?( D-1 ?3H8? 8?8&
?1.11**.B?21!
8?8&1..D-11.&(&/)7 1-411
1411! D-1 )1*. 1, B?23> )1 * .
11*.4,344 &)3 4&0!
Creating XML tables
11)1B?2 14,,,1,4
1!*,4B?2.1)11.!
11.
YF&(4 R'!R4 R6<<7.6'RFZ
Y$$+$LA RHRZ
Y$N$ R.=<00'0!.!<'.RK R>R+$LA R3344RZ
Y+5,Z
YG,ZL6A43Y"G,Z
YZ$Y"Z
Y"+5,Z
Y+5,Z
YG,ZG[4Y"G,Z
YZNY"Z
Y"+5,Z
YZA)4)3344HY"Z
YD+$5,Z
YZ%Y"Z
YDAZD4Y"DAZ
Y"D+$5,Z
Y?D+$Z'...Y"?D+$Z
Y"$NZ
Y$N$ R.=<0<:!<07/<7RK R>R+$LA R3344RZ
Y+5,Z
YG,ZX44&LY"G,Z
YZD4Y"Z
Y"+5,Z
Y,,D,GH R3\43RZ
YG,ZL&Y"G,Z
YZK)4Y"Z
Y",,Z
YZH4Y"Z
YD+$5,Z
YZ4>DY"Z
YDAZD4Y"DAZ
Y"D+$5,Z
Y?D+$Z'...Y"?D+$Z
Y"$NZ
Y"$$Z
*11,1
$
Y$$Z
Y$N@$-K-+$LAZ
Y+5,ZYZYD+$5,ZY?D+$Z
YG,ZYZYZYDAZ
Y+5,ZY$N@$-K-+$LAZ
YG,ZYZY+5,ZY,,ZYZYD+$5,ZY?D+$Z
YG,ZYZYG,ZYZYZYDAZ
/1 1 11 .1-..),)!=-)1 ,
1411,?8*1,1-E)1
14))--11!
11&(&/!.1,11-4-7.B?2
1),14*11)41*.4-4)
!
21E.17*!3**14-E).)1*4,
1!
14.)xsamptag1
&39
+5,97!;-
910;-
,,9:!;-
D+$5,9:!;-
?D+$49:;;
4 AA%3 H>4& H&3&
-)1*41
AUTHOR TITLE TRANSLATOR PUBLISHER DATEPUB
FH&1*
&1 **
B?2
PQ (411 """
DF! B?2@ F1<
?1. 11
1
"""
2141-**!4.111*,1!
11.11*&(&/.*,.)1
P2Q..-,11PXQ.),!*7.
11)1*.1--1!--4)1)H
,111P;3/@?(QP2@3/@?(Q)1&(&/417
,11)H,1
$
!
&(&/1))-4B?2 !111
1.4)*11.411E4114)1.
)!
$
D)7 1),17)1*)41)E1*,1
..!
$
4.1..1)E**1!/11)14.1.,
1)111**)1!D-1--)
!
=--11*.)4)1M41,Coltype)*1*.4
.4*!/T+11)1!/.-
)4,)11
&39
$9'7;-
K90;-
+$LA910;;
4 AA%3 H>4& H&3&
344 A%3 ]
/1)1.-,
ISBN LANG SUBJECT
"# "
"
. **1
"# $ '
. **1
-.)-,1*1.-11*.44*
.!1714*.1 field_format
*-11-1)1
&39
$9'7;>4>& ]-
K90;>4>& ]-
+$LA910;>4>& ]-
+5,97!;-
910;-
,,9:!;-
D+$5,9:!;-
?D+$49:;;
4 AA%3 H>4& H&3&
& $$344 8 $N
-I4
)M--4-)>&&3
/1-.-,1
SUBJECT LAN
G
TITLE AUTHOR
**1 . &1**B?2 FH&1*
**1 . B?2@ DF!
-)E4!1E3C2B?211111
14)11-,**1!1
.1))...-1**
B?2.!
$$
Using Xpath’s with XML tables
B*11)4B?21!/)1B*11*.)4
/@@?(*!.G11,&(&/11B*11TJJ2
7*)12-1-B?2.!
/-1)4..)!=-.11
1-1-)1*.1,;D8(
1)H*./:23/*!
/.:. *1-1))1*.*14--
.1,B*HE147!1
&39
49'7;>4>& ]$-
)90;>4>& ]K-
)M910;>4>& ]+$LA-
)>90!;>4>& +5,"G,-
)90!;>4>& +5,"-
4910;>4>& -
910;>4>& ,,"]D,GH-
>90!;>4>& ,,"G,-
90!;>4>& ,,"-
3)490!;>4>& D+$5,"-
490!;>4>& D+$5,"DA-
%49:;>4>& ?D+$;
4 AA%3 H>4& H&3&
& $$344 8 $N
/14.7)*111**11
/1*.4,)....!
/1*.44*,))4*.7.T+.)1!
/1*.4*.1)H,11,TJ!
/1*1-417*)1*.1
)1* . .,TJ)1!/*)
)1*.1,TJJ1-!
/I4
4-4-->--4>&
&38is not null
*1
ISBN TITLE TRANSLATED TRANFN TRANL
N
LOCATION
"# $ '
B?2
@
*\ N,1
*
F1 < 1
Libxml2 default name space issue
@11-)7 11.1.1*!1
B*4111*1-)..4*.7!.1
**11*.4/@@?(*1B*,,1*
$ R""C6&9; BIBLIOR
$
/111)...1*.B*.)1!1
4910;>4>& RC6&9; R-
Note/11111114-4-8?8&!
Direct access on XML tables
8111)B?2)1!/11B?2)1)11G1
H1.G!
=-),*714*!1-1)1.!
8?*11111)1.14*11-.
114!/14.),1*.*1111.
7- )., ! 1 11B?2 .1 1) 1L!
@4-4-1*1*1)4*1)11!/.1111
*))4).4*)41,B?2)).
***4*.*.!
Accessing tags with namespaces
DD-18?8&1**1)1,*.7)*
J.:.*!1,.,1!7
YF&(4 R'!R4 R+G6<RFZ
Y3&@ R3@)&&%RZ
Y@OZ
YO3 R6'0'.<00017'!=:0'<=7R R1=1<<:.07<:00<7'7/RZ
Y@Z//'!<7'0<=<:'=.=Y"@Z
Y4&Z0!':6!:6!'':@7:@!7!!!IY"4&Z
Y"O3Z
YO3 R6'0'.<0'.0..1'/:!/07R R1=1<<7<!1000/7/07!RZ
YZ/=<=<0=./<7.=:'0Y"Z
Y4&Z0!':6!:6!'':@7:@!<!!!IY"4&Z
Y"O3Z
YO3 R6'0'.<0'/0:=77<7.1=7R R1=1<</0..'111!!=<'RZ
YZ/=='.<=:1<0!'.!:Y"Z
Y4&Z0!':6!:6!'':@7:@'!!!!IY"4&Z
Y"O3Z
Y"@OZ
Y"3Z
.&(&/)
A,$9
JJ)90'-'/;+J>4>&J ]-
JJ)90!-'/;+J>4>&J ]-
JJ)90'-'/;+J>4>&J @-
J4&J4&>& R####66??@&&@!!!IR
;K AA?G+A5, 4'J%3J H
J>4&J && @O
344 &)3 &
C>&
81*41
$'
lon lat ele time
H " #$ #$" $
'' #$ J$J $$$
H " " ""'$ # '
'
J$J $$$
H " ' $#' #' ""
J$J $$$
4*.7T,1,L!
=-11-E-)7 1**!/111.,,
1*
A,$09
JJ)90'-'/;+J>4>&J ]-
JJ)90!-'/;+J>4>&J ]-
JJ)90'-'/;+J>4>&J RC6&9; R-
J4&J4&>& R####66??@&&@!!!IR
;K AA?G+A5, 4'J%3J H
J>4&J && RC6&9; OR
344 &)3 4&0
/K
C>&0
81*41
lon lat ele time
H " #$ #$" $
'' #$ J$J $$$
H " " ""'$ # '
'
'!## #"'"#$ J$J $$$
H " ' $#' #' ""
'!##"#$ " J$J $$$
/1T],1,L!/111-E-8?8&!
Having Columns defined by Discovery
1*11)?814*11G).1*.!D1
.&;(@/(/@2(1&(&/14LB?2.
*1*.1!/11*11)4.B?2)1).=/?2)1!
1xsamp))1*.4,
&3
4 AA%3 H>4& H&3&
& $$344 8 $N
21E--141*.1,3=D&;(@/(/@2(1
A,$J&3J9
J$J9'1;+JG?G,J ]-
JKJ90;+JG?G,J ]-
J+$LAJ9'0;+JG?G,J ]-
J+5,J90:;+-
$#
J,,J9'0;?G++-
JJ91!;+-
JD+$5,J90';+-
J?D+$J9:;+
;K AA?G+A5, 4'J$#DJ H
JGJ @"?"H&"H&3&J$J $$JD6
J 8 $N
1I7*.1L1)..17
,.1*,--1!@111*.14*
&=@;)1B?21*.)4*!)11
.111,1-1!
.*7.1141E&(&/41B@/=*,
1,2(0(2**1!/1).1E
B@/=!1
&3
4 AA%3 H>4& H&3&
& $$344 8 $N-( '
/1-.)1
A,$J&3J9
J$J9'1;+JG?G,J ]-
JKJ90;+JG?G,J ]-
J+$LAJ9'0;+JG?G,J ]-
J+5,G,J9'7;+
JG?G,J +5,"G,-
J+5,J9<;+
JG?G,J +5,"-
J,,D,GHJ90:;?G++
JG?G,J ,,"]D,GH-
J,,G,J9=;?G++
JG?G,J ,,"G,-
J,,J9/;?G++
JG?G,J ,,"-
JJ91!;+-
JD+$5,J9'7;+JG?G,J D+$6
5,"-
JD+$5,DAJ97;+JG?G,J D+$6
5,"DA-
J?D+$J9:;+
;K AA?G+A5, 4'J$#DJ H
JGJ H&3&J$J $$
JDJ 8 $N-( '
/1)11IE-4E5*6).)
E1.!*11.1.
*11!/1)411
*B@/=1.!
/*-.-1-).41,)E1
4 AA%3 H>4& H&3&
& $$344 8 $N-( '>)
$
@-1E,
)&&&-%3&%3-)&4E4E-
)-3>&
9,1*.-)1-)
Name Type Size Nullable Xpath
3 &=@; +
2@< &=@; +
3>F(&/ &=@; +
@>/=;:;3/@?( &=@; @>/=;J;3/@?(
@>/=;:2@3/@?( &=@; @>/=;J2@3/@?(
/;@32@/;:;(B &=@; $ /;@32@/;J+;(B
/;@32@/;:;3/@?
(
&=@; # /;@32@/;J;3/@?(
/;@32@/;:2@3/@?( &=@; ' /;@32@/;J2@3/@?(
//2( &=@;
>23=(;:@?( &=@; >23=(;J@?(
>23=(;:2@&( &=@; >23=(;J2@&(
8@/(> &=@; $
Write operations on XML tables
9.41>*811-B?2 )1!=-41
1..*1.-11*.1.)4
1.,1.!111**1-1-)E1,
xsamp)xsampall)-
44&3
94--)M-)-4-3)4-3);
()9.=<00'0!.!70.->-\\-44-
H-3344-%D4-'..<;
/.-1E
)M-)-4--3)4>&&3
(4,11--,1
SUBJECT AUTHOR TITLE TRANSLATOR PUBLISHER
**
1
FH&1*
&1 **
B?2
>22 (411
**
1
DF! B?2@ F1< ?1.111
,\\ @? B?22,,@**1 >22 (411
=-.-**4II4xsampall))11.
)M-
9)>--);)-4-
9>--;-
93)4--4;3)4>&&3
$"
11**4-,1-
SUBJECT AUTHOR TITLE TRANSLATOR PUBLISHER
**
1
FH&1*
&1 **
B?2
(411
**
1
DF! B?2@ F1< ?1.111
,\\ B?22,,@**1
D**M3*4)1-1xsamp)1-1)1
-B?2.11).xsamp
Y$N$ R.=<00'0!.!70.RK R>R+$LA R\\RZ
Y+5,Z44Y"+5,Z
YZH-3344Y"Z
Y,,ZY",,Z
YD+$5,Z%D4Y"D+$5,Z
Y?D+$Z'..<Y"?D+$Z
Y"$NZ
&(&/561)H,1*.xsamp)!111)H,1
71xsampall).1)!.--
))I4B?2.)4.)1-41-)E.
11xsampall)4111*1.,
>&&384 .=<00'0!.!70.
4 4 &3 94- )- )M- )>- )-
4-3)4-4-%;
()9.=<00'0!.!70.->-\\-4-4-
H-3344-%-D4-'..<;
-)EB?2.-I1
Y$N$ R.=<00'0!.!70.RK R>R+$LA R\\R
Y+5,Z
YG,Z4Y"G,Z
YZ4Y"Z
Y"+5,Z
YZH-3344Y"Z
YD+$5,Z
YZ%Y"Z
YDAZD4Y"DAZ
Y"D+$5,Z
Y?D+$Z'..<Y"?D+$Z
Y"$NZ
NoteD11111-,),,
P/;@32@/;Q-1)H1,11-E1D-14
Multiple Nodes in the XML Document
21)E)7*B?2.!D1)5*6
, ).)E!D-,*
..,M&(&/*14--*11)1)1
41**)!
/.11,,114-111
),*1.-EG)-1.)!/
11*41*.45*657*6*-,)!
1-xsamp2)E1
&309
$9'7;>4>& ]-
K90;>4>& ]-
+$LA910;>4>& ]-
+5,9:!;-
910;-
,,910;-
D+$5,910;-
?D+$49:;;
4 AA%3 H>4& H&3&
& $$344 8 $N-3 '-)6
+5,-4&4 0
112*1*.17).1-)7*!.
1*..1!@41)-),-,11,
11
!-4I411
4-)M-)-4>&&30
/1-1*4.-,1
ISBN SUBJECT AUTHOR TITLE
"# "
"
**1 FH&1*
&1**B?2
"# "
"
**1 ^1X) &1**B?2
"# $ '
**1 DF! B?2@
"# "
"
,\\ @? B?22,,@**1
11111.).-1!=-.-I4
4-)M-4-3)4>&&30
11-)
ISBN SUBJECT TITLE PUBLISHER
"# "
"
**1 &1 **
B?2
(411
"# $ '
**1 B?2@ ?1.111
"# "
"
,\\ B?22,,@**1 (411
11**I41*,--17*!
/111-1,)11-)...-)-E,).
..4*! =- 1 1 .- 1 )-
-1*1)!1141)-.11-)!
1
)9C;>&&30 "C,341C"
/1411-1)1)1D=(;(15*61
**4).-1!
)9);>&&30 "C,34:C"
)94;>&&30 "C,341C"
4-)M-4-3)4>&&308
)YZ
/11I4*1
ISBN SUBJECT TITLE PUBLISHER
"# "
"
**1 &1 **
B?2
(411
"# "
"
**1 &1 **
B?2
(411
"# $ '
**1 B?2@ ?1.111
"# "
"
,\\ B?22,,@**1 (411
(,1**11*,--17*
)1*-11-1!
Intermediate Multiple Node
/5*6) !.--17*,-
xsampall)-),!/xsampall2))-
&309
49'7;>4>& ]$-
)90;>4>& ]K-
)M910;>4>& ]+$LA-
)>90!;>4>& +5,"G,-
)90!;>4>& +5,"-
4910;>4>& -
910;>4>& ,,"]D,GH-
>90!;>4>& ,,"G,-
90!;>4>& ,,"-
3)490!;>4>& D+$5,"-
490!;>4>& D+$5,"DA-
%49:;>4>& ?D+$;
4 AA%3 H>4& H&3&
& $$344 8 $N-3 '-)6
+5,-4&4 0
/4..15*61*!/1,)
)1-I411
)M-)-4-)>>4-)
-%>&&30
/1I41*41
SUBJECT LAN
G
TITLE FIRST LAST YEAR
**1 . &1**B?2 FH
&1*
"""
**1 . &1**B?2 ^1 X) """
**1 . B?2@ DF! """
,\\ . B?22,,@**1 @ ? ""
/1*1)1.4.111*11.1-*,,.
1,!1G1,11U.--11-G1
..-)E1-),B?2 .!8-411)E
11.1*.,14!>*1*
)3&30)> 4&8) N
)3&30% 0!!08) $
)3&30) 48% 0!!0
@.1*1.1-1*,5@..-1611*,
5@..-1 61I41-1
subject lang title first last year
**1 . &1**B?2 FH&1* ?
**1 . &1**B?2 3 ?
**1 . B?2@ DF! """
,\\ . B?22,,@**1 @ ? ""
D1)11>*.11B?2.1
)!/.1*-E4/1*,4.
)E11-1.-7*-1)1148@/(>.
)E!1***1-1,)1-
*!
Making a List of Multiple Values
@-41* 1 11E &(&/ E 1* 1.
*1!/14).5*61!
7*-.4xsamp2).)4
&30344 8 $N-) +5,-4&4 1
/1T(7*11*.2,17).11!-.
-I4
4-)M-)R+5,9;R-4>&&30
--,.-,1
ISBN SUBJECT AUTHOR(S) TITLE
"# "
"
**
1
FH&1*^1X) &1**B?2
"# $ '
**
1
DF! B?2@
"# "
"
,\\ @? B?22,,@**1
*,5*61*11))1&(&/1E--.
1*!
/1)-xsampall2))11
*E,-11..11.11-E11
4-4!
What if a table contains several multiple nodes
/1))4,1)11.,4*
1,111,G1!
Support of HTML Tables
?1)1=/?2 1)*11)4&(&/)1=/?2
,,1.*)-147.B?2!*B?2I1*,1
))41,,-111*=/?2!/11.1,
,1!
=-4)11*B?2147)1..1.=/?2
)1!1
YF&(4 R'!RFZ
Y$Z
YZ
YZYZ&Y"ZYZ44Y"ZYZ?434Y"ZY"Z
YZ
YZY&Z5)&Y"&ZY"Z
YZY44Z$-+NY"44ZY"Z
YZY4ZX>)3-4Y"4ZY"Z
Y"Z
YZ
YZY&Z)Y"&ZY"Z
YZY44Z?&OY"44ZY"Z
YZY4Z&Y"4ZY"Z
Y"Z
Y"Z
Y"$Z
=..,1YZY"Z,11.=/?2)1!9G1
1,B*.1)111.1.,
1-111..17*.1!/111)4
1*.4,Colnode)*,1.1,1.7*
9
J&J9'/;>4>& &-
J44J9'/;>4>& 44-
J?434J910;>4>& 4;
4 AA%3 H>4& &
& 344 8 -
/)-)1*41
Name Origin Description
=1
>X D.*,
/), 8E 1)1
=-4-)11=/?2!7*coffee.htmK.
Y$)&&% R4)&>)3>>>
)&%-%3>>>9>
);-8O84)RZ
$
YADZA)3>>>)&%Y"ADZ
Y,Z
Y5Z&Y"5Z
Y5ZA)3Y"5Z
Y5Z%3>A>>Y"5Z
Y5Z)FY"5Z
Y",Z
Y,Z
Y?ZY"?Z
Y?Z'!Y"?Z
Y?Z3Y"?Z
Y?ZY"?Z
Y",Z
Y,Z
Y?ZL?4Y"?Z
Y?Z7Y"?Z
Y?Z?>Y"?Z
Y?Z#Y"?Z
Y",Z
Y"$Z
=14*1)4/8,7!91,1
1)1!41,))4*1--!
=1-1)&(&/
>>9
J&J9'/;-
JA)3J49<;-
J%3J9'/;-
J)J9:;;
4 %3 H>4& >>&
& $ '344 A%3 5
91*.4.1)4*1)41,Coltype *T=/?2!
(*1)1-).flag*11)4.
1I!--)1*4)
Name Cups Type Sugar
/!
37
(1*11
F!8 8. 91
Note 1D1*.T n1.1n-1.)
-11)1E**!
Note 2117*-1*.411, Rownode Colnode
*1 )1 - Coltype 1 1 T=/?2 4 . ,8 ,
A ?!
Note 3/Coltype*1-4.1.-11,.!;,L1
/, &1,.!
@)+ &1)!
=&1 &)4*1!
New file setting
3*114-,)-.!!-1,.
1714!D1*.T=*---.
)1!/11.41..=/?2)1)1*4-))-1!
3-1H*1117
Option Description
Encoding /,.-.,>/H!
Attribute @1.TR1*)4TU)!
HeadAtt
r
@)1)-!
211.1.-,1
9
9/:;-
(490!;-
)9/:;-
4349077;-
&)4%9'0;;
4 AA %3 H >4& & 6
& $ %
344 %3 5-4 6<<7.6'-
4) '34 7- %8
3**1,).1714.11).1)4.-,
1
443)4&-3)4(4-
3)4)-3)4434-3)4&)4%>&4>6
&4&3)483)4%3 ?
-,.-,.
YF&(4 R'!R4 R6<<7.6'RFZ
Y^66A%AAB4'!'!!!<))'<-0!'166Z
Y$ R'R34 R7RZ
Y, R%8RZ
Y5ZY"5Z
Y5Z(4Y"5Z
Y5Z)Y"5Z
Y5Z434Y"5Z
Y5Z&)4%Y"5Z
Y",Z
Y,Z
Y?Z4Y"?Z
Y?Z'7Y"?Z
Y?Z%D&Y"?Z
Y?ZA&3444%4>44Y"?Z
Y?ZK&&Y"?Z
Y",Z
Y"$Z
/1.)11*4)-))-1,1)3H"H7
'
handler version author description maturity
? ! ?4,@) &*)411.@, <
Note/B?2 , 1,41*. B?2 )
...8@/@:&=@;3(/-1-41>/H.B?2)1!/.)
8@/@:&=@;3(/ 1 1 ) 1*. 1*. 1 >/! / (,
1*.11.4.-B?2.1,.71,.1,,
41*.!
'
JSON F3* )G 1 ,-, H, . -4 1
!?4**1,4-F3*=1*F3-
7,1.1...*41.!
11*11)I4*1.)1E!?,8
11,F3*E,,!1,3C211.4)41,1*.4*
.1E41!
/&(&/,11.4?8)41**,)1)1F3.1!
/11E.B?2)1)4,)11),-1)..
-1)*11!
211..5biblio3.json61F3I.B?2Xsample.-
1)B?2)*
_
R$R@R.=<00'0!.!<'.R-
RKR@R>R-
R+$LAR@R3344R-
R+5,R@
_
RG,R@RL6A43R-
RR@R$R
`-
_
RG,R@RG[4R-
RR@RNR
`
-
RR@RA)4)3344HR-
RD+$5,R@_
RR@R%R-
RDAR@RD4R
`-
R?D+$R@'...
`-
_
R$R@R.=<0<:!<07/<7R-
RKR@R>R-
R+$LAR@R3344R-
R+5,R@
_
RG,R@RX44&LR-
RR@RD4R
#
`
-
RR@RH4R-
R,?R@_
RD,GHR@R3\43R-
R,,R@_
RG,R@RL&R-
RR@RK)4R
`
`-
RD+$5,R@_
RR@R4>DR-
RDAR@RD4R
`-
R?D+$R@'...
`
/1.1..171,F3!
Arrays/411I)E11.1*1!
Objects/414)E1!/41*1.*1*
*1.E4)-)I1.-)4T.-)4!
Values01)4)G!/41)1,)-)I,
.)!
/1*1-4.&(&/)1.1)44,1.
)G1!(4-))-*.-)G1-*1
E4),!
@.14)1.-)E41)
M&39
$9'7;-
K90;-
+$LA910;-
+5,9'0<;-
910;-
,?9<!;-
D+$5,90!;-
?D+$49:;;
4 AA%3 L
G4& 441M
.-7I4
4-)-4-3)4>&M&3
D,1
isbn author title publisher
"# "
"
FH&1*
&1 **
B?2
(411
"# $ '
DF! B?2@ ?1. 11
)4.1)G1)1.1,
1.)G1*)4)E!D144.1.
41!
=-,1,4*!.F3 .1)1,
)G*1E)14-@;;@93!D14)1
EB?2*11*.411)4,)14
)G1...4*14)1)1!
/11-4&(&/)11*.4 (28:;?@/ *5F@/=61
11)74-11*4-141!
=17*.-))1.-,1,
1,11)H)G11*.4-4
M&39
$9'7;-
)90;>4>& K-
)M910;>4>& +$LA-
)9'0<;>4>& +5,RR-
4910;>4>& -
4910;>4>& ,,D,GH-
9<!;>4>& ,,-
D)490!;>4>& D+$5,-
49'/;>4>& D+$5,DA-
#49:;>4>& ?D+$;
4 AA%3 LG4& 441M
<I4
4-)-3)4-4>&M&3
/11
title author publisher location
&1 **
B?2
FH&1* ^1
X)
(41 1
B?2@ DF! ?1.
11
1
= 17*1-, 1- 7. . -
57*64,,-.4
M&39
$9'7;-
4910;>4>& -
)G9'0<;>4>& +5,CG,-
)9'0<;>4>& +5,C-
#49:;>4>& ?D+$;
4 AA%3 LG4& 441M
11*41
"
ISBN Title AuthorFN AuthorL
N
Year
"# "
"
&1**B?2 FH&1* """
"# "
"
&1**B?2 ^1 X) """
"# $ '
B?2@ DF! """
The Jpath Specification
Caution11.&(&/F*1*.1,).
F3.1*)--1,41!111.
*)-?,8*1!/T1*1*)4T!!1
4 1 * ?,8 14 - 1I )E1! @4 1*. 1*.
&(&/1*) _V`11.7*, _7`.*4!=-)1
-*11471)1)4,3(:&=@;R)-@2/(;
/@2(!
11*.*.-I!(1*1E41
11.*-11,)G*1).-11,
4!X41*.11*)4T!!
1).1.1.)E1)4
a+5,' ""%
a+5,' ""?$%
+5,@'@""%8DA5, @
/TS5S!6*.71*.1.*)-&(&/!
/41*.1-1)*11
Specificatio
n
Array Type Limit Description
[n] or n
16
@ !@! /En.4!
[*]
@ (7*!<-.4!
["string"]
3, &11*)41*.string!
[+]
?E1.4H1!
[x]
?E*.4H1!
[!]
?E,.4H1!
[>] or [<]
@ ;,11H.4!
[#]
@ !@! ;).14!
[]
@ (7* . 7* )G! -1 3 .
11*)455!
@ !@! .47*.7*)GE
.1.!
Note 1D2?/11**)4.1m 411m),
.2?/*)1*./:23/!/2?/.1!
'
/n))1)1*,@3()*!/.1
-11,F1-))1.)111!
'
Note 2@-4-1)7*%1.*-14HH1
1(B@8**1.1
D 3 +5,
@>/=;1E4.*141111!(7*1
4)7*411)1)G
21E17*.expense.json1-@**7@!
/)jexpall7*1,-E4
M39
X59'0;-
XN490;>4>& aXNC+$,-
X5910;>4>& aXNCHDCX5-
+)9<-0;>4>& aXNCHDC+;
4 AA%3 LG4& 3M
WHO WEEK WHAT AMOUNT
F !
F !
F "!
F & !
F $ "!
F $ '!
F $ #!
F $ #!
F $ $!
F $!
F !
'!
$ #!
$ !
!
!
F & "!
F !
F !
F $ & #!
F $!
F & !
F "!
F !
/) jexpw 1-1--1),1,.1.*1
-E
M389
X59'0;)-
XN490;)>4>& aXNC+$,-
X5910;)>4>& aXNHDR-RX5-
+)9<-0;)>4>& aXNHDb+-
'
B,K)9<-0;)
>4>& aXNHD^+;
4 AA%3 LG4& 3M
WHO WEEK WHAT SUM AVERAGE
F & '"! #!
F $
! '!'
F '! !
'! '!
$ ! '!
! '!
F & ! !
F $ & #! #!
F & #! $!
211-1)jexpz
M3E9
X59'0;)-
XN9'0;)>4>& XNR-R+$,-
+9/:;)>4>& XNRbRHDb+-
+)9<-0;)>4>& XNbHDb+-
BK9/:;)>4>& XNRbRHD^+-
+BK)9<-0;)>4>& XNbHD^+-
BK+)9<-0;)>4>& XN^HDb+-
B,K)9<-0;)
>4>& XN^HDC+;
4 AA%3 LG4& 3M
WH
O
WEEK
S
SUMS SUM AVGS SUMAVG AVGSUM AVERAGE
F $ '"!O!O '!
#!
#! O'!'O!
$'! "! '!
$ '!O !O !
! '!O'!O'!
$! '!'# '!
F $ !O#!O#!
"!
!O#!O$!
$"! $! '!
*11
&1-1*1!
& 1-1-E1.-1!
&1111.7*11.-E!
&$11.7*11)4*1!
&1-1-E17*1,1!
&'11.1,1!
,.-E11.7*11!
&1,7*1)4*1!
-)4...*11))11.)G7*1,3C2I4!
Handling of NULL Values
F11null7*)41)GE41!D,,G11
)))11*,G117*4
*4)11*,111,4)G!&(&/1E
4..)-7**1!
'
=-1*11)1*.4-1*1!/11)41,
1,111)connect_json_null!/..:G1:15PQ6U)
,.1)4
M) +
/1,11*1-1*417.)G.
1.4!
11*11)&(&/,1)4
M) +
D,11**)G7411!=-11,
).41.4!
Having Columns defined by Discovery
1*11)?814*11G).1*.!D1
.&;(@/(/@2(1&(&/14LF3.
*1*.1!/11*11)4.)1*1)44.)G1
)1&(&/11.)G*E41..
)G*1!1jsample))14,
M&34 %3 L
>4& 441M
21E--11*.1,3=D&;(@/(/@2(1
A,$JM&3J9
J$J9'1;+-
JKJ90;+-
J+$LAJ9'0;+-
J+5,J(907/;?G++-
JJ91!;+-
J,?J(907/;?G++-
JD+$5,J(907/;?G++-
J?D+$J49:;+
;K AA?G+A5, 4'J$#DJ L
JGJ 441M
1I7*.1L1)..17
,.1*,--1!1F141
)G111*.10@;&=@;1,., '1**14),,
1)H)G11!)11.111,1
-1.1F@/=141!
.*7.1141E&(&/41F@/=*,
1,2(0(2**1!/1).1)H)G1
EF@/=!1
M&304 %3 L
>4& 441M344 ( '
/1-.)1
A,$JM&30J9
J$J9'1;+-
JKJ90;+-
'
J+$LAJ9'0;+-
J+5,G,J9'7;+JG?G,J +5,G,-
J+5,J9<;+JG?G,J +5,-
JJ91!;+-
J,?D,GHJ901;?G++JG?G,J ,?D,GH-
J,?,,J(907/;?G++JG?G,J ,?,6
,-
JD+$5,J9'7;+JG?G,J D+$5,-
JD+$5,DAJ97;+JG?G,J D+$5,DA-
J?D+$J49:;+
;K AA?G+A5, 4'J$#DJ LJGJ 441M
JDJ ( '
/*)1&(&/,11-4--41!=@>/=;
41..-141.1-)1141
1*.5(7*R@>/=;6*1!.14*)44,1!
/1)11IE-4E5*6).)
E1.!*11.)GE41.
*11!/1)41
1*F@/=E41.!
21),H411*14)G!
Note31!'!$&(&/1156-14*)
!1,.sresto.json
aW:WWWW&3WW1WW@WW,1W_aW,WW@WW1W'b`b
aW:W WWW2&1@WW1WW3*1WW,1W_`b
14-1,14,))4
M!
4 %3 L>4& M
344 D% !-( ' '0<
/)-1*141
A,$JM!J9
J4J449';+-
J&J90/;+-
J)44J9<;+-
JJ9';?G++-
JJ9';?G++JG?G,J a!-
JJ449';?G++JG?G,J a!
;K AA?G+A5, 4'J$#DJ L
JGJ MJDJ D% !-( '-3 '
J,AJ '0<
/5,16-1)1.4 !-111E**
1**411*@*R1*1!
JSON Catalogue Tables
@-41F3)1*.111,)!1
44 %3 L
>4& 441M344 ( 0>) )&
)&&-%3&%3-)&4E4E-M3>&
4
/1*4
column_name type siz jpath
'$
e
3 &=@;
2@< &=@;
3>F(&/ &=@;
@>/=;:;3/@?( &=@; AUTHOR..FIRSTNAME
@>/=;:2@3/@?( &=@; AUTHOR..LASTNAME
//2( &=@;
/;@32@/(8:;(B &=@; TRANSLATED.PREFIX
/;@32@/(8:/;@32@/;:;3/@?
(
&=@; TRANSLATED.TRANSLATOR.FIRSTNAM
E
/;@32@/(8:/;@32@/;:2@3/@?( &=@; ' TRANSLATED.TRANSLATOR.LASTNAME
>23=(;:@?( &=@; PUBLISHER.NAME
>23=(;:2@&( &=@; PUBLISHER.PLACE
8@/(> /(<(; $
@11141.-,).4141!
Finding the table within a JSON file
<.5.)E!G16
_
RR@
_
R4R@RH...#...R-
R>&R@_
R&R@R&$%R-R4R@RH'0R
`-
R&R@RO4>80!'!^R-
R4R@
_
R&R@RA&&R-
R4OR@R3@""888>O&"H..."3"#...R
`-
_
R&R@R4OR-
R4OR@R3@""888>O&"H..."3"#...R
`
-
R%3R@R)R-
R4&R@R0!'!6!<6!00'@0=@::b!!!!R-
R)34&R@R0!'!6!<6!00'@0=@::b!!!!R
`-
_
R4R@RH..<#..<R-
R>&R@_
R&R@RD%4R-R4R@RH'<R
`-
R&R@RX&%FR-
R4R@
_
R&R@RA&&R-
R4OR@R3@""888>O&"H..<"3"#..<R
`-
_
R&R@R4OR-
R4OR@R3@""888>O&"H..<"3"#..<R
`
-
R%3R@R)R-
'
R4&R@R0!'!6!<6!00'@0=@::b!!!!R-
R)34&R@R0!'!6!<6!00'@0=@::b!!!!R
`
`
/)--4L1*1)44.56)G!=1-11
1*.&;(@/(/@2(1
M>O9
J?J9'!;>4>& 4-
J&J910;>4>& >&&-
J%?J9'/;>4>& >&4-
JJ(907/;>4>& &-
J4J9'/;>4>& 4&-
J4OJ(907/;>4>& 44O-
J%3J9'/;>4>& %3-
JAJ4&>& ####66??SS@&&@
>4>& 4&-
J+3J4&>& ####66??SS@&&@
>4>& )34&;
4 %3 L>4& >OM36
44 M -3 4
/11F(&/*,1F*.)!1-4
4)7*)4(B@8*.*:1!
111,1*11*,11
.11*..!
/F*.)G*11147F*).141*1
1)1*.1,n.!
Note@1**14)1,;(//9R 1)-!
JSON File Formats
/7*1-11..14)...-41)E1
)1,.,-*1,1*F3147
.4)G@4!2E.B?2.144*14
*111*11!/1*14.1)1L
.4!/)1)11.1,L)4*Pretty=2-
1*.4))111.!
@ . - 1 ..7*?,8.11.--1
*4141.!1
{ "_id" : "01001", "city" : "AGAWAM", "loc" : [ -72.622739, 42.070206 ], "pop" : 15338, "state" : "MA" }
{ "_id" : "01002", "city" : "CUSHMAN", "loc" : [ -72.51564999999999, 42.377017 ], "pop" : 36963, "state" : "MA" }
{ "_id" : "01005", "city" : "BARRE", "loc" : [ -72.1083540000001, 42.409698 ], "pop" : 4546, "state" : "MA" }
{ "_id" : "01007", "city" : "BELCHERTOWN", "loc" : [ -72.4109530000001, 42.275103 ], "pop" : 10579, "state" : "MA" }
…
{ "_id" : "99929", "city" : "WRANGELL", "loc" : [ -132.352918, 56.433524 ], "pop" : 2573, "state" : "AK" }
{ "_id" : "99950", "city" : "KETCHIKAN", "loc" : [ -133.18479, 55.942471 ], "pop" : 422, "state" : "AK" }
/,.51!G161 " 1!/)1)1.-11*.4
*Pretty=0*1!1
449
J4J97;O%-
J4%J910;-
''
JJ)9'0-/;>4>& !-
JJ)9'0-/;>4>& '-
J33J49<;-
JJ90;44 );
4 AA%3 L>4& 44M
'0<344 3% !
1.n41*.1.,1!
D1,1.)1*11)4&(&/E83&30?/)!;-1
*1)41))4,!@,11
))7-).,.4,)1!/51)6*.
5161&(&/1)E7,-*11)!
1)1%1-1.*4R1%1L1)1*.1,2;(&2
*!141*.41111J-)..1
4 1 . *1, -1! ) ) ,1 1 1 1 4
!
@.711 )4 4R- 11 1 ) 11 1
*1F34!@1,*,1,
1I)E1)1.-)4!11,1.
,*,)1,,7;(//9R -4!
Alternate Table Arrangement
D11-4*1)F3.1E4.
)G1!=-*11)171!@))4.41))
4.1-))G1)G!-)14
)4,4!
211-.1)14.41!/.
[
[56, "Coucou", 500.00],
[[2,0,1,4], "Hello World", 2.0316],
["1784", "John Doo", 32.4500],
[1914, ["Nabucho","donosor"], 5.12],
[7, "sept", [0.77,1.22,2.01]],
[8, "huit", 13.0]
]
@))1.1
M9
JJ49/;>4>& '-
JJ910;>4>& 0-
JJ)9'!-:;>4>& 1;
4 %3 L>4& M
344 D% '-L& '-$ ' '0<
&11*.)4*1-41!4.11)1).1)
)1-11)4Base*.*1!@-**11
Jmode=1!1-4*.)11!/F1
@4.)G1!/11.!
@4.@4!2E1!
@4.1!
'#
D,11I14*.4111*..1U-
1I-1,--11&(&/E-1-1!1
44M()907-$O>-':':;
@.111*41
a b c
' & !
=
D
!'
#$ F8 !$
"$ ) !
# 1* !##
!
E.1 !$$
>1*.41*1)4.1!
Getting and Setting JSON Representation of a Column
1*11)1*41.F31,1*,
F3.!/111*.F@/=)45V6-)G4-)1*.!
1
M&309
$9'7;-
90;>4>& K-
M)9077;>4>& +5,C-
4910;>4>& -
#49:;>4>& ?D+$;
4 AA%3 L>4& 441M
-I4
M)>&M&30
-1*4K
json_Author
_aW;3/@?(WWFH&1*WW2@3/@?(WWWbaW;3/@?(WW^1WW2@3/@?(WWX)Wb`
_aW;3/@?(WWDF!WW2@3/@?(WWWb`
/11-E1*1*.11)41F31,!
/1.1.,1--1)-!
CRUD Operations on JSON Tables
/ 3C213(;/>8@/(8(2(/(.41** . F3 )1!
3(;/>8@/(.,11*1*)1!
=-1111-.1)G141!
'
&,)G11*)171-41-B?24*!/
.)G-.1)).-)...
.F3.!?.11)1,.1*.4,.*..
)G1!
-*)11-4,.41.4!4*1)
,)!1..1.4)11**1
,411)1,)7*,14!
1.41. biblio.json )1) jsampex )1)1!
8,1*,,11*11)1,13C21!7*
,.1.X).^1F
)3M&3)> L8) N
=--)-,
)3M&3)> L84 .=<00'0!.!<'.
11-,.1.)114113!
D,1)..1-4,1.)E!
8(2(/(--)E3(;/--*-
1.,-14!=-*L)43C2,,
,1-41*.41!3,E
)3M&3)> A-) ?4O
84 H4
=-11713C2!8111*11)M)I11
1)1*.1.)*11E!
-411*.4).-17*4!3**1,
--5B?2@6)E--),G1
1.)E-11)E.)!
M)9
G,9/:;-
9/:;;
4 AA%3 LG4& 441M36
44 M '+5,
/
C>&M)
*1
FIRSTNAME LASTNAME
DF!
11F3)14.)G1--.41-1!
44M)()9A-?4O;
DE1-14)4
'"
C>&M&3
/1-1*4
ISBN Title AuthorFN AuthorL
N
Year
"# "
"
&1**B?2 FH&1* """
"# "
"
&1**B?2 F X) """
"# $ '
B?2@ DF! """
"# $ '
B?2@ &1 8E1 """
Note.1)-),)-4)E1-)..E--.
1*.)E1)!/1).)4,1*;D8)!
=--41)41,F3*11F3@?2(
)!/1))4
)3M&30M)
_RG,R@RX44&LR-RR@RD4R`-
_RG,R@RAR-RR@R?4OR`
84 .=<0<:!<07/<7
=-.7.1)4.4!=-11I11.4,
)1-4--F31json_Author!
/.-1*..11!/4-!
JSON User Defined Functions
@,1.1-)4*171
#
&(&/*11->81
1*.4 * F3 ) 4* 14 ) )1 ), 1
&(&/)48224I)
!
*?8! !.F3.1!1111*11)
1.1)1!=-7,>8F3.11
I4.1-E)1-44,L,11..,
111!
=11.&(&/.1U).I!
Name Type Return Description
G1 3/;< ?EF3.1I,!
G1:E:4 3/;< ?EF34,1,1!
G1:4::
1
1 3/;< @11.14,.-,,1!
G1:4: 3/;< @11.14,11,1!
G1:4: 3/;< 81n.1.14,!
#
3K.1
*1JJ)!JE)JJ)JG1H.1J
*1JJ,)!J41I.J):41I.:G1c
*1JJ),1!!J11J4JG1:.:.1::$
3@**7&E1.11*)4!
#
Name Type Return Description
G1:E:)G 3/;< ?EF3)G,1,1!
G1:)G: 3/;< ?EF3)G,1,1!
G1:)G:E4 3/;< ?EF3)G.E4J*1!
G1:)G: 3/;< @11.1)G,11,!
G1:)G: 3/;< 81n.1.1)G,!
G1:)G:1 3/;< ;11.)GE4114!
G1:)G:1 3/;< ;11.)G114!
G11:,*:1L /(<(; 31F1<*3L1!
G1,:,*:1L /(<(; ;1F1<*3L!
G1:4:,* @,,,
3/;< ?E1F341.,,!
G1:)G:,* @,,,
3/;< ?E1F3)G1.,,1!
G1 3/;< ;1F@/=11!
G1:: 3/;< ;1F@/=1.1.!
G11 /(<(; ;1.1
!
F11:* /(<(; ;1.F@/=1!
G1::, 3/;< ?,1-41-)G1!
G1:,: 3/;< @111F1)4F@/=E4!
G1,:1, 3/;< @1111,)4F@/=E4!
G1,: /(<(; @111,)4F@/=E4!
G1,: ;(@2 @111)4F@/=E4!
G1:1: 3/;< 31*1!
G1:1: 3/;< 11*1!
G1:*: 3/;< >*1*1!
G1:. 3/;< ;11.F1.!
G.:E 3/;< ?EF1..1F1.1,!
G1:1L 3/;< 3L1.5F)6.!
G):4 3/;<V ?EF34,1,1!
G):4::1 3/;<V @11.14,.-,,1!
G):4: 3/;<V @11.14,11,1!
G):4: 3/;<V 81n.1.14,!
G):)G 3/;<V ?EF3)G,1,1!
G):)G: 3/;<V ?EF3)G,1,1!
G):)G:E4 3/;<V ?EF3)G.E4J*1!
G):)G: 3/;<V @11.1)G,11,!
G):)G: 3/;<V 81n.1.1)G,!
G):)G:1 3/;<V ;11.)GE4114!
G)::, 3/;<V ?,1-41-)G1!
G):,: 3/;<V @111F1)4F@/=E4!
G):1: 3/;< 31*1!
G):1: 3/;< 11*1!
G):*: 3/;< >*1*1!
G):. 3/;<V ;1.F1.!
3,1**F31,1!/11,141*.
F3147!/1*,1)4*11-11),,-TG1:!
#
/1141-F3>8,1F3>8-1),1
-5G1:6111!/11-4.1F1
*.7)45G1:6!
1),,1).,H*11!81
1,1,*111,1!,*1
*J7*!,1--*1!
/11.17.-,1
"
>)4M())4&
>)4M&O%)4&
>)4M%())4&
>)4M%)4&
>)4M%)4&
>)4M&OM)4&
>)4MM))4&
>)4MMO%)4&
>)4MM)4&
>)4MM)4&
>)4MM4)4&
>)4MM())4&
>)4M34E)4&
>)4M34E)4&
>)4M%3)4&
>)4MM3)4&
>)4M)4&
>)4M)4&
>)4M4)4&
>)4M43)4&
>)4M4&&)4&
>)4M4&)4&
>)4M4)4&
>)4M4)4&
>)4M)&
>)4M4&)4&
>)4M44&)4&
>)4M)34&)4&
>)4M>4)4&
>)4M>4&O)4&
>)4M44E)4&
>)4M4%)4&
>)4M4%())4&
>)4M4%)4&
>)4M4%)4&
>)4M4M)4&
>)4M4M))4&
>)4M4MO%)4&
>)4M4M)4&
>)4M4M)4&
>)4M4M4)4&
>)4M44&&)4&
>)4M44&)4&
>)4M44&)4&
>)4M444&)4&
>)4M4)34&)4&
>)4M4>4)4&
Note1 G1.1 . -- ,**H1 1 .
4!1*11)13C2I1)1.1111!=-
-,**,11)-1E4)4!
"
/1-E1D-14!271)1*.1T:!1!
#
JsonValue(val):
;1F311,.1
LB)91':'/;
JsonValue(3.1416)
!$'
Json_Make_Array([val1[, …, valn]]):
/1.-15F1:@46*111.&(&/!-1)1
?8! .1F3.1,5F1:@46.!/.
111>8)1*&(&/1*.,111
1.F.1!
F1:?E:@41 1,, F3 4-1,11)1!
7*
LO%97/-1':'/-%&4RGR-+;
Json_Make_Array(56, 3.1416, 'My name is "Foo"', NULL)
_'!$'W?41dWdWW`
Note/,1)!.141!
Json_Array_Add_Values(json_doc[, val_list]):
/.1,1)F341,!/,11)1.1
4!7*
L%B)9LO%97/-1':'/-&4-
+;-&-8&;%
Array
_'!$'WWWWW/-W`
Json_Array_Add(json_doc, val_list, [arg3], [arg4] …):
/.1,1)F34!/1,11).14!
7*
L%9LO6
%97/-1':'/-&4-+;-
&;%
Array
_'!$'WWWW`
Note/.1411*11),,-TG1:!
--1-,F3@?2( )4)
)3M&30M) M%9M)-
M&OM9AG,-?4O;;
84 .=<0<:!<07/<7
#
Note&,,F3 *.7)4G1:E json_author 1,
*,1*1*,-11,!
Additional arguments:
.,,1,1*.1*1L)1.
L%97-1-<-=-.M-:-0;%
Array
_$#"`
.1,,11*.1F1*4).!1
L%9_RR@'-RR@0-RR@1-:`M-7-'-;
Json_Array_Add('{"a":1,"b":2,"c":[3, 4]}' json_, 5, 1, 'c')
aWWW)W WW_$`b
Json_Array_Delete(json_doc, index, [arg3] …):
/.1,1)F34!/1,1,,E
)1.,,G11,.!7*
L%?9LO%97/-1':'/->-+;-';
%
Array
_'W.W`
--1-1.F3@?2( )
)3M&30M) M%9M)6
-';84 .=<0<:!<07/<7
@F1*)1*.11,,!)11*.4-.G1
,1**!
Json_Make_Object([val1[, …, valn]]):
/1.-15F1:)G6*111.&(&/!-1)1
?8! .1F3.1,5F1:)G6.!/.
1->8F1:)G:X41!
F1:?E:)G11,,F3)G!1
LOM97/-1':'/-&4-+;
/)G1.-*11*,,,1!/E4.*1.
,.1*.1!
Json_Make_Object(56, 3.1416, 'machin', NULL)
aW'W'W!$'W!$'WWWWW>22Wb
D1*.4E41)4,,1,1
LOM97/P%-1':'/34-&4)-+
%;
#$
Json_Make_Object(56 qty,3.1416 price,'machin' truc, NULL garanty)
aWI4W'W*W!$'WWWWW,4Wb
.11*.7)4TG1:*1*,E411**.*.7!
/1.1.41.-,1.)E4),)4.
LOM9&4)-&-4-4;>&
&3%8& D,
Json_Make_Object(matricule, nom, titre, salaire)
aWW$'#WWW@/(;WWWW8;(&/(>;WW1W$!b
Json_Object_Nonull([val1 [, …, valn]])
/1.-E1EF1:?E:)G)56,1,1
)G!
@,1,156.4F3 141)G141
)G1,4)1!
1411,1111-,)11!
Json_Object_Key([key1, val1 [, …, keyn, valn]])
;1,,F3)G!1
LMN%9P%-7/-34-1':'/-)-&4-
%-+;
/)G1.-*1.E4J,1!
Json_Object_Key('qty', 56, 'price', 3.1416, 'truc', 'machin', 'garanty', NULL)
aWI4W'W*W!$'WWWWW,4Wb
Json_Object_Add(json_doc, pair, [path] …):
/.1,1)F3)G!/1,11*1)G!
7*
LM9_R4&R@R64R-RP%R@0=-R34R@0:..`
M-);8M
newobj
aWWW/H1WWI4W #W*W $!""WWW)Wb
Note.1*.E44711)G11*)4-!
/1,,1F1*,)G-!
Json_Object_Delete(json_doc, key, [path] …):
/.1,1)F3)G!/1,1E4.*!
7*
LM?9_R4&R@R64R-RP%R@0=-R34R@0:..`
M-P%;8M
#
newobj
aWWW/H1WW*W $!""b
/1,,)F1*)G),.!
Json_Object_List(json_object):
/.1,1)F3)G!/1.14,1.E41
71,)G!7*
LM49LOM97/P%-1':'/34-&4
)-+%;;RN%4R
Key List
_WI4WW*WWWW,4W`
Json_Object_Values(json_object):
/.1,1)F3 )G!/1.14,1.
171,)G!7*
LMB)9_RR@'-R8R@0-RR@1`;
RB)4R
021
_ `
Note/1.1-471111!
JsonSet_Grp_Size(val)
/1.111F1<*3L!/111)4.-,,,,
.11,.).1,*!1F1<*3L
)1.-*111,!
JsonGet_Grp_Size(val)
/1.1F1<*3L!
Json_Array_Grp(arg)
/11,,,.E14..1,.-1)4
I4!211**1-pet)
name race number
F ,
?4 ,
?4
21)
))
X
X ) '
8 ,
8 .1
/I4
#'
&-M%39;>&3)3%&
-
name json_array_grp(race)
_WW`
8 _W,WW.1W`
F _W,W`
X _WWW)W`
21)
_W))W`
?4 _W,WWW`
*)-F3 ,,,.114114
E-.1,E-,).-1.1)!
/.).1.,*1!/11.JsonGrpSize-1
.1))11,F13:<*:3L.
!11-E,
,)1*11))4.1, JsonGrpSize ,.).-1*
,*.)!/414,471!
Json_Object_Grp(arg1,arg2)
/1.-E1EF1:@4:<*!E1F3)G.-1*11.1
.1,!01*11.1,-)E4111-1!
/1)1-I4
&-MM39)&-;>&3)3%&
/1I41
name json_object_grp(number,race)
aWWb
8 aW,WW.1Wb
F aW,W b
X aWW W)W'b
21)
aW))W b
?4 aW,WWWb
JsonLocate(json_doc, item, [int], …):
/.1,1)F3!/1,1)!/)
)1G1!&111)I4*).!
/11W1-I4W%1,1,1)1-N!
/1.1G1*.1.!7*
L9_R+5,R@_RGR@RL)R-RR@RBR`-
_RGR@RLOR-RR@RR``M-LO;D
/1I41
DJsonGrpSize11&(&/11*connect_json_grp_size111)1!
/1)*41.71,**1-.11)-E141&(&/
,11!
##
Path
S!@>/=;3_`!
/*1471-11F3&(&/)1!
4.*..1.1!/*)1
1*.4-1*1)!1
L9:7-0<-1/-:7-<.-:7;>4-
L9:7-0<-1/-:7-<.-:7-0;-
L9:7-0<-1/-:7-<.-:7!;J8%3J-
L9:7-0<-1/-:7-<.-1/-:7M;M
firs
t
second wrong type json
S_` S_ `_` PQ S_ `
1,1*11111)4.!=-1*11)1*.41,
)*1114)4,,1),,)456
L9_R+5,R@_RGR@RL)R-RR@RBR`-
_RGR@RLOR-RR@RR``M-B,4;D
Path
S!@>/=;3_`!2
Json_Locate_All(json_doc, item, [depth]):
/.1,1)F3!/1,1)!/1.
1*11.14.1,1!7*
L9:7-0<-1/-:7-<.-:7;
/1I41
All paths
_WS_`_`WWS_`_`
_`W`
/4)**.1!1,).1.
G14
LK9L9:7-0<-
1/-:7-<.-:7;-aU;R>)R
/1*41
Nb of occurs
.1*.,,1* 1 ! /1 1
71*1!/1.1))1.*71
17-*.*1!
#
JsonContains(json_doc, item [, int])
/1.)1E-1!1,1
11. JsonLocate .U4,1!/,
111-1!
JsonContains_Path(json_doc, path)
/1.)1E-F1*1!/,
11*1-1!
Json_Item_Merge(json_doc1, json_doc2)
/1.,1-41-)G1!4111)4,.14
1.14!1
L&9LO%9--;-
LO%9-->;;R,)R
/.1
Result
_WWW)WWWWWWWW.W
`
)G1*1.1)G.1)G.E41714U
-1*..1)G11-.,*.1)G!
1
L&9LOM9'RR-0RR-1RR;-
LOM9:RR-7RR-/R>R;;R,)R
/.1
Result
aWWW)WWWWW$W.W'b
Json_Get_Item(json_doc, path)
/1.11)1.G1*111.1,!/1,1
G1*.)1),G1)4TV!.
.-4E,)11.*.!1
LK&9LOM9>R>4R-
LO%9-11;RMR;-;
R4&R
/*1)T1!V)11*1.-1)E
,!/
item
_WW`
Note/4115G1:161G11*,!=-
5G1:6*.711E**-E,)G1)*!
{JsonGet_String | JsonGet_Int | JsonGet_Real}(json_doc, path, [prec])
/.1,1)F3!.11,-1-)1G1
!/1,1*.).1,
4,1.!7*
#"
LK49_RP%R@=-R34R@0.7!-R%R@)`-34;R4R-
LK9_RP%R@=-R34R@0.7!-R%R@)`-34;RR-
LK,9_RP%R@=-R34R@0.7!-R%R@)`-34;R,R
/1I41
Strin
g
Int Real
"! " "!
/.F1<:;),,1*.4).,1.
!1
LK,9_RP%R@=-R34R@0.7!-R%R@)`-34-:;R,R
/1-
Real
"!
/,*1*.4*1.417*57*6_V`*!1
LK9LO%9:7-0<-1/-:7-<.;-:;R,OR-
LK9LO%9:7-0<-1/-:7-<.;-U;R)&R-
LK49LO%9:7-0<-1/-:7-<.;-R-R;RAR-
LK9LO%9:7-0<-1/-:7-<.;-b;R)&R-
LK,9LO%9:7-0<-1/-:7-<.;-^-0;R(R
/1
;
E
) & 3 @,
" $ '$" $ $!'
Json_{Set | Insert | Update}_Item(json_doc, [item, path [, val, path …]])
/1.11*F31!/J**1
.,!/*)4,*)1-
,1-7*1!
L& *171,11H71,1!
L& 111-*,71,1!
L+3& *1only71,1!
(7*
]M LO%9'-0-1-LMN%9P)-:;;
L&9]M->-a'-7-a14P;RR-
L&9]M->-a'-7-a14P;RR-
L+3&9]M->-a'-7-a14P;R+3R
/1I41
Set Insert Update
_W.W
aWIW$WIWb`
_
aWIW$WIWb`
_W.WaWIW$b`
Json_File(file_name, [arg2, [arg3]], …):
/.1,1).!/1.17..11**1)
G1.!.4,11*..71-),*1!>*-
,1)1*.
@1,,1*1)H)!
@,,1*.1pretty...!
/1.1.41,G1,.G1.1.G1.!
11**1,.tb.json1
_R4R@7-R%3R@R>R-R4R@7-<-.`
_R4R@/-R%3R@RR-R4R@7-.`
(7,.)-I411
LK49LG49M-!;-a'%3;
R%3R
/1I41
=--11.1)1F):41*.4
.I1!*1.1)1.I11.4G1
)1.G11.1.-),!
Jfile_Make(json_doc, arg2, [arg3], …):
/.1,1)G1
!-,,11,.,
*4., 4!/1.E1G1.,.1,
!
/1,1.!.1*.1,.1
11)..1,U11.1.1.!
/1.)1..G1.!11**1,--.
.)!G11)-I4
L>4O9MM>4-0;
/tb.json.-),
_
R4R@7-
R%3R@R>R-
R4R@
7-
<-
.
`-
.1G11,Jfile_Make-41)11.1G1*.!
Type
_
R4R@/-
R%3R@RR-
R4R@
7-
.
`
The “JBIN” return type
@1.1,G11,%-1),1-Json_HH*
-1),1- Jbin_!/111-.*.1*41.)
.-.11!
/11-4&(&/>81-E4!/ Json .1-,G1
1,11*1*11)44!/4-E1
).,U1L1-G11,!
.G11),1)I1,1,!1,-
1*G1.1%1)4-4%)1-1.4-G1
.111*1G1.1!
/*1L,*1, Jbin .11)11*1
.1!41L4)1-,
, . 11 4 ! /1 11 1LH*1 1*1
-1*11,4.)4
-)4-41'#11L.G11,!1
LOM9L4%9L46
%9--;-;RL4>R;R,)R
/1I41
Result
aW.W_WWW)WWWWW`b
= )4 G1 )4 F):@4 1 * )4 F):@4:@
F1:?E:)G1L4E.11,!-)1L
*1-1.1,5F16.1!
F)11,L11)11),,)45F):6!/11-4
F1:?E:)G.111*.5F):.6!
D**1.,L11M/1.11,1,E
.11),1-LH1,!1
L4%9--;
/1I4*1
*,1 ) ,L L 1,! * .
?81*1*41))1)1,!
Jbin_Array('a','b','c')
4F14
Note D1,)45F)6. ) 1 1, Json_Serialize
.-1I*1)5F)61!1
L44E9L4%9--;;
/1I41
Json_Serialize(Jbin_Array('a','b','c'))
_WWW)WWW`
Note11*7*11I1,F1:?E:@4.!
Using a file as json UDF first argument
D14G1>81,41)!/11
1-G1,-1.,.!/,,1
*4.G1.!14-.1,1G1.E
>811,1.1)1-),,-G.:
...1.-1-)--1*4.!
/G11)47,..I*!/1)-.)
.41.!/--411*.41)H..)1
! 3*.4,.F1:F):,1!
! 3*.4,,.*11)..1
1E4..-F):11)1-F1:!1
L>4O9_RR@'-RR@::-77`M-M;
L%9LG49M-;-//;
/1I41
Json_Array_Add(Json_File('test.json', 'b'), 66)
_$$''`
G11%.HH1)1)4F1:.-I4
L%9LG49M;-//-;
1--1.F1:-.1)1!
Json_Array_Add(Json_File('test.json'), 66, 'b')
aWWW)W_$$''`b
)1test.json.1.!/11)1Json_File.1
1,*1,*..7).).!/11,
E--).....!
=-..1F):.4,.!F):
11,1..*.*1
4*1)1-*1,11,
L%9L4G49M-;-//;
/1I41
Json_Array_Add(Jbin_File('test.json', 'b'), 66)
1!G1
/1.1.!/1)E-
LG49M-1;
F1:N1!G1N
aWWW)W_$$''`b
/1-4.1,1)41I41)1.)111
9
4O%-
M>49'!;);
44()9'-M;
1)jfile_colsG11.!.-*)4
)3M>4
L%9L4G49M-;-//;
8 '
/11test.json.1).jfile_cols!/1)E)4
LK49M>4-'@C;>&
JsonGet_String(jfile_cols, '[1]:*')
aWWW)W_$$''`b
Note-1*.41.)),,)45G.:61
G1.1E--1.-),,1*.41I1!
Using “Jbin” to control what the query execution does
/11**4,*-,G1.1!D1.-1.
-1, Json_File .1,.4,.)1.4,
.G1*4.G1.!/11-1, Jbin_File.
11L)4E411)!@11-1
G1.1.4.1.*.4..!/11
)441L,)41.!
=-5F)6*.1.111L)41
1.4G1.!7*1*1-I1
"CG4P)%C"
LOM9L4M9L4G490M;-
:RR;RL4'R;R,)R
"CP)%C"
LOM9LM9L4G490M;-
:RR;RL>4'R;R,)R
$
I1
Result
aW)WaWWW)W WWWW$bb
.1I4 Jbin_Object_Add 1 1L 5F)6 .1
Json_Make_Object G1 1 1L . ! &1I4 . bt2.json 1
.!/1I41,*4.1.G1.-.4,!
=-1I4Json_Object_Add 1.4G1.1.!
/Json_Make_Object.11.*11.E1)G.
11L1!/1.)-,4))-1
...I4!
/.1,5F)6,.1),.11,11411
1.-,-G1.11).!
Using JSON as Dynamic Columns
/F33C2,,1.1)1141!
1?8,117*.41
9
6Z4&&(910;34&%O%-"C&&4)>4&C"
6Z%&4"C?%&4)&84C"
6Z;
*)%N-!8>>9!!7;
,B+
6Z94?$64-A+A,9-)-4E-H;;
*)%N-'8>>9!!:;
,B+
6Z94O333-A+A,9-O-34-7!!;;
*)%N-'8>>9!!!;
A4&&-A+K9%&4-;G,
b66666666666666666b6666666b
4&&
b66666666666666666b6666666b
4?$64)
4O333O
b66666666666666666b6666666b
0849!!.;
"C,&()&@C"
+D?%&4 A+?9%&4-R34R;
6ZX5,A+K9%&4-; O
*)%N-'8>>9!!!;
,8&@'A@'X4@!
"C)&@C"
+D?%&4 A+??9%&4-8%-1%;
6ZX5,4&& 4O333
*)%N-'8>>9!!!;
,8&@'A@'X4@!
"C#)4)&-94>&4?$'!!';
&844()4L>&@C"
A4&&-)&49%&4;G,
b66666666666666666b666666666666666666666666666b
4&&)&49%&4;
b66666666666666666b666666666666666666666666666b
4?$64J4EJ-JJ
4O333JJ-J8%J
b66666666666666666b666666666666666666666666666b
0849!!!;
A4&&-A+L9%&4;G,
b66666666666666666b6666666666666666666666666666666666666666b
4&&A+L9%&4;
b66666666666666666b6666666666666666666666666666666666666666b
4?$64_R4ER@RHR-RR@R)R`
4O333_RR@ROR-R8%R@R1%R`
b66666666666666666b6666666666666666666666666666666666666666b
0849!!!;
/11))-G111,G1>81
"CLP)4(C"
M9
6Z4&&(910;34&%O%-"C&&4)>4&C"
6ZM(97'0;"CL)&84C"
6Z;
*)%N-!8>>9!!:;
,MB+
6Z94?$64-LOM9)-H4E;;
*)%N-'8>>9!!!;
,MB+
6Z94O333-LOM9O-7!!34;;
*)%N-'8>>9!!!;
A4&&-LK49M-;G,M
b66666666666666666b6666666b
4&&
b66666666666666666b6666666b
4?$64)
4O333O
b66666666666666666b6666666b
0849!!!;
"C,&()&@C"
+D?MM LM?9M-34;
6ZX5,LK49M-; O
*)%N-'8>>9!!!;
,8&@'A@'X4@!
"C)&C"
+D?MM LM9M-1%8%;
6ZX5,4&& 4O333
*)%N-'8>>9!!!;
,8&@'A@'X4@!
"C#)4)&-&844()4L>6
&@C"
A4&&-LM49M;G,M
b66666666666666666b66666666666666666666666666666b
4&&LM49M;
b66666666666666666b66666666666666666666666666666b
4?$64RR-R4ER
4O333RR-R8%R
b66666666666666666b66666666666666666666666666666b
0849!!!;
A4&&-MG,M
b66666666666666666b6666666666666666666666666666666666666666b
4&&M
b66666666666666666b6666666666666666666666666666666666666666b
4?$64_RR@R)R-R4ER@RHR`
4O333_RR@ROR-R8%R@R1%R`
b66666666666666666b6666666666666666666666666666666666666666b
0849!!!;
=-1,F3),1.17,41
>1.,,1)44**1!
1**.41411,.41!
@11.1)*.G1)4F@/=141!
11).1G1.1!
'
D7*>81)14-1**-1!
Converting Tables to JSON
/F3>81F*5V6.4*-.1).1
F3.!1.biblio3.json-1*14)))4,
xsample.xml.!/1)E1
M'98(97!!;>4>& C;6
4 %3 L>4& 441M
344 M& 0
@
44M'
MM)9$-)K-+$LA-
M%39M&OM9)>G,-)
;;M+5,--
M&OM9D,GH-M&OM9>
G,-;M,,;M,?-
M&OM93)4-4DA;MD+$6
5,-?D+$;>&&30)3%4
/7G)-1-4F1)G)4 3(2(&/ 11
3(;/ 1).-)11-7G1*4R )4.!11
FR )1111,1.4G1)G1!
@-411)1),..--).biblio3.json.
71
M&319
$9'7;-
K+K90;>4>& K-
+$LA910;-
+5,G9'0<;>4>& +5,@H@G,-
+5,9'0<;>4>& +5,@H@-
910;-
,?910;>4>& ,,@D,GH-
,,G9'0<;>4>& ,,@G,-
,,9'0<;>4>& ,,@-
D+$5,90!;>4>& D+$5,@-
A90!;>4>& D+$5,@DA-
?49:;>4>& ?D+$;
4 AA%3 L>4& 441M
**)4
44M&31C>&&3
/111*!=-1111*
1!/11-4-1. xsampall . xsampall2!=--11,
*11)M=,-14))F31,1!
M09$9'7;-)(9'7!;
>4>& +5,@C;4 %3 L
>4& 441M344 M& '
)3M0)
#
9M%39M&OM9)>G,-
);;>&&3084 M04;
Voilà!
Converting json files
D1G1.1)...4*,pretty*!*
),.11).-prettyI-1)4&(&/G1)!/
)11*1-4...11Jfile_Make.!
1.E1..1*..1,147
L>4O9M)&->4&-3%;
/. 1* - G1 1. Jbin_File . )1
1E1)!1)EG1.tb.json*4R1
)1*4)4
L>4O9L4G49M;-!;
Performance Consideration
?43C21,3C2F34*1G17),.F3
!/111*1,-7,F3.1!.1*11)
4-4-,1L,1)*1!
&(&/4-E11,1*1,F31-.*1,
)-,.-E,147!<411
*L,)1F3..11)1L!/41-)
11*)1--E,),F3.!
/.1).*4R!@11)14)4
F3>81)14*1-.-41)111!11)
1)4F3)!F3)1*1-)G1
1*,-4-E,!7,)1)4)1
7**141!
<41*E,7.7)4..)4&(&/1)41,F3)1F3
>81,!3,1))4)1)4>81!/1)1
*41)1-414*)1!
Specifying a JSON table Encoding
@*..F311,11)>&8(!@1..1
7*1-.1)G1@3&!/1)1>&8(1,4
F3.11,>/>/'>/ !
/1*.4I,G118@/@:&=@;3(/&(&/*!
Retrieving JSON data from MongoDB
&11.1K3C2K)1*,?,811KF3HE13,*
1!/1*14*111.&(&/-411?,8
-17*F3.!/1*1.,*4R.!0-1
3C21)1)-1!
3&(&/1!'1-*11)411?,81!/11
**1.?<)4*1)!=-F3)111-
..-4
!
,?<1**1111).?<)1!
1)41*.4,?,8>;-,)!1
3M4(9
490:;)-
4&9'0;)-
4O(91!!;)>4>& 4OC;
4 %3 L& 4(% 7'0
4 &@""@0=!'=
11file_name*-1*)4connection*!1>;),
. ?,8 1! / tabname * 1 .
?,8-)1 dbname *)1
)1,.1)1!
/-4-E111.?,81L&(&/1
1.4-..!/1*11L,)4?,8*1,)4&(&/
1)1*.-1!&(&/11)11.-I4
11J-1!/1-4E1*11)1.F3
)4*)1141!
=--E),F311,?<)4*1*.-4!
NoteF3)11,?,811*1*.?<*1colistfilter, driver
pipe!/41)?<)*!
/ 4*1.5.,65L,6.1.D-1 1!
111**14.contact.ini11
$,
& $
>& 4(4
0')G4$)4
4% %66
E43 .0'1!
!.7:1/0./!
!/=!!/!:'/
X
& &4
>& $
4 '."!0"'.<7
/:4
4% $4
E43 .7!'1
!1:11==1/!
+N'
& &4
>& 5%
4 !<"''"0!!1
':1$)&,
4%
E43 X'0$D
&(&/4-1)-..-41!
"
Column layout
/.1-41,1),*11),E414-
1*4!111)
9
9'/;> '-
&90!;-
>&910;-
4>& ??""####-
9/:;-
4%90!;-
E439<;-
9'/;;
4 AA%3 >4& 44
/-14)11*.4 > '!@
1 1 . E41 - - 1*4 111! / 4*)
*,E44*,177*,
.E4!.1
-&-4-4%->&
/11-1*4.).!
contact name hired city tel
(; >22 114H1H?7 "!$!'! "!'
D(2 3 "H H" !$!##!'
>X 3 HH 2 >22
4E41.111)UE41 711
1*41.-11)*1)E.JJ#.
1..1/>22!
@*1)**1)!/).)*1
4!/411-1,111)
.11.1!
Note 1D1,.14711-1-))-1
- ) * 1 . 71, 1! /1 .-, - 1
I
)3>& 5%8 +N'
449->&;()9+N'-5%;
Note 2111*18(2(/(11E4--
1!
Row layout
/),.)*1.1.1E41
11! * 4 .1 4 . *1 1 1 win.ini . .
D-1 4 my.ini .)--4)11..
E41!111-41,.1),-*1E4
-11)1E4E4!
11.)
9
"
49'/;> '-
O%&9'/;> 0-
()910;;
4 AA%3 >4& 44
344 %) ,8
115246*111*4.&)4.4,1
),,)4T&.-4 1*4!/1.1).41!/
,*,1,.!3*.4> '.1> 0.
E4!-1-E4!
C>&
D1*4.-,1
sectio
n
keyname value
(;
(; .
(; 11 11
(; 4 114H1H?7
(; L* "
(; "!$!'! "!'
(; '!#!'!$!'
D(2 3
D(2 .
D(2 "J J"
D(2 11 '$,111
D(2 4
D(2 L* "
D(2 !$!##!'
>X 3
>X . =4
>X JJ
>X 11 $;!
>X 4 2
>X L* D
NoteD*11,)11)...X)41!),
.,41111L)11,.7*
344 4E '/N
"
External Table Types
1148&F8&17141)41
1)4*1)E13/@2(!>1-***1!
/14*1)111)1),,)11!4*1
4*
ODBC / ) 1 11 )1 . )1 , 141 *, 8&
!8&11.?1.14)D-1!27
1)1*1*.**,8&11!&4478&1
1**!
JDBC /)111)1.)1,141*,F8&!
F8&11*F**4)1)4F**1!
>1,4.&&OO**11)1*11)),1
.7!=-1)1,F-**1111.)-&OO
F8&!F8&1)*.1*,1411!
Mongo /11?,811)1?,8F8?,8&
8!?,1)-?81)11**,FF8&!=-)1
1I1)?,8&8)1*1)4*1
4))41)1)4-*,?8.1!
MySQL /14*1*.-411)1),,?43C2?8
1!11?43C2@117)!(,1))1,
(8(;@/(8B*,11*.4*114)4&(&/)11E1
*11)11)1),,1!
PROXY41)41)4*111)1.)!
(#)#
/. 7) 4*1% 8& F8& ?< ?93C2 % 1*.,,
.-,.)
! /1!/111*.&(&/*!
! /)-11!/1)1*.-1,1,
1*.&(&/*1!
! /.1!/1)1.&(&/.1,14
?8.!
/-41-E11)41)1,71)41,
3C21
$
),7,I4!/*.1114
17*11,!/11*
.)41!
/11-4.3(2(&/I1&(&/11 cond_push ?8.
7. D=(;( 1.,I4)I41
1!/111I4,))41!
=-)!11)&(&/..1*11)4
1*.4-11!/11)41*.4,1-3;&8(*!
7*
A,$))&K AA$#D XXX
AA 'connecton string
$
1I1,@.1.?<!
"
,A?G 3%)%-)9C;)&>&))&
)3%3%
8,1<;>91-))41,1)4.
1)E!
/14)1)4,3;&8(*.5*)6*.I4D=(;(
11E11.)H)1)1!.?81)1-1
countrycustomers!3**1,,I41
ACG,))&X5,9)% +N,)% +;?
)&Z7
=--ED=(;(1)13;&8(M/4*)1
! D.!
! D)1.1!
! =-E---)D=(;(1=@0<1!
/.1*)11)4**,1.-11!/)7* 3;&8(
)1
,A?G 3%)%-)9C;)&>&))&
8T)3%3%(4T
/ %s 3;&8( *1.*)*1.,I4 D=(;(
1!.3(2(&/I411*.4D=(;(1*)D=(;(1*
1-).)4411R!
/ *)1 1) 1)4 , )1. 11 )
1)1411J11,,,.11)=@0<
1!7*11
A,$))&K AA$#D XXX
AA 'connecton string
,A?G 3%)%-)9C;)&>&))&
8T)3%3%(4T
D 4 )& C)9C;)% 3%
/111*.)4@2@3*)1*1!1.1H1*
1.1,
! /11
! @I1,!
! @TV,111*,,,.!
$! /!
D1.&(&/-))EI411
3%)%-)9C;)&>&))&
893% +N,3% +;)3%)%(4
)9C;Z7
Note31,?43C2?8*11=@0<1!
11*)1*.1
D 4 )& C)% 3%
Note@*711 =3),1*.4-*1*1-
!/)1*.15D65D=65=65=D6!141)1)4.&(&/
"
1. 3;&8(!/41111-3;&8(14
=@0<*-=@0<*1).D=(;(*-
I1,G1!&(&/*.
4*!
Note3;&8(1).?<)1)-4.,171
1)1?<)4**!
*+##), &
ODBC*8)1&411@.11,)1,1411
8?3! &(&/ 11 1 @ 11 8?3 - ,
*1*.**.!@7*111?43C21)
1,?93C2)4*!
Note2778&1)1!
CONNECT ODBC Tables
/1 )1 , 4* 8&! 7* . 5&116 ) 1
@11Z)14.-11
A)&9
A)&?(97;-
A&3%&(9:!;-
A&(91!;-
A4(91!;-
(9/!;-
A4%(9'7;-
,4(9'7;-
DA(9'!;-
A)%(9'7;-
D(90:;-
G(90:;;
4 %3 ?$AO4E '!& A)6
&
A4 ? ??$* A@"D&
G4"4>>>4">>4"'!11"GDX??$
/)*.1)!1I.1)1...
.&(&/)!1.11111111!
. )1 &(&/ ) 1* 1, 8& , .1
.1)1*.!11))1*41
A)&4 %3 ?$A
O4E '!& A)&
A4 ? ??$* A@"D&
G4"4>>>4">>4"'!11"GDX??$
-1*11.11,-1)41.
SQLColumns.!/1)4*11**)41*
-1184*1*,#!@1111.111
).1!/1-444-114,)1
4)4show create table1.41*.1!
/2&X:3A(1*.-)11;-13L-,-1.
8&)!@1)4,;-13L,4.,*11!
"$
.41*.41*1.4)1711
)!=-4),.114,
.1!34*11).***!111
1*)(?29((4.4)1
&39
D&497;)-
G+(91=;);-
D5H(9:;)-
5,?-
?D&491;)-
L$A+,#(9'7;-
,#)9'0-0;);
4 AA%3 ?$A& D#6
4 ? >44
/1.,1;3/:@?(2@3/:@?(F:&8(F:<;@8(1!
*1 >22:@?(1 . , )1 *1!/ 4*.
=;(:8@/(-1,.timestampdate4*.8(/:-1
,.charinteger!
&4111**48&)1
! &14*1.-41I,)4.!
! 1!$7,.8&)11*.4411E4=-
)1&(&/.-1I4117,-
)1)41.1**1!;7,1)-1!$
! /11.&(&/8&1**13(2(&/3(;/!>8@/(8(2(/(
11**1-1-41)-!*118&)
-(B(&3;&*1)-41**1!
Random Access of ODBC Tables
Note&(&/1!8&)17)!01!$17,
.48&)4*!
=-1I1I118&)U.1-1G
)1)4I1**,,)1!
/1-41)*111&(&/8&)!/4
*,.-,)*1
Option Type Used For
E:3L , 3*.4,-11L!
?4V , 3,114!
3)V >1,1)1!
V/)1*.*:1!
D,-1)11*-4)1111*.4-11L
I.,)1L111L.*1--111!/1
1-114.1 (/&= &(&/-1..
*1111!
@-411411?(?;9*!/1*)1
.-,1
"
1 41.!1-)11I413(2(&/11
-4D=(;(11!
2 ?41LI1,.11I)!/41
.,11I!/)-1.4!/1
)1-)-)11114111)H1),
,).G!
3 @.1I417,111L41!1.
.11I,!/11.)1*11)!/1)11.
;8(;911-?811*1,!
)1-4;8(;911max_length_for_sort_data )
,1.1 $1*41!I1114)1
*4-D=(;(111!/11)11.
;8(; 9 I4?8.1411I411*1.
1!.1).11.1),!.),.*1
15,614*111?81.1.
)!.1,max_length_for_sort_data)1.1)1
-E))).4..11I ?(?;9
*1)1 !
)1,)14*11)41E4)11)
1!11411-).11*.4,1
1*1-11)4.1!=-1)111**)4
11!
D&(&/1!$-4*1111*.411)
7!/11)4-1*,.1)117!
/1)1.)1,)14117,1
)4?93C2)4*)4(8(;@/(8,!
/1*11)41I47I1.7)
1).1!11),
&>3/?(;))1
A)C>&)&
-41 custloc ..1)1 *1* customer )4
1)4D=(;(1!
Retrieving Data from a Spread Sheet
8&1)1)1)1)),,(71*1
HA
4 AA%3 ?$A& AA
A4 ? G4?$* ?@"$"?"A$D
/11**11)L.1,11.1)
&/@&/1,5.6!;.(7.-1*.4)1
111!41E
C>&
/1-7.(71*4
Nom Fonction Societe
11 "/
?1 03@<*>?
;4@, ?1
"'
8 =,
/,4
8
0@ <8
/1D4 (*@111
/18I @118<1>;33@
/1, ;1*1)381 8(B@&2
=14 ;1*1)81 .&,
2 8?E,& 210
212 ;*,81 @
?1( ,
=,11*-1.&(&/-,)!
Multiple ODBC tables
/*.*))78&)1-4*414*1)4
.1.1(7@11)1!/11,.)1
.8CRfilename--1)1.*R)1
.!1)1(7.1&@ $!71&@ $ !71Y
&@ $ !71))411
!:&) 9? 9'.;- 34 (9/:;-
?4)9'7-0;-A4)9'7-0;;
4 AA%3 ?$A&)43 '
P R& O)
4 ? G4?$* ?@"$"A"A0!!:C
,.**4,.141.(71)5)E
6!/1718& 11** multiple= !/ qchar *-11*.
E.1I1118& * -)
1)E13C21471!
Caution@11,)1),,4,?81?43C2
8&!/14-E11)1!
Performance consideration
/7,)1.8&1-),4*11&(&/
715*)6*.I4 D=(;( 118&I4!&*)
11)1)41!*11,1.1
E*)114...1?81..
147!.111,1)H111E**!/1-1.7,
1!
/E-11,1,1)144E--4)4
11111111!)E4I11.1
-1*11,1,11111*1!
Using ODBC Tables inside correlated sub-queries
>E1)I1741)I174
1!1-8&1WI4W!31)1)4&(&/-1
*,1,.?(?;93&;22@2(*1
"#
Option Description
8. *,WI4W)41,11H1),
I41?&1
?4R 3,1141?93C2)1!
3)R91 >1,1)1!
Note?(?;93&;22@2(*11)1*./:23/!
1)111111EI114,7*.1)1
11*).),)1!=-.)1,4
1)1).1.1.!.11**)4
1,1)111,41-1,4))
4*)1-1)HI41,11!
.111.1)1L11*11)1*.4 2&X:3A( *I
1,4,11!/-11),.1.)11
41-,4,1!
@,-E1*-1)HI48&))4*4
.)1?81.)*LI4*4.17!
Accessing specified views
1.1*.4,1)/@@?(*1*11).
5-6-1.1,-*3;&8(!1
A,$))&9
)%(9'7;+-
)&49/;+;
K AA$#D ?$A$ANI '!
AA ? ??$* A@"D&G4"4>
>>4">>4"'!11"GDX??$
,A?G )%-)9C;)&>&)&)3%
)%
1*4)1&(&/.
A,$))&K AA$#D ?$A$ANI '!
AA ? ??$* A@"D&G4"4>
>>4">>4"'!11"GDX??$
,A?G )%-)9C;)&>&)&)3%
)%
/-7,.1
C>&))&8)&Z1
/*11,.,*)41)41-14,11
-4-11*.4!/1
country customers
L "
<4
?7
3*
>X #
"
>3@
0L
$
/1E1*11)1**111G,1)1
7,*1,11!/1L11.,8&!
CRUD Operations
/4 .4,*1 3(;/ >8@/( 8(2(/( 1!/4)
7111.44.1)1)1J4!
INSERT Command
D1,18&)1118&)!/11
E4..-11)I411
44'C>&0
D18&) 14.)1711!11
1,-418&).!
&(&/141**3(;/111
44'()90-?);)34O%)3& 8
5*E4*6*.1,-1.E41
*!
UPDATE and DELETE Commands
>E3(;/>8@/(8(2(/(1**1*.-4!41*)
11**U&(&/11**H)111.
*11,,!
/11G1*11*114711
.7!211**1-)
49
449.;)-
&(9'0;)-
4>))-
&(910;>));
K AA$#D ?$A& 4
AA ? *41?)? P41
A5, )><?A5, )><
D**)4
444()9'--89;-G4;-
90-G-0!'06!=6':-;-9:-4-'./<6!761!-4;
/.now()-)7)4?818&)!
211-**1-*,)!.-1I4
+34& K43484 '!
&(&/-*11
+D?4& K434X5,4 '!
""
D1G1*))4),)E
E1 )E11. I,1. C>/(8 11*.!/1
-)11)7)4!
/111*).1,*1*1,1111
51Y. *.Y6 1** )4 11!=-1
111)1-41)4?8!
! ?81E-))!/-)*11.-)
74!/.11*?43C2147!
! ,7)41*1111*1
147!
! @.3(/D=(;(1),11!
$! ?*H)>8@/(8(2(/(1**)4&(&/!
/11*11))1)?811,3C2,,!41
14)1.1*.3C2,,!1E4-1E<;(
2D:;;/9-1147-41!
3.11)..-1411.!1
)344 89;84 0
/1 - -E - 3C2 1 , 5E- 1 .6
11,!1*14*1
)344 98;84 0
/11)4)*11.1.->227)4?8
1-7)43C2!1),1)E41
111*4*1.17)41
&(&/*11*.8&)1)4*1)-!
Sending commands to a Data Source
/1)1,1*1)4*.8&)1!21117*
49
&&(9'0<;)-
)&497;)> '-
&(9077;> 0;
4 %3
4 ?4( *41?$A
?4(? P41XA5, %
344 '
/E4*111(B(&3;&*.!
/(B(&3;&*11)-)111!?1.
1111!/.)111*.4
)7 ,1 .7! /. 11)1
)4.,.2@<
Flag=0 /7!
Flag=1 /..-1H1.1)..
111!
Flag=2 /411,!
=-11)1*.41M47,11
C>&48&& a command
/1-11*.D=(;(111.1
7!/147.D=(;(K11)7411-)!1
C>&48&&
A,$49
?434&%O%)4&-
&9'0;)-
4-
&(910;;
/11
command numbe
r
message
&;(@/(/@2(8,*4E4Y @..-1
--18&)-4)
A,$4
K AA$#D ?$A& 4
AA ?4( *41?$A
?4(? P41XA5, %
A5, )><?A5, )><
D**41,1**3(;/1
4449&-4;()9-0!!76!/6'0;
4449&-4-&;()9G-+-?;
4449&-4;()9)-'..<6'!60=;
4449&-4-&;()9L-'./<6!761!-;
@11
C>&4
I
D
name birth rem
/ H'H >22
>22 8
/ ""HH # >22
$ F "'HH 21
@4.1>8@/()7.crlite)
C>&48&&
)344 0!'06!=6':8? 0
/11
command number message
*1)RN H#HN-8R
@..-1
21.4
C>&48? 0
I
D
name birth rem
H#H 8
/147111,41!1*11)11
147)4.,1*11
3)&9&(9077;;
?G*?
C>&48&& &
-411E1
&934;
/11*11)4-1,1,!
Sending several commands together
<*,11111471.1)141.
.!/1111.-,147
C>&48&&49
)344 0!'06!=6':8? 0-
)344 0!!.6!<6'!8? 1;
D11171*1..1
!/.n11*7Rn)4.*1!
Note 11*11)1*.4 3;&8( *-, (B(&3;& )!-)
1)4.-D=(;(111*.!
Note 2?111-1,111*)41H1!
Note 3C1111)1*!/1))41,..I,
1
Note 4/11)41147!
Note 531**41*.)1!=-4114)-
1)1),,)11,147schema!tabname!
Connecting to a Data Source
/--411)11
! >1,3C28&&3,
! >1,3C2&8383
/.1-411&3,-1*11)-11)1
!11*-4)4.&(&/11!
/1-411*.-4-8&1G1,.831
).8&>78&1114.1)1
!41*11-)1*..831*.!
(1) Defining the Connection String
>1,.1-41,1)1*.!/11111..1E
-,8&)1)1*,*,141111,
-4..!
/..8&&3,1
464@@ &3%644)4)464
&3%64@@
4)@@ 4)6O%8 4)6()?,B, _4)6()`
4)6O%8@@ ?+?DX?4(6>464)6O%8
4)6()@@ 64
4(6>464)6O%8 44>4
DH1,1L1U.11U)H
E4-11H11U)H4)1H11U.83E4-
11114.)E1!81,,E4-1)1
1_`abUMVRe+1)!/. 83 E4-
114.)E11,)E1!1.,.141
.E4-111)E11d!@**1
)1).8;0(;E4-11)
11U-1)1I!.)
11)11)41)*.
1,!
ODBC Defined Connection Attributes
/8&.)1
DSNH.1
!
DRIVERH.!91183H111!
FILEDSNH..,)1!
UID/PWDH41*11-)1I1.!
SAVEFILEHI183)111.!
)183*)1!/1,,.
8;0(;.183.e1*,11
.1*1!D1*.4,.8C.1,full
*..,)!;.1*.8&.
7147.1,!
(2) Using a Predefined DSN
/11)41*.4,*1*5>1836141!
1,*15165*11-6)*41*.*1
D, 1 1,G1 1 . *. 83!
1
A,$4K AA$#D ?$A& 4
AA *41?)
D +? #-+ &-D8 &%3
Note1 11)*)4583R6!
8& /)1 27J>7 1 8& )1 4 - 78&
1!@44-8&.4.,1N1*!7*
.?33C2334)14-/831!
?E11,41I1441I11*118&1
.,8&1!
91 83).*,.!9-831,
8&@1D-18&@78&N1 <> ,)4,1
.)!.!
.4,27J>7-1,/@2(:/9(R8&
A@''!7)4?$A?4(A34
")"%"4"4@>4>)
91E11,41I14W41IW1,*11
8&)4!**.1,*,11
.71,1*)43(27.,!
/411E.,*11
)6)&%P")"%"4"4
SELinux
3(2711*)1!.4E3(2711,*)1E141,
!,!JJ,J11,1,!,!JJ,JJ!,!
mysqld can't load some executable code, so it can't use the ODBC driver.
(7*
A@''!7)4?$A?4(A34
")"%"4"4@>4>)
@,
%3 BA& )49'1<:<.!!<7:!/@=/;@(@V4V_)`
>34 ':11&& R&%PR3 R")"%"4"46
R( &6!4 10=.0'0
)>4)@%&@&%P@! )6
>4)@M@)@! >4
mysqld can't open TCP sockets on some ports, so it can't connect to the foreign server.
(7*
,,,'0./95#!!!;@K'=:)4?$AG?*
(+)>&AA
@,
%3 BA& )49':01!.:'=7'!.@:11;@(@4
_&`>34 1'.1&& R&%PR ':116
%&)@%&@&%P@!
%&)@M@&P3@! 3O
ODBC Catalog Information
1 . 1)1*,1. 1 8&1
.)171,11)C>@2(;D(;.11
-&@/@2<3&=(?@11!
&@/@2< 1 **4 4 1 )4 1 11 ) 3&=(?@ .4 D(; 1
1*18@/@@3(..?43C2!
/111.111*.111..11
-114..5.61!1516
5)16111*)41,111!311
)1.!
$
&(&/..1--411*.41.
! D1*.8@?()*1,)48&)113&=(?@!
! /) 1 ) 1*. 1 5sch.tab6 5cat!sch!tab6 -, 1, 1
,.!
D)1I.)1*8@?(!1
Tabname DBnam
e
Description
1! /).11!
1! 4) /).1111*
4) /).4)1
[
=19-+6,:5
-------|-----------------
>='
?='/
@='
/,)?8*1I4
CREATETABLE5
1'615
,4,ACONNECT
DEFAULT'61!,-A"
CONNECTIONA;B!AC+813B!;
-2,3-CD,A;+B';
B-3'61!,-A
-,A;C+813!'6,-;7
SELECT<6,:5FROM7
E--------+-------------+
==6,:5=
E--------+-------------+
== =
=F= &G=
=F= &G=
E--------+-------------+
D11.-411.1?81)..
1 1, K2!324K )! @1 1 1 *,
14-?8&!
/1*..1,K2!324K)4*,4*,141
1).4N7*,111E43.1-
**41)1!
Using systemd
D271)1114141)1
.1411N.KHH)K.!
/11)41,K,)K)KI!)JK!1
U(4")"4"%&"%&"&4(4
(4
(4& K K,K,#X<<<7.D'
111411
U%&&6
/1?8
U%&&4(4
9-***1.)1
SELECT<6,:5FROM7
'
E--------+-------------+
==6,:5=
E--------+-------------+
=>='&=
=?='&/=
=@='&=
E--------+-------------+
Using Windows
?1.D-11N,)1-4141127)1
I41K2!324K)4141!14
*H**1K'K-1*,1!
41K!K1)!1
KK,K,#X<<<7.D'"&
NoteK G " .< .*KHH0"H"$HB,#
L/
-
' *+##), &
/F8&) 4*1) 1)- 11. ?8!=- .
*.1*11).GF8X-11*1).
1.4*
'
11!
/1-I
! /F38X114141!
! /G-**11.1)4141!
! @.11F8&171)1-,8?3!
-1-111.?8!
Compiling from Source Distribution
(-FF8X1)1 &?E11 .-
11!127FF8X*E,,)1*E-)4
&?EE*.11,11,11
&?E(J1J1J&?EJ?1JE,=3@,1!&?E$11,
K&/.F11,F:F@;:(B(&>/@2(F:F@0@&:(B(&>/@2(
KF:F@0@=:(B(&>/@2(F:F@0@8&:(B(&>/@2(
D1***F*.71-*E,-1!1
>)-1),
3LB5 ")"4"M(&"M(6<6
@.*.&(&/F8&4*-1111.4!
Compiling the Java source files
/41.G-**111111F8&1!11)
4&(&/14!
'
/ 1)F).!G.1 F).-** 4!-11
1)FD**1!G1471,-**1!
#
/.-**F).111-4,1
8?,!,&!-**1)834
*,*,!=-41)*14!
/)-**1
Wrapper Description
JdbcInterface >1E-)11-4!
ApacheInterface 1@*H)* *E,1.)1E,
18&11-4F8&1!
MariadbInterface ?E1?81!
MysqlInterface ?E1?41I1!?1)1-?43C2
*111!
OracleInterface ?E11!
PostgresqlInterfac
e
?E11,1I1!
/-**1)4.11*.)4connect_java_wrapper111)14
1wrappers/JdbcInterface!/-**1.)1)1*.*1
1wrapper*.5)611!
Note&.,G,1,111*)4G*E,-11
1*!=-114.&(&/1*E,.111,!
/F).-**1-411)-F1*14!41)1
1-**4*1F).!G.1*,4-1
* 1 4 11 * . F0?! ; 11 1
FD**1!G 1 -**1 , 1 1 )4 ?< ) 4*!
/.1-4)*!
&*, @*. -** I1 @* H8& *E, )
1!-**1)14-,F8&11))
-*,!
1,7**-**1IG.EFD**1!G.
)41)!
1,G.*,41)1*)11*.11*!
8*,-1141411.1),4!
Setting the required information
.4*-F8&)&(&/1L
-E-E,-F*11)!/1-11.
! 2,44F0?)4!
! &,F0?!
! (1)1,-G-**11!
$! &,1F8&!
F0?)4114E&(&/*,!/11E
*11)1&(&/*,1)*-F8&)4*
-F38X11!-1111F8&)4*-)
),1F38X))&(&/1,,!
JVM Library Location
.F0?)4G!D-1)G!127-1*1)4
*&(&/.1)-1.!/1**1*
27-F*E,-11*!
.F@0@:=?()-17*17*)&(&/11.
1, 1 .! -1 1 1 * ) 28:2;@;9:@/=
)!11*)1E,)1*
271*.4..11)1*,2711
1!
/.&(&/-,))connect_jvm_path11.!
)1-1,11*.-1before the first use of
the JDBC table type!7*
M(&3 R")"4"M(&"M(6<6"M"4"41</"4R
#
M(&3 R")"4"M(&"M(6<6"M"4"41</"(R
14)ID-1)1*F0?)411)
.,14!
1)41&(&/IF0?!
Java Class Path
/111.*1F11-,111!D&(&/111-
)G-**1111-11F8&111
,*1G.1!.@*.-**1)111*11
G11)4@**E,!.?<)11,?,8F8
-)111*F8&*1!
Caution/111*1*111*F0?F0?-,
).114**4!)1?81H,
**1F0?)14-)1,..?8
1!/.)1141).41F8&)4*..1!
-1-)*41-11!
/*-**1111*4,wrappers1)H4!.
F).!G.-11*14-1.-)4G.
!1-)11-*1*11!.41.1
1))1,J4-&(&/1.1-
4*F).!G.!
)41)11,)1G.1)1*,
4-1*1-41411*)F0?!
;,*1.1F8&141!;)
*1G.1.!3**11)&2@33@/=
!11*)4T27)4TUD-1!
.&2@33@/=)711.1)1?81.1,!9E
11,>8.*)4&(&/1)1
>)4()4&
(9AD5;
#
/)411.1.!/1)41*L
)11.41,!
"
?1.1 - 1 I.1 11,!/. &(&/
,))11.!/*11*.1)-)
*1.4.&2@33@/=)!@1.G
*1)connect_class_path1)1*.-1,1)1)1
).1,F8&)4*..1!
/41IJ11*)4&(&/),,.11*!
@17*1-1?8-,1127
+H@1*H" fS1J1JJ41IJ)J41IHHH1HH.H1,H
,R41 HH1E*H) HH:G:*RWJ1J)JGJGHHJGJ)J'J1W
HH:11:*RWJJJ)J!J1,JJJJ3>;&(J41IH
HGH'!! J41IHHGH'!! H)!GW
CONNECT JDBC Tables
/1 )1, 4*F8&!11**1,4-11 boys )
7)1,141*,F8&
%9
&9'0;-
4%9'0;-
4-
4;
/111)F8&4)11
M% 4 %3 L?$A& %
4 M@&%P@"""&F)
/&(&/*1>;211)1-1!1
147*178?317*111
?43C2?8)11,?43C2F8&!
@1.8&1.)-))414*11!
/11,.111.8&!
Note/)>;21*1.48?3,.!
?43C2?811.)1.!
Using a Federated Server
@4F8&)1*.41*11!1
1**1,4)11,71,1I).1
M))44 %3 L?$A
& ))4
4 M@3P@F) 3Q38 38
91
(3'>48333P349
5-
?$-
+,3-
DX,?38-
D,!-
AN-
X,3;
-F8&)))4
M))44 %3 L?$A6
4 3'& ))4
)4
M))44 %3 L?$A
4 3'"))4
41.)),1-,
)11,11!
F8&1>;21)1!&(&/1>;2..
11.->;214711.?43C2
?81,1I!=-18?31111..>;2147!
111*4*=3/.)4I>;21.!
1
(>4833349
5M@@4@]@'70'@-
?$#-
+,%&-
DX,?&-
D,!-
AN-
X,#;
-4)-1,E1
&34 %3 L?$A6
4 "5,D#
Note11,1I1118@/@@3(1,1)1
)1*.&/)1..!
Connecting to a JDBC driver
D11)1)4F).-**1111*1
*-,&(&/F8&)1!1.F-**
1111)4DriverManager.getConnection.E1,1
URL /1>;241*.&(&/*!
User @11*./:23/>22.1*.!
Password @11*./:23/>22.1*.!
/>;21*,8?3!;..1*.F8&
.1*.1471!>1*11-1)1*.*1!
-)1>;2)*..4*,8?3!
?43C2111-)1.!=-.1,1I11,
1)1*.1,&(&/dbname*11)T*))4.!
1)11,1,1I)F8&)-11
1
M'4 %3 L?$A
4 M@3P@"""&& 3)4& '
344 + &-D8 &
.*1)>;2111*11-!@11,
.11T1T*)1))4)4
M'4 %3 L?$A& '
4 M@3P@&F) &Q38 &
Note*111.F8&)G.1LF8&
)4,A>&!111.48?3
).11*1. M(P?4(!/1)
1*.1*8;0(;)**1!=-1F8&11
1$1.H,I1*)1*.!<,111
1I17)G.!
/-**11111I1111!311.1
1-**11*.-,)
Scrollable
28
8114*Rforward_only41Rscroll_insensitive!
Block_size D)111.1L!
Fetch Size
/. 1L1). -1 4 )4
-8?3!1.*1F8&!1I.1
1).?43C2?81!
/?43C2J?81-1)4I4E*
4!/11,4.111)-,,11
EI4.-4717*!
/ 1-11,), ) 7*, ,1 11411*.4
2&X:3A(*4*)!=-*)1
3**147I411
4-&-3>&M44&4'!
E-,1&(&/118?3I4
A4-&-3G,4
1I4big),),-1!=,41*.)E1L1
-,)-**G11.1-11*1!=--1,
11?43C2J?8111-1)4I4!
/. -**11- 1,111I4 1*7
,!
/)-11.-E1,.1*111.?43C21
-E.-111-1.?8**4,1!
/,-1141?43C211?8)11!1*11)1
1),-).7.11.1!
/)1*.*1!
Connection to a Data Source
/11G*.-41)1)11E**.
1)H1-114!/1E11)1,1.1
-1..1!
&(&/*-**1FD**1!G.!/1.
1.11&(&/14!/-**1)1*.
,))connect_java_wrapper-.15F).6!
1)1*..)*1)41,* wrapper 1!
1
M%
4 AA%3 L?$A& %
4 M@&4@"""F) Q) >
344 X33 4>- '
/4)11.1F).1,11!
/Apache1111*)4@*H1H)* *E,)
1-1,1*,11!=-@**E,
1)11IG.111)11*
! &1H)* H !!!G
! &1H* H !$! !G
! &1H,,,H! !G
Note11)1)..41!
/11 11*)4,F8& !/ 4 .
-**1)1-41IH'!! ?81,3C2!
>E11*1-**),.F0?-1
*,IG.171,1*.11*!
Random Access to JDBC Tables
/111).8&)1)1-F8&)1!
1.?43C2?81)144-
1141,?(?;9*-)-1.4!1)
1*.41.1)1-!
Other Operations with JDBC Tables
(7*.-1,11*.)4*1F8&*1-
8&)1.F8&)11-4!;.8&*E-)
@11,1*.-13;&8(
&;>8*1!
3,11!
F8&,.!
Note3F8&1.-,) time_zone )1),1-11
**1-11393/(?!.11),1.1
4&E b0@!!
JDBC specific restrictions
&,1174.11,/11**4!
1111.8&)4*!
Handling the UUID Data Type
1,3C21>>84*411'!/113C2
?84*!/)1-1)41*1!
>>8-)1&=@;'-.111,14!24
1,3C2>>8-)E&=@;0@;&=@;!(7*
>1,1,3C2)testuuid7)1
c3)4))4d
A)&%3?>)
66666666b666666b66666666666666666666
4))4))4(:9;
&
1.1)I)4
3M))44
%3 L?$A& ))4>) )&
4 M@3P@F) 3Q38 38
&RR-)&&RA)&R-%3
R%3R-%3&R&R-)&4ER4ER>&M))4
/1I41
Table Column Typ
e
Name Size
1
$#$'$#
1
1, 7 $#$'$#
Note1,3C2-1L1.1 $#$'$#-1*).
?8!&(&/ , . connect_conv_size 111 )!@1 .
/(B/14*1 3C2:0@;&=@;1.H3C2:/(B/!
@11,1)F8&)4
A,$M))4K $#D L?$A$ ))4
AA M@3P@F) 3Q38 38
-))4141
A,$JM))4J9
J4J91/;?G++-
J&J(9<'.0;?G++
;K AA?G+A5, 4'
AA M@3P@F) 3Q38 38
J$#DJ L?$AJ$J ))4
Note" ),:::1L:!
2N1**
$
44M))49&;()9G4;
44M))49&;()9;
C>&M))4
,)@
id msg
$)#H$H$H#H
' )" )#
1
'".H"$#H$"HH.#$. #$ 3
=id1.8(@>2/.1,3C2-11*.1
uuid_generate_v4().
)1.?8!1
44M))4
()90><17><6=1!6:0>16'16<7101<>-4;
44M))4()9+-);
44M))4()9-&;
C>&M))4
;1
id msg
$)#H$H$H#H
' )" )#
1
'".H"$#H$"HH.#$. #$ 3
..)H#)H$ .HH ). 1
PQ
.H''H$)"H)#H$"#'.$
/.111*.1>>8*1!/11>22!/
1, ,1 F >>8! >8@/( 1 )4 1
1*.!
/11)-E
C>&M))484 0><17><6=1!6:0>16'16<7101<>
>&M))484 0><17><6=1!6:0>16'16<7101<>
=-1.1
C>&M))4844OT:0>1T
34,
'0./@K'=:)*)%@3P)4D*6
34@
,,,@34@))4ee)O8
4@33&6
)&%3
)1&(&/:*1.D=(;(1I411,3C2
A4-&G,))4X5,4NT:0>1T
2X(*1**4>>81,3C2!
/1-111)-1&(&/ connect_cond_push!*1
1*.4.:*11).&(&/.1)!114
7
3) !
8,1-1-)7)4?84I4-.4!
Executing the JDBC tests
1171)41))1I,1-EL,
*,141)G*E,F8&18?3!
/-.jdbc.testjdbc_new.test11,?8F8&1
.G.1*.1!/41)7)-4,D-1U
1*4,*–enable-disabled-,11!
=-27111.F0?)4!.7,41
7*F@0@:=?()1*.7.G17*
28:2;@;9:@/=,*F0?)!
&.*+###),&
Note@/1.1.1-4*-41)4-?81!
! !!/1?<4*-1)4-*,?8.1
-@&O6?AAX5K
31-)<@4!
3,-&(&/1!'!-)1)-?8111
.)1 #?<)4*-)1)-)41)11**H
,F8&!=-11.&(&/),1<@1)?<)4*
-)1).?811!!),,414!11*11)
).1,1111E-*,?8.1-*K
'+,'-39-63+4++
&11.1K3C2K)1*,?,811KF3HE13,*
1!/?<4*11411?,811)1!
@11,?,8.&(&/)..-41
! @1?<)?,8&8!
! @1?<)?,8F8!
! @1F8&)1,14)?,8F8&1!
$! @1F3)?,8&F8!
Using the MongoDB C Driver
/114).)41)1)4.11*.1!
/ *. 11 . ?,8 & 8 1 . !# )1 4 * *E,
,!D1)1
! 1))1?,8&8!
! &.,*1?8!
D11.?,&81)1-
)1*.4-*,!
'
D*11)11*.11-4)11IG*1,1
)11.11,G!
Using the Mongo Java Driver
/11)-1)1,F8&1**-*,.1.
! !)41)
"
!/4,1
! 1?,8F8)4-,1jar.!311)!.
*11)111!
! @*&2@33@/=) connect_class_path
)!/11E-1F8&1!
&11)1)4-F-**1 Mongo3Interface Mongo2Interface! /4
)F8&1)FD**1!G.!.1 .F811
1*.4501R 6*1-,)1!
Using JDBC
3.71,F8&?,81!
Using JSON
31*.*.F3/)/4*!
/.-,1)1?<)4*!
CONNECT MONGO Tables
&,,?<)1I1,?,8
1!
@?<)1.11?,8!/)-1-)
1!1))1?,81*restaurants4
1,11.-,
9
4(90:;)-
&(9/:;)-
)4490!!;)-
)9'/;)-
)4(9'0;);
4 %3 K& )
)><4 &@""@0=!'=
Note/11)4.&.4?,8&811F
.4?,8F811!.)))1*.)4
8;0(;*)1*.*1.1&!
=-.1.1)41F31!
/)11test )4.!/1>;11)1
?,81!/1-17*1*11
1-1.*!1..?<)11-
1*.4,!
>1,141)!/1)))4
4 %3 K& )
)><344 ( 6'
"
D)41)1)?<)4*1*11)11
?,81,(?!3@**7.1!
#
=5RH611411*14)G!D1
-.5R6))1
A,$JJ9
J4J90:;+-
JJ(9'1/;+-
J)J9'1;+-
J)44J9/:;+-
JJ(9/1<;+-
J&J9.<;+-
J)4J9<;+
;K AA?G+A5, 4'J$#DJ K
J$J )J?A5,J )><
1address1F3)Ggrades1F34!>E
F3)4*G11*.4,-F*11*4,F3
*1.!1
&->&4&41
name address
?1EE3* aW),WW#WWW_H#!'$!$$`W1WW?1E@WWL*WW$' Wb
D4N3 aW),WW$'"WWW_H#!"'#$!'' "`W1WW)1@WWL*WW Wb
;41;1 aW),WWWWW_H#!"$!#'##`W1WWD1#3WWL*WW"Wb
MongoDB Dot Notation
/1111)G411*.4F*?,8147
89
4(90:;)-
&(9/:;)-
)4490!!;)-
)9'/;)-
(9/7;>4>& -
)449'/;>4>& )44-
E4397;>4>& E43-
9';>4>& !-
49:;)>4>& !-
JJ>4>& !-
)4(9077;);
4 %3 K& )
)><4 &@""@0=!'=
&--->&84&47
.1,8141*.42*,4!
name street scor
e
date
?1EE3* ?1E@ JJ $
D4N3 )1@ J J $
8G ;41 ) @
;1
D1#3 'J"J $
;& 3-@ J'J $
/X1X '; $JJ $
MONGO Specific Options
/?,8147.F*1-&(&/1*.141!/1
..1)))4..-4!1*11-,
?<)1!
Option Type Description
Colist 3, *1*11?,81!
Filter 3, C41)4?,81!
Pipeline* ./&11**!
Fullarray*
>1-,-814!
Driver* 3, &F!
Version* , /F81.1
*/)1*.*1!
Note..1*1.?,8!
Colist Option
>1 *11 .. *1 - E, ?,8 1 1
1!.1 projection-,11!1
41.)1114)4&(&/!=-)1
-1,14_id-4-,E*
4 %3 K& )
)><344 ( 6'
4 _R3M4R@_R4R@!-R4&4R@7``
17*-1*limit*-E1E3C21!
/1*-E14-&!D1,F11)
4 _R4R@!`
@-)1*.-111!
Filter Option
/1*111*.45.6-E11-1)!3**1,--
)11E,(,11?
),-)4
4
4 %3 K& )
"
)><
4 _R3M4R@_R)44R@!``
>4 _R)44R@R4R-R)R@_RaR@RR``
344 ( 6'
@.-1E
C>&4
/1I4-
_id borough name restaurant_id
$#.. E4 /E3*&*1* $'
$#.""
"
E4 &*3* $#'
$#.. E4 /? $''
$#..#' E4 81-E $'"$
$#.." C1 3-) "
Pipeline Option
D1*11*.1)49(3Colist *1?,8**
**4,)!/11*-..,,1117*,41E
--F3)1!1
09
&(9/:;)-
)9'/;)-
4&)-
9';)-
49:;);
4 %3 K& )
)><
4 _R3434R@_Ra&R@_R)44R@RGR``-_Ra)6
84R@RaR`-_Ra3MR@
_R4R@!-R&R@'-R)R@'-RR@RaR-RR@Ra
R-RR@RaR```
344 D434 '
1**5S614.5S-61,14-)7*
8.415S*G61_idcuisine1,1
F*.,11!
&--->&0
8) $
/1I4*1
name grade score date
13E @ JJ $
13E @ "J J $
13E J'J
/1E*11),,1E--F3)1
&-(9;(>&0
)3%&(4(Z 07
&)1,,11grades4!
name average
4
& $'
<81
217 "#
Fullarray Option
/1*E2*14*-,)-814,
1*.4,1!1&(&/,.71,1
4!111?,8tar)4
9
A4(91!!;)>4>& C;
4 AA%3 K&
/.TV1--1F11!/111
Collection
aW:WaWSWW.'"")#"".".)WbWWWGWW*1W
_#!$!'! !#!`b
aW:WaWSWW.'"")#"".".WbWWW)EWW*1W_ !$'!#"!`b
/Fullarray*)1,,11*1.
prices4!
4 %3 K
4 _R3M4R@_R4R@!``
344 ( '-G)% #
/)1)1
A,$JJ9
J4&J9<;+-
J34!J)9'0-/;+JG?G,J 34!-
J34'J)9'0-/;+JG?G,J 34'-
J340J)9'0-/;+JG?G,J 340-
J341J)9'0-/;?G++JG?G,J 341-
J34:J)9'0-/;?G++JG?G,J 34:
;K AA?G+A5, 4'J$#DJ K
JAJ _R3M4R@_R4R@!``
JDJ ( '-G)% #
@11*41
item prices_0 prices_1 prices_
2
prices_3 prices_4
G #! $! '! ! #!
)
E
! $'! #"! >22 >22
CRUD Operations
@.4,*11**!=-*,1,411)
1*.-4!2E-Fullarray*-1,11*.44
1!1--))4
49
4)-
&9'0;)-
)&9'/;)>4>& 3&>4-
&9'/;)>4>& 3&-
491;)>4>& 3-
34')9<-0;>4>& !-
340)9<-0;>4>& '-
341)9<-0;>4>& 0;
4 %3 K& 4
4 &@""@0=!'=
-1*11)**)4
444()
9'=<.-X&-4(4-$-7/-1':-01/-<:7;-
9'7'7-5-L-&4-10-/7'=-.<'0-+;-
90!':-A))-G-$-0!-+-=:-<'17/;
/1-)
n m surname name ag
e
price_1 price_2 price_3
#
"
D ' $ ' $
= F 3 '# " >22
$
& >22 #$ '
Note.1714-,)1,?,81
4!
)*)4I111
)34341 <11/8 0!':
/E-41,1)
449
4)-
&9'/;)>4>& 3&>4-
34(9077;>4>& ;
4 %3 K& 4
/1)11*41
_id name prices
#
"
_!$ !'!$`
F _'!#"! `
$
_PQ#$!!'`
Note/11))1E41E-F3)11,F3
>8.1!1
&-M934-b;)&34-
M934-^;(34>&44
/1I41
name sum_prices avg_prices
!" $!'
F '! " !'$
#' #!'
NoteD,411,!
Status of MONGO Table Type
/1)4*11*!11,.,1F34*11
?,81!1411),)1-41*.1
).)4**!.-11.1F3)11
*11,1)4?,831*1.F3-11,
*L*1!1,?,8&81)-1.111,
?,8F8!
Current Restrictions
*5&@/>&R)161*4!
*13;&8((B(&3;&**4?<)1!
&/0*+##&01&
/1)4*11)41I@11?43C2?8)-!/1)1)
11!/11E- (8(;@/(8
1,,*1-1..1!
&4(8(;@/(8E147)11).1
49
)&49:;)-
49'7;);
4 AA%3 #*
4 &%P@""]""33
/1,11471)4(8(;@/(8
&@"")&@38]&@3""&
&@"")&]&""&
&@"")&@38]&""&
1)1*.1..1
4 R4R
4 R4">R
=-1)7-&(&/1*1!1
49
)&49:;)-
49'7;);
4 AA%3 #*& & 33
4 &%P@""]
/**&(&/14711*
49
)&49:;)-
49'7;);
4 AA%3 #*& & 33
344 ) -
/1*.*1
Option Default value Description
Tabname /) /.)11!
Dbname /8 /)1-)1!
Host 1V /1.111!
User /1 /1!
Passwor
d
*11- @*1*11-!
Port /41* /*.1!
Quoted ./)1)I!
VD 11 1*.1 516 11)1 27 1, 27
1E1!D-111)1)4.1,14.1)!.
/& * 1 1! @ 1 1*.4 115!61 **
. 1 )! /1 E1 *11) 1 1 ) 4*1 - 1 1E**,
-E,!
Caution/E.?93C2)1..*e
?93C2).11-11!0-1).)4
4,,1..1
34 %3 &%P
AA &%P@""]""33
,A?G 4-)9C;>&&3%)3%4
D1*.1.?93C2)17111)-1
))41)1.1*...!/4*1)4*1**
)4&(&/.14*.11),1
)1,1,84*1!
Note1*),)4D=(;(1E*4**)-
1)4**1,.D=(;(K1!
.4-1,.*&(&/
,..1!1
44 AA%3 #*
$
4 &%P@""]""33
/1-essai)-11people)!.,)1
&(&/*)4*1184*1E--11)
1E**!
Charset Specification
D11,)&(&/1111.)
11(8(;@/(8,1!
81*.41.1...).1-
11)!/11)111)
1 -,
!. 1E*11, . 1 >/- ,
>11*.4.111!
/111*11)4).11,
..11!@11)41)111,41
-11!
Indexing of MYSQL Tables
7141.-?93C2)1!/11)1&(&/1114
I1-1!1.41E
C>&48)& 01
&(&/-111I4
A)&-4G,33X5,)& 01
.people)17num7,-)11!/111
-.-E!
=-7)1*..1*)1G)
?93C2))1-11*,.-1!1
4-&->3->%
>&4M4>4 >4
8>%Z'!!!!
.id.)11)4cnc_tab?93C2)17-1E4
.1E44117.?93C2cnc_tab)!.11,56
7,11(8(;@/(841.-1.)-),
G*11!=-)11-1)41*3(2(&/111-)
1.4-,.--1.),)!
*411*.47.11.G,)8/
7G.17)!/1-1*11.
)G-1)4!
CRUD Operations
/&(&/?93C24*1**13(2(&/3(;/1-..>8@/(
8(2(/(! / ?93C2 4* 11 1 1 8& 4** 3(;/
>8@/(8(2(/(1!;.8&*.11,!
>8@/(8(2(/(1.-11)11),
?43C21147.-)-41*))4)11!
/11.)).)41,.)T:1:11.
,1!
14.41E4-1E<;(2D:;;/91-11.1
3(/D=(;(11!
=-1111H)11!211**14 t1)
1-7I411
)344 9&>&'84 )&;
8)& 0
D*144-1. t1 )711.1.
1!Dt11714111)4,4t1)
'944-&9';;4 $AN5
/1-E*1**4*71!
-,?93C2).t1)11*)
11111t14!
/.*4*1.17)41-41
&(&/*11*.?93C2)1)4*1)-!
Sending commands to a MySQL Server
/1)E.8&F8&)1)4.,1*.)-)11
1,1.7!
9
&&(9'0<;)-
8449:;)> 1-
)&497;)> '-
&(9077;> 0;
4 %3 &%P
4 &%P@""user]host"database
344 '- 0
/E4*111(B(&3;&*.!
/(B(&3;&*11)-)111?43C21!?1.
1 1 11! /.)1 1 1*.4
)7,1.7!/.11)
1)4.,.2@<
Flag=0 /7.
Flag=1 /)...-11).1.-
11!
Flag=2 /411,!
Flag=3 /).-,1!
=-11)1*.41M47,11
C>&8&& a command
/1-11*.D=(;(111.1
7!/147.D=(;(K11)7411-)!1
C>&8&&
A,$339
)&49:;34&%O%)4&-
49'7;)
'
/11
command warnings number message
&;(@/( /@2( ** ,$ *4 E4
Y
@..
-1
Sending several commands in one call
).17)1-)4..!/11
11.-,147
C>&8&&49
R)3334 884 0R-
R)3334 84 1R;
D11171*1..1
!/.n11*7Rn)4.*1!
Note 11*11)1*.4 3;&8( *-, (B(&3;& )!-)
1)4.-D=(;(111*.!
Note 2 E111 1 1 1 ) 1*! 3* I1 1 ) 1* .
11*.)-1*I1)I1.1 1*.)-)
I1!
Note 331**41*.)1!=-4114)-
1)1!
Note 4&417@>/&??/!
Retrieving Warnings and Notes
.111-,1)111111151--,16
)1?43C211*1-1,1!/.,,-,1
I11*.E4-4!
/-,71)1411H
I4,5*16111)4*)4
(B(&3;&)!/15*161
Warning /,-,1
Note /,1
Error /,11-,1M
41)1*111741).516!1
C>&8&&49X4--
34>4%-
%944O%)4&-&(910;
);4 4-
R44%9&;()9;-9+;-9;R-
R44%()90-?);)34O%)3&
8R-
R44%9&;()9G);-9G4(;-94;R-
44%94;()9+;-
R)3%& G)84 :R-
C>&%;
/11,E1
command warnings number message
#
*).7114
@..-1
>E-)N4N
)4E4:1,Y
@..-1
141,1NN>22N/N
@..-1
D,
$
&N1,N)
141 N87N*E4Y
@..-1
141,1NNNNN37N
$
>E-N1,NN.1N
141>22
@..-1
D,
'$
N1,N1N.
*411,RNN-R$
@..-1
1V.4
;111
/7.)1. ?@B(;; *!41
-1**7!
.115161111)1)!@
?93C2)-(B(&3;&*-**.1)11!
Connection Engine Limitations
Data types
/17E4!7,. )41!94)))-H
74,*1-1!
/.-,4*1N)1
/
@;9
/92 2?(8>?22<2
/9/(B/ ?(8>?/(B/2</(B/
<44*1
Note 1 /(B/ 1 -! =- , *1 1 ,
:4*: ::1L 141)1)4.1./(B/
11*!
Note 2(>? 3(/4*114*1!>E-,)
4)111,!=-1*1.
3(/,.B&2)1!
SQL Limitations
/.-,3C2I11**
;(2@&(/
3(;/!!!8>2&@/(X(9>8@/(
CONNECT MYSQL versus FEDERATED
/&(&/?93C2)4*1),1*.(8(;@/(8B
,! / 1 . ?93C2 4* 1 11 , )1 1 . 4 -
&(&/)1!/1-1114-11,)1.1&(&/)4*11
1/2B&2&&>;0/1,11&(&/)14!D
,)1&(&/)14*1141,4?93C2
)!
=-11-41?93C2&(&/)141..1
! D )-)1)4/2)!/1)141*.4
*1.1)H)1..1,(8(;@/(81)H)!
! D1141*.)43;&8(*!/11,
11.G)11,*,JG,)1!/1)
-(8(;@/(8,!
! /E,.push_cond.41-11
)!/1111L.11).),)1!3
1.18&)4*!
$! )1-(B(&3;&*!
! D,11!1E1,!
.4H)*,,)E1,)441
(8(;@/(8,516)1*.4,(B(&3;&*!
$2/
@;B9)1)11.)-!1
))1)41B)
%4 %3 D,H#& %
3*4;B9),.4*-/@@?(11*.
%4 & %
1)41))41-)1.*74)MD111
)41)4)4*111/2B&2&&>;0/! ;B9
)&(&/)1,4))1)1.4,111)4
)4*1114&(&/)1!
Proxy on not CONNECT Tables
D1)H)1-&(&/)&(&/41*4
&(&/).?93C24*11!/111,1.*1
.?93C2)!11*11)1*.4;B9)1,;B9
1/:23/*1.?93C2)!.111*1
4?93C24*11!
4.*11);B9)1!=-
111-1-1,,1,*11-!/14**1&(&/
1*11-I1),;B9)1!1
,
2344 D8 &%3
=-1.1)-*11-)1)41)1
))41-)U*.)11-11
!/11*.11)1-)1)4*74)1
)1!/11,*1.1 3(2(&/ 1
11*11-!3**1,T*74111*1-)
2344 ) 3%
Using a PROXY Table as a View
@;B9)1)1)41..4-4)1-!1*74
)1171.)G)!11*11).11-..
14*141.,1!1
4%9
4%(9'';-
"
%9'0;> '-
4;
4 AA& %
C>&4%
/1-1*4
4 )4 )
1 F "'HH
1 =4 "#H'H#
3F1 <, "HH
&, 3 "#"HH
81 F1 "" HH
1 "'H"H
=-1*.4...1)1.)41)
4.)41!7.!/.,*.boy11*1
.1.)41)name!
Avoiding PROXY table loop
&(&/1-;B9 ;B9)1).1441.!.
.)1).4)1-7,
I4)!=-11*11)4.)1!D1,)1-1
*).)-.1)E.)1.!
/11)1-1,(8(;@/(8)1!
Modifying Operations
@3(;/J>8@/(J8(2(/(*1)1-*74)1!=-111
**4,1)1**4*74)!
Note@;B9;B9)1)4*17)!
Virtual Table Types
/1)1**4!3-E15-16)11.
*,141!
3
B&2 )1)1)-E;B9 )1!/14*)1-
)G)111.1!*111-)G
)1.3(/4*!
3**1-Tchlist’))1*41
mother children
3* 0@4
21) 24&18
&
& ?
F @3F
D..-1--)11-1J)4
,B&2))4
A,$49
&9'0;+-
49'0;?G++> 0
;K AA%3 HA& 4
344 & 4
/&2@?(*1*.1.,11!/1-.
C>&4
/I1-
mother child
3* 0
3* @4
21) 24
21) &1
21) 8
&
>22
& ?
F @
F 3
F
F F
3,11)
D,children.1-**1*1>221*..
5*6!.1)E1,-,>22!
=-.1)--),!
E1.1*,!
*4.,-1!)1111!
3*.4,.1&;(@/(/@2(11*!D,1
1.-14*,)!
/.,*1)1*1.,,))
1*..1,!
/5*6child)114!1
C>&48)94-'-';
/1-
mothe
r
child
3* @4
F @
.I415*6-*-)!1
&>&4
/1-G11
mother
3*
21)
&
&
F
/11-4*.111.1
)9C;>&4 )7
)94;>&4 )'!
)9&;>&4 )7
<*,1,1..1
&-)9C;>&4)3%&
;*1
mother COUNT(*)
&
&
F
21)
3*
DI4
&-)94;>&4)3%&
<11,1
mother COUNT(child)
&
&
F $
21)
3*
3*1).1)4*
Option Description
Sep_cha
r
/1*15*6.1!
Mult 17).*1!11471L
.).1!/)1*./:23/!
Using Special Columns with XCOL
3*1)1B&2)1!/141.1;D>?,1E
.1.1!1
A,$409
O4+DA ,X?-
&9'0;+-
49'0;+> 0
;K AA%3 HA& 4
344 & 4
/1)-)1*41
rank mothe
r
child
3* 0
3* @4
21) 24
21) &1
21) 8
& ?
F @
F 3
F
$ F F
/14.1.4
A&-4G,408O '
;,
mothe
r
child
3* 0
21) 24
& ?
F @
=-.-,*.4,,1.1,
))4
A&G,408OZ0
/11)1-*),E1-41!/-4)
111,)1;D>?
A&G,40)3%&(4)94;Z0
XCOL tables based on specified views
1.1*.4,1)/@@?(*1*11).
5-6-1.1,-*3;&8(!1
(4 %3 HA
> 8(44OR34&4E84R
344 A& B)
/.1
()>&(4&4'!
/1-1*41,E
value
7:,R
7:,:R
7:,:1:R
7:,:1R
7:,:1:1R..
,::*1-R..
7::*1-R
:,R
:-:E41R
.1R
Note@B&2)14!
423
2EB&2)4*&&>;171;B94*-.,)-
,11,1E.!)1,..-.)
-.11*1,41,1-1)
,.-.)G)!7*1**1,-pets)
$
name dog cat rabbit bird fish
F
?4
21)
X '
8
D))4
39
&(9'0;)-
9/;)-
)&4);
4 %3 )& 3
344 )A )&-,OA A6
4 --4-4->4
D1*4,)4
C>&3
D-,1
name race number
F ,
?4 ,
?4
21)
))
X
X ) '
8 ,
8 .1
11.1&1*)*I-1
1,)4&*!D11H*1H11
-1,-11*!
*1*-1-11,)4;E&*!/1
1.1.-.& 1
.!*1E-.*1-1)1,number!
/1)4**EI1-)*E,)1!
1E--1*.E41*41E
C>&38)&Z'
9-,1
name race number
F ,
21)
))
X
X ) '
8 .1
Note 12E.B&2)1-*.I1*4,!1
1*11)1;D>?1*-11!
Note 21&-156-1-,.*1H
1.1!.&11)-11,.1
,*1H1!
)1)1..-11.!&(&/11),
.1.1*.!7*
4 %3 )
4 M)%->)%-&-34-&%-M)-M)%-))-3&-
-(&-&344 O &-) %
> G&-<M)%-=>)%-0&-'34-
<&%-':M)-07M)%-'!))-'13&-00-0<
(&-':&
/1)11*41
name month day
G4
.)4 #
*
4
G $
G4
,1
1*)
)
)
) $
Note@&&>;)14!
$!
/1)4*)11.1.)-1)
*),5*65.161!@*)1,*,11
11)4.*.,41)!
7*11**14.-,5(7*116)
Who Week What Amount
'
L 1 $ '<!!
$ : G '=!!
L 7 $ ':!!
L 1 G '0!!
L : $ '.!!
L 7 A '0!!
L 1 G '.!!
$ : $ '7!!
L 7 $ '.!!
L 1 A 0!!!
L : $ '/!!
$ 7 G '0!!
$ 1 $ '/!!
L : G '=!!
L 7 $ ':!!
L 1 A '.!!
L : G '=!!
$ 7 $ 0!!!
L 1 G '<!!
L : $ ':!!
L 7 G '0!!
L 1 $ '<!!
L : A '=!!
L 7 G '0!!
,)11,NDNNDEN.1..1NDN.
.*,1,N@N.1.-),1.-,
11
Who Week Beer Car Food
$ 1 '/!! !!! !!!
$ : '7!! !!! '=!!
$ 7 0!!! !!! '0!!
L 1 '<!! '.!! '<!!
L : !!! '=!! !!!
L 7 11!! '0!! '0!!
L 1 '<!! 0!!! 1'!!
L : :.!! !!! 1:!!
L 7 ':!! !!! '0!!
3C2)14,11*1..4)41,5,*)461
4
8-8O-8-)&9&);>&3
)3%8-8O-8
=-1-4,*41-)G11,3C2!(1,)
3C2*,,.18?31I1*!
/)4*.&(&/E1,11*!
#
Using the PIVOT Tables Type
/,11-7*)G1.1-)-1
34(
4 %3 34(& 3
-4114).11*411-)G114
C>&34(
/&(&/*.0/)4*1.-EI1.
1)
! ,516)4.1.1)
!
! ,56)4.1,!
! &1,,,,.153>?6)4.!
$! &1,7,5<*46516,,114!
! <,1156.,586.!
'! 3*,1.4).)!
Note/1)561))1,1156
/-).111)11!
...111&/)*1),.11)4!
1.--1*4,7*1.*1*1*1
.-E1.-,1
34(0
4 %3 34(& 3
344 D4(A XO-G)4 BK
-14,
C>&34(0
D1*41,)
Who What 3 4 5
'! ! !
! #! !
F ! ! '!
F & "! #! !
F ! ! !
F ! '! $!
F & ! ! !
F ! #! !
Restricting the columns in a Pivot Table
211**1--). expenses 1,7*11.**
*1--E-1),!D1G1)4,.pivex)week
.1!
,516561-E14.))1*)1!/4.-
1.)1*)1.-41)7*41*.!
34(3)&8O
@41)-,).)!/11)1.1
1*.)-41E**,-11)1*.-3X&2
*!1
34(
4 %3 34(& 3
344 O43A 8O
/1-,.-)1
WHO Beer Car Food
! ! "!
F ! $! !
F ! ! ##!
Note;1,111-1)11711
)*.*)!/11*.E41***,*,!
PIVOT Create Table Syntax
/&/)1.0/)111.-,147
A,$pivot_table_name
9column_definition;
K AA$#D DB
_$ source_table_name,A?G source_table_def`
D 34(344
/.1-11.1
! @1.1),,1),5.165*61!
! 5861,1.,,,5.161.1
.5*6!/4)45.,6*!
/optionssub-options).)1
Option Type Description
Tabname [DB.]Name /.)5*6!.1SrcDef1)1*.!
SrcDef SQL_statement /11,41I)!
DBname name /.)1,1)!8.1
)1!
Function* name /.,,,.1.13>?)4
.!
PivotCol* name 3*.1.-111.
561,.,*!
FncCol* _func`name_` 3*.1.516!..func(name)
11,,,.11func!
SkipCol* name_UnameY` 3*.1 . 1 ) - 1 1E*
1*)41H1!
Groupby* Boolean 3/91.)41<;>9.!
Accept* Boolean /*H,1!
V/1*11)1*./:23/!
"
Additional access options
/.11- 0/ 11,1)-
38.11)7
! /1)1&(&/)!
! /38.*11*.!
! /1)11!
$! /11*.!
4. 0/ 1 41 11, 1R11R 1,
*11-*R'!=-14)-1*.1
1*11--14,511611,-,1,
*)!
3*.4 =3/ >3(; @33D;8 J ;/ *1 /:23/ .
*11111),1*.17,
<;>938.I4!
Defining a Pivot table
/**4--41.0/)
! 71,)-!
! 84,,3C21,1*!
Defining a Pivot Table from a source Table
/tabname1)*11,.1)-!
)1-1<*K4-)4,7*- <;>9
*11*.1!84-)-41<;>9.!
Directly defining the Source of a Pivot Table in SQL
@41)4.1,SrcDef *1
**,*)4.!
@1-1)**/)1.)1,.
7.<;>K91!41111*1)41*.41
-, * )! /14))1111.*7*11
,.,JG,)1!
/11SrcDef*.*,*1!11**1
.17*-41-E1$!D.11*4)4
C>&34(88O49:-7;
=--.1)1,)M11-41.*
)11
34(:
4 %3 34(
344 D4(A 8-GA &)
?> 8-8O-8-)&9&);>&3
88O49:-7;)3%8-8O-8
.41)11.14**411)1.,1-
E...*.-1!4)417*111
1.111G-,1143C21!/411
$
4 1*1) . 1 . 1 1 . . *
*11,!
>1,38.1*117*111J1.1!1
34(9
X9'!;)-
X9'0;)-
G4)9<-0;> '-
4)9<-0;> '-
)9<-0;> ';
4 %3 DB
344 D4(A 8O-GA &
> 8-8-88O 1G48
8O 748O-)&9&);C
/7/&>&3)3%8-8-8O
-1
C>&34(
D1*41
Who What First Middle Last
$!"' "!$ !
! ! #!#
F ! ! '!$
F & $!'$ ! #!#
F ! ! #!#
F ! !$$ "!$
F & ! ! !
F !' !$ #!#
Note 1*1,1.7114SrcDef*
*.,*)41!
Note 21 SrcDef, 14,11 1,
7*11114,L)4*1!
Note 3SrcDef11I11)1*)111*11
?8)-I1!@41*.4)-)I1!
Note 4D.&(&/.1!=-)14.
.11?-)*.!
Specifying the columns corresponding to the Pivot column
/111).171,5*6!11**1,
-.-,pet)
name race number
F ,
?4 ,
?4
$
21)
))
X
X ) '
8 ,
8 .1
,1,race1*1-
34(
4 %3 34(& 3344 D4(6
A -)3% '
/1,11
name dog cat rabbit bird fish
F
?4
21)
X '
8
4-411,)M1-1-40/)1,**1.-
&&>;)1!
D4. 1*.4)1)-**1 .
111,56M/11*,1*.
*1.,1!
First case.1*.*11*.11-4,1*4)
I4-)-11,1,H,-1!)1
111)1-,)11*.1-1,
-1.*11)!.1**141H
)4-1*)!
Second case/**-11*.!1
34(09
&(9'0;)-
4)>)!> '-
4)>)!> ';
4 %3 34(& 3344 D4(6
A -)3% '-3 '
-)1H,1-),!/1)-)1*41
name dog cat
F
?4
21)
$
X
8
Third case@5*6-11*.-.,I !@H,1
-)1!1
34(9
&(9'0;)-
4)>)!> '-
4)>)!> '-
4)>)!> 0;
4 %3 34(& 3344 D4(6
A -)3% '
/1)-)1*41
name dog cat other
F
?4
21)
X '
8
Note1,*15*6.1)1*)1-
-1.*71-*)-1!
Pivoting big source tables
/1411)1E4!.*14111,)
441!11*11.,11-,
),,*)4-1,)4,11.)1.
714!
;11)4-11)**1)-,*)
*)1.!/1))4,)1,11
-1.*!
Note@0/)14!
3*
/14*-1.,)11.)1.4,4*!/11.7)
*)11).1.4*!/14*1)1.7)-1
-?(;<(,!
/1.1./2)41141.)1.
1!..111..1)H)11)1*.
)41*1,)42@< *.!. @&&(/*119
1711.1)H)1*-)*1H
$
.)1,1!/4*11)..
1-).114!
Note.1*..1..1).)1!
$
/1*1)4.!
$
/.)1.1)H)11)1.)1*.8@?(
*!)1.)11)1*.)1!
1K))1)employe)1(,1
)employee.db2)1147.1)
A,$&39
,97;+> '-
(9'0;+> 0-
H&49';-
(9'7;+> 1-
K,97;?G++> :-
?D,9:;+> 7-
A,,#97;?G++> /-
,#)9<-0;+> =;
K AA%3 $4 &3%-0&6
3%344 3 '
/1.11))11)4.471-..)4
*1,)42@<*!&sex7114(,1)2@<1!1
1-.)!
1I4
&--4-%>&&383& 1'<
&*4
NAME SEX TITLE SALARY
@;>8 >22 0(8(>; "#!
?@;&=@
/
>22 0(8(>; !
?@;8 >22 @8?3/;@/ #!
> >22 <((>; #$!
@/(;( >22 <((>; '!
2>2>/( >22 3(&;(/@;( $"!
/@;/( >22 (;@/;&( !
D(;/=(; >22 8;(&/(>; $!
0/>; >22 0(8(>; !
@&;/ 3@2(3?@ "'!
?(;&=@/ 3@2(3?@ #!
3=;X9 @8?3/;@/; #!
D@2/(; (<((; #$!
/<= (<((; '!
=(9 3(&;(/@;9 $"!
2>?=(@8 /93/ !
D(;/=(; 8;(&/; $!
D=((2; 3@2(3?@ !
/.1"-1,.).sex!/4-.
17)/>22!
$$
Sub-tables of not CONNECT engines
3)H)1 11 1 ;B9 )1! &(&/1)H)111
?43C2@1*11)E-;B9,?93C2.*1!.11-
**4&(&/)1.1!
Using the TABID special column
//@81*)11.-)-1.1
1141.1)H)1!
211.-,7*- ?43@?)111,
?(;<(1*
'9
49'';)-
&90!;;
4 AA%3 #*& '
344 -)
09
49'';)-
&90!;;
4 AA%3 #*& 0
344 -)
9
&9<;)34 $?-
49'';)-
&90!;;
4 AA%3 $4 '-0
C>&
/1)43(2(&/11
tab-
name
a message
7 /1,
7 )
7
7 /1,
7 )
7
-.41I4
C>&8& 0
&(&/-4L-14xt2)!/11.4-
4.-1)H)1./2),41)H)1!
Parallel Execution
/114)1),1.7!
$
D1)H)1..11
1*11)7I1
1141.1I4!/)11/=;(@8 *41!
@*1).1)4*
Option Description
Maxerr /7).11,)1)1).11!8.1!
Accept .11,1*1!8.1.1!
Thread .)1*7.1)H)1!
/1*1)1*./:23/ !
Note 1@/2)14!
Note 2@1,/24*1*)1*.4,*)
1)H)!/1-)1)1!
4 &/0
>1,14*111.(8(;@/(8?(;<(,1!
MERGE11)1,)11)?43@?)1?43@?
1.,.?8!=-/2111.&(&/)1
) )1 1 )1 ) 1 1*. 4 ?93C2 )1 1
4*.)1),!
/211.7)!/,)11)564G11
1./2)!.4*.,)1.
1*,./2)-)!@1-1.
. /2 ) . /2 1 71 . , )
1*,-)1!.11)H)..4)
11)4*11,2@<*.&(&/!
=-./24*,,?(;<(1/2)14H4U
3(;/11**)4 /2!/.1?(;<(111.?43@?
)1)1-).1*11,)4?43C2@!
FEDERATED(X)/.(8(;@/(81114?43C2J?8)1!
/?93C2)4*.&(&/11)&(&/*18&
F8&)4*111)1.4;83*,8&F8&,
?43C2141.e
@ G . (8(;@/(8 1 11 4 )! 4 ), /2
?93C2)1&(&/)111.)11)!.
11)H)1)..11!D3(2(&/1*4,
- ) ) , 11 , . 1 1)14 *1! /1 1 , .
1))E,411!
Remotely executing complex queries
?4*1111-1).!&(&/)1
-..4-,*4L)1!211**1-1
-E1m1, m2, … mn1.-)1t1t2!
3**1--711I411
/1),1.74!>1)11)7..11!
8.)711!
$'
'-)&90;>&'-084 4)3%'
/1114*)1!;,1.t1t2)1.11)
.-E..!/<;>9*11)4,1,)1),*11!
Gt1t2)14)4.G*1),1
),,/@83(;081*!
@1)*L)4.,I4)471
,4111. <;> 9 I1!=1-!
)-47I4!1.m1
'4 344 &'
> '-)&90;0>&'-084 4
)3%'
1..!@1-)I..1-1
..11!/ )11)4..
1!/11)13;&8(*!/1-1.)1-
-1c1sc2
36
!
/)-1.1)1
4 %3
4 '-0-2-n344 %
-411)4
'-)&90;>&
@1-E-)1114E1*
#
E,1I41*H.1),)1141!
Providing a list of servers
@1,11-I4111..!1
*11),1)H)1!11)1*-)11*.4
1.11 3;&8( I41)1!/1-)1.>;21J1
1!
11**1,.1111 Y1n-.11
-)*11)/2)-,,,1.I47.)4
P)&>444
4 %3 $> P)%
4 ('-(0-2-(344 %
1
(4 %3 $> 6
]](44 -(
C>&(
/1*4
@@version
'
/,1.3;&8(I4&(&/17!/1-E1
1E!=-.114)71,44
41*.41&;(@/(/@2(1!
#
/147*!>14.1*4.F;@!
$#
!!H?8H),
!! H?8
=111*.111*,,?8.
1server_one!
#!
/1*)4*11**)4&(&/0)4*0;8421,
) 4* 8; D-1 ?, 1 /) /4* D? 5?
@1164*?@&!
/1)15)16,4*41)*1
1,1*.,1!111-0-114),1
1*-1!
Virtual table type “VIR”
@0;)1),43*01!14**41151L6
4,).-11!11,147
A,$namecoldefK AA$#D B,
$ANI n
/*2&X:3A(*,11L.).,.1*.!D1
11*.11I3(C>(&()51I:::Size6!
Displaying constants or expressions
?48?31HH)1.56!?43C2?81
147-)11*.!D&(&/41**1-
)-),.),)1*411!7*
(44 %3 B,O4E '!
9P)>--4;8-
)9P9;-'/;()>&(4
/1-
what value
/1I.1 !
/1I. 1 !$$ ' #"
/1I.1 !# #'##
/1I.$1 !
/1I.1 ! ''#"##$""#"
/1I.'1 !$$"$"#$ ###"
/1I.#1 !'$#'$"#
/1I.1 ! $ # $#$'"
/1I."1 !
/ 1I.
1
!' ##'''#"
D**M1E56)1?8)1
1!4.&(&/10;)1-1*!/1
)1-3=D&;(@/(/@2(1
A,$J(4J9
$
JJ49'';+JDAJ ,X?-
D,,#N#9JJ;
;K AA?G+A5, 4'J$#DJ B,
J$ANIJ '!
/11*15611-)1,.!1*4
).7111*,17!
1*11)1*.41.0;))41)&(&/1*1
1!1
(409
4O%)34 84-
4'4499C9b';;"0;(4)-
40449990Cb';C9b';C;"/;(4);
4 %3 B,O4E '!!!!!!!
C>&(404&4..7-7
/1)1-111.1I.n.1,1
n sig1 sig2
""' $"'
'
"$$'
""# $"#
"$"
"" $"
$""
""" $""
1L.))4),1*41!=-1
1)I1!1
C>&(408 '//:7'!
3 I4 1 4 , . rowid - 7! )4 .
&(&/1561*4E4!@40;)1)7)4
;D8;D>?1.)!/117.-11!
Generating a Table filled with constant values
@1,1.)1-.)-).44*1
,),11!
/1114-)!21.)22(;1
>44 %3 B,O4E 7!!!!!!
=-11L,),,1)--,!2.-)*H
.-.J1-.7*
39
449/;O%)-
&9'/;)-
%>9<-0;;
$"
443-)O8-+>&>48
Y '!!!!
/1-,),-1)*-!
3(C>(&())11.22<!
VIR tables vs. SEQUENCE tables
DG11.0;)11I3(C>(&()!/1471
1...1
C>&P'!!'7!3'!
))-0;).1LQR)4
C'!>&(488'!'7
/...1)).1.0;)1!>.4
4411*.41).!
DIR Type
@).4*8;11..1*111!/8;)1
&/)111
)9
?,B90;-
D5(907/;-
G(907/;-
G#D9:;-
I)9'0-!;> 7-
?G?4&;
4 AA%3 ?,>4& SSC
D1I4)11..1,14158; VC6
1-.7141**14S
1I4
>&-4E-&4>4>&)
8>&4OTT
81*41
fname size modified
## H'H "
1I:
H'H
Note*1)1.,*11I4.)4.
D-1.7)11-.1
Flag
value
Information
/1ED-1
/.*
/.
Flag
value
Information
/.4*
$ /.)
/.1L
' /1-11
# /111
/.
The Subdir option
D1*.)1subdir*11
.1 1*. 4 .1 .4, . *
1)H1.1*.4!11,
9
D5(907/;> '-
G(907/;-
G#D9:;-
I)9'0-!;> 7;
4 AA%3 ?,>4& C>&
344 )4 '
3-)9C;-)&94E;>&)3%3
9-,.-,111-,-4)1?8)11
-1,.;?.1
path count(*) sum(size)
d&3d)H
! !#d1Iddd
'$$'"
d&3d)H! !#d1Idd41Id #'
d&3d)H! !#d1Idd1d "'
The Nodir option (Windows)
/ Nodir *)1.11.
* .1.1 1)4 .!/11&(&/1!'!
1441,*-1D-1!81-
127!
Note/-4.1E*111*11)!/.8;
)1)71-11,*11!
-*-1,subdir*I18;)11-1
1..41,)..111)H1!
8;)1)1**1..11*R )!=-11
11.1-1-*71!
Windows Management Instrumentation Table Type “WMI”
Note/1)4*1)D-14!
D? *1 *, 141 . , - 1 *1 *
.! 3 ?1. 1 . , D? D?& 1
D?&?3**!
/&(&/ D? )4* )111*1 *). 1*,
*,,*.D?G4)..D?-,)!*1
*11.1)1)I1.*1)1!
/D? )1*4,.,.D?*41*
1*11L.4-!/)1-4
.11D?&?31)-11*1111
1*41.**1.11!
/1.)11)1111.**14-
!1
49
>4%&910;)-
97!;);
4 AA%3 X
344 &3 SS4-A >A44
D?)1-.1..!/)7*14
, 11I.1. D?& 1.4111
41!
11.1.111),Td 1*11..
D?)1-1*11*.!31114**1-1
4*4)E--,)!/.41D?&?3
**)1-)4I)1&(&/!
@4 11 1*. 1 1 . 1 . 1 1*1! Td
1*11.1T?1.:&@1.T: 1*11
.1TD :&*341!14111),-TD :
1 114 14 1*.4, 11 1 T - ..4 1 11
TD :!
7*.4.)1
AD,?4 AA%3 X
-.1,11&*341.
&?0 1*!1
C>&3
D111
Column Row 1
&* &*141*
81* &*141*
.4,)
2B@B " #@"
@1*"
3X>)
>>8 & 8H#H8& H@#(H8@$''
0 @
01 @1*"
Note/111*11*4))-1<>!
Getting column information
@11-,D?)1E1.!-4E-
1*11.-.114.-1
4*1.1**1!=-)1&(&/1..D?
*41*4.,1&(&/-G)!
@44,1.1,,)1)-!
Performance Consideration
3D?*1)41-1-!/1111.1.-)G
1111 1,*) 1 .!/4 ,4
4 - 1! =- 1 4 -1 *
5&?:8611!/11-41)E)!
141*11)11L)41,T(1)*!/
1&(&/11.-11,.1)1!
/T(1**111L.1114.--1
111!
=-1*11)E)41D?*11-*
&?:8@/@2(11!?1.141)
5@,I4,.11.CIM_DataFile*)1
.1E4..*.1*1*1*,!6
1*I411
)9C;>&4&84( ?@34O
SS4?$SST
1*11.*))42X(1!/.-1E,.1
1*.14111,8;)4*1!
Syntax of WMI queries
C1D?*11,DC2,,3C2,,!&(&/1
G).E,DC2I4!=-)1.1.DC2147D=(;(
1-),4-1*,.-,11
! .!
! *1)--1!
! 7*114&(&/1
$! (/D((*1!
,- D=(;( 11 1*,11-1 ) )4?84
7*1.&?:8.11.1,)!
=-1*1414711*.41I1!DC2
1,L11)1)111.11*.1
7!?4.1,L11)?1.)411.
)1DC2?83C2!=17*.I4)56
)4
4&9
&(9077;)-
4>44&);
4 AA%3 X
344 A?G4-4& 7!!!
/1)1*.-.-&?8@/(/?(11D?11
.1.)481)?,/1E
C>&4&84( ?@3 SSD)?$SS$4SS
&4>4Z0!'0!:'7!!!!!!!!!!!!b'0!
/11471)141*!/71.
yyyymmddHHMMSS.mmmmmmsUUU
144111,.>/&!/1
.1H*14-I414!
Note 1/D?)4*1)4D-111.&(&/!
Note 2D?)14!
Note 3D?)17)!
Note 4D?11,11111!
MAC Address Table Type “MAC”
Note/1)4*1)D-14!
/1 4*111*4 , . )** ) 1
-E1!/1)14711
tabname9columndefinition;
4 AA%3 A
&1).41)11,.!!14-1*41
.1*.,*!/1.,
Fla
g
Valeur Type
=1
8
8311 $
$ 4*
3*8 '
' ;,
# 74
83
'
81*
?@&11 $
/4*
$ 8=&
11 '
' 3>(/1E '
# <@/(D@9 '
8=&1 '
" =D3
4D3 '
34D3 '
21)
$
Fla
g
Valeur Type
217*1
Note/..1,,11I.*
11*.-E1.*!
14.)macaddr1
&9
5(9'10;> '-
A(9'10;> ''-
90:;> '0-
D9'/;> '7-
K8%9'/;> '=-
4&> 01;
4 AA%3 A
.47I4
--43-8%->&&
-.7*
Host Address IP Gateway Lease
20(
;
H@H8H@$H'H' !!! !!! "#HH
20(
;
H8H(H"H"H " !'!! " !'!! $ H"H
OEM Type: Implemented in an External LIB
@,&(&/*11.)4*11*.**1411
,L-41)4171,.,-**18D!/1
11&(&/.1.)1*1*&OOI)
-**11.-*.1&(&/)4*1!&(&/1
1-**1*,1*,71)))!
/1)7-**1&/)111-)-!
9columndefinitions;
4 AA%3 &) 4&
)%3 ##D34
344 %3 >
/*module,1.8221)4*,(?-**.
)4*!/1)41)*,4E*,1>81!
/1)417*.<?9/9(!/*subtype)1&(&/
.7*.1-)4*!*1*)4(?
4*1)1*.-option_list*!
&.1)1*.4.7-**1.!1
1 7* . &?9/9( , 1 .1 . *) )4
&(&/14.!
D-*11)1*.-411).44*,
(?4*11))4(?4**1!
An OEM table example
/(?) ?<-1111-**7*11?<E)1
-?8)41)1,)),?<)4*!
.1, 1 7*, <?< &?< .11 )
)*,4.1-E!
Some currently developed OEM table modules and subtypes:
Module Subtype Description
) =(22 @1*(?-**1*4,)14,5=-6
, ?< ()11,)1)1?,81!
/). & =1.1,D=4*.!
/).7 & =1*&4.1!
/).7 C =1CE,.1!
&*E &;X =1&8;N1.&*E>/N1!
/)*, 2< @11)1.,88?31**1814!
=-*(?-**1.1*.1!
'
Catalog Tables
@,)11.))1!11-
?43C21118(3&;(3=D!@**)11G1*1-
11-)..4)1)11I1
1G)111)H11!
11)I4,1.7)1)4
I-1*1!2117*
3**1--11)1.?1.@11)118&4*)!/
.1.-1)11.)171,11!/,--
,)-7.11.3C2/)18&.
4>9
&(9'0<;)-
%3(9'/;);
4 %3 ?$A>)
A4 ? ??$* A@"D&
G4"4>>>4">>4"'!11"GDX??$
/3C2/)1.111,.-,1
Field Data Type Null Info Type Flag Value
/):& 28:&@/ #
/):3
28:3&=(
?
/): 28:@?(
/):/4* ' 28:/9(
;E 28:;(?
Note/./4*,0&(&/*1.11!
=-.1.,)1)7*
11,4*.)1!.1*.11
7.1*,3C2/)111),..-,
.,1*.!
//):/4*)/@2(393/(?/@2(0(D!
1,)1--1-1E
&>&4>8%3 $
/1-
table_name
&,1
&11
(*41
1
3**1
3**1
#
---)11&>3/?(;3)!1&(&/
1*.8&)11141*.4)1
A)&4 %3 ?$A
A4 ? ??$* A@"D&
G4"4>>>4">>4"'!11"GDX??$
=-.-*.1*.44.4-1E--
.1.)!D,1.-,)!/11-
)4>4 %3 ?$A6
& )&>) )&
A4 ? ??$* A@"D&
G4"4>>>4">>4"'!11"GDX??$
@41*11)1*.4-.,)--
)4>9
)&&9'0<;)-
%3&90!;)-
49'!;)> =-
3&49/;)> .;
)&49/;);
4 %3 ?$A& )&>) 6
)&
A4 ? ??$* A@"D&
G4"4>>>4">>4"'!11"GDX??$
/,.
C>&)4>
-111)
column_name type_name length prec nullable
&18 0@;&=@
;
&*4
0@;&=@
;
$
& 0@;&=@
;
&/ 0@;&=@
;
@11 0@;&=@
;
'
&4 0@;&=@
;
;, 0@;&=@
;
1& 0@;&=@
;
&4 0@;&=@
;
0@;&=@
;
$
7 0@;&=@
;
$
-4&>3/?(;3)1
A)&9
A)&?(97;-
A&3%&(9:!;-
A&(91!;-
A4(91!;-
(9/!;-
A4%(9'7;-
,4(9'7;-
DA(9'!;-
A)%(9'7;-
D(90:;-
G(90:;;
4 %3 ?$AO4E '!
A4 ? ??$* A@"D&
G4"4>>>4">>4"'!11"GDX??$
217*--1..,)!/1)1
11.8&3C2&1.18&1!&1.1-41
1,1..-,1*,)4*
Field Data Type Nul
l
Info Type Flag
Value
Returned by
/):&V 28:&@/ # 8&F8&
/):3V 28:3&=(? 8&F8&
/): 28:/@@?( " 8&F8&
&: 28:@?( @22
8:/4* 1' 28:/9( @22
/4*: 28:/9(@?( @22
&:3LV 28:;(& $ @22
..:2,V 28:2(</= @22
8:8,1
V
1' 28:3&@2( ' @22
;7 1' 28:;@8B # 8&F8&
?93C2
) 1' 28:>22 8&F8&
?93C2
;E1 28:;(? " 8&F8&
?93C2
& 28:&=@;3(/ ?93C2
X4 $ 28:X(9 ?93C2
8.: !@! 28:8(@>2/
, !@! 28:;0
8:. 28:8@/(?/ ?93C2
B*JF* 0 ' G2B3G+1- ' B?2JF3
V/11,111.&(&/!)1.4,
11,14,1,115)MprecisionN<H
),,,**.,*1$,1'
"
Note@2218&F8&?93C28&30;B9/2B?2F3B&2
D?)4*1!?)!
D1,111-1..11,.,
--,..111!
/*111.111.&>3/?(;3
)17*
! //@@?(*141*.4-I)1!
! /&@/>&*-1)11,)1*.4-
-.!
Note./@@?(*)1*.1)-1.
)1.1!
&4)&@/>&
Function Specified as: Applies to table types:
&:/@ tab1 8&F8&?93C2
&:&2 col1 8&F8&?93C28&30B?2F3;B9B&2/2
D?A
&:83 datasource1
dsn
sqldatasource
1
8&
&:8;0(; driver1
sqldriver1
8&F8&
Note4)*..1*.1I!
/ 8@/@3>;&( 8;0(;3 .11*41.)11
8&1)141!
/3C2831.111,.-,1
Field Data Type Nul
l
Info Type Flag value
'
28:@?(
81*
'
28:;(? "
/,14.1
)
4 AA%3 ?$A>) ?
/3C281.111,.-,1
'
Field Type Null Info Type Flag value
81*
9(3 28:@?
(
@)1 '
9(3 28:;(? "
9,1-
4(
4 AA%3 ?$A>) 4(
Another example, WMI table
/,),)1.D?1111)*11
11-D?)*1*T.R1!.1*.
1.1,))1,.-,
Name Type Flag Description
&: &=@; /.**4
8:/4* / /3C24*
/4*: &=@; /3C24*
&:3L / $ /.,
..:2, / 8*1,
3 / ' 8*14*
.4-11...1,*!
7*).,51*6)4.)
AD,?A9
A)&&9/:;)-
?%3491;)-
%3&9'/;)-
49/;)> 7-
D490;)> /;
4 AA%3 X>)
-I4
C>&3
-1*41
Column_name Data_Type Type_name Lengt
h
Prec
&* &=@;
81* &=@;
.4,)
&=@;
&=@;
3X>) &=@;
>>8 &=@;
0 &=@;
01 &=@;
'
/1*.1.,)!
Note 1/,..)1-1.))1)4
G11),.!
Note 2/31.71,111U ..)
1U.1!
Catalog Table result size limit
1,)1*11E.)458146-)1
1*.&/)111144!
4.11.7).
Catfunc Max lines
81 '
8
31
&1
/)1
D).1.)117-,111)4
&(&/!/114*-1)1-111,
4)1-)11*.!
.1**11*11)1.1,?@B;(3*.1
4 %3 6
4 ? ,A+? %&DX? &
344 ''!!!!>) )&
)1)11L).I417U
-)I411
)9C;>&
'
Virtual and Special Columns
&(&/1**1?8*111!11*11)1
),&(&/1*!2117*-1)!
/boys)-1*14)1
%9
4)&49/;)>)!34 84-
&9'0;)-
4%9'0;)-
4)>& ??""####-
4)>& ??""####> 1/-
4491;94&&34>>9%-4-4;;(4)-
>9'!!;)>)34 G?;
4 AA%3 GH>4& % :<
D.- &(&/ 1* 1! 9, 4 U 1 .
3(&@2*1*.11*.!
Note . 1 1*. . 1* 1 , )4 &(&/! /4
1*.G1*,,-,11,1-1,--1!
..agehired&(&/*1)1*.11
..1,),1.!
/
C>&%84%
D1*4.-,111
linenum name city birth hired agehired fn
F 1 "'HH
H'H
$ d)d1Idd)41!7
=
4
1 "#H'H
#
H$H
d)d1Idd)41!7
' 1 "'H"H
H H
d)d1Idd)41!7
(71,1*11.-,)
Special Name Type Description of the column value
ROWID , / - ) ) *! /1 I
I-.)1-1
)-,-1!
ROWNUM , /-).,*.-1!/11..
.;D8 .* )1/2JB&2J&&>;J0/ )1
B?2 )1 - * . 8 )1 -
;D>?11.-1!
FILEID
FDISK
FPATH
FNAME
FTYPE
3, 2(81...1-),1!>1.
* . * )1 *1 )4 1 .1! /
1*1)14*..!
TABID 3, /.)1-),1!>1../2)1!
'
Special Name Type Description of the column value
PARTID 3, /.*1-),1!3*.*
)1!
SERVID 3, /..1111)4?93C2)!
58&6.8&)5F8&6.F8&)5&6
.)4*1!
Note&(&/ 1 41** 1! =- ;D8 1*
1*111.1E1!
Note&(&/1**41)14)1**!
'$
Indexing
7,1.-41*LI1!X41*-41
G)11)7!-1).14.-11M
.44*)41)7)1,4-1
)1-,)1I4!=-.11
1)E7,),)1&(&/1-1,
)1I4!
&(&/*1.7,4*1
! 37,
! E7,
! ;7,
$! 847,
! 07,
(
&(&/171111.1,,1,4
1*..!/&(&/1**11.171.1
..)1)4*1!
9.&;(@/(K/@2(11,&;(@/(K8(B1
@2/(;K/@2(1!117.14!/4)**
1,8;K8(B1@2/(;/@2(11117.1!
7141-)1.)4 3(;/ >8@/(
8(2(/(1-3(8(B*1,!.4.,1
) 41)E, *71) 1.
1!/71-)1-E,)!1
O'84
44'()9;
44'()9;
)O
.)-1.)47**17,711)
1*,.1*11!/11 /?A( /@2(
!
-)17.11-**,)!/1111.
.*11*11)4.1111,1...)1!
>E1&(&/11711.1)4..
..) . 4!8*,
3(8(B)*711I.1*.1.3(8(B1!
1.711*.1*47.)*!.4*831.
*:;?@;9!7!/1E1*11).1)11.-
..*111****17.11-!
.7 .1...1)11)1
1.-..711*.4)17.-B2(:@?(*!
Note 171) / >22&(&/1**,71 ,
1!
Note 2?;;11)417,.1)!
'
Note 3.77,11**!.1*.&(&/,,1*.7)1-
7!
Handling index errors
/-4&(&/17,141*.!@).1,11.
7,!4.)1).- /?A( /@2( 11
71!.11*,71!=-&(&/),
H1,U1))E,1)!/11.
7,1
/4,7)!114)
1).1)E7!
(,*17)4I7!11.7-1
-,41I1.1!.14
I1414**1!
)11.,E71-/?A(/@2(!
Index file mapping
/7,*11&(&/E1714.7
.!/1))4,7.1,1.-14)45.**,6!
)1.**,11,connect_indx_map141)!3
..**,
#5(
/*J*&(&/11-*11)J-)4)E1.n-1n
),,2&X:3A(*.&/).*,
)4*!/11..7.1B80(&)1)1*..
).183&30?/!
)E )1.*L). 1 .11
516,441)),*11
-1!E7,*11E*)E1--1..*-
,)E!/11*.11!
91-,))41,83/;Rd*!/d
)scattered, clusteredsorted!,4)1!E7,11
4.111!
Difference between standard indexing and block indexing
E7,14)4&(&/-,1I4)!/1
1-17,11))E7,11!
I4417)1!=-)E7,)
11,.-1*4,11J11!
/)E7.1.1E117.1!
Notes for this Release:
*1.4)&(&/411
11J7)*1.)E),1E*)E,
*)1!1-*L.11*4)
)11)414)11.1)
41/?A(/@2(1*L.!
31**11,1411,1!&1H
1,1).,,11!**1,1E>*1
*1)1.,,-*L,)!
''
E7,)--41!X*,J7171,.)E
E*,)*-,E-,-11)E!/11H
-41.,1)*L7*.11.-)IH
!/)***)41,411!/1
11)4?@B:83/*11-,)!H
*)E7,-)1.1)1,?@B?1,4
!
&(&/*.)E7,1111!/.)E
7,1*.4111111.1
4&9';)4'4
44 )&4 <-
2,(
;7,11*.?93C2)4*!1I -(8(;@/(8
1,1!@#*)11**71*1!111)1
41)1**171!D?93C2)11G1
3(2(&/ 11 - 1-,1 1
7,-**)!
-)1&(&/1-*11) *.-1 .
,I41**1..7147!
/4)4*11)471-11
G)1!-1I-1-)13(2(&/I4!
,#(
@71546117-111)1.
1*.I4!/1**1.1I1--)1G)47E4!
.5from”)1),5to”),)1L)1.-1)
--171)!
1.)41,71-))4.,)4
,71L.11!/.11).
5from6)11)1-),-G,*1!
-1,.1,471
7,1.1.711!
/G*11-4-1..,-1!
1)11I4-1,7.?;;!
&1,7).1.)1)4)E7,!
D1,7&(&/11141.11!
/11*1*4*!1.711G1
*44)!
>.41,,1),*4)4?8.)&(&/1
,).-1,147,!/.411
411-411*GI1E4,4
11.G,)!=-7)41)41,
89@?7*!1I4
4-)9C;>&4-34383
33%Y'=)% 1!YZ''4
8:0./.:1:)3%4%
3I4G,diag)patients)414,.)1),!
/*4E4pnb.patients))4
'#
34334&%O%
3434&%O%93;&&?#A%& '
Note/14)1.1714.41
3=D8(B!
Note /14-4G1,89@?*-**,,
7!/11.)1E1!
!(
**1 4 )1 . 4* 0; 1 ) 1*.4,
3(&@2R;D83(&@2R;D>?!
'
Partitioning and Sharding
&(&/1**1?43C2J?8*1*.!1 1-41
?43@?8)41,@;//,1)).1-E!/14*
.*,111.15L*,6!
, )141)*1.)111.141,
1-41,41!....*1.)111*
)1..1!/1H1)4-1.1*11
E-1*,.-?8)11*,,11
.,1111,.1,.!
&(&/E111*.)4*,-4*1.*,
! *,!(*111*.E*)1!
! /)*,!(*111*)E/2)1!
Partition engine issues
>1,*111I1,)1-41
1*,),1
! (,1*.7*1,L1147-
)1!/-E1 )-1*1 &;(@/( /@2(
1.-)4@2/(;/@2(1!
! / 1, - 1*. . ) 1 1 )4 *,! /
-E11*.41,/:23/!
! ?43C2*1),c#"!1.11*,1111.1
5*11)-61) 1!/1E1-,1-
1)!/1-E)1),1)*.4.7!
/.-,7*11,)-E147111111!
$
*,**1.)1&(&/)4*1!2E.*)1*411
11.11.G1!/..1.*)1
! 811),1...1.-,*!
! >E*)1*)14!
! >E*)1*)1)7)!
$! /.1,.*1!
! C4*,14)4*,!
/).1,..4*,-)1--
)!-)1.-.11*.*.1
?>4&@ table_nameUDUpartition_nametable_file_type
>4&@ table_nameUDUpartition_nameindex_file_type
1.)
A,$'9
4N#+-
&B,A5,910;;
K AA$#D GH
344%94;9
344>4()9'!;-
344&4()97!;-
344()9HB+;;
'"
&(&/-,4.1
'UDU>4>4
'UDU>4>
'UDU&4>4
'UDU&4>
'UDU>4
'UDU>
/111-*,1.,1!@1..1&(&/
*-)1)E,1*)1!F1.1..!
Note.1)H*,11-).17.1
table_nameUDUpartition_nameUDUsubpartition_name.type
table_nameUDUpartition_nameUDUsubpartition_name.index_type
Outward Tables
/*)1--)1*-4.471,
.1!/.1111E*)171,.1!/1
4.471,)11)1*,.1).
471,.1!
/.111111)4-4.11!111**1-
-E)..7..1
@S?S3'
@S?S30
@S?S31
/1))4,)11
09
44)-
&(910;-
4H?94;;
4 %3 GH>4& @"?"3T
344%94;9
344J'J()9'!;-
344J0J()97!;-
344J1J()9HB+;;
/1.*,.14,)4*,
,2(:@?(*5%s6*)4*!
. )-14. 11-**, * .!
=-..1711141*1)4-*
.11)!/11**,)4E4)4
1)17*7*11)141-1
,1!
7*)1*).)14).111*)1
).1)1)1)!3**1,--
561 -14*!7.-)1-1,-
)).41E
C>&084 '0
#
/1--1!/11)1,*.I4*,-
4E11*-11-1-,*!
-4E.-,1)..1*11.I111
A344&-8G,4>&4&36
44X5,& 0
@
AAX54Y'!5'X54Y7!501?
3-A+9C;G,31K,+D$#3
.41)),11*!=-.4
1)114-,!
Partitioning on a Special Column
/111-.1.*)1)1.
,1*,!111**1-*))1.-,
.1
&3"
&3"4
&3"
(.,1E.
?@4
G4&@(9'/;
&@(91!;
$4@
54@
L@9'!;
%@)9<-0;
@*)).1)4
&)&39
44+-
>4&(9'/;+-
&(91!;+-
4+>& ??""####-
4+>& ??""####-
M9'!;+-
%)9<-0;+
;4 AA%3 GH>4& &3"C&)43 '
/111.--*)1.111.
.,*.!(4.1E.11
-41,1!
=-11!1)1*-)1)4*
.!1-)))4
3&39
44+-
>4&(9'/;+-
&(91!;+-
#
4+>& ??""####-
4+>& ??""####-
M9'/;+-
%)9'!-0;+-
4%9'0;>)34 D,?-
4H?94;
;4 AA%3 GH>4& @"?""T
3&3
344%4)&94%;9
344JJ()49;-
344JJ()49;-
344J4J()494;;
Note 1--1*1)1.&(&/*1!
Note 21*@;/81.*--1!
Note 3-1@?(1*1)1.11*.
1),*!
/1411*)11.1--)*boston.
),1* bostone=--E1)1*,1E-)
1*1)11.city-1!
D**1.-**)4M
443&394->4&-&-4-4-M-%;()
9'0!7-5%-A(-'.<06'!6!=-0!'!6!.60'-K-'07!!!!!;
443&3()
9'70:-L4&-$&-'.<76!/6'<-0!'06!=607--70!!!!!-4;-
9':1'-L%-XO-'.<<6!16'0-0!'06!<6!.-,,A5-:/70'<=-;-
9'</:-LO-?4-'..'6'06!'-0!'16!06'/-?BD-/17:!7!-;
/,. city7*4)4.-)1)4*,
-*1-1!-),)4&(&/1*
),)-,!1
4%->4&-M>&3&38449'70:-':1';
/1I41
city first_nam
e
job
)1 F4 ;(3(@;&=
,
F 3@2(3
(4,-E11.city-1).1!
Partitioning of zipped tables
/-1141**
.)1)11L**.1*,11-41)!/11
file_name*1*.4,.L*.11T[1*1,
.1!
.)1)14L*.,111-))4*,
T[1*entry*!
#
Note.)1)11L**.1,114.111
*11)!>1,1)H*,E*1411**4!
$
D)*,*1*414*1)41)H)!&*1
*,1),1.-,.1
! /*1))1)4..,1!/1171,
.*,!
! /*1))1)4,141**,*,!
! )1)11),)1,!
$! 2E./2)11.*)1141
.1)H)1!
/-411*)-)4*.,)1 ;B9
?93C28&F8&!211-111*7*!3**1,-
.-,)1
'9
44)-
&(910;;
4 &%4&
09
44)-
&(910;;"C4 4?$C"
19
44)-
&(910;;
4 %3 AB
D.1*)1,1)11*41*1)4
19
44)-
&(910;;
4 %3 D,H#& T
344%)&94;9
344J'J()9'!;-
344J0J()97!;-
344J1J()9HB+;;
=.*1)H)-))4*,T[1*.)
*)4*!-.-
441()
9:->);-9=-(;-9'!-;-9:!->%;-
9/!-4%;-9<'-4%;-9=0-(%8;-
9''-(;-9'-;-917-4%>4(;-9<-4;
/-1-)1)..1)H)1,*.!/1)
1)47,I4
344&-8>&4>&4&36
448& 1
#
/1I4*1
partition_name table_rows
$
$
C4*,1.1.1
34344C>&184 <'
/1I4*1
i
d
select_type table partitions type possible_keys key key_len ref rows Extra
3?2( * @22 PQ P
Q
PQ P
Q
>1,-
D7,11I441)H)7-)1!
Indexing with Table Partitioning
>1,;B9)4*11!=-11111;B9
8&)17)!/..4-))7411
?93C2)4*!/&;(@/(/@2(1-)11
:9
44O%)-
&(910;;
4 %3 #*& T
344%)&94;9
344J'J()9'!;-
344J0J()97!;-
344J1J()9HB+;;
/id11E4)4*1-?93C2!/1E13)H)1)
11)4, ?43C211?93C2)1 !1.14 -4
&(&/1)H)111)1,)1-14111-4!
=-7,G1E*)157,6-4(8(;@/(8
)1!/11-1,I4)-1-)
I4!111**141E
C>&:84 =
/I411-)
AJ4J-J&JG,J'JX5,J4J =
I4E11,)1-1)
4-4)4:*1.)1E..1.G!/,
117,1)4).1)7!
/11177 7)711)1*4)1,
)171notE711)H)1!
#$
Sharding with Table Partitioning
>1,)*,,!11)H)111)
11*11)1)1*11-1!
/14)I111)41111
11.*4)1!)G111*,).*.1!
11**1,-.-,)1
'944O%)-&(910;;
4 >4 &%P@""]'""
0944O%)-&(910;;
4 >4 &%P@""]0""
1944O%)-&(910;;
4 >4 &%P@""]1""
&,*)11,1-)1E---t4)
79
44O%)-
&(910;;
4 %3 #*& T
344%)&94;9
344J'J()9'!;-
344J0J()97!;-
344J1J()9HB+;;
/4..1)*-.,rt1rt2rt3)1!=-.
-E111)1-4!/11)111,)?43C2@1
-*)!)4&(&/11(8(;@/(8)1
1)4(8(;@/(8,11)!
/&(&/?93C2)4* ),14-441411
)1!*11)1.4>;21!
111-)*))1
/9
44O%)-
&(910;;
4 %3 #*
344 &%P@""]T""
344%)&94;9
344J'J()9'!;-
344J0J()97!;-
344J1J()9HB+;;
3,1)
! @1-1).*,4111,!/11-4
-11*.156*1!
! *1)H)15[16*.1,1)*)4
*!
! 14. )11
(8(;@/(8,1111)1!
/111*1-1,111.1)H)1!-.
1)H)111)44..1,1M1
#
G'@4 &%P@""@4]'0=!!'@11!=""'
G0@4 &%P@"">@>3]("&3"0
G1@4 &%P@""])@77!7""
/-11!/.111*1.1,..1
*1
=9
44O%)-
&(910;;
4 %3 #*
344 &%P@""T
344%)&94;9
344J@4]'0=!!'@11!=""'J()9'!;-
344J>@>3]("&3"0J()97!;-
344J])@77!7""J()9HB+;;
/1-,,,*11.1111
)1.44711G11!1.1)
(J(J>4833&%P36
4
9'0=!!'-
-
)-
384-
311!=;
3451:-651:6-).*)-)
1
<9
44O%)-
&(910;;
4 %3 #*
344 (T
344%)&94;9
344J"'J()9'!;-
344J8"0J()97!;-
344J"J()9HB+;;
-)1*.)11)11.1.
4-xt11,)1151:[1J76*1
-)G1565-656!
Sharding on a Special Column
/I- 1 ) -.*, 11 ) -) *,!
&*1-,11)1*4)111-
1).1*!1
.9
44)-
&(910;-
9'/;>)&434 D,?-
4H?94;;
4 %3 #*
344 (T"
344%)&94;9
344J&4J()49&4;-
#'
344JJ()49;-
344J8J()498;;
/17*111.11)51:651:16
51:-16)15116!117*
id1E4!
$,
1'$1!
1*,-1-).1,1-?8-4
-E111*)-1,1*-&(&/!
Update statement
D1*)1)4*1111
)30& P)84 :
-E1*.41*)4&(&/!=-111
)304 :'8& >)
/111*)4&(&/!/11 id ),*.
*.,,14I.-)*!
/-41)4*,1-H1-.
!=-11-414*)-&(&/)
&(&/1**1>8@/(1*.-4.)4*?93C2
/1)*4!?--E14-1)
8,-.41,.-
>&084 :
440()9:'->);
Alter Table statement
&(&/-)1@2/(;K/@2(11E4,)
!/11-4@2/(;/@2(1)1.4*.7*.1
-,.!1, @2/(; /@2( *)-1*1
)1*1-)111**1)14*!
@1-1111.1*71!-11*.)1
,).)*H)-1.1)
.!F1).-&(&/)1**,)11
,1.471,!
Rowid special column
(*),1*41);D81*1E.
-1*-)!/11.*)1;D8;D>?
I!
##
Using CONNECT
/1.&(&/1)11,11.-1
L)1!/1..E,11)4.1*
1.I4E1&(&/41..*,7*,.
?8)11.4*1.111,**1!=-11
.1**1!
174*1)4&(&/11)*7,H1!14
.1.,1)-*,1!..471
.),)71441),..1.1.)H
7!4111,**1.1.1.!
.41,&(&/4L.1).)47*1171
.1.)4)!>1/?A(/@2(*
).1,)1)1!
/111&(&/11,)1)4L11-14
1.11.1,-,!
Performance
.14 , *,)4*! 1 8& )1 4
1.118?3!.4.I17)1-4
*L4-E)11.4*!411
41*-&(&/!7.EB0(&)1).1-
1)4111
Afastabletable_specsC>&slowtable
B.))1J.1)E1.2&X:3A(
-1!0(&)1)4...)1,414.-),1
I4!.)1.1)1L?@(8*4.1**4
I1!
Create Table statement
-.-)E1.&(&/)1
Inward /4)-1.11*.!@*4.-),
.tabname.tabtype-)**E.,1!/4
I2(*,)1.1,**1!
Outward /4&(&/)111711.1!/4
1.)1)I2(*,!
Drop Table statement
-)18*/)1G11).)11
)!=-**,-)11)1-!
AlterTable statement
.1,@2/(;/@2(1--)1!&4*)41
1..)*)-!*@
.1).4)1.4)!&1I4)4*
1).1-47*.!@1,**,
.4,14)-,)1...11-7*4,)4
2@<*4)-,-*-11,1!
#
3.1.@2/(;1.7,1-1,*111?@(8
=><( 1*. )G1-4.111!?.4,
2&X:3A(*1,-B81*0(&)1U-11..0(&
)11*4.)111L1)
*.)E1L!/111.111*.-.
)E1L!
1111.*H)-)1E.
)1).!/111.11,))1)1.!
Update and Delete for file tables
&(&/711.-,-..,1
! *4.4,-1*,-1-)
.!/11.1-4-7,11!
! 1, *4 . E,1! /11 I- *,
),)11111!
/)-1,1*1111)connect_use_tempfile!/11
111)),1
Value Description
NO /.1,1-411!111-*,)
,)111)14.1,!
AUTO /11.!1&(&/1,1!&41
I 7*- *,), )183&30
?/-.**,.!
YES >1,*4.11-17*1-.**,1
. 0(&)1 -, .8)11.)
,)1.**,1.!
FORC
E
2E9(3).,.**,).)4*1!
TEST ;1.&(&/*!
/.@>/.1)11*1!>1,*4.1,)1
),-*111*4)41!
,)&
841,7.14,111-E5.16*.
1)41,11*1-11.**1!=-44-1
1*74?8)1!/11741*.7)1,
&(&/!111**14-*. xsample.xml
B?2 .*14,7*?43@?) biblio ),, connect
)1!@41)4
444 &%4&C>&&30
/1111?43@?)11,B?21)
. )4 xsampall2 &(&/ ) ?8 biblio )! .
1.))41,)31
&1.11,.11**4,.1!=-)1
&/)*11*1).1.xsample.xml.-,
biblio),1biblio)-.4xsample.xml.!
@ 1 ) ) 1. )4 . 3C2 *1!/1E1-E, -
&(&/.7))1.1G11,2@81!
#"
(),&
/11)14*11)-&(&/*..11**)41
T..31!211I4
3)4&-3)4(4(4-3)4)
)-3)4434434-3)4&)4%&)4%
>&4>&4&3)483)4%3 ,KK
3**1,4-,1.1I4. handlers.htm B?2J=/?2 .
-,1*4,)-111-4
F1&(&/)-)1E.
)
4 AA%3 H>4& )& %
344 & $-%3 5
-4) '34 7- %8
3)4&-3)4(4(4-3)4)
)-3)4434434-3)4&)4%&)4%
>&4>&4&3)483)4%3 ,KK
=.1,-.31.-,&!
/&(&/*11-1*14!/1-)1,
,handlers&(&/)T.,-I41!
Note 1/1)41.)4*I1,7*
&(&/*1!11.14)**-1
1!
Note 2/15*,16)51*61,74*1**
.&(&/)1!1)144*)4nn),.
connect_conv_size,))!
$
/8&F8&?93C2/2D?)4*11*1-1
).-1)4;831D?*!3?8!,
*1-1)4.!1.1141.1)4
34&4E84 4443)8
1,41I-1*1.1
&%P66664443)8
Note 1 1*.4, %1 1 * 1 11,1 . &(&/ *
)1?81-41!
Note 21?8!!$&8/:>3=8D,1*!=-
1)1&(&/11*1-4!
)6
/&(&/1<@1!-1-1,).)*G
-.?43C2 $.58&(&/6*,!-117*1
1)111)-1.111!=11.
.*1
! @,)4*1!
! ?E11.14
! 3**,*7./21)H)1-7..11!
$! @,4*1*1,1.1,!
! 3**,7,)1!
'! @,*L1)E7,47,!
#! 3**,?;;
! 3**,*,
*,1),.1*4-1!1*),111,
1*)4?8!
Security
/1.&(&/,I12(*,7*.5-6)1!/11
)*1!/1.&(&/5-6)1111.
1-E-,.171,1)*4-4!
1,111)11-41*11)4
1-2(*,!
Appendix A
3F31*.1!
Expense.json
_
RX5R@RLR-
RXNR@
_
R+$,R@1-
RHDR@
_
RX5R@R$R-
R+R@'<!!
`-
_
RX5R@RGR-
R+R@'0!!
`-
_
RX5R@RGR-
R+R@'.!!
`-
_
RX5R@RAR-
R+R@0!!!
`
`-
_
R+$,R@:-
RHDR@
_
RX5R@R$R-
R+R@'.!!
`-
_
RX5R@R$R-
R+R@'/!!
`-
_
RX5R@RGR-
R+R@'=!!
`-
_
RX5R@RGR-
R+R@'=!!
`-
_
RX5R@R$R-
R+R@':!!
`
`-
_
R+$,R@7-
RHDR@
_
RX5R@R$R-
R+R@':!!
`-
_
RX5R@RGR-
R+R@'0!!
`
`
`-
_
RX5R@R$R-
RXNR@
_
R+$,R@1-
RHDR@
_
RX5R@R$R-
R+R@'/!!
`
`-
_
R+$,R@:-
RHDR@
_
RX5R@RGR-
R+R@'=!!
`-
_
RX5R@R$R-
R+R@'7!!
`
`-
_
R+$,R@7-
RHDR@
_
RX5R@RGR-
R+R@'0!!
`-
_
RX5R@R$R-
R+R@0!!!
`
`
`-
_
RX5R@RLR-
RXNR@
_
R+$,R@1-
RHDR@
_
RX5R@RAR-
R+R@'.!!
`-
_
RX5R@RGR-
R+R@'<!!
`-
_
RX5R@R$R-
R+R@'<!!
`
`-
_
R+$,R@:-
RHDR@
_
RX5R@RAR-
R+R@'=!!
`
`-
_
R+$,R@7-
RHDR@
_
RX5R@R$R-
R+R@':!!
`-
_
RX5R@RAR-
R+R@'0!!
`-
_
RX5R@R$R-
R+R@'.!!
`-
_
RX5R@RGR-
R+R@'0!!
`
`
`
$
Appendix B
This is how the REST feature can be added as a library called by an OEM tableBefore making
the REST OEM module, the Microsoft Casablanca package must be installed as for
compiling MariaDB from source.
(.11)1-)41)41&(&/1.1
111.4 E ! 1 - . .1 71, 1 !'! .
&(&/tabrest.cpprestget.cpptabrest.hmini-global.h!11&(&/
.1tabrest.cpp14!/1)))4,,
- 1 . 1 . ?8 1 ;(3/. -,
?81.!,L7,.&(&/11.14-
)11.14,).1!
D-111.013!?E-*4822*G
1.1 tabrest.cpp restget.cpp!011 14, 114
1 cpprest 38X!F1**1.*G
4-&(&/1-1-Eha_connect.lib.
)41.?814ha_connect.dll4)41)!
@11E**,.*G**4?.rest.def.
- 1 . * 1 71, &(&/ 1 .1! . 4 E, ),
.,E1&J&OO&,*,;)41*.1
?H8),822J?841-1-1,.!
/1141*)1,*-27e3,(?
.1-**-
-1,OOJ27)74*E4)-4--N
1VW)4WV. E, **11! , ,L..
]:!1]1WWE))4]:!1]1714-1*1
]JJ!1!.]]JJ!1!.!J:!.]V@8V1.1),-W)W!
14G1E:!1)4
a)6"3"")"4/:"4
/1 *1)4-1 ),1-5)6! in /usr/lib64/ )1 -1
4.libcpprest.so&1))4!/11..JJ!1!.!1
1-14.cpprest)4!H1*11-1.
141-1,.!
/-1)*1(?-
a&O4&
a&
a&O4,
a&O6>&&O
a)3")""&%P"4"3)4
/oemrest.mak.
U+H
ADD bb
? bb
? ","
? "&"4(4"4?$"("""
A? ")"4/:
U>&34M>4)4%&44%
AGK 6X6616 bb''6>DA6>646a9?;
U,36!1%6>)
?GK 6a9A?;6336
UG%&44%
?#NGK 6
U&3>&-)6K4
U,4%4(>4
,
,AADD a9?;33a9?;33
$LADD a9?;a9?;
U36()
@a9,;
a9,;@a9$LADD;
a9?;a9$LADD;a9?GK;a9?#NGK;6a]
UADD)>4
a9?;@a9?;33a9?;&446a9?;
a9?;3&a9?;a9?;>4&a9?;3&a
9?;a9?;>&a9?;Ma9?;a
9?;&
a9ADD;a9AGK;6a]a9?;a9CG;33
a9?;@a9?;33a9?;&446a9?;
a9ADD;a9AGK;6a]a9?;a9CG;33
U(%4
@
a9,;a9$LADD;a9,;
/38&8)11.&(&/1.1,
libcpprest.so)!/4)14814-1
)G.1!
@4*.,1H.H!D4-)),)!
/1,.11!11!1)**,4.
?8 1! /4-))13C2 )11*4*,&;(@/(
/@2( 1 /@2(:/9( * F3 B?2 )4 /@2(:/9(R(?
3>/9(R;(3/?8>2(R1!1g!@41**14T1
)4,4E!
/.4*1F3)4.!.1)1*.E1
D fG%3 Hf
/))1!1
A,$88
K AA$#D ?+ ,+$#D ,
G 8&
5D 3@""&338&3""07">F
P -)Q& &Q334 /.!=0<.'!=':/<<1!=/'>00
D G%3 H-( 1-,8 8
Note117*1B?2.-1.-1,L)4&(&/
11!11.*T;-R-!
'
.4)E,4*1F;@14)4!
#
Appendix C
Compiling Json UDFs in a Separate library
@,F3>81)4&(&/)411
-441*)4!
/11-&(&/1*).4-111>81-
?811,!
/E4111?81!/E*G,
1.1
! G1.!**
! G1!**
! !**
$! 1!
! *,!**
'! *!**
#! G1!**
jsonutil.cpp11)-14-E..-,
OP 3".P
OP (.P
OP*.P
OQ".R
OQ.R
OQ.R
OQ.R
OP".P
P'P4-5)S7T
4U4*!V5)S7T
HWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWH
HW-.*.5B-2WH
HWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWH
*3*WDB-D7
DB-D%BG5D42+2<D!X<"<"<S8227T
,B5.W<DB-D<<)IJS7T
O33933
3"'G656B2,.
S
Y'65.7
THW'G6WH
OHW8:WH
3"'G656B2,.
S
55.F-18,KG2!,7
THW'G6WH
42,5
S
7
THW42,WH
OHH8:
HWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWH
HWD"#WH
HWK-.()D!"*.WH
HWZ <822WH
HW0*-..)WH
HWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWWH
)WDB!"5D42+2<)W*<V3V
S
DD++26,B,1**.7HHD.
5Y* HH".!
*A#R!7
VA55VELHW7HW1*V*WH
**.A5DD++26,B,1*7
55VR**.#RG%SHW. *WH
*5#R<
P. 9%([5A[A[P<
5V<**.#R-3G<**.#RG%7
8227
THHV
HHB.".*$
*A%D5*<**.#R-3G7HHD"3"%
**.#R-3GEAV7HH$\*"%
**.#RG%#AV7HH$V*"%
5*7
THHDB!"
9.)4*4J*1.)!
31?811*,*,
*1..1,J1*E4>8,,4
)41G1.!D-1
/4.11,11soname*!
/111-1,>811-4
/connect_json_grp_size))11!/,*1L11!
1.-,1*)411,11stderr!
!
"
Index
*"$$
, "$'#
&,1
&:&2&1
&:8;0(;81
&:83831
&:/@/)1
&,)1
1$
&*1
8@/(:;?@/
(28:;?@/
(28:2(</=.1
2@<$
3(&@21'
*11
8/4*1
/9(:</'
TYPE_DATE
/9(:2@/'
/9(:/'
/9(:3=;/'
/9(:3/;<
)1 $$$"$"' $
$$$#'##
:.# "'#$"'
, $'#
(7.1$"'"#
(8(;@/(8$$
.:.$"$$$$'
$
(28:2(</=#$
., "#"" "
$ $$"'#"
'#'
.$# "
$'#"$$$
$' $"" " $
'#'###
=@'
=1
=/?2$$'#
7 $$''#'
71 '#'
7,""# $'#'
JSON$ $$ #"'' ''$
''''#''"### ##$##'
####"'##
@41
)G1
01
4$'""
27 "$
?(;<($$
* $ ' $ "#
$ '##
>22$" $ $
#'
$
..1$ " "'#'
*:1$$$'$
$'""
$$ $$$$" $#
*11-
*
*74 $
E$
1"
3*&1
2(8'
;D8'
;D>?'
/@8$$$'
1)$"
/)*1
2&X:3A(E.11L
&@/>&&,.
&?;(33&*11.
8@/@:&=@;3(/ 8 1
#
8@?(3)1
(8<2,
2(:@?( $
=(@8(;
=><(, <
2;(&2;,
?@(8>1,.**, #'
?8>2((7)4822
?>2/2(?*.1) $
/:23/21.*1
C&=@;C,
C>/(8C.1
;(@829)
3(:&=@;3*
32/3*0(&.1
3>/9((?)1)H4*
/@2(:/9(4*
/@@?(3) #
B2(:@?(7.
/)/4*1
4.1$ $#'
##
&301&30 $ '#
#
8@3(.1$ ###
8;841, $$'$
$"
83/7.1$ $ '
B7,7.$ "
'#'##
"
?/.1$ '"$
&.,.1$"""
?@&111$'
?93C2 /@2( @&&(33(8 0@ ?93C2
@ $ $
$$$$#' #
&&>;/)-
8& /@2( $
"$""'"##"$'
##'#
(?(74*4*
$$
;B9/)$
/),$
/),1
/),)$
/221.&(&/)1 $
$ $$$'#
0(&0.1$' $
#'##
D? D-1 ?,
1 $'
#"#
B&2/)-$
B?2 ; =/?2 2(3 $$$$
$$'$ $'#
'###
>B $ "
>1 $$$$#"
D-1 "$""$'
$"
B?2)*1
@)'
&$
(,'#
(7*
2
?
;-
B*$$
