6/27/97


      The following is a little program I wrote based upon the UCX sample
routines to allow one to "pull" data from an external ODBC data source 
such as MS-SQL server or MS-Access tables.  The program was quickly 
thrown together and probably can be "jazzed up" considerable.

      In order to use the program, you must first have the ODBC drivers 
installed on an NT server and define an ODBC (System) database source.
You will also need to have the Microsoft Internet server running on 
the NT server.  You must then create two files that allow you to 
retrieve data from your database.  The first file is a Microsoft 
"IDC" file that has the necessary SQL commands to retrieve the desired
data from your data source via ODBC.   An example of this file is as 
follows:   


Datasource:myodbcsource
Template: return.htx
SQLStatement:
+SELECT USERNUM,ASTATUS,EMAIL_YN,PUB_EMAIL,SSN,FName, 
+LName, MINIT,OTHER,SALUTATION,CTITLE,DOE,DOT,DEGREE,ROOM,Phonenumber,
+STATUS,CURR_EMP,CURRSTD
+FROM VMS_ACCT_LIST
+WHERE (MAILTO=-1 AND CURR_EMP=1 AND 
+(ASTATUS=1 OR ASTATUS=2) AND EMAIL_YN=1 AND PUB_EMAIL=1) ORDER BY LNAME,FNAME,MINIT
DefaultParameters:LName=%,FName=%
 
Note that "myodbcsource" is the name of your ODBC source defined through the
Microsoft ODBC 32 administrator program.



The second file is an "HTX" file 
through which the data is returned.  An example of such a file is as follows:


**"USERNUM (15)","ASTATUS (1)","EMAIL_YN (1)","SSN (11)","LNAME (25)",
"FNAME (20)","MINIT (20)","OTHER (10)","SALUTATION (10)","CTITLE (80)",
"DOE (20)","DOT (20)","STATUS (2)","CURR_EMP (2)","CURRSTD (2)","DEGREE (12)",
"ROOM (15)","PHONE (10)"$$
<%begindetail%>
**"<%USERNUM%>","<%ASTATUS%>","<%EMAIL_YN%>","<%SSN%>","<%LName%>",
"<%FNAME%>","<%MINIT%>","<%OTHER%>","<%SALUTATION%>","<%CTITLE%>",
"<%DOE%>","<%DOT%>","<%STATUS%>","<%CURR_EMP%>","<%CURRSTD%>","<%DEGREE%>",
"<%ROOM%>","<%PhoneNumber%>"$$
<%enddetail%>


Information on using IDC/HTX files can be found under Microsoft's Web page.



The first record defines the field names for the data being returned
and the size of the data field.  A Starting delimeter of "**" and ending 
of "$$" is used so that the VMS "GETDATA" can determing the beginning
and ending of each record to decode.
The data is then returned in all subseqeunt records in the same manner.  
The file returned should look like a "comma" delimeted file with the data 
in quotes.


These two files have to be placed on the NT server and an alias pointed to
them in the IIS server so that they can be accessed via the web. Once you 
have these two files created and the necessary permissions defined in you  
IIS web server and the ODBC source defined, you should be able to 
use any browser client to test the files.  You can point your browser to 
"http://myntserver/myscripts/myidcfile.idc" where you substitute
the correct path information in the above. If this all works ok via your 
browser, then you know that your NT part of the settings are ready.


Now for the VMS part.

Get the GETDATA.ZIP file and unzip it into a VMS directory.

Use the "MAKE.COM" to compile and link the "GETDATA" program.
(The program was written using Digital's UCX for the TCP/IP server.  
It would probably have to be modified in order to use it with any other 
TCP/IP server).

Define a logical "EXE_DIR" to the location where you have the executable.

Edit the MYSOURCE.INP file and change the first line to point to your
NT server and "/myscripts/myidcfile.idc" to point to the location you 
have your "idc" file define as above.  

The GETDATA program creates three files, a "mysource.def" FILE into
which the FIELD names will be written along with the sizes, 
a "mysource.scr" file where the raw data or any error message is written 
when the program "gets" the data and a "mysource.out" file into which 
the data is rewritten into a formatted file structure.

Finally, modify the "PULL.COM" file to point to your NT server and to the
name of your "input" definition file.  Then execute the procedure and 
hopefully you should receive the same data that you would have when
accessing the "IDC" file via a web client.  If there are problems 
in accessing the file, check the file "mysource.scr" for messages.


Again, this was a quick program used in order to retrieve data from an 
MS-Access database routinely and can certainly be modified or refined 
to work much better and cleaner.  All I request from anyone retrieving 
the program is that you share your enhancements with the rest of us
on the net and not try to "commercialize" this process as it should always 
be considered as "freeware"


Best of luck.

Charles Cooper
Director of Research Computing
University of Texas
School of Public Health
1200 Herman Pressler
Room 806
Houston, Tx  77030

cooper@utsph.sph.uth.tmc.edu