ODBC/Methods/Read

From J Wiki
Jump to: navigation, search
ODBC: Data Sources | Handles | Data Driver | Error Messages | API: Connect Read Update Create Bulk Insert

To read data from a file, you first select the data you want to read using the ddsel verb, which returns a statement handle. You then use ddfet or ddfch to fetch the records.

The left argument of ddsel is a SQL selection expression. Here are typical examples:

Select all records (* means all columns):

   sh=. 'select * from tdata' ddsel ch

Fetch the first 3 records:

+--------------+-+----+--------+--------+-----+
|Macdonald B   |F|D101|19590600|19780500|32591|
+--------------+-+----+--------+--------+-----+
|Genereaux S   |F|D103|19450300|19660200|95415|
+--------------+-+----+--------+--------+-----+
|Koebel R      |M|D101|19371100|19800900|63374|
+--------------+-+----+--------+--------+-----+

Fetch the next record:

   ddfet sh
+--------------+-+----+--------+--------+-----+
|Keller J      |F|D101|19510500|19740400|48898|
+--------------+-+----+--------+--------+-----+

Close the statement handle:

   ddend sh

You should always close the statement handle when you no longer need it. However to avoid repetition, the remaining examples do not show this.

Select males with salary exceeding 40000:

   sel=.'select * from tdata where sex=''M'' and salary >= 40000'
   sh=. sel ddsel ch
   ddfet sh,4
+--------------+-+----+--------+--------+------+
|Koebel R      |M|D101|19371100|19800900|63374 |
+--------------+-+----+--------+--------+------+
|Newton R      |M|D108|19560100|19790200|73368 |
+--------------+-+----+--------+--------+------+
|Dingee S      |M|D103|19641000|19830900|46877 |
+--------------+-+----+--------+--------+------+
|Rogerson G    |M|D101|19571200|19830200|108777|
+--------------+-+----+--------+--------+------+

Select only the name, department and salary fields, where date of birth is before 1950:

   sel=.'select name,dept,salary from tdata where dob<19500000'

Fetch the first such record:

    ddfet (sel ddsel ch),1
+--------------+----+-----+
|Genereaux S   |D103|95415|
+--------------+----+-----+

Use ddfch to return data in columns:

   sh=. sel ddsel ch
   [a=. ddfch sh,_1
+---------------+----+-----+
|Genereaux S    |D103|95415|
|Koebel R       |D101|63374|
|Denny D        |D101|46939|
|Bugg P         |D101|47165|
|Anctil J       |D108|60974|
|O'Keefe D      |D101|66377|
|Cahill G       |D108|81358|
+---------------+----+-----+