SQLServer レコードの取得
ＳＱＬの結果を ＨＴＭＬに出力する
接続文字列
lesson004.con
DRIVER={SQL Server};SERVER=xxxxxx;DataBase=adventureworksdw;UID=yyyyyy;PWD=zzzzzz;
ＨＴＭＬ出力用スクリプト
lesson003.vbs
Option Explicit Private fs Set fs = CreateObject("Scripting.FileSystemObject") '接続文字列 読み込み Private tsCon Set tsCon = fs.OpenTextFile(WScript.Arguments(0)) Private sCon sCon = "" If Not tsCon.AtEndOfStream Then sCon = tsCon.ReadLine End If tsCon.Close Set tsCon = Nothing '接続 Private con Set con = CreateObject("ADODB.Connection") With con .Provider = "SQLOLEDB" .ConnectionString = sCon .Open End With 'SQL 読み込み Private tsSql Set tsSql = fs.OpenTextFile(WScript.Arguments(1)) Private sSql sSql = "" Do Until tsSql.AtEndOfStream sSql = sSql & tsSql.ReadLine & vbNewLine Loop tsSql.Close Set tsSql = Nothing '結果を HTML に保存 Private tsHtml Set tsHtml = fs.OpenTextFile(WScript.Arguments(2), 2, True) '2 = ForWriting Dim rs Set rs = CreateObject("ADODB.Recordset") With rs .ActiveConnection = con .CursorType = 0 'adOpenForwardOnly .LockType = 1 'adLockReadOnly .Source = sSql .Open End With If Not rs.EOF Then tsHtml.WriteLine "<!DOCTYPE HTML PUBLIC ""-//W3C//DTD HTML 4.01 Transitional//EN"">" tsHtml.WriteLine "<html>" tsHtml.WriteLine "<body>" tsHtml.WriteLine " <table border>" tsHtml.WriteLine " <tr>" tsHtml.Write " " Dim i For i = 0 To rs.Fields.Count - 1 tsHtml.Write "<th>" & rs.Fields(i).Name & "</th>" Next tsHtml.WriteLine "" tsHtml.WriteLine " </tr>" Do Until rs.EOF tsHtml.WriteLine " <tr>" tsHtml.Write " " For i = 0 To rs.Fields.Count - 1 tsHtml.Write "<td>" & rs.Fields(i).Value & "</td>" Next tsHtml.WriteLine "" tsHtml.WriteLine " </tr>" rs.MoveNext Loop tsHtml.WriteLine " </table>" tsHtml.WriteLine "</body>" tsHtml.WriteLine "</html>" End If rs.Close tsHtml.Close Set tsHtml = Nothing Set fs = Nothing '切断 con.Close Set con = Nothing
実行形式
C:\>cscript c:\study\vbscript\chapter006\lesson003.vbs c:\study\vbscript\chapte
r006\lesson004.con c:\study\vbscript\chapter006\lesson005.sql c:\study\vbscript\
chapter006\lesson006.html //nologo
出力結果
lesson006.html
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> <html> <body> <table border> <tr> <th>AccountKey</th><th>ParentAccountKey</th><th>AccountCodeAlternateKey</th><th>ParentAccountCodeAlternateKey</th><th>AccountDescription</th><th>AccountType</th><th>Operator</th><th>CustomMembers</th><th>ValueType</th><th>CustomMemberOptions</th> </tr> <tr> <td>1</td><td></td><td>1</td><td></td><td>Balance Sheet</td><td></td><td>~</td><td></td><td>Currency</td><td></td> </tr> <tr> <td>2</td><td>1</td><td>10</td><td>1</td><td>Assets</td><td>Assets</td><td>+</td><td></td><td>Currency</td><td></td> </tr> <tr> <td>3</td><td>2</td><td>110</td><td>10</td><td>Current Assets</td><td>Assets</td><td>+</td><td></td><td>Currency</td><td></td> </tr> <tr> <td>4</td><td>3</td><td>1110</td><td>110</td><td>Cash</td><td>Assets</td><td>+</td><td></td><td>Currency</td><td></td> </tr> <tr> <td>5</td><td>3</td><td>1120</td><td>110</td><td>Receivables</td><td>Assets</td><td>+</td><td></td><td>Currency</td><td></td> </tr> <tr> <td>6</td><td>5</td><td>1130</td><td>1120</td><td>Trade Receivables</td><td>Assets</td><td>+</td><td></td><td>Currency</td><td></td> </tr> <tr> <td>7</td><td>5</td><td>1140</td><td>1120</td><td>Other Receivables</td><td>Assets</td><td>+</td><td></td><td>Currency</td><td></td> </tr> <tr> <td>8</td><td>3</td><td>1150</td><td>110</td><td>Allowance for Bad Debt</td><td>Assets</td><td>+</td><td></td><td>Currency</td><td></td> </tr> <tr> <td>9</td><td>3</td><td>1160</td><td>110</td><td>Inventory</td><td>Assets</td><td>+</td><td></td><td>Currency</td><td></td> </tr> <tr> <td>10</td><td>9</td><td>1162</td><td>1160</td><td>Raw Materials</td><td>Assets</td><td>+</td><td></td><td>Currency</td><td></td> </tr> </table> </body> </html>
出力イメージ
lesson006.html
| AccountKey | ParentAccountKey | AccountCodeAlternateKey | ParentAccountCodeAlternateKey | AccountDescription | AccountType | Operator | CustomMembers | ValueType | CustomMemberOptions |
|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | Balance Sheet | ~ | Currency | |||||
| 2 | 1 | 10 | 1 | Assets | Assets | + | Currency | ||
| 3 | 2 | 110 | 10 | Current Assets | Assets | + | Currency | ||
| 4 | 3 | 1110 | 110 | Cash | Assets | + | Currency | ||
| 5 | 3 | 1120 | 110 | Receivables | Assets | + | Currency | ||
| 6 | 5 | 1130 | 1120 | Trade Receivables | Assets | + | Currency | ||
| 7 | 5 | 1140 | 1120 | Other Receivables | Assets | + | Currency | ||
| 8 | 3 | 1150 | 110 | Allowance for Bad Debt | Assets | + | Currency | ||
| 9 | 3 | 1160 | 110 | Inventory | Assets | + | Currency | ||
| 10 | 9 | 1162 | 1160 | Raw Materials | Assets | + | Currency |
