slide1 l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Introduction PowerPoint Presentation
Download Presentation
Introduction

Loading in 2 Seconds...

play fullscreen
1 / 60

Introduction - PowerPoint PPT Presentation


  • 433 Views
  • Uploaded on

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

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Introduction' - Gabriel


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
introduction
Introduction
  • Tutorial
  • How to publish the contents of a database using features available in Microsoft Windows
  • Local machine
  • Legacy client/server
  • Intranet/Internet
introduction3
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
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
Agenda
  • Scripting
  • Data Access Methods
    • Overview
    • Configuration Issues
    • Sample Scripts
  • ActiveX Controls
    • Overview
    • Sample Scripts
    • Office Web Components
scripting7
Scripting
  • Script is the glue
  • Vbscript and Jscript
  • Legacy Windows WSH – Windows Scripting Host
  • Browser Client-side and Server-side
scripting8

SAS

M

D

A

C

A

C

T

I

V

E

X

Browser

SQL

Server

Script

Window

Access

Scripting
slide9

MDAC

Microsoft Data Access Components

slide10
MDAC
  • Universal Data Access
  • ODBC – Open Database Connectivity
  • OLE DB – OLE Database
  • ADO – Active Data Objects
  • RDS – Remote Data Services
  • ADO.Net - ???
slide12
ODBC
  • Circa 1990
  • Ubiquitous drivers
  • Not an Object-Oriented API
  • Relational bias
ole db
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
slide14
ADO
  • (currently) Highest level interface
  • Simplified object structure
  • Relies on OLE DB
slide15
RDS
  • Makes ADO data sources accessible across a network
  • HTTP, HTTPS, or DCOM
  • Uses Microsoft Internet Information Server as conduit
odbc data sources
ODBC Data Sources
  • Control Panel Administrative Tools Data Sources (ODBC)
  • SAS ODBC Drivers
    • System Data Source
    • TCP Portc:\WINNT\system32\drivers\etc\services
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

SAS ODBC vs. OLE DB
msdfmap ini
MSDFMAP.ini
  • RDS Security
  • C:\WinNT\System\MSDFMAP.ini
  • Create token to identify local data source for remote access
  • Determine permitted access
msdfmap ini23
MSDFMAP.ini

[connect default]Access=NoAccess[sql default];Sql=" “ ' <---comment this line out[connect PDB]Access=ReadOnlyConnect="Provider=sas.LocalProvider.1; Data Source=d:\PDB\Detail\“[connect SasRemote]Access=ReadOnlyConnect="Data Source=SasLocal"

other rds iis issues
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
recordset object
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
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

Warning

Code to Follow

ado script wsh
ADO Script - WSH

Set rs = CreateObject(“ADODB.Recordset”)ConnectionString = “DSN=SasLocal”Sql = “Select PCTCPUTM from PDB.System”rs.Open Sql, ConnectionStringWhile Not rs.EOF Msg = Msg + CStr(rs.Fields(0).Value) + vbCrLf rs.MoveNext endMsgBox MsgD:\Cmg2000\AdoWsh.vbs

rds script wsh
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, ConnectionStringWhile Not rs.EOF Msg = Msg + CStr(rs.Fields(0).Value) + vbCrLf rs.MoveNext endMsgBox Msg D:\Cmg2000\RdsWsh.vbs

ado script client side
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, ConnectionStringWhile Not rs.EOF Msg = Msg + rs.Fields(0).Value + vbCrLf rs.MoveNext endMsgBox Msg</SCRIPT> </HTML> D:\Cmg2000\AdoClient.htm

ado script server side
ADO Script – Server-Side

<HTML><%Set rs = CreateObject("ADODB.Recordset")ConnectionString = "Data Source=SasLocal“Sql = "Select PCTCPUTM from PDB.System“rs.Open Sql, ConnectionStringResponse.Write("<SELECT>" + vbCrLf)While Not rs.EOFstr = "<OPTION>" + CStr(rs.Fields(0).Value) + vbCrLf Response.Write(str) rs.MoveNextWend http://.../AdoServer.asp%> </OPTION></SELECT> </HTML>

activex objects36
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
activex no script
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
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 'adCmdTableDirectset grid.DataSource = rs</SCRIPT></HTML> D:\Cmg2000\DataBound.htm

office web components
Office Web Components
  • Ship with Office 2000c:\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
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
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
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
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
Populate Spreadsheetfrom RecordSet

Row = 1While Not rs.EOF Col = 1 While Col <= rs.Fields.Countss.ActiveSheet.Cells(Row, Col).Value = rs.Fields(Col - 1).Value Col = Col + 1 Wend Row = Row + 1 rs.MoveNextWend

populate faster
Populate Faster

Set field = rs.FieldsSet cell = ss.ActiveSheet.CellsRow = 1While Not rs.EOF Col = 1 While Col <= rs.Fields.Countcell(Row, Col).Value = field(Col - 1).Name Col = Col + 1 Wend Row = Row + 1 rs.MoveNextWend

owc chart
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
ColumnBar Line Pie Scatter Bubble Area Doughnut RadarStockPolar

ClusteredStacked Stacked100MarkersSmooth ExplodedFilledHLC OHLCCombo

Chart Types
owc chart api
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
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
OWC Chart - SetData
  • Method used to identify series1) 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
chart from spreadsheet
Chart from Spreadsheet

set cs.DataSource = ss.ObjectSet chart = cs.Charts.Add()Set c = cs.Constantschart.Type = c. chChartTypeColumnClusteredchart.HasLegend = Truechart.SetData c.chDimSeriesNames, 0, “A2“chart.SetData c.chDimValues, 0, “C2:C4“chart.SetData c.chDimCategories, 0, “B2:B4"

references
References
  • Programming Microsoft Office 2000 Web Components
  • By Dave Stearns
  • Microsoft Press
references57
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
SasToOwc.htm – Sample App

D:\Cmg2000\SasToOwc.htm

issues
Issues
  • Much of this is Windows-only technolgy
  • Un-terminated character strings when accessing SAS ODBC data sources through RDS