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.
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 as an Access file. 
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''
junk=. 'columns:',":cols=: axcolumns'map_coord'
junk=. 'read Map Coords:', ":(1 4$,4 1 {. 1 3}. cols), "; map_coords=:axreadsel 'map_coord';'(lat >= 0)'
map_coords=: 2 }. map_coords
xmap=:, ; 1 }. map_coords
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 950 950'

label_L0. ixmap8=. IO1 9998=xmap
ixmap9=. IO1 9999=xmap
if. i8lti9=:ixmap8 < ixmap9 do. ixmap1=:ixmap8 else. ixmap1=:ixmap9 end.
xmap1=: ixmap1{.xmap
xmap=: (ixmap1+1)}.xmap
ymap1=: ixmap1{.ymap
ymap=: (ixmap1+1)}.ymap
nx=: 1{.$xmap
NB. xmap=: xmap;ymap
dat1=: xmap1;ymap1
NB. pd opt
pd 'frame 1'
pd 'xtic 1 4;ytic 1 4; yint 40; xint -65'
pd 'grids 1'
pd 'gridcolor white'
pd 'title Gulf of Maine & Georges Bank'
pd 'titlefont arial 40 italic'
pd opt
pd dat1
NB. Chose next color to use
if. i8lti9 < 1 do. icolor=:icolor+1 end.
if. icolor=2 do. opt=.opt3 elseif. icolor=1 do. opt=.opt2 end.
if. nx > 2 do. goto_L0. else. goto_L1. end.

NB. Now plot the track line and stations:
label_L1. junk=. 'columns:',":cols=: axcolumns'locals'
NB. out 'read all:',":(1 7$,7 1 {. 1 3}. cols), ";axreadall 'locals'
locq=:'AL0006 leg III'
out 'read select:',":(1 7$,7 1 {. 1 3}. cols), ";selout=: axreadsel 'locals';'(labn >= 4)and (lat > 40)and (lon > -72)'
NB. out 'read selection:', ":(1 7$,7 1 {. 1 3}. cols), "; axreadsel'tablenam'
NB. station=: ,;1{. 3}.selout
datetime=: 1{. 4}.selout
dt1st=:,13{.junk=: ,1{.;datetime
dt1st=: dt1st,':'
dtlast=:,13{.junk=: ,_1{.;datetime
dtlast=: dtlast,':'
laty=: 1{.selout=: 5}.selout
lonx=: 1}.selout
dat1=: (,;lonx);(,;laty)
pd 'textc 500 940 ',locq
pd 'textfont arial 30 bold'
pd 'textc 500 905 from ',dt1st,' to ',dtlast
m0=: 6!:0	
m9=: ('/'"_) 4 7} [: ": 1000"_#. 3: {. [: <. m0
pd 'textfont arial 30 italic'
pd 'textc 500 870 accessed on ',m9''
pd opt4  NB. yellow
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=: (0.03 * addvecx)+ 1{xvec
xvec=: 1}.xvec
ychar=: (0.03 * 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''