Arnim Nay EDV-Beratung

Top

MyTables

In diesem Beispiel wird per VB-Skript (unter Windows) die Verbindung zu einer ORACLE Datenbank aufgebaut und alle Tabellen des Users in formatierter Form im Browser ausgegeben.

Skript: MyTables.vbs

Dim objIE, objDoc

'Open a new browser session
Set objIE = WScript.CreateObject("InternetExplorer.Application")

'The connection string to SCOTT on ORCL (see tnsnames.ora)
sConnectionString = "Provider=MSDAORA;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) _
(Host=theserver.de)(Port=1553)))(CONNECT_DATA=(SID=ORCL))); _
User ID=SCOTT;Password=TIGER"
Set connection = WScript.CreateObject("ADODB.Connection")
connection.ConnectionString = sConnectionString
connection.Open
IF Err.Number <> 0 THEN
WScript.Echo Err.Number
WScript.Echo Err.Description
WScript.Quit
ELSE
Set rs = CreateObject("ADODB.recordset")
strSQL = "SELECT table_name FROM user_tables ORDER BY 1"

rs.open strSQL, connection, 3, 3
rs.MoveFirst

'Set Browser Parameters
objIE.AddressBar = False
objIE.FullScreen = True
objIE.MenuBar = False
objIE.Resizable = True
objIE.Silent = True
objIE.StatusBar = False
objIE.TheaterMode = False
objIE.ToolBar = False

objIE.Width = 300
objIE.Height = 600
objIE.Visible = 100

objIE.Navigate "about:blank"
objIE.Visible = True

Set objDoc = objIE.Document

'Display Output Text (HTML) with inline stylesheet
objDoc.Write "<html><head>"
objDoc.Write "<title>User tables (SCOTT)</title>"
objDoc.Write "<style type=""text/css""> "
objDoc.Write "table {border: 0px; empty-cells: show; background-color: #000000} "
objDoc.Write "tr {border: 0px;} "
objDoc.Write "th {text-align:left; padding:5px} "
objDoc.Write "td {border: 0px; padding:5px} "
objDoc.Write ".theader {background-color: #CFCFCF; text-align:left; padding:5px} "
objDoc.Write ".tdata {background-color: #FFFFCC; text-align:left; padding:5px} "
objDoc.Write "</style> "
objDoc.Write "</head>"
objDoc.Write "<body>"
objDoc.Write "<table>"
objDoc.Write "<tr class=""theader"">"
objDoc.Write "<th align=left>Table name</th>"
objDoc.Write "</tr> "
'Set table row data element for every selected data element
WHILE NOT rs.EOF
objDoc.Write "<tr class=""tdata""><td>" & rs("table_name") & "</td></tr>"
rs.MoveNext
WEND
objDoc.Write "</table></body></html>"
END IF

Version 1.0
04.09.2012