Transcript Publishing Your PDB Under Windows

Member:
Reset
System
Folder:
d:\PDB\
Member System in Library d:\PDB\ Beginning Thursday, November 02, 2000
I
J
K
L
M
N
O
1 PCTRGQTA
PCTDPCTM
PCTINTTM
PCTPRVTM
PCTCPUTM
PCTUSRTM
ALIGFIXU
2
15.71
0.845
0.331
6.189
19.672
13.485
3
15.71
0.389
0.169
3.656
11.709
8.053
4
15.71
0.367
0.168
3.704
12.458
8.752
5
15.71
0.273
0.127
2.637
8.585
5.947
6
15.71
0.35
0.139
3.223
11.723
8.5
7
15.71
0.395
0.146
3.423
10.739
7.315
8
15.71
0.563
0.205
4.357
13.092
8.735
9
15.71
0.929
0.311
6.579
19.929
13.352
10
15.822
1.197
0.402
10.362
29.489
19.128
11
16.38
1.419
0.482
28.815
53.956
25.141
12
16.38
1.272
0.43
12.282
33.391
21.107
13
16.38
1.303
0.452
12.243
34.578
22.335
14
16.38
1.262
0.438
11.22
32.047
20.827
0.
0.
0.
0.
0.
0.
0.
0.
0.
0.
0.
0.
0.
P
CNTXTSWT
391.761
222.268
248.417
189.72
215.093
226.972
267.96
361.801
459.929
523.814
484.003
508.456
492.237
1.5
1.
Series
0.5
0.
Introduction
Tutorial
How to publish the contents of a
database using features available in
Microsoft Windows
 Local machine
 Legacy client/server
 Intranet/Internet
Introduction
“Might be a little bit heavy on the VB/
HTML side for old-time capacity
planners...”
Vic Soder, CMG ERB
If you have ever written a COBOL
program, you are overqualified for web
development.
Disclaimer
All products mentioned are the property
of their owners
It may possible to use these
technologies to publish data in ways
that were not intended, or that have
licensing implications
Agenda
Scripting
Data Access Methods
Overview
 Configuration Issues
 Sample Scripts

ActiveX Controls
Overview
 Sample Scripts
 Office Web Components

Scripting
Scripting
Script is the glue
Vbscript and Jscript
Legacy Windows
WSH – Windows Scripting Host
Browser
Client-side and Server-side
Scripting
SAS
SQL
Server
Access
M
D
A
C
Script
A
C
T
I
V
E
X
Browser
Window
MDAC
Microsoft Data Access
Components
MDAC
1)
2)
3)
4)
5)
Universal Data Access
ODBC – Open Database Connectivity
OLE DB – OLE Database
ADO – Active Data Objects
RDS – Remote Data Services
ADO.Net - ???
MDAC
Application
ADO
OLE DB
MS ODBC
Provider
ODBC
SQL-aware Database
NonSQL DB
ODBC
Circa 1990
Ubiquitous drivers
Not an Object-Oriented API
Relational bias
OLE DB
COM interface
Support for broader spectrum of data
repositories
Interface used by ADO
ODBC data sources accessible via “MS
OLE DB provider for ODBC”

Possibly with reduced function
ADO
(currently) Highest level interface
Simplified object structure
Relies on OLE DB
RDS
Makes ADO data sources accessible
across a network
HTTP, HTTPS, or DCOM
Uses Microsoft Internet Information
Server as conduit
RDS
HTTP
ADO
IIS
Application
OLE DB Provider
Requesting
Process
ODBC Driver
Serving
Process
Database
MDAC Configuration Issues
ODBC Data Sources
Control Panel
Administrative Tools
Data Sources (ODBC)
SAS ODBC Drivers
System Data Source
 TCP
Port
c:\WINNT\system32\drivers\etc\services

SAS TCP Port
SAS ODBC vs. OLE DB
ODBC
Separate install
Full SQL support
Runs Proc OdbcServ
instance
Field name is
variable label
OLE DB
Auto install with V8
SQL not supported
Direct lib/member
access
Field name is variable
name
MSDFMAP.ini
RDS Security
C:\WinNT\System\MSDFMAP.ini
Create token to identify local data
source for remote access
Determine permitted access
MSDFMAP.ini
MSDFMAP.ini
[connect default]
Access=NoAccess
[sql default]
;Sql=" “
' <---comment this line out
[connect PDB]
Access=ReadOnly
Connect="Provider=sas.LocalProvider.1;
Data Source=d:\PDB\Detail\“
[connect SasRemote]
Access=ReadOnly
Connect="Data Source=SasLocal"
Other RDS/IIS Issues
IIS uses IWAM-servername as proxy
(Launch IIS Process Account)
WQExxxxx.TRC diagnostics
Recommendations (SAS)
ODBC – Start Proc OdbcServ manually
 OLE DB – give IWAM-servername read
