- 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 > DBISQLC. (In Release 2005 and later, DBISQLC is supplemented by a somewhat different version called DBISQLG.)
- 3. When prompted, type your SOS ID and password in the appropriate fields on the LOGIN tab, then next to ODBC Data Source select “SOSDATA” and click OK.
- A screen with multiple windows will open. Type your query in the Command (DBISQLC) or SQL Statements (DBISQLG) window and click the Execute button (or press <F9> in either version).
DBISQLC:
DBISQLG:
If you want to save the results of your query in a file, 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 spreadsheet file in the SOS folder called “pt-emails.wks”. This “LOTUS format” file can be opened, manipulated, and printed using Microsoft Excel or Lotus 1-2-3:
SELECT lastname, firstname, email
FROM rv_patients
ORDER BY lastname, firstname
; OUTPUT TO c:\sos\pt-emails.wks FORMAT LOTUS
You are not limited to spreadsheet output, by the way. In the example above, the results are output in spreadsheet format because of the keyword LOTUS 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:
| ISQL Version | Format | Suggested filename extension (type) | Keyword | Comments |
| DBISQLC only | Plain text | TXT | TEXT | Output can be edited and printed with Notepad or your word processor or other text editing program. |
| DBISQLC andDBISQLG | Spreadsheet (Lotus or Excel) | WKS | LOTUS | Technically, this is the Lotus 1-2-3 format, but Excel will recognize and open the file correctly. |
| DBISQLC andDBISQLG | Comma delimited | CSV | ASCII orASCII QUOTE ‘’ | To eliminate quote marks around each data element, add QUOTE ‘’ (the word QUOTE followed by a space and two apostrophe marks). |
| DBISQLC and |
DBISQLG
Generic databaseDBFDBASEII or DBASEIIIMany programs have import filters for “DBF” format files. Excel, for example, will open DBF files.DBISQLC and
DBISQLG
Fixed width columnsTXTFIXEDData records appear in fixedformat with the width of each column either the same as defined by the column’s type or specified as a parameter.DBISQLC and
DBISQLG
Web pageHTM or HTMLHTMLOpen with your web browserDBISQLG onlyExcel spreadsheetXLSEXCELExcel spreadsheetDBISQLG onlyXML web browser formatXMLXMLSome programs and systems can import and/or process data in XML format.
6. You can save this query with a name by clicking File > Save and run it whenever you want by opening DBISQLC or DBISQLG 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\ASA\Win32\DBISQLG C:\SOS\EMAILS.SQL” (SOS Release 2005 and later)
————————–
Documents you may find helpful include the rather dated, but still relevant, annotated data dictionary: http://www.sosoft.com/files/downloads/sosddct.pdf
and a set of entity diagrams showing links among tables for the most commonly used data: http://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.
