NB. Access 97/2000 ODBC examples
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:       system\examples\data\jdata.mdb
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
NB.   axcolumns    columns of tdata
NB.   axreadsel    read a selection from tdata
NB.   axupdate     update a field
NB.
NB.   demo         run a demo

require 'dd'

demo=: 3 : 0
out=. 1!:2 & 2
empty axconnect''
out 'columns:',": axcolumns''
out 'read all:',": axreadall''
out 'read selection:', ": axreadsel''
empty axupdate''
out 'read updated selection:', ": axreadsel''
empty dddis CH
)

NB. =====================================================
NB. axconnect
NB. connect to access driver,
NB. define and return connection handle CH
axconnect=: 3 : 0
CH=: ddcheck ddcon 'dsn=jaccess'
)

NB. =====================================================
NB. axcolumns
NB. read columns of tdata
axcolumns=: 3 : 0
ddcheck 'tdata' ddcol CH
)

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

NB. =====================================================
NB. axreadsel
NB. read a selection from tdata
axreadsel=: 3 : 0
sel=. 'select * from tdata where sex=''M'' and salary >= 40000'
sh=. ddcheck sel ddsel CH
if. #sh do.
  r=. ddfch sh,_1
  ddend sh
  r
end.
)

NB. =====================================================
NB. axupdate
NB. update salary field for Abbott K (increment by 1)
axupdate=: 3 : 0
sel=. 'select * from tdata where name=''Abbott K'''
sh=. ddcheck sel ddsel CH
if. #sh do.
  newsal=. >: {. , >{:,ddfch sh
  usel=. 'update tdata set salary=',(":newsal),' where name=''Abbott K'''
  ddcheck usel ddsql CH
end.
)