permission to PDB

MDAC Interfaces
ADO Objects
Properties
Connection
RecordSet
Command
Errors
Fields
Parameters
RecordSet Object
Set rs = CreateObject(“ADODB.Recordset”)
Methods
 rs.Open, rs.Close
 rs.MoveFirst, Rs.MoveNext
Properties
 rs.BOF, rs.EOF
 rs.Connection
Field Object
Fields collection contains Field objects,
one for each column in the table
rs.Fields.Count – number of columns
rs.Fields(x).Value - value
rs.Fields(x).Name – label
Warning
Code to Follow
ADO Script - WSH
Set rs = CreateObject(“ADODB.Recordset”)
ConnectionString = “DSN=SasLocal”
Sql = “Select PCTCPUTM from PDB.System”
rs.Open Sql, ConnectionString
While Not rs.EOF
Msg = Msg + CStr(rs.Fields(0).Value) + vbCrLf
rs.MoveNext
end
MsgBox Msg
D:\Cmg2000\AdoWsh.vbs
RDS Script - WSH
Set rs = CreateObject("ADODB.Recordset")
ConnectionString = “Provider=MS Remote;” + _
“Remote Server=http://ServerName;” + _
“Handler=MSDFMAP.Handler;”+ _
“Data Source=SasRemote”
Sql = “Select PCTCPUTM from PDB.System“
rs.Open Sql, ConnectionString
While Not rs.EOF
Msg = Msg + CStr(rs.Fields(0).Value) + vbCrLf
rs.MoveNext
end
MsgBox Msg
D:\Cmg2000\RdsWsh.vbs
ADO Script – Client-Side
<HTML>
<SCRIPT LANGUAGE=vbscript>
Set rs = CreateObject("ADODB.Recordset")
ConnectionString = "DSN=SasLocal“
Sql = “Select PCTCPUTM from PDB.System“
rs.Open Sql, ConnectionString
While Not rs.EOF
Msg = Msg + rs.Fields(0).Value + vbCrLf
rs.MoveNext
end
MsgBox Msg
</SCRIPT>
</HTML>
D:\Cmg2000\AdoClient.htm
ADO Script – Server-Side
<HTML>
<%
Set rs = CreateObject("ADODB.Recordset")
ConnectionString = "Data Source=SasLocal“
Sql = "Select PCTCPUTM from PDB.System“
rs.Open Sql, ConnectionString
Response.Write("<SELECT>" + vbCrLf)
While Not rs.EOF
str = "<OPTION>" + CStr(rs.Fields(0).Value) + vbCrLf
Response.Write(str)
rs.MoveNext
Wend
http://.../AdoServer.asp
%>
</OPTION></SELECT> </HTML>
ADO Script Options
Access Type
Script Method
Local Machine WSH or
Client-Side
Client / Server WSH or
Client-Side
Intranet
Client-Side
UNC Accessible
Internet
Server-Side
Access Method
Any
RDS or
OLE DB
RDS or
OLE DB
Any
ActiveX Objects
ActiveX Objects
COM subroutines
Visible and not
Microsoft and not
ProgId (OWC.Spreadsheet) or ClassId
“0002E510-0000-0000-C000-000000000046”
Create them
 Read/write properties
 Call methods
 Handle events

Member:
Submit
Folder:
System
d:\PDB\
Member System in Library d:\PDB\ Beginning Thursday, November 02, 2000
A
1
2
3
4
5
6
7
8
9
10
11
12
13
14
B
C
D
E
F
G
H
ActiveX – No Script
<HTML>
<OBJECT HEIGHT=500 WIDTH=100%
classid=clsid:C4D2D8E0-D1DD-11CE-940F-008029004347>
</OBJECT>
</HTML>
D:\Cmg2000\NoScript.htm
ActiveX – Data Bound
<HTML>
<OBJECT id=grid height=600 width=100%
classid=clsid:0ECD9B64-23AA-11D0-B351-00A0C9055D8E>
</OBJECT>
<SCRIPT LANGUAGE=vbscript>
Set rs = CreateObject("ADODB.Recordset")
Set cn = CreateObject("ADODB.Connection")
cn.Open "Provider=sas.LocalProvider.1;Data Source=d:\PDB\“
rs.Open “System", cn, , , 512 'adCmdTableDirect
set grid.DataSource = rs
</SCRIPT>
</HTML>
D:\Cmg2000\DataBound.htm
Microsoft Office Web
Components
Office Web Components
Ship with Office 2000
c:\Program Files\MicrosoftOffice\Office\MsOwc.dll
Spreadsheet


