Jack Ort wrote:
> But the Wiki states that ddfetch should be used to read large tables to
> "bound nouns". When I follow the example in the guide, I get a return
> value of just "0".
>
> [r=. ddfetch sh
>
> 0
>
> Can someone explain how a bound noun is used?
I’m speaking from a position of ignorance here; haven’t used ODBC in a decade,
but ddfetch has the following definition:
ddfetch_jdd_
3 : 0
w=. y
if. -.isia w=. fat w do. errret ISI08 return. end.
if. -.w e.1{"1 CSPALL do. errret ISI04 return. end.
if. sqlok sqlfetchscroll w;SQL_FETCH_NEXT;0 do. DD_OK else. errret
SQL_HANDLE_STMT,y end.
)
which relies on
sqlfetchscroll_jdd_
'libiodbc.dylib SQLFetchScroll s x s x'&cd
and
DD_OK_jdd_
0
So ddfetch is effectively just a cover function for ODBC SQLFetchScroll, which
is defined to return a code indicating success or a specific type of failure.
In your case, you’re getting 0, which is DD_OK: in other words, it worked.
But then where’s the data? Googling [ODBC SQLFetchScroll] turns up some MSDN
pages talking (again) about bound columns, and googling [ODBC bound columns] it
turns out that binding columns to variables in a programming environment is
explicitly supported and made efficient by ODBC.
According to MSDN, this is implemented by another ODBC method, SQLBind. So,
searching the the jdd namespace for verbs containing the word ‘bind’:
'bind' nl_jdd_ 3
+-------+
|bindcol|
+-------+
bindcol_jdd_
4 : 0"1 1
'sh col rows'=. y
'type precision'=. x
type=. fat src type
name=. (":sh),'_',":col
bname=. 'BIND_',name
blname=. 'BINDLN_',name
(blname)=: (rows,1)$2-2
if. type e. SQL_CHAR,SQL_VARCHAR do.
…
Well, that does look promising. Seems like it’ll create named J variables to
hold the columns from the dataset.
But how do we call it? Or is it called on our behalf? Let’s turn to trusty old
Find-in-Files
addons/data/odbc/odbc.ijs:56: sqlbindcol=: (libodbc, ' SQLBindCol s x s s * x
*') &cd
addons/data/odbc/odbc.ijs:83: bindcol=: 4 : 0"1 1
addons/data/odbc/odbc.ijs:151: sqlbindcol sh;col;tartype;(vad bname);len;<vad
blname
addons/data/odbc/odbc.ijs:789: z=. (6 7{"1 x) bindcol (0{y),.(>: i.#x),.1{y
addons/data/odbc/odbc.ijs:1353: if. sqlbad sqlbindcol q do.
So, in the entire J script system, including all Addons, it’s only called from
within the ODBC script itself, and it hasn’t been exported to the z locale. So
it’s likely a private method.
So we’re probably meant to use a cover function for it. Of all the hits listed
above, only the last two are actual invocations of bindcol . The very last is
inside a function named ddins, which is probably focused on insertion and so
not what we’re looking for. The second to last is inside a function called
dbind, which seems more promising.
So let’s try our little Find-in-Files trick again. I’ll spare you the long
list, but it’s much more widely employed than bindcol, and is used extensively
outside of just the ODBC script. Combining through that list, a number of hits
stuck out at me, but here’s the real gem:
addons/labs/labs/system/odbcinv.ijt:141: Let us invert some columns of tdata
using ddbind and ddfetch.
Look, it’s a lab (in the /labs/ folder), named odbcinv, which means it’s
tutorial material focused on ODBC, and likely relates to “inverted”, or
column-oriented databases, i.e. binding J nouns to database columns.
Double-clicking that entry in the FiF panel to get some more context:
B. =========================================================
Lab Section Using ddbind and ddfetch to invert data
For very large tables it is better to use ddbind and ddfetch
instead of ddfch. ddfch trims and converts incoming data. In some
cases, for example with date data, the conversion will take a fair
amount of time. With ddbind and ddfetch you can completely
control the fetch and convert procedure.
Let us invert some columns of tdata using ddbind and ddfetch.
)
NB. select all rows
sh =. 'select * from tdata' ddsel ch
NB. bind and fetch rows
ddbind sh
ddfetch sh
Et voila. So that’s how it’s used. I’m sure the Lab goes into more detail if
you need it.
-Dan
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
