Converting ODBC column dataypes in J 
Author Message
 Converting ODBC column dataypes in J

   Here's an interesting J problem that came up when I was
writing a simple routine to transfer SQL table data via ODBC from
one SQL database to another.

   As you all know the input formats and supported primitive
datatypes of SQL databases vary.  Because of this "variety" it's
not entirely trivial to copy a table from one SQL system to
another.  You are always facing a sequence of lame and irritating
datatype conversions.

   In J ODBC contexts this problem can be systematically tackled
in the following manner.

   Suppose a query fetches a set of columns from SQL A that you
want to insert in SQL B.

    ch =. ddcon 'dsn=books'
    sh =. 'select author,year,type from atable' ddsel ch
    a  =. ddfet sh , _1

   Table (a) may look like.

+----------------------------------------+----+-----------------+
|NASA                                    |1969|Non-fic          |
+----------------------------------------+----+-----------------+
|James Blish                             |1969|Sci-fic          |
+----------------------------------------+----+-----------------+
|C.M. Kornbluth & Frederick Pohl         |1969|Sci-fic          |
+----------------------------------------+----+-----------------+
|Ed: Frederick Pohl                      |1969|Sci-fic          |
+----------------------------------------+----+-----------------+

   Each column of (a) has to be mapped to a corresponding INSERT
format of (b).

   Assume we have a set of monadic verbs (b from a) that carry
out these conversions.  A typical conversion verb (bfam) can be
applied to column m as follows:

    a =. (bfam &.> m {"1 a) (<a: ; m)} a

   For every column there is a corresponding conversion verb.
Construct a gerund of column format conversion verbs

    colconv  =.     bfa0 ` bfa1 ` bfa2  .... ` bfan

   The problem reduces to how we can efficiently apply all the
conversion verbs to the fetched table enmass.

    b =.  colconv ConvertColumns a

   I could not find an efficient non-looping solution to this
problem and ended up applying this kludge:

ConvertColumns =: 4 : 0
if. (#x.) ~: {:$ y. do. 'verb/column mismatch' return. end.
col =. _1 [ lim =. #x.
while. lim > col=.>:col do.
  y. =. (((col{x.) /.) &.> col {"1 y.) (<a: ; col)} y.
end.
y.
)

Any replacements for this clunker would be appreciated.
-----------------------------
John D. Baker



Sun, 11 Oct 1998 03:00:00 GMT  
 
 [ 1 post ] 

 Relevant Pages 

1. how do i convert a multi column into a single column in the output in fortran

2. JS-EAI with *JS*-callback

3. js.exception 3279.js

4. C55+ODBC+Oracle8i : Error after adding new column in Table

5. sql-odbc: accessing row elements by column name?

6. Need to convert 80-column punch cards to PC

7. convert a row-oriented sparse matrix to column-oreinted

8. convert output results in from column to row

9. Problems Converting to a oracle ODBC driver

10. Way of converting MEMO data type to equivalent ODBC type

11. sum all columns based on column 1

12. Help: using hidden columns with columns resizable in a list box

 

 
Powered by phpBB® Forum Software