NB. Joe Kunkel's experimentation with SQL of Lobster serum database.
NB.  a) Discover how to change the ODBC setup using the Help File
NB.  b) modify the axcolumns, axreadal, axreadsel for serum table
NB.  c) define cols, extract column headers, rotate and append to out
NB.  d) modify select to pick all females greater than 1000 g
NB.  e) integrate reading of map coords for plot routine into 
NB.  f) created boxed vectors of data [dat=: xmap;ymap]
NB.  g) lat=9998 gets sequential sets of boxed vectors
NB.  h) lat=9999 gets sequential color red=coast blue=borders drkgreen=50-fathom
NB.  i) axreadsel now accepts boxed 'tnam';col-criterion as y.
NB.  j) axconnect now connects to access driver-nam=y.
NB.  k) axcolumns now gets columns of y.= 'tnam'
NB.  l) axreadal now gets source table of y.= 'tnam'
NB.  ijkl) allow more flexible access with fewer routines 
NB.  m) axreadsel now gets and plots select sets of stations.
NB.  n) defined plotchars to plot box at vector of points.
NB.  o) autorun demolob''
NB.  p) Plot of CarpL vs RostCL
NB.  q) Plot of wgt vs RostCL
NB.  r) range of x and y axis determined to adjust char size
NB.  s) set yint to define the y-intercept of the CarpL vs RostCL plot
NB.
NB. Uses Access 97/2000 ODBC 
NB.
NB. See odbc.txt for general comments on these scripts
NB.
NB. requires ODBC setup of:
NB.   data source name:  jaccess
NB.   data source: ...(path)\lobster-serum.mdb registered in the
NB.                    ODBC database registry. 
NB.
NB. The Access driver connects to an Access file. ODBC tables
NB. correspond to tables in the file.
NB.
NB. verbs:
NB.   axconnect    connect to access driver y.
NB.   axcolumns    columns of y.= tdata
NB.   axreadsel    read a selection from y.= tdata
NB.   axupdate     update a field
NB.
NB.   demolob''    run a demo

load 'plot trig'

require 'dd'
trans=: |:     NB. matrix transpose
IO1=: ] i. 1:	

demolob=: 3 : 0
out=. 1!:2 & 2
empty axconnect'jaccess'
icolor=:0
NB. junk=. 'columns:',":cols=: axcolumns''
NB. out 'read all:',":(1 13$,13 1 {. 1 3}. cols), ";axreadall''
NB. out 'read selection:', ":(1 13$,13 1 {. 1 3}. cols), "; axreadsel''
NB. junk=. 'columns:',":cols=: axcolumns'map_coord'
NB. junk=. 'read Map Coords:', ":(1 4$,4 1 {. 1 3}. cols), "; map_coords=:axreadsel 'map_coord';'(lat >= 0)'
NB. map_coords=: 2 }. map_coords
NB. xmap=:, ; 1 }. map_coords
NB. ymap=:, ; _1 }. map_coords
NB. opt=. 'backcolor lightgray;color red,darkgreen;textcolor darkblue;type line'
opt1=. 'backcolor lightgray;color red;textcolor darkblue;type line'
opt2=. 'backcolor lightgray;color blue;textcolor darkblue;type line'
opt3=. 'backcolor lightgray;color darkgreen;textcolor darkblue;type line'
opt4=. 'backcolor lightgray;color orange;textcolor darkblue;type line'
opt5=. 'backcolor lightgray;color black;textcolor darkblue;type line'
opt=. opt1
pd 'new'
pd 'backcolor teal'
pd 'new 20 20 470 960'

pd 'frame 1'
pd 'xtic 20 4;ytic 20 4;yint 60'
pd 'grids 1'
pd 'gridcolor gray'
pd 'title Gulf of Maine & Georges Bank'
pd 'titlefont arial 30 italic'

NB. Chose next color to use

