I wanted to use Varchar data via ODBC on Windows and Access. ddfet returns the
variable length data as a boxed vector, but is too slow to be useful with large
databases. ddfch returns Varchar data as Cx255 matrix, efficient and fast, but
not the form the data is stored in.
Attached is a modified ddfchvc that returns two columns for each Varchar column.
The first is the Cx255 text matrix. The second is the Cx1 list of lengths for
each Varchar row. This allows the original data to be reconstructed while
preserving the general result format of ddfch. The performance is comparable
with ddfch, since the list of lengths must be created in either verb. The
reconstruction of the variable length data as a vector of enclosed vectors can
be done with something like:
(<(>1{x)([:<@[{.)"1 >0{x),2}.x
--
David Mitchell
ddfchvc=: 3 : 0
COLUMNBUF ddfchvc y
:
clr 0
if. -.(isia x) *. isiu y do. errret ISI08 return. end.
'sh r'=. 2{.,y,1
if. -.sh e.1{"1 CSPALL do. errret ISI04 return. end.
r=. (r<0){r,_1
if. SQL_ERROR-:ci=. getallcolinfo sh do. errret '' return. end.
buf=. (_1=r){r,x
if. sqlbad z=. ci dbind sh,buf do. SQL_ERROR return. end.
cv=. GCNM {~ GDX i. ; 6 {"1 ci
one=. 0<r
dat=. ((+/12=;6{"1 ci)+#ci)#<i.0 0
if. r=0 do. dat return. end.
fetch=. sh;SQL_FETCH_NEXT;0
while.do.
rc=. >{.sqlfetchscroll fetch
if. sqlbad rc do. errret SQL_HANDLE_STMT,sh return. end.
if. SQL_NO_DATA=src rc do. ddend sh break. end.
c=. dddcnt sh
z=. ''
for_i. i.#ci do.
n=. (i_index{cv) `:0 dddata sh,i+1
if. _1 e. len=. dddataln sh,i+1 do.
ndx=. I. len = _1
if. 2 = 3!:0 n do.
n=. ' ' ndx } n
else.
n=. __ ndx } n
end.
end.
if. c<buf do. n=. (fat c) {.n end.
if. 12=;6{i_index{ci do.
if. c<buf do. ll=. (fat c) {.len else. ll=. len end.
z=. z,(<n),<,.ll
else.
z=. z,< n
end.
end.
dat=. dat ,&.> z
if. one do. if. c<buf do. ddend sh end. break. end.
end.
dat
)
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
