(#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).

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: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:

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.

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.

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes:

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

This site uses Akismet to reduce spam. Learn how your comment data is processed.