1 / 60

Introduction

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

Gabriel
Download Presentation

Introduction

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction • Tutorial • How to publish the contents of a database using features available in Microsoft Windows • Local machine • Legacy client/server • Intranet/Internet

  2. 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.

  3. 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

  4. Agenda • Scripting • Data Access Methods • Overview • Configuration Issues • Sample Scripts • ActiveX Controls • Overview • Sample Scripts • Office Web Components

  5. Scripting

  6. Scripting • Script is the glue • Vbscript and Jscript • Legacy Windows WSH – Windows Scripting Host • Browser Client-side and Server-side

  7. SAS M D A C A C T I V E X Browser SQL Server Script Window Access Scripting

  8. MDAC Microsoft Data Access Components

  9. MDAC • Universal Data Access • ODBC – Open Database Connectivity • OLE DB – OLE Database • ADO – Active Data Objects • RDS – Remote Data Services • ADO.Net - ???

  10. MDAC

  11. ODBC • Circa 1990 • Ubiquitous drivers • Not an Object-Oriented API • Relational bias

  12. 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

  13. ADO • (currently) Highest level interface • Simplified object structure • Relies on OLE DB

  14. RDS • Makes ADO data sources accessible across a network • HTTP, HTTPS, or DCOM • Uses Microsoft Internet Information Server as conduit

  15. RDS

  16. MDAC Configuration Issues

  17. ODBC Data Sources • Control Panel Administrative Tools Data Sources (ODBC) • SAS ODBC Drivers • System Data Source • TCP Portc:\WINNT\system32\drivers\etc\services

  18. SAS TCP Port

  19. 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

  20. MSDFMAP.ini • RDS Security • C:\WinNT\System\MSDFMAP.ini • Create token to identify local data source for remote access • Determine permitted access

  21. MSDFMAP.ini

  22. 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"

  23. 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

  24. MDAC Interfaces

  25. ADO Objects

  26. RecordSet Object • Set rs = CreateObject(“ADODB.Recordset”) • Methods • rs.Open, rs.Close • rs.MoveFirst, Rs.MoveNext • Properties • rs.BOF, rs.EOF • rs.Connection

  27. 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

  28. Warning Code to Follow

  29. 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

  30. 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

  31. 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

  32. 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>

  33. ADO Script Options

  34. ActiveX Objects

  35. 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

  36. ActiveX – No Script <HTML><OBJECT HEIGHT=500 WIDTH=100%classid=clsid:C4D2D8E0-D1DD-11CE-940F-008029004347></OBJECT></HTML> D:\Cmg2000\NoScript.htm

  37. 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

  38. Microsoft Office Web Components

  39. 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

  40. 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)

  41. 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)

  42. 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

  43. 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

  44. 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

  45. 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

  46. 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)

  47. ColumnBar Line Pie Scatter Bubble Area Doughnut RadarStockPolar ClusteredStacked Stacked100MarkersSmooth ExplodedFilledHLC OHLCCombo Chart Types

  48. 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

More Related