(#207) Running Queries with DBISQL

The following instructions are specifically for SOS Release 2010 and later.

1. 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.
2. 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.)
3. When prompted, type your SOS ID and password (you may find that your password will work only if typed all UPPERCASE) in the appropriate fields on the LOGIN tab, then next to ODBC Data Source select or type “SOSDATA” and click OK.
   
4. 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).

SELECT lastname, firstname, email
FROM rv_patients
ORDER BY lastname, firstname
; OUTPUT TO c:sospt-emails.html FORMAT HTML

You are not limited to HTML 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:

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:SOSSAbin32DBISQLC
C:SOSEMAILS.SQL“, or “C:SOSSAbin32DBISQL
C:SOSEMAILS.SQL”

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.

Print Friendly