I am working with Dyalog APL and having trouble with getting the column names of a table resulted from an SQL query. More precisely, I am working with the workspaces (WS) sqapl and LOADDATA provided by Dyalog. Although the functions LoadSQL and SQA.Do from the LOADDATA and sqapl WS, respectively, make it possible to import a table resulting from an SQL query (e.g. a join of some tables), they do not return the column names.
Let's say we are in the following setting:
We have the following query that combines some tables
qry←'select COL_1, COL_2, C.* '
qry,←'from TABLE1 A, TABLE2 B, TABLE3 C '
qry,←'where C.COL_3 = B.COL_3 '
qry,←'and A.COL_4 = B.COL_4'
and want to get the resulting table together with the column names. We can call the functions LoadSQL and SQA.Do as follows:
⍝ With WS LOADDATA
tbl1←LoadSQL ('MySQL' 'psw' 'userID')qry ⍝ tbl1 is a table without column names
⍝ With WS sqapl
SQA.Init '' ⍝ Initiate
SQA.Connect 'C1' 'MySQL' 'psw' 'userID' ⍝ Connect to a service
tbl2←⊃⊃(SQA.Do 'C1' qry)[3] ⍝ Keep only resulting table (3rd element)
⍝ again without column names
Note that the WS sqapl offers the function SQA.Columns which I can use as follows
(2⊃SQA.Columns 'C1' 'MY_TABLE')[;4]
This works fine provided the table MY_TABLE exists already, but I cannot make it work for a table that I get from a query as the one above. Is this possible?
What you're after is SQA.Describe. See the documentation.