NB.ado wrapper
NB.
NB. module for data access with ADO
NB. 
NB. REQUIREMENT
NB. 
NB.    MDAC 2.6, or above
NB.       http://msdn.microsoft.com/data
NB. 
NB. USAGE
NB. 
NB.    cs=. prompt_ado_''
NB.    myado=: cs&rs_ado_
NB.    myado 'select * from tdata'
NB. Macdonald B   	F	D101	19590600
NB. Genereaux S   	F	D103	19450300
NB. ...
NB. Livingston P  	F	D101	19580900
NB. Holliss D     	F	D101	19600500
NB. 
NB. Author: Oleg Kobchenko 03/09/2004
NB. 03/26/2004 added boxed results; field names; throw
NB. 03/28/2004 added typed results (tcols)

coclass 'ado'

throw=: 3 : 'jthrow_ado_=: y. throw.'

NB.*prompt_ado_ v choose ado connection
NB.   strConnection=. prompt_ado_''
prompt=: 3 : 0
wd 'pc promptform'
try. try.
    wd 'cc r oleautomation:DataLinks'
  catch. throw 'DataLinks is required' end.
  try.
    wd 'olemethod r base PromptNew'
    r=. wd 'oleget r temp ConnectionString'
  catch. throw wd 'qer' end.
catcht. r=. ''[smoutput jthrow end.
wd 'pclose'
r
)

NB.*rs_ado_ v execute SQL with ado recordest
NB.   result=. strConnection rs_ado_ strSQL
rs=: 4 : 0
wd 'pc rsform'
try. try.
    wd 'cc r oleautomation:ADODB.Recordset'
  catch. throw 'ADO is required' end.
  try.
    wd 'oleset r base ActiveConnection *',x.
    wd 'olemethod r base Open *',y.
    if. '1'=wd 'oleget r base State' do.
      r=. mode wd 'olemethod r base GetString 2 ',":ROWS
      wd 'olemethod r base Close'
    else. r=. 1 end.
  catch. throw wd'qer' end.
catcht. r=. ''[smoutput jthrow end.
wd 'pclose'
r
)

NB.*ROWS v output number of rows or _1 for all
NB.   ROWS_ado_=: 10
ROWS=: _1

getcollect=: 4 : 0
  wd 'oleget r base ',x.,'; oleid r ',x.
  s=. '' for_i. i. 0". wd 'oleget r ',x.,' Count' do. 
    s=. s,<wd 'oleget r ',x.,' Item ',(":i),';oleget r temp ',y.
  end. s
)

fields=: 'Fields'"_ getcollect 'Name'"_
types=: 'Fields'"_ (0&".&>@getcollect) 'Type'"_

fcells=: <;._2@(,&TAB) ;. _2
fcols=: <@:>"1@|: @ fcells

num=: ,.@(0&".)&.>
isnum=: e.&2 3 4 5 6 11 14 16 17 18 19 20 21 131 139
apply=: 4 : '(,@:(x.;.2)~ (#&1)@#) y.'
typed=:  {&(str`num) @ isnum

NB.*mode v output mode: one of str|cells|cols|tcols
NB.   mode_ado_=: cells_ado_
mode=: tcols

NB.*str v mode: tab and CR separated string
str=: ]

NB.*cells v mode: cells of boxed strings
cells=: fields ,  fcells

NB.*cols v mode: row of string columns
cols=:  fields ,: fcols

NB.*tcols v mode: sames as cols, but numerics converted
tcols=: fields ,: typed@types apply fcols

NB. ==================================================

0 : 0 NB. press Ctrl+R on each line in succession

NB. select Microsoft Jet OLEDB, Next >>
NB. Select "<j home>\system\examples\data\jdata.mdb"

cs=. prompt_ado_''
myado=: cs&rs_ado_
myado 'select * from tdata'
mode_ado_=: cells_ado_
myado 'select * from tdata'
mode_ado_=: str_ado_
myado 'select * from tdata'

myado 'create table test1 (aa char(10), bb integer)'
myado 'insert into test1 (aa,bb) values (''a1'',22)'
myado 'insert into test1 (aa,bb) values (''a2'',44)'
myado 'select * from test1'
myado 'drop table test1'
)