Using ColdFusion , Flash, SAS, Excel, and Access with Oracle

happylandcannedSoftware and s/w Development

Jul 2, 2012 (4 years and 9 months ago)

411 views

Using
Using
ColdFusion
ColdFusion
, Flash, SAS, Excel, and
, Flash, SAS, Excel, and
Access with Oracle
Access with Oracle
Presenters:
Presenters:
Michael Fox: MS, Computer Science
Michael Fox: MS, Computer Science
Alla Guseynova: MS, Applied Mathematics
Alla Guseynova: MS, Applied Mathematics
Johns Hopkins Oncology Center
Johns Hopkins Oncology Center
R
R
esearch
esearch
I
I
nformation
nformation
T
T
echnology
echnology
S
S
ystems
ystems


https://www.rig.
https://www.rig.
onc
onc
.
.
jhmi
jhmi
.
.
edu
edu
Research Information Technology
Research Information Technology
Systems (RITS)
Systems (RITS)


Group, 6.5 years of team work
Group, 6.5 years of team work


Michael Fox, DBA and project leader
Michael Fox, DBA and project leader


Alla Guseynova, Database Architect/Project
Alla Guseynova, Database Architect/Project
Leader
Leader


Allan Grimm, Sr. Programmer/Analyst
Allan Grimm, Sr. Programmer/Analyst


Nicholas
Nicholas
Bollweg
Bollweg
, Sr. Programmer/Analyst
, Sr. Programmer/Analyst
RITS Expertise
RITS Expertise


Project management
Project management


requirements analysis, functional specifications
requirements analysis, functional specifications


Database management
Database management


Oracle, Sybase, SQL Server
Oracle, Sybase, SQL Server


Web Development
Web Development


ColdFusion, Flash, SAS, JavaScript, HTML
ColdFusion, Flash, SAS, JavaScript, HTML


Systems administration
Systems administration


Windows 2000, XP, NT, UNIX
Windows 2000, XP, NT, UNIX
RITS Tools and Applications
RITS Tools and Applications


Intel platform
Intel platform


NT 4.0, W2K, XP, Server 2003
NT 4.0, W2K, XP, Server 2003


Oracle version 8i, 9i
Oracle version 8i, 9i


ColdFusion MX
ColdFusion MX


Flash MX
Flash MX


MS IIS 5.0
MS IIS 5.0
Cold Fusion
Cold Fusion


From Macromedia
From Macromedia


Provides application services
Provides application services


Works with IIS and other web server
Works with IIS and other web server


Rapid code development
Rapid code development


Tag based language
Tag based language


Connects to Oracle natively or via
Connects to Oracle natively or via
ODBC
ODBC
Flash
Flash


From Macromedia
From Macromedia


authoring environment for creating
authoring environment for creating
interactive websites
interactive websites


Works alone or with
Works alone or with
ColdFusion
ColdFusion


Requires Flash player on client
Requires Flash player on client


We use
We use
ColdFusion
ColdFusion
to connect it to
to connect it to
Oracle
Oracle
SAS
SAS


From SAS
From SAS


Procedural language
Procedural language


Statistical and analytic tool
Statistical and analytic tool


Numerous, full
Numerous, full
-
-
featured functions
featured functions


Well documented
Well documented


Well run classes by SAS
Well run classes by SAS


Connects to Oracle via SAS/Access
Connects to Oracle via SAS/Access
Excel
Excel


From Microsoft
From Microsoft


Spread sheet application
Spread sheet application


Connects to Oracle via ODBC
Connects to Oracle via ODBC


Paste in tables from the web
Paste in tables from the web
Access
Access


From Microsoft
From Microsoft


Database for non
Database for non
-
-
programmers
programmers


Connects to Oracle via ODBC
Connects to Oracle via ODBC


Import
Import


Link
Link


Mix Oracle and Access tables for
Mix Oracle and Access tables for
applications or reports
applications or reports
ODBC
ODBC


Stands for Open
Stands for Open
DataBase
DataBase
Connectivity
Connectivity


Developed by SQL Access Group
Developed by SQL Access Group


Middle layer or driver between an
Middle layer or driver between an
application and a
application and a
DataBase
DataBase
Management System (DBMS)
Management System (DBMS)


Application and DBMS must be
Application and DBMS must be
ODBC
ODBC
-
-
compliant
compliant
Oracle Networking
Oracle Networking


Can identify and resolve Oracle
Can identify and resolve Oracle
servers various ways
servers various ways


We use TNS Names
We use TNS Names


Important Oracle files
Important Oracle files


Tnsnames
Tnsnames
.
.
ora
ora


Sqlnet
Sqlnet
.
.
ora
ora
Sqlnet
Sqlnet
.
.
ora
ora
NAMES.DEFAULT_DOMAIN = ONC.JHMI.EDU
NAMES.DEFAULT_DOMAIN = ONC.JHMI.EDU
SQLNET.AUTHENTICATION_SERVICES= (NTS)
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES,
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES,
HOSTNAME)
HOSTNAME)
TnsNames
TnsNames
.
.
ora
ora
ONCRIS.ONC.JHMI.EDU =
ONCRIS.ONC.JHMI.EDU =
(DESCRIPTION =
(DESCRIPTION
=
(ADDRESS_LIST =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
(ADDRESS = (PROTOCOL = TCP)(HOST = goethe
goethe
)(PORT = 1521))
)(PORT = 1521))
)
)
(CONNECT_DATA =
(CONNECT_DATA =
(SERVICE_NAME =
(SERVICE_NAME =
oncris
oncris)
)
)
)
)
)
PBMT.ONC.JHMI.EDU =
PBMT.ONC.JHMI.EDU =
(DESCRIPTION =
(DESCRIPTION
=
(ADDRESS_LIST =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST =
(ADDRESS = (PROTOCOL = TCP)(HOST = bmt
bmt-
-
nt
nt
)(PORT = 1526))
)(PORT = 1526))
)
)
(CONNECT_DATA =
(CONNECT_DATA =
(SERVICE_NAME =
(SERVICE_NAME =
pbmt
pbmt)
)
)
)
)
)
Demo
Demo


Tests for connectivity
Tests for connectivity


Ping
Ping


Tnsping
Tnsping


SQL*Plus
SQL*Plus


SQL Worksheet
SQL Worksheet


ODBC setup and test
ODBC setup and test
ODBC Setup
ODBC Setup
ColdFusion
ColdFusion
ColdFusion
ColdFusion
Data Sources
Data Sources
Excel
Excel
Excel screen shot
Excel screen shot
Access
Access
SKCCC Clinical Research Reports:
SKCCC Clinical Research Reports:
Automated Generation
Automated Generation


Scheduled tasks
Scheduled tasks


Development
Development
System
System


Read data from
Read data from
remote Oracle
remote Oracle
database
database


Run SAS programs
Run SAS programs


Sends reports to FTP
Sends reports to FTP
server
server


Web server
Web server


Retrieves reports from
Retrieves reports from
FTP server
FTP server
Demos
Demos
Live demos of connections,
Live demos of connections,
applications and reports
applications and reports
Head and Neck Cancer
Head and Neck Cancer
Dataabase
Dataabase
Clinical Research Office
Clinical Research Office
SAS reports
SAS reports
Microarray
Microarray
Database
Database