.PL63 .PN1 .....L.....................................R...L.....................A .FO2 AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA>@@ [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[ UNOTE 100 Page # of 3 .HE4 .....L.......T...............................R.L.....................A From :‰ Customer Services Ref: UNOTE 100~ Re   :‰ Using other Databases with Ucalc 26 Aug 92~ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA>@@ [[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[[ |....L..T.......T.......T.......T.......T.......T.......T.......T....J QUESTION@@ EEEEEEEE How can I use other databases with the Uniplex Spreadsheet? ANSWER@@ EEEEEE By default, the Uniplex spreadsheet uses the DATABASE and PIPE commands to link cells on dynamic enquiries to one or more Uniplex databases. Thus if the user links a database, testdb, to a spreadsheet cell,@@ AAAAAA using the following commands:-~ ‰ / I(ntegrate) D(atabase) N(ame) testdb~@@ AAA A A AAAAAA ‰ @pipe("select col1 from tableA where col2 = '1990'")~@@ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA then, ucalc invokes usql in background to service this and any other PIPE requests (ucalc loads just one copy of usql, and sends PIPE statements to it whenever a calculate is required). The default flags used by usql when it is running as the backend process for ucalc are: ‰ usql -f9 -q -E1~@@ AAAAAAAAAAAAAAA |....L..T...R...L.......T.......T.......T.......T.......T.......T....J -f9‰ Ascii tab field delimiters~@@ AAA -q‰ No banners are required~@@ AAA -E1‰ Any error messages generated by the contents of a PIPE@@ AAA command are to be sent to ucalc as a single text line starting with the ascii SOH character. |....L..T.......T.......T.......T.......T.......T.......T.......T....J UCALC to USQL Protocol@@ EEEEEEEEEEEEEEEEEEEEEE Having started usql as the backend database processor, ucalc then expects to interact with it using a simplistic MASTER/SLAVE protocol:~ |....H....L.....T.......T.......T.......T.......T.......T.......T...........R i) ucalc is always the master; every time the spreadsheet is calculated it writes the PIPE argument (usql command) down the pipe to usql's standard input.~ ii) It then reads (newline-delimited) text lines back from usql's standard output until it reads a blank line (ie: just a newline character) which indicates "end of response to the PIPE command".~ iii) ucalc then analyses the first, or only, non-blank response line (any additional non-blank lines are ignored - as are all but the first field on a non-error response line). iv) If this line starts with the character of ASCII value SOH (binary 1), then the line is assumed to be a usql error message, and it is displayed on the ucalc message line and the current cell is not updated. v) Otherwise, the first "field" on the line (all text up to the first TAB, or up to end of line, if no TAB) is used to set the data value of the cell (ie: if the field contains a numerics, the data value is set to the corresponding number, otherwise the data value is set to the text string). |....L..T.......T.......T.......T.......T.......T.......T.......T...........R .PA Using a backend other than USQL~@@ EEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE In true Uniplex tradition, ucalc can be configured to call a "database backend" processor other than usql, using the -p flag.@@ AA ‰ Note, however, that if the spreadsheet contains any DATABASE@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII commands, the -p flag is ignored and usql is always invoked.@@ IIIIIIIIIIIIIIAAIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII So, if anyone has a database (or any other interactive data enquiry/update system) interface program that can be made to adhere to the simple protocol outlined above, it can be used to process PIPE command arguments instead of usql.~ The invocation is:- ‰ ucalc -p mysql@@ AAAAAAAAAAAAAA Example Application:~@@ EEEEEEEEEEEEEEEEEEEE Suppose that you have a price list in a flat file which you need to access from your spreadsheet to draw in descriptions etc.~ 1. Enter the following shell script and save it in a file called 'mysql' and make it executable.~ .FN FX-NORMAL # MYSQL - This script takes two parameters from the spreadsheet and uses~@@ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # the first one as a search key for the file named by the second~@@ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA # variable. ~@@ AAAAAAAAAAA while read COMMAND ; do~ case "$COMMAND" in~ database*) # Avoid action on normal database ;;~ RETURN:*) # Perform a valid function~ set xx $COMMAND ; shift ; shift~ set yy `fgrep "$1" "$2"` ; shift~ echo "$*" ;;~ *) # Error messages~ echo "\01$0: Unknown command - \"$COMMAND\"" ;;~ esac~ echo # required for end of input marker~ done~ .FN NORMAL 2. Enter the following data and call it prices.list.~ 1001 Door (Wooden) 12.50~@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 1002 Door (Steel) 12.50~@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 1003 Door (neoprene) 16.50~@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 1004 Door (red) 12.50~@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 1005 Door (blue) 22.50~@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 1006 Door (green) 32.50~@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII 1007 Door (12') 42.50~@@ IIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIIII .PA 3. Start the Spreadsheet using the command:~ ‰ ucalc -p mysql~@@ AAAAAAAAAAAAAAAAA 4. In column 1 fill the first 11 cells with the numbers 1000 to 1011~ 5. In Column B enter the function:~ ‰ =pipe("RETURN: ",A1," prices.list")~@@ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA 6. Copy the function to the first 11 cells of column B~ You can also use a direct command if required: eg.~ ‰ =pipe("RETURN: 1008 prices.list")~@@ AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA