The following instructions are specifically for SOS Release 2010 and later.
- First, it is necessary to configure your user ID in the Admin Module (Users and Passwords) to allow access from 3rd party products (Access tab option “Grant read-only access from non-SOS products”). If you are not cleared for this type of access, you will have to ask a user with full access to run the query for you. You cannot run queries with the “SUPER” ID; it must be another ID that has the “Grant read-only access” option enabled.
- Launch the ISQL utility by doing Start > Programs > SOS Applications > DBISQL. (An alternate, somewhat less user-friendly version can be launched by Start > Programs > SOS Applications > DBISQLC.)
- When prompted, type your SOS ID and password (you may have to type all letters in your password in UPPERCASE) in the appropriate fields on the LOGIN tab, then next to ODBC Data Source select or type “SOSDATA” and click OK.
- A screen with multiple windows will open. Type your query in the Command (DBISQLC) or SQL Statements (DBISQL) window and click the Execute button (or press <F9> in either version).
Using the DBISQL version, use Data > Export to save your results in a file. In either version you can add a line to query to do the same. Add a semicolon, then an OUTPUT statement to the query. The following simple query creates a list of patients along with their email addresses and saves it in a “web page” file in the SOS folder called “pt-emails.html”. This HTML file can be viewed in your web browser, or opened, manipulated, and printed using Microsoft Excel:
SELECT lastname, firstname, email
FROM rv_patients
ORDER BY lastname, firstname
; OUTPUT TO c:\sos\pt-emails.html FORMAT HTML
You are not limited to HTML output, by the way. In the example above, the results are output in HTML format because of the keyword HTML after FORMAT. By changing the keyword, you can choose from an assortment of output file formats. Among the formats available for your query results are the following:
| Format | Suggested filename extension (type) | Keyword | Comments |
| Plain text | TXT | TEXT | The output is a TEXT format file with one row per line in the file. All values are separated by commas, and strings are enclosed in apostrophes (single quotes). The delimiter and quote strings can be changed using the DELIMITED BY and QUOTE clauses. If ALL is specified in the QUOTE clause, all values (not just strings) are quoted.TEXT is the default output type. This output format used to be called ASCII in previous versions of DBISQL. |
| Fixed width columns | TXT | FIXED | The output is fixed format with each column having a fixed width. The width for each column can be specified using the COLUMN WIDTHS clause. No column headings are output in this format. |
| Web page | HTM or HTML | HTML | Open with your web browser. Also can be loaded in Excel and some other spreadsheet applications. |
| XML web browser format | XML | XML | Some programs and systems can import and/or process data in XML format. |
Note that you can leave off the OUTPUT statement entirely and export using the menu DATA > EXPORT at the top of DBISQL. HTML is still the suggested format, however. You can display it in your browser by double-clicking, or open and manipulate the file containing the result set using Excel.
You can save this query with a name by clicking File > Save and run it whenever you want by opening DBISQLC or DBISQL and clicking File > Open to recall it. By convention, queries like this are given names ending in SQL, like EMAILS.SQL, for example. I would suggest that you save it in your SOS folder so you don’t lose it. If you really want to get fancy, you can create a shortcut on your desktop to run your query with one double click (plus typing in your ID, etc. when prompted):
“C:\SOS\SA\bin32\DBISQLC C:\SOS\EMAILS.SQL“
or
“C:\SOS\SA\bin32\DBISQL C:\SOS\EMAILS.SQL”
Documents you may find helpful include the rather dated, but still relevant, annotated data dictionary: https://www.sosoft.com/files/downloads/sosddct.pdf
and a set of entity diagrams showing links among tables for the most commonly used data: https://www.sosoft.com/files/downloads/sosdpics.pdf
It is most important that you review the final chapter (Accessing SOS Data from Other Programs) in sostech.pdf, located in your SOS folder. This chapter explains the basics of the database organization. You will be lost in doing anything beyond the simplest queries without that foundation.