clsid:0002E510-0000-0000-C000-000000000046
ProgId = OWC.Spreadsheet
Chart


clsid:0002E500-0000-0000-C000-000000000046
ProgId = OWC.Chart
Pivot Table - OWC.PivotTable
DSC - OWC.DataSourceControl
OWC Spreadsheet
Resembles Excel spreadsheet
Visible or not
Contains one or more Worksheets
Worksheets contain cells
spreadsheet.worksheet.Cell(row,col)
 Single Worksheet then
spreadsheet.Cell(row,col)

OWC Spreadsheet Range
Identifies a rectangular group of cells
Used by methods that operate on
groups of cells
set Range = spreadsheet.Columns(x)
 object.Range(TopLeft : BottomRight)

OWC Spreadsheet API
ss.ActiveSheet.UsedRange.Clear
ss.ViewableRange =
ss.ActiveSheet.UsedRange.Address
ExcelStyleCellReference =
ss.Columns(Columns).Address
Set c = ss.Constants
Constants available at run-time
OWC Spreadsheet Formatting
ss.TitleBar.Caption = “string"
ss.Columns.ColumnWidth = 100
ss.Rows(x).Font.Bold = True
range.NumberFormat = "hh:mm“
ss.Columns(x).Hidden = True
ss.DisplayRowHeaders = False
ss.ScreenUpdating = False
Populate Spreadsheet
from RecordSet
Row = 1
While Not rs.EOF
Col = 1
While Col <= rs.Fields.Count
ss.ActiveSheet.Cells(Row, Col).Value =
rs.Fields(Col - 1).Value
Col = Col + 1
Wend
Row = Row + 1
rs.MoveNext
Wend
Populate Faster
Set field = rs.Fields
Set cell = ss.ActiveSheet.Cells
Row = 1
While Not rs.EOF
Col = 1
While Col <= rs.Fields.Count
cell(Row, Col).Value = field(Col - 1).Name
Col = Col + 1
Wend
Row = Row + 1
rs.MoveNext
Wend
OWC Chart
General purpose charting functions

45 chart types
ChartSpace - one or more charts
Careful with terminology
Series – points to be plotted as a group
 Value axis is vertical (y)
 Category axis is horizontal (x)

Chart Types
Column
Bar
Line
Pie
Scatter
Bubble
Area
Doughnut
Radar
Stock
Polar
Clustered
Stacked
Stacked100
Markers
Smooth
Exploded
Filled
HLC
OHLC
Combo
OWC Chart API
Set cs.DataSource = ss.Object
Chart data will come from spreadsheet
Set chart = cs.Charts.Add()
Add a chart to the chart space
cs.Clear
Clear all charts in the chart space
Set c = cs.Constants
Constants available at run-time
OWC Chart - Formatting
chart.Type = c.chChartTypeLine
Basic chart type – 46 varieties
chart.HasLegend = True
Chart will have legend
chart.HasTitle = True
Chart will have title
chart.Title.Caption = “C1”
Take title from cell C1 in spreadsheet
OWC Chart - SetData
Method used to identify series
1) Dimension or attribute of chart
2) Data source
3) Data Reference
chart.SetData c.chDimSeriesNames, 0, “A2“
Variable name that will appear in legend
chart.SetData c.chDimValues, 0, “C2:C4“
Cells containing value (y) axis data points
chart.SetData c.chDimCategories, 0, “B2:B4”
Cells with category (x) axis data points
OWC Chart Series
30
25
20
15
InetInfo
10
5
0
1:00
2:00
3:00
OWC Chart Series
1
A
Process
B
StarTime
C
PctCpuTm
2
InetInfo
01:00
30
3
InetInfo
02:00
20
4
InetInfo
03:00
25
Chart from Spreadsheet
set cs.DataSource = ss.Object
Set chart = cs.Charts.Add()
Set c = cs.Constants
chart.Type = c. chChartTypeColumnClustered
chart.HasLegend = True
chart.SetData c.chDimSeriesNames, 0, “A2“
chart.SetData c.chDimValues, 0, “C2:C4“
chart.SetData c.chDimCategories, 0, “B2:B4"
References
Programming
Microsoft Office
2000 Web
Components
By Dave Stearns
Microsoft Press
References
SAS ODBC User’s Guide and Programmer’s
Reference, SAS Institute
Microsoft Developer Network Library (MSDN)
Microsoft TechNet
MSNews.Microsoft.com newsgroups


Public.Data.ADO.RDS
Public.Office.Developer.Web.Components
http://www.Able-Consulting.com/
SasToOwc.htm – Sample App
D:\Cmg2000\SasToOwc.htm
Issues
Much of this is Windows-only technolgy
Un-terminated character strings when
accessing SAS ODBC data sources
through RDS
Summary