Juniata College DUG Spring, 2005

1
Juniata College DUG Spring, 2005
PHP, Linked Servers, and ODBC for Web Data from
Unidata TT3308
2
Juniata College DUG Spring, 2005
David Fusco - Director of Technology
Operations Rick Brown Database Systems
Analyst Joel Pheasant Director of Web
Technologies
3
Juniata College Overview

• Background of Juniata and our Projects
• The technology that were using
• Front-end processing
• Middleware and MS SQL
• Unidata direct calls and stored procedures
• QA

4
Juniata College Background

• Quick Facts
• Traditional Liberal Arts College
• 1400 FTE
• Been w/ Datatel for 22 years
• Colleague R17, Ben 5.1, WA 2.16
• Campus is development-friendly
• On-line Catalog, Event Scheduler, Prospective
  Student Application
• WA as-is, customize outside of this as needed

5
Juniata College Background
6
Juniata College Technology Choices
SQL and VB
UniObjects
7
Juniata College Portal Integration
8
Juniata College Portal Integration
9
Juniata College Portal Integration
10
Juniata College Front-end Processing

• Why PHP?
• Open Source / non-proprietary
• Server-side scripting / hides code
• Logins and passwords are hidden
• Fast little overhead
• Melds directly into HTML
• PHP Parser also reads HTML
• Supports numerous Dbs
• SQL friendly
• Large knowledge base in the industry

11
Juniata College Front-end Processing

• Three primary methods of data retrieval
• Data dump
• Stored Procedure calls Rick will discuss
• Direct ODBC calls
• All three make use of MS SQL in the middle
• Three examples
• On-line Application
• Event Scheduler
• On-line Catalog Information

12
Juniata College On-Line Application
Note that the value EB.AC is then stored for
this student
13
Juniata College On-Line Application
lt? result_programs sybase_query("Select ID,
VAL_INTERNAL_CODE, VAL_EXTERNAL_REPRESENTATION
from openquery(UNIDATA, 'select from
ST_VALCODE_VALS WHERE ID \"CAREER.GOALS\"')
ORDER BY VAL_EXTERNAL_REPRESENTATION") while
(row_programssybase_fetch_array(result_programs
)) echo "ltoption value\"".row_programs1."\
"" if (row_programs1 row2'study') e
cho " selected\"selected\"" echo
"gt".row_programs2."lt/optiongt\n" ?gt
14
Juniata College Event Scheduler
15
Juniata College Event Scheduler
16
Juniata College Event Scheduler
class_result _at_sybase_query("sp_ResRoomMultiDay
'building','room','date1','date2','start_time
1', 'end_time1',''") if(sybase_num_rows(clas
s_result) gt 0) class_row
sybase_fetch_row(class_result) return
"Conflict class_row1" else return 0
17
Juniata College Event Scheduler 2
18
Juniata College Event Scheduler - 2
19
Juniata College Event Scheduler - 2
datatel_room sybase_query("select from
openquery(UNIDATA, 'select CLRM_CAPACITY,
CLRM_TYPE from CLASSROOM where
ID\"room_id\"')") result
sybase_query("select from openquery(UNIDATA, 'se
lect s.EQP_DESC, c.CLRM_EQUIPMENT_QTY,
s.ID from CLASSROOM_EQUIP c, EQUIPMENT_SQL
s where c.ID \"room_id\" and
c.CLRM_EQUIPMENT_AVAILs.ID and
c.CLRM_EQUIPMENT_AVAIL ltgt \"\"')")
20
Juniata College On-line Catalog Info
21
Juniata College On-line Catalog Info
22
Juniata College On-line Catalog Info
lt? sybase_connect('dataserver',username',paw0r
d') result sybase_query("select from
openquery(UNIDATA,'select CRS_TITLE, CRS_NAME,
CRS_DESC, CRS_SESSION_CYCLE, CRS_YEARLY_CYCLE,
CRS_MIN_CRED, CRS_COURSE_TYPES, CRS_NO from
COURSES_SQL where CRS_DEPTS\"".dept."\"
and CRS_STATUS\"A\" and CRS_NO gt100 and
CRS_NO not like \"...XX...\" and CRS_NO !
\"INS\" order by CRS_NAME')") while (row
sybase_fetch_array(result)) row0
strtr(row0,'ý',' ') echo "ltpgtltspan
class\"course\"gt".row1."nbspnbspnbsp".r
ow0."lt/spangt (" switch (row3) case
'B' echo "Fall amp Spring " break case
'F' echo "Fall " break case 'S' echo
"Spring " break default echo "Varies
" break echo row5." Credit" if
(row5 ! 1) echo "s" row6
strtr(row6,'ý',',') if (row6) echo "
".row6 echo ")ltbr /gt\n" row2
strtr(row2,'ý',' ') echo row2."lt/pgt\n\n"
?gt
23
Juniata College ODBC License

24
Juniata College ud_database

• Verify UniRPC is running ps ef grep unirpc
• /usr/ud60/includegt cat ud_database
  
  
• 
  
  
  
• DATABASELIVE
  
• UDTHOME/usr/ud
  
• UDTACCT/datatel/live/collive
  
• TRACE_LEVEL0
  
• 
  
• /usr/ud60/includegt

25
Juniata College UCI Config Editor

• Install ODBC Driver on Client (SQL Server)
• uci.config

26
Juniata College VSG

• IBM Visual Schema Generator
• Create SQL type schema/metadata
• Create views on files (tables)
• Grant Permissions
• Lie A little

27
Juniata College SQL Server Set-up

• SQL Server
• Remote Server (Unidata)
• Microsoft OLE DB for ODBC
• IBM ODBC for Unidata

28
Juniata College Query Collive
29
Juniata College Stored Procedures

• SQL Server Regular Stored Procedures
• Extended Stored Procedures

30
Juniata College Query Of Unidata

• CREATE PROCEDURE sp_ResRoomMultiDay
• _at_inBLDG varchar(20),
• _at_inROOM varchar(10),
• _at_inDATE varchar(30),
• _at_inEndDATE varchar(30),
• _at_inStartTime varchar(30),
• _at_inEndTime varchar(30),
• _at_QUERY varchar(5000)
• AS
• SELECT _at_QUERY 'Select from openquery(UNIDATA,
  
• ''select from UNIRESERVED_NF WHERE (
• ((CALS_DATE
• ''''' _at_inDATE '''''
• AND
• CALS_DATE
• ''''' _at_inEndDATE '''''

31
Juniata College Insert into Unidata

• CREATE PROCEDURE usp_AddAppMini
• -- Use to insert records into Unidata file for
  ELF import into Collive
• _at_ID varchar(30),
• _at_financial_submit varchar (2),
• _at_personal_address1 varchar(30),
• _at_XAI_BATCH varchar(10),
• _at_QUERY varchar(8000)
• AS
• -- Force select to fail to return empty columns,
  will then allow insert. ODBC driver limitation
• insert openquery(UNIDATA,
• 'select
• ID,
• financial_submit,
• personal_address1,
• XAI_BATCH
• from X_ADMIT_IMPORT_SQL where 10')

32
Juniata College More Information

• Openquery insert problemhttp//support.microsoft.
  com/default.aspx?scidkbEN-US270119
• Distributed Queries http//www.winnetmag.com/SQLSe
  rver/Article/ArticleID/8992/8992.html
• Using VSG and the Schema API (IBM)
• Developing UniData ODBC Applications

33
Juniata College DUG Spring, 2005

• Questions ???
• http//explorer.juniata.edu/dug/index.html