Vax-Oracle to APL*PLUS/PC
Vector 5:1, page 119
Transcribed from the printed issue; not yet reviewed. Read it in the PDF of the issue, page 119
Introduction¶
Judging by the popular computing press, the flavour of the month in the database world is undoubtedly Oracle, and its favoured environment is the Dec Vax range. At the same time, it has become very clear that the mini-computer will never provide the kind of user-interface that PC users have come to expect. The conclusion of this article is that APL is one tool which just might come to bridge that gap.
With APL, you can build on the concept of the ‘workstation’, where the Vax is essentially just a database engine (a job it does rather well), with your PC acting as an ‘intelligent agent’ rather than just a dumb terminal. These notes describe the low-level interface which underpins a plant information system, based on the IBM PS/2-50 (complete with hypertext and windows) and an Oracle database held on a MicroVax-II.
The PC side of the system is entirely written in APL*PLUS/PC, and uses the standard RS232 port to drive a serial line to the Vax. For maximum security of data (not needed in an ‘enquiry only’ system) you might want to use Ethernet and Decnet-DOS. This would require some coding in C to drive the Ethernet link, and at the moment I have no idea how I would set about this! In terms of response time, simplicity, and ease of installation, I think the simple serial link wins every time.
Making Contact¶
APL has a number of rather handy add-ins tucked away: one of these is a basic terminal emulator. As I noted in my review of APL*PLUS/PC Rel.7, I have little or no faith in the VT100 mode now offered; maybe you will have better luck! The simple cover function TALK is all you need to get started. You could dispose of the ⎕CMD into your AUTOEXEC.BAT to save a bit of time.
R←TALK;WAS;JUNK;POKED
---------------------
[1] ⍝ Set up terminal for use with serial connection
[2] '====== TERMINAL: Use Alt/F8 then <Enter> to exit ======'
[3] ⍝ POKE: FULL DUPLEX; TEXT KBD; BACKSPACE; ALT-HOME[2]
[4] POKED← 115 118 172 174 175
[5] WAS← 1 1 127 48 48 ⎕POKE POKED
[6] JUNK←⎕CMD 'MODE COM1:9600,N,8,1 >NUL'
[7] 'Press <Enter> to connect ...'
[8] JUNK←1 ⎕POKE 117 ⋄ JUNK←⍞
[9] JUNK←WAS ⎕POKE POKED
[10] R←'End of Terminal Session'
∇
While you are testing things out, you can login by hand, but for comfort and convenience you will soon need a simple script such as the function below:
R←LOGIN;CHK
-----------
[1] ⍝ Run login script starting where necessary ...
[2] R←'' ⋄ CHK←RS∆CHK ⋄ →0 IF 'SQL>'∧.=4↑CHK
[3] CHK←RS∆FLUSH ⋄ CHK←RS∆CHK ⋄ CHK←⎕DL 1 ⋄ CHK←RS∆CHK
[4] →0 IF 'SQL>'∧.=4↑CHK
[5] ⍝ Keep checking prompt until we get to SQL>
[6] →Tsrv IF 'Local>'∧.=6↑CHK
[7] →Vax IF 'User'∧.=4↑CHK
[8] →VMS IF '$'=1↑CHK
[9] →Dead IF '!!!'∧.=3↑CHK
[10] ⍝ Unknown prompt .... give up gracefully ....
[11] R←'*** Unknown response .../',CHK,'/LOGON terminated'
[12] R←'/' ∆TAB R ⋄ →0
[13] Dead:R←'*** Connection to database failed ***' ⋄ →0
[14] ⍝ Take system forward from recognised prompt ...
[15] Tsrv:'Login to Decserver ... '
[16] 'name: ' RS∆SR 'CONNECT MYVAX' ⍝ ... DEC nodename
[17] Vax:'Signon to Vax ....'
[18] R←'word: ' RS∆SR 'SMITH' ⍝ ... Username
[19] '$ ' RS∆SR 'SECRET' ⍝ ... Password
[20] VMS:'Starting SQL*PLUS from VMS .... '
[21] R←'$ ' RS∆SR 'SET TERM/UNKNOWN'
[22] R←'$ ' RS∆SR 'SET TERM/WIDTH=256/NOTAB/ECHO'
[23] 'SQL> ' RS∆SR 'SQLPLUS SCOTT/TIGER'
[24] ⍝ Check that we have reached SQL ... logoff if not!
[25] CHK←RS∆CHK ⋄ →Exit IF 'SQL>'∧.=4↑CHK
[26] R←LOGOUT ⋄ →0
[27] Exit:R←'Logged in to SQL*PLUS ...'
[28] CHK←'SQL> ' RS∆SR 'SET SPACE 0'
[29] CHK←'SQL> ' RS∆SR 'SET VERIFY OFF'
[30] CHK←'SQL> ' RS∆SR 'SET HEADING OFF'
[31] CHK←'SQL> ' RS∆SR 'SET LINESIZE 480'
[32] R←R CAT SQL∆SYNCH
∇
This calls a number of lower-level functions (anything beginning RS∆) to drive the serial port; these will be explained in detail in the next section. Other points worth noting are:
- it flushes the port, and then gives the Vax a couple of pokes with a second’s delay in between. This is overkill, but reliable.
- having got through to VMS, it ensures that the terminal is correctly set up. Note the “width=256” setting, all the others are defaults and are just to make sure that no-one else has been there before you.
- having reached SQL*PLUS, you again set some parameters. If you have an SQL id to yourself, you could put all these into LOGIN.SQL and save a bit of to-ing and fro-ing on the line.
- SQL∆SYNCH sets the PC clock to the same time as the clock on the Vax. You probably don’t need to do this.
Logging out is much more straightforward. Again you need to find out where you are, and then just send the sign-off commands:
R←LOGOUT;CHK
------------
[1] ⍝ Run logout script ...
[2] Chk:CHK←RS∆FLUSH ⋄ CHK←RS∆CHK ⋄ CHK←⎕DL 1 ⋄ CHK←RS∆CHK
[3] →Exit IF 'Local>'∧.=6↑CHK
[4] →Exit IF 'User'∧.=4↑CHK
[5] →SQL IF 'SQL>'∧.=4↑CHK
[6] →VMS IF '$'=1↑CHK
[7] →Dead IF '!!!'∧.=3↑CHK
[8] SQL:'Logout from SQL*PLUS ... ' ⋄ '$' RS∆SR 'EXIT'
[9] VMS:'Exiting from VMS .... ' ⋄ '' RS∆SR 'logoff'
[10] Exit:R←'Logged out ' ⋄ →0
[11] Dead:R←'***'
∇
Finally, just in case you do want to fire queries automatically ‘on the hour’, or simply display an accurate clock on the screen:
R←SQL∆SYNCH;MAT;DTE;DAY;MON;YR;HR;MIN;SEC
-----------------------------------------
[1] ⍝ Synchronise PC clock with VAX time ...
[2] ⍝ Note that the seconds are deliberately random, to ensure
[3] ⍝ that queries run 'on the hour' are staggered.
[4] ⍝ Uses preset query QD which gives 'DD MM YY HH MM SS'
[5] ⍝
[6] MAT←START 'QD' ⋄ R←MAT ⋄ →('*'=1↑,MAT)↑0
[7] MAT←20↑MAT[1;] ⋄ DTE←MAT
[8] DTE[(~DTE∊'0123456789')/⍳⍴DTE]←' '
[9] →(DTE∧.=' ')↑0 ⋄ DTE←⍎DTE
[10] ⍝ Use ⎕INT to set DOS system date ...
[11] DAY←DTE[1] ⋄ MON←DTE[2] ⋄ YR←1900+DTE[3]
[12] R←(11008 0 ,YR,DAY+256×MON)⎕INT 33
[13] ⍝ Use ⎕INT to set DOS system clock ...
[14] HR←DTE[4] ⋄ MIN←DTE[5] ⋄ SEC←DTE[6]
[15] SEC←¯1+?60
[16] R←(11520 0 ,(MIN+256×HR),256×SEC)⎕INT 33
[17] R←'PC clock reset to ... ',MAT
∇
At least all your workstations will agree, even if they are all wrong! One thing I have found useful is a regular check on a system status file (say every 15 min); this normally just says “available”, but can be set to things like “shutdown”, in which case the workstation will log itself off, even if it is running unattended in a far corner of the factory!
Driving the Serial Port¶
This is one of those areas where there is very little code, but it has to be right! Everything in the system depends absolutely on these few functions, so I can vouch that they have been rather well tested over the past year or so!
The first essential is to know to whom you are speaking! The easy way to find this out is to hit <Enter> and have a look at the prompt that you get back:
R←RS∆CHK
--------
[1] ⍝ Check where we are in the system ....
[2] RS∆OUT '' ⋄ R←RS∆FLUSH ⋄ R←(~R∊⎕AV[1 8])/R
[3] R←TAB R ⋄ R←(R∨.≠' ')⌿R ⋄ →(0∊⍴R)↑Dead
[4] R←R[(⍴R)[1];] ⋄ R←(R≠' ')/R ⋄ →0
[5] Dead:R←'!!!'
∇
If you are in VMS, it will come back with “$”, and so on. It clears the input line with RS∆FLUSH, which just keeps collecting characters as long as they keep coming, and uses TAB to kill line-feeds and tabulate on ⎕TCNL:
R←RS∆FLUSH;IN;CT
----------------
[1] ⍝ Clear anything currently on the line
[2] CT←1 ⋄ R←''
[3] ⍝ Get input buffer. Max 2100 chars ...
[4] Wait:IN←(0 1 2 2 0 2100)⎕ARBIN ''
[5] IN←¯1↓IN ⋄ R←R,IN ⋄ →(⍴IN)↑Wait
[6] →(⍴R)↑0
[7] →(50<CT←CT+1)↑Dead ⋄ →Wait
[8] Dead:R←'!!!'
∇
(See page 132 for a listing of TAB, and other utility functions.) The process of sending data down the line is not quite as simple as it looks; here is RS∆OUT, which should be a one-liner, but isn’t:
RS∆OUT XX;IN;LEN
----------------
[1] ⍝ Send text to serial port, no reply required.
[2] XX←,XX ⋄ LEN←40 ⋄ →(LEN≥⍴XX)↑Tail
[3] Send:IN← 1 0 2 0 ⎕ARBIN LEN↑XX
[4] XX←LEN↓XX ⋄ →(LEN<⍴XX)↑Send
[5] Tail:IN← 1 0 2 0 ⎕ARBIN XX,CHR 13
∇
The snag is that if you send too many characters too fast, you start getting batches of BEL characters (⎕AV[8]) in the echo. Eventually the Vax gives up on you and just ignores the input altogether. By looping in 40-character chunks you give it time to absorb the input at a sensible rate. Owners of very fast PCs may have to shove a bit of extra delay in here … I suggest putting a BEL-detector into RS∆IN and doing some experiments!
Having fired off your command with RS∆OUT, you need a reliable way of knowing that you have collected the entire response. Again, you can look for the characteristic prompt, and simply collect everything that comes up the line until you get a match. It pays to have a timeout as well, just in case your Vax has died, or someone has pulled a plug:
R←PARA RS∆IN MAX;XX;CT;PARA;FLG;IN;BL
-------------------------------------
[1] ⍝ Hang about until something comes up the line.
[2] ⍝ When it does, check for complete message, then return it.
[3] ⍝ <PARA> is the delimiter string to collect up to ...
[4] ⍝ <MAX> is the number of cycles to wait, before giving up.
[5] ⍝
[6] CT←1 ⋄ R←'' ⋄ BL← 24 70 1 8 ⎕WGET 1
[7] ⍝ Get input buffer. Max 2100 chars ...
[8] Wait:IN←(0 1 2 2 0 2100)⎕ARBIN ''
[9] IN←¯1↓IN ⋄ FLG←(-12+⍴,PARA)↑R,IN ⋄ →(⍴IN)↑Appd
[10] Null:CT←CT+1 ⋄ 24 70 1 3 ⎕WPUT 3 0 ⍕CT
[11] →(CT≥MAX)↑Timeup ⋄ →Wait
[12] ⍝ Add input to text buffer, check if it contains target
[13] ⍝ string and go back for more if not.
[14] Appd:R←R,IN ⋄ 24 73 1 5 ⎕WPUT 5 0 ⍕⍴R
[15] →(∨/FLG ⎕SS PARA)↑Done
[16] CT←1 ⋄ →Wait
[17] Timeup:→(⍴R)↑0 ⋄ R←'!!!' ⋄ →0
[18] Done: 24 70 1 8 ⎕WPUT BL
∇
This runs a couple of counters in the lower right corner of the screen, viz the timeout counter and the number of characters collected so far. These were originally put in to help me during debugging, but I have left them in the finished system as they give users some idea of how their request is progressing.
Finally, the workhorse routine RS∆SR, which sends the command, checks the echo, and returns the tabulated result (obviously less the echo!):
R←PARA RS∆SR TXT;RSP;LEN;CHK
----------------------------
[1] ⍝ Pass text to RS232 and collect reply up to PARA
[2] ⍝ Global ∆TIMEOUT controls clock cycles to wait ... say 500
[3] TXT←,TXT ⋄ PARA←,PARA ⋄ LEN←⍴TXT
[4] RS∆OUT TXT ⋄ RSP←PARA RS∆IN ∆TIMEOUT
[5] RSP←(~RSP∊⎕AV[1 8])/RSP
[6] ⍝ Check that echo really was ....
[7] CHK←LEN↑RSP ⋄ →(∧/(CHK∊' $',∆CRLF)∨CHK=TXT)↑Ok
[8] '***** POSSIBLE DATA ERROR ......' ⋄ TXT ⋄ CHK ⋄ ' '
[9] R←BAREIN 'Press <ENTER> to proceed ....'
[10] Ok:R←TAB LEN↓RSP
∇
What you do with funny data is up to you! This just reports the problem and continues; until I see this happen I refuse to worry about it! The one catch is from the “Password: ” prompt, where you don’t get any echo of your password (see LOGIN) – hence the escape clause on line[7].
As I noted in the introduction, there is no integrity check on received data. One easy thing to do is to set the Vax to transmit on even parity; you then need a version of ⎕AV with all the odd characters displaced by 128. Everything you receive should occur in this, and a quick look-up and index will get you back to the bottom 128. Of course you will need to shift outgoing characters the other way so that the Vax only sees even parity at its end!
Preparing and Sending an SQL Query¶
I think I will again start at the top level function, and work down; this after all is the way the workspace was written! The basic requirement is to pass a character vector to SQL, and get usable data back into your workspace. An example could be:
MAT←SQL 'Select ename,deptno,salary from emp'
MAT
JONES | 20| 12876|
HARRIS | 20| 7890|
KING | 50| 76000|
SQL>
This differs in two ways from the default format returned by SQL. Firstly there are no headings, and no space between columns; secondly, there is a delimiter character between each column, and a closing delimiter on the end. The reason for the lack of headings is that you will want to do your own anyway, and they can play havoc with the column widths. Also, at 9600 baud, the less junk you send down the line the better!
The delimiters are simply to make it easy for you to split out the columns as separate APL variables; there are numerous ways of doing this, but an unambiguous set of markers is obviously a sensible starting point.
I hope that most if this code is self explanatory; the detail of the called functions follows:
R←FLG SQL QRY;lab;CT;POS;PMPT;LSIZE;FM;HARDCR
---------------------------------------------
[1] ⍝ Send query to SQL and return data ...
[2] ⍝ FLG defaults to 1 ... query may be re-wrapped if so.
[3] R←LOGIN ⋄ →('*'=1↑,R)↑0
[4] LSIZE←160 ⋄ ⍎(0=⎕NC 'FLG')/'FLG←1'
[5] ⍝ Skip wrapping if FLG is ¯1
[6] →(FLG=¯1)↑Lp
[7] ⍝ Compress out spare blanks, and ravel. Sets HARDCR.
[8] QRY←SQL∆PREP QRY
[9] ⍝ Take query and pass to SQL in bite-size lines.
[10] ⍝ Response of 'ln-no' is awaited, and checked.
[11] QRY←LSIZE SQL∆WRAP QRY
[12] ⍝ Peel any COL/BREAK/COMPUTE commands off the front ...
[13] LpC:→lab←(⍴HARDCR)ITERATE EndC,CT←1
[14] PMPT←'SQL> ' RS∆SR DTB QRY[HARDCR[CT];]
[15] EndC:→lab[CT←CT+1]
[16] QRY←(~(⍳1↑⍴QRY)∊HARDCR)⌿QRY
[17] Lp:→lab←((1↑⍴QRY)-1)ITERATE End,CT←1
[18] PMPT←5↑ 3 0 ⍕CT+1 ⋄ PMPT←PMPT RS∆SR DTB QRY[CT;]
[19] End:→lab[CT←CT+1]
[20] QRY←QRY[CT;]
[21] ⍝ Pass last line, and capture result of entire query ...
[22] Tail:R←'SQL> ' RS∆SR DTB QRY
∇
Note that we start with a LOGIN; most of the time this will just get the “SQL> ” prompt straight back, and do nothing. However it is always worth checking that your Vax is still there! Assuming you have omitted the left argument, the function now sets about minimizing the number of characters in your query. The first stage is to take out any nice indentation, and to ensure that the obligatory semi-colon is there at the end:
R←SQL∆PREP QRY;POS;MSK;CHK
--------------------------
[1] ⍝ Prepare a query for transmission
[2] ⍝ Ensure that SQL commands (e.g. COL) go on a separate line.
[3] ⍝ records these lines in <HARDCR> ....
[4] HARDCR←⍬ ⋄ →(2>⍴⍴QRY)↑Vec
[5] CHK←∆UPPER QRY[;⍳3]
[6] POS←(∨/CHK∧.=⍉ 3 3 ⍴'COLCOMBRE')/⍳1↑⍴QRY
[7] QRY←QRY,' ' ⋄ QRY[POS;(⍴QRY)[2]]←'⌶'
[8] HARDCR←POS
[9] QRY←¯1↓∆NEATEN(,QRY),' '
[10] Vec:QRY←QRY SQL∆SSR 'from⋄FROM'
[11] QRY←DLB QRY
[12] POS←(QRY ⎕SS 'FROM')/⍳⍴QRY ⋄ →(⍴POS)↓Skip
[13] →('DEL'∧.=3↑QRY)↑Skip
[14] →('INSERT'∧.=6↑QRY)↑Skip
[15] ⍝ Insert '¦' between columns to give delimited result ...
[16] POS←POS[1] ⋄ QRY←((POS↑QRY)SQL∆SSR ',⋄,''¦'',′),POS↓QRY
[17] ⍝ Add a closing delimiter unless 'Select *'
[18] →('*'∊8↑QRY)↑Skip
[19] QRY←QRY SQL∆SSR ' FROM⋄ ,''¦'' FROM'
[20] ⍝ Ensure it ends with a semi-colon ...
[21] Skip:R←QRY,(';'≠¯1↑QRY)/';'
∇
There is also some subtle patching of the query, to ensure that you get those column delimiters returned to you. We look for the word ‘from’ as in:
… and patch in the delimiter symbol wherever we find a comma:
… and at the end of the list for good measure. Commas within ‘()’ are left well alone, otherwise expressions like:
… are well and truly wrecked!
R←OLDSTR SQL∆SSR CMD;s1;s2;d;p
------------------------------
[1] ⍝ SQL Replace strings within OLDSTR.
[2] d←'⋄' ⋄ R←OLDSTR
[3] →(⍴s1←(¯1+p←CMD⍳d)↑CMD)↓0
[4] s2←p↓CMD
[5] →(⍴p←(OLDSTR ⎕SS s1)/⍳⍴OLDSTR)↓0
[6] p←(1,(⍴s1)≤1↓p-¯1⌽p)/p
[7] ⍝ Ignore strings which fall within parentheses ....
[8] →(⍴p←(~p∊(≠\OLDSTR∊'()')/⍳⍴OLDSTR)/p)↓0
[9] R←(~(⍳⍴OLDSTR)∊¯1+,p∘.+⍳⍴s1)/OLDSTR
[10] p←(p-0,+\(¯1+⍴p)⍴(⍴s1)-⍴s2)∘.+¯1+⍳⍴s2
[11] R←(~(⍳(⍴R)+(1↑⍴p)×⍴s2)∊,p)\R
[12] R[,p]←(⍴,p)⍴s2
∇
The resulting vector can now be re-wrapped into a reasonable line length. Anything up to the terminal width is OK. Note the one catch, which is that any SQL*PLUS commands (which are simply formatting instructions and have nothing to do with SQL itself) must go on separate lines.
PARA←WID SQL∆WRAP QRY;POS;WID
-----------------------------
[1] ⍝REWRAP SQL QUERY TO FIT GIVEN WIDTH.
[2] ⍝ Targets for line breaks are ' ,'
[3] ⍝ Lines ended with '⌶' are preserved
[4] PARA←(0,WID)⍴' '
[5] Loop:POS←(1+WID)|⌊/(⌽WID↑QRY)⍳', '
[6] POS←WID-POS-1 ⋄ POS←POS⌊¯1+(POS↑QRY)⍳'⌶'
[7] PARA←PARA⍪WID↑POS↑QRY
[8] QRY←POS↓QRY ⋄ QRY←(+/∧\(5↑QRY)∊' ⌶')↓QRY ⋄ →(0≠⍴QRY)↑Loop
∇
The commands and lines of SQL are then sent to SQL*PLUS; this returns a “line-number” prompt until it sees the final semi-colon. Hence the check on line[18] that we have got the prompt back before the next line is fired off. The final line closes the query, and we get the full result back, along with a final “SQL> ” prompt.
What you do to handle possible errors is up to you. I tend to check the result with a function like:
RC←SQL∆CHK R
------------
[1] ⍝ Check for SQL errors
[2] RC←1
[3] →(R ∆IOTA 'ERROR')↑0
[4] →(R ∆IOTA 'unknown')↑0
[5] RC←2
[6] →(R ∆IOTA 'no rec')↑0
[7] RC←0
∇
… and if I get a zero back, I know I can strip the last two lines which will simply be a blank, followed by the “SQL> ” prompt. On the other hand something like “no records selected” may not be an error at all, so you can simply hand it back to the user. Real problem messages like “ERROR: table or view does not exist” should obviously be reported back, with some helpful packaging, such as the phone number of your help desk!
Capturing the Result as APL Variables¶
This is the final stage, which will let you interface SQL fully into a PC-based system. Of course you will not have nested arrays to play with, but you can get very similar results with data-handling functions such as my “DB-385” utilities. These run off simple tables of variable names, such as:
DBDEFN
∆NAME CH[20] .... name
∆DEPTNO NUM .... department number
∆SALARY NUM .... gross salary
MAT
JONES | 20| 12876|
HARRIS | 20| 7890|
KING | 50| 76000|
SQL>
DBDEFN SQL∆SPLIT MAT
⍴∆NAME
3 20
+/∆SALARY
96766
Here are the two remaining functions to carve up your result array into variables:
R←DBDEFN SQL∆DB QRY;RC;REPLY
----------------------------
[1] ⍝ Send query to SQL and assign into the variables in DBDEFN
[2] ⍝
[3] REPLY←0 SQL QRY
[4] RC←SQL∆CHK REPLY ⋄ →(Err,Empty)IF 1 2 =RC
[5] DBDEFN SQL∆SPLIT REPLY
[6] R←'OK' ⋄ →0
[7] Err:R←'*** SQL ERROR: ',,REPLY ⋄ →0
[8] Empty:R←'*** No records selected ***'
∇
NL SQL∆SPLIT MAT;POS;LEN;DPTH;TYPE;lab;FORM
-------------------------------------------
[1] ⍝ Split out columns of table into variables.
[2] POS←MAT[1;]⍳'¦' ⋄ MAT←(MAT[;POS]='¦')⌿MAT
[3] POS←(MAT[1;]='¦')/⍳¯1↑⍴MAT
[4] LEN←¯1+¯1↓(POS,0)-0,POS
[5] POS←¯1↓0,POS
[6] ⍝ Convert NL to the correct format and note whether number,char,dat
[7] NL←(¯2↑ 1 1 ,⍴NL)⍴NL
[8] WIDTH←(¯1↑⍴NL)⌊(' '∧.=NL)⍳1 ⋄ DPTH←(1↑⍴NL)⌈⍴POS
[9] TYPE←'NCD'⍳,(DPTH,1)↑∆LJUST(0,WIDTH)↓NL
[10] NL←(DPTH,WIDTH)↑NL
[11] FORM← 4 9 ⍴36↑',∆MATEXEC'
[12] Lp:→lab←(⍴POS)ITERATE End,CT←1
[13] ⍎NL[CT;],'←',FORM[TYPE[CT];],' MAT[;',(⍕POS[CT]),'+⍳',(⍕LEN[CT]),'
[14] End:→lab[CT←CT+1]
∇
A more general strategy would be to ask SQL to “describe” the table, and then use this information to break the data down. I have so far avoided this, simply to cut down the amount of data flying up and down the line.
Summary¶
Using these functions, or something like them, you can seriously contemplate harnessing the power of APL and the PC environment to provide a far more flexible and easy to use route to SQL-based data than anything currently available in packages like SQL*FORMS.
Appendix: Other Useful Functions.¶
Here is a selection of the utilities used in the system. Please excuse any missing ones, I have long ceased to notice such things in my code; they just get used as if they were part of the language!
MAT←TAB VEC
-----------
[1] ⍝ SET UP RETURNED DATA AS TABLE
[2] ⍝ Kill line-feeds, and leading CRs
[3] VEC←(VEC≠⎕AV[11])/VEC
[4] VEC←(+/∧\(12↑VEC)=⎕TCNL)↓VEC
[5] MAT←⎕TCNL ∆TAB VEC
∇
R←A CAT B;MAX
-------------
[1] ⍝ Superimpose any two char things
[2] A←(¯2↑ 1 1 ,⍴A)⍴A ⋄ B←(¯2↑ 1 1 ,⍴B)⍴B
[3] MAX←(¯1↑⍴A)⌈¯1↑⍴B
[4] R←(((¯1↓⍴A),MAX)↑A)⍪((¯1↓⍴B),MAX)↑B
∇
R←DTC MAT;WID
-------------
[1] ⍝ Delete trailing cols
[2] WID←+/∨\⌽' '∨.≠MAT
[3] R←((1↑⍴MAT),WID)↑MAT
∇
R←DTR MAT;LEN
-------------
[1] ⍝ Delete trailing rows
[2] LEN←+/∨\⌽MAT∨.≠' '
[3] R←(LEN,(1↓⍴MAT))↑MAT
∇
R←DTB VEC
---------
[1] ⍝ Delete trailing blanks from VEC
[2] R←(+/∨\⌽' '≠VEC)↑VEC
∇