NB. dbase ODBC examples
NB.
NB. See odbc.txt for general comments on these scripts
NB.
NB. requires ODBC setup of:
NB.   data source name:  jdata
NB.   data source:       system\examples\data\   (subdirectory of J)
NB.
NB. The dbase driver connects to a subdirectory. The specific
NB. file used here is system\examples\data\tdata.dbf.
NB.
NB. verbs:
NB.   dbconnect    connect to dbase driver
NB.   dbcolumns    columns of tdata
NB.   dbreadsel    read a selection from tdata
NB.   dbupdate     update a field

require 'dd'

demo=: 3 : 0
out=. 1!:2 & 2
empty dbconnect''
out 'columns:',": dbcolumns''
out 'read all:',": dbreadall''
out 'read selection:', ": dbreadsel''
empty dbupdate''
out 'read updated selection:', ": dbreadsel''
empty dddis CH
)

NB. =====================================================
NB. dbconnect
NB. connect to dbase driver,
NB. define and return connection handle CH
dbconnect=: 3 : 0
CH=: ddcheck ddcon 'dsn=jdata'
)

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

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

NB. =====================================================
NB. dbreadsel
NB. read a selection from tdata
dbreadsel=: 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. dbupdate
NB. update salary field for Abbott K (increment by 1)
dbupdate=: 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.
)
