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

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

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

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

6. Scripting

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

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

9. MDAC Microsoft Data Access Components

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

11. MDAC

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

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

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

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

16. RDS

17. MDAC Configuration Issues

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

19. SAS TCP Port

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

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

22. MSDFMAP.ini

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

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

25. MDAC Interfaces

26. ADO Objects

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

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

29. Warning Code to Follow

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

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

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

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

34. ADO Script Options

35. ActiveX Objects

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

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

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

40. Microsoft Office Web Components

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

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

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

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

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

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

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

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

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

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