NB. Now plot the track line and stations:
junk=. 'columns:',":cols=: axcolumns'serum'
NB. out 'read all:',":(1 10$,10 1 {. 1 3}. cols), ";axreadall 'locals'
locq=:'AL0006 leg III'
out 'read select:',":(1 10$,10 1 {. 1 3}. cols), ";selout=: axreadsel 'serum';'(Sex > 1)and(CarpL > 0)and(RosCL > 0)and(CarpL <999)and (RosCL < 9999)'
NB. out 'read selection:', ":(1 7$,7 1 {. 1 3}. cols), "; axreadsel'tablenam'
NB. station=: ,;1{. 3}.selout
datetime=: 1{. 1}.selout
dt1st=:,13{.junk=: ,1{.;datetime
dt1st=: dt1st,':'
dtlast=:,13{.junk=: ,_1{.;datetime
dtlast=: dtlast,':'
carl=: 1{.selout=: 7}.selout
roscl=: 1{. 1}.selout
spanx=: ;(>./;carl)-(<./;carl)
spany=: ;(>./;roscl)-(<./;roscl)
dat1=: (,;carl);(,;roscl)
pd 'textc 500 960 ',locq
pd 'textfont arial 20 bold'
pd 'textc 500 930 from ',dt1st,' to ',dtlast
m0=: 6!:0	
m9=: ('/'"_) 4 7} [: ": 1000"_#. 3: {. [: <. m0
pd 'textfont arial 20 italic'
pd 'textc 500 900 accessed on ',m9''
NB. pd opt4  NB. yellow
NB. pd dat1
pd opt5  NB. black
junk=. plotchars dat1 
NB. pd opt2
NB. pd dat2

pd 'new 510 20 470 960'

pd 'frame 2'
pd 'xtic 20 4;ytic 500 4'
pd 'grids 1'
pd 'gridcolor gray'
pd 'title Gulf of Maine & Georges Bank'
pd 'titlefont arial 30 italic'
locq=:'AL0006 leg III'
out 'read select:',":(1 10$,10 1 {. 1 3}. cols), ";selout=: axreadsel 'serum';'(Sex > 1)and(wgt > 0)and(CarpL > 0)and(CarpL <999)and (wgt < 9999)'
NB. out 'read selection:', ":(1 7$,7 1 {. 1 3}. cols), "; axreadsel'tablenam'
NB. station=: ,;1{. 3}.selout
datetime=: 1{. 1}.selout
dt1st=:,13{.junk=: ,1{.;datetime
dt1st=: dt1st,':'
dtlast=:,13{.junk=: ,_1{.;datetime
dtlast=: dtlast,':'
carl=: 1{.selout=: 7}.selout
wgt=: 1{. 2}.selout
spanx=: ;(>./;carl)-(<./;carl)
spany=: ;(>./;wgt)-(<./;wgt)
dat1=: (,;carl);(,;wgt)
pd 'textc 500 960 ',locq
pd 'textfont arial 20 bold'
pd 'textc 500 930 from ',dt1st,' to ',dtlast
m0=: 6!:0	
m9=: ('/'"_) 4 7} [: ": 1000"_#. 3: {. [: <. m0
pd 'textfont arial 20 italic'
pd 'textc 500 900 accessed on ',m9''
NB. pd opt4  NB. yellow
NB. pd dat1
pd opt5  NB. black
junk=. plotchars dat1 
NB. pd opt2
NB. pd dat2

pd 'show'

NB. empty axupdate''
NB. out 'read updated selection:', ": axreadsel''
empty dddis CH
)

NB. =====================================================
NB. plotchars X;Y
plotchars=: 3 : 0
xvec=: ,;1{.y.
yvec=: ,;1}.y.
addvecx=: _1 1 1 _1 _1
addvecy=: 1 1 _1 _1 1
label_P0. xchar=: ((5$0.01*spanx)* addvecx)+ 1{xvec
xvec=: 1}.xvec
ychar=: ((5$0.007 * spany) * addvecy)+ 1{yvec
yvec=: 1}.yvec
pd xchar;ychar
if. 1 < $xvec do. goto_P0. end.
)
NB. =====================================================
NB. axconnect
NB. connect to access driver,
NB. define and return connection handle CH
axconnect=: 3 : 0
CH=: ddcheck ddcon 'dsn=',y.
)

NB. =====================================================
NB. axcolumns
NB. read columns of location
axcolumns=: 3 : 0
NB. ddcheck y.='table-nam' ddcol CH
ddcheck y. ddcol CH
)

NB. =====================================================
NB. axreadall 'table-nam'
NB. read all 'table-nam'
axreadall=: 3 : 0
sel=. 'select * from ',y. 
sh=. ddcheck sel ddsel CH
if. #sh do.
  r=. ddfch sh,_1
  ddend sh
  r
end.
)


NB. =====================================================
NB. axreadsel 'table-nam';'criterion'
NB. read a selection from lobster
axreadsel=: 3 : 0
sel=. 'select * from ',(;1{.y.),' where ',(;1}.y.) NB.' and (lat <= 100) '
sh=. ddcheck sel ddsel CH
if. #sh do.
  r=. ddfch sh,_1
  ddend sh
  r
end.
)

NB. =====================================================
NB. axupdate
NB. update id# x record to have wgt = 
axupdate=: 3 : 0
sel=. 'select * from serum where id#=660'
sh=. ddcheck sel ddsel CH
if. #sh do.
  newwgt=. >: {. , >{:,ddfch sh
  usel=. 'update lobster set wgt=',(":newwgt),' where A=''B'''
  ddcheck usel ddsql CH
end.
)

NB. Run it automaticly

demolob''