Using Cold Fusion Flash SAS Excel and Access

Using Cold. Fusion, Flash, SAS, Excel, and Access with Oracle Presenters: Michael Fox: MS, Computer Science Alla Guseynova: MS, Applied Mathematics Johns Hopkins Oncology Center Research Information Technology Systems n https: //www. rig. onc. jhmi. edu

Research Information Technology Systems (RITS) n Group, 6. 5 years of team work n n Michael Fox, DBA and project leader Alla Guseynova, Database Architect/Project Leader Allan Grimm, Sr. Programmer/Analyst Nicholas Bollweg, Sr. Programmer/Analyst

RITS Expertise n Project management n n Database management n n Oracle, Sybase, SQL Server Web Development n n requirements analysis, functional specifications Cold. Fusion, Flash, SAS, Java. Script, HTML Systems administration n Windows 2000, XP, NT, UNIX

RITS Tools and Applications n n n Intel platform NT 4. 0, W 2 K, XP, Server 2003 Oracle version 8 i, 9 i Cold. Fusion MX Flash MX MS IIS 5. 0

Cold Fusion n n n From Macromedia Provides application services Works with IIS and other web server Rapid code development Tag based language Connects to Oracle natively or via ODBC

Flash n n n From Macromedia authoring environment for creating interactive websites Works alone or with Cold. Fusion Requires Flash player on client We use Cold. Fusion to connect it to Oracle

SAS n n n n From SAS Procedural language Statistical and analytic tool Numerous, full-featured functions Well documented Well run classes by SAS Connects to Oracle via SAS/Access

Excel n n From Microsoft Spread sheet application Connects to Oracle via ODBC Paste in tables from the web

Access n n n From Microsoft Database for non-programmers Connects to Oracle via ODBC n n n Import Link Mix Oracle and Access tables for applications or reports

ODBC n n Stands for Open Data. Base Connectivity Developed by SQL Access Group Middle layer or driver between an application and a Data. Base Management System (DBMS) Application and DBMS must be ODBC -compliant

Oracle Networking n n n Can identify and resolve Oracle servers various ways We use TNS Names Important Oracle files n n Tnsnames. ora Sqlnet. ora

Sqlnet. ora NAMES. DEFAULT_DOMAIN = ONC. JHMI. EDU SQLNET. AUTHENTICATION_SERVICES= (NTS) NAMES. DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)

Tns. Names. ora ONCRIS. ONC. JHMI. EDU = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = goethe)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = oncris) ) ) PBMT. ONC. JHMI. EDU = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = bmt-nt)(PORT = 1526)) ) (CONNECT_DATA = (SERVICE_NAME = pbmt) ) )

Demo n Tests for connectivity n n n Ping Tnsping SQL*Plus SQL Worksheet ODBC setup and test

ODBC Setup

Cold Fusion

Cold Fusion Data Sources

CRO Research Personnel Table Flash Accessing Oracle by way of a Flash interface happens in two parts. First, Net. Services must be enabled within Action Script in order to open a gateway for your Flash swf to speak with Cold. Fusion. Once open, Flash can call Cold. Fusion queries stored within a cfc file.

Cold Fusion Components When Flash calls upon a stored query, the Cold. Fusion cfc executes the query which calls Oracle, and then passes the results back to Flash through the Net. Services gateway.

Action. Script Interaction Action. Script functions are used to create dropdown menus, and select the current values.

Interactive Interface This creates an interactive interface where a user can update records within a spreadsheet format. Records are then updated via a function in Action Script, which again activates the Cold. Fusion cfc, and updates Oracle.

Excel screen shot

Access

SKCCC Clinical Research Reports: Automated Generation n n Scheduled tasks Development System n n Read data from remote Oracle database Run SAS programs Sends reports to FTP server Web server n Retrieves reports from FTP server

Demos Live demos of connections, applications and reports

Head and Neck Cancer Dataabase

Clinical Research Office

SAS reports

Microarray Database