VBSでCRUD

概要

VBSでデータの永続化やってみる。

テーブル生成

create.vbs

Dim cd
Dim sh
Dim db
Dim sql
Set sh = CreateObject("WScript.Shell")
cd = sh.CurrentDirectory
Set db = CreateObject("ADODB.Connection")
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cd & "\test.mdb;"
sql = "CREATE TABLE blog_data (id integer IDENTITY(1,1) NOT NULL PRIMARY KEY, title text NOT NULL, body text NOT NULL, modified timestamp DEFAULT NOW() NOT NULL)"
Set rs = db.Execute(sql)
msgbox "ok"

Create（生成）

add.vbs

Dim cd
Dim sh
Dim db
Dim sql
Set sh = CreateObject("WScript.Shell")
cd = sh.CurrentDirectory
Set db = CreateObject("ADODB.Connection")
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cd & "\test.mdb;"
sql = "INSERT INTO blog_data(title,body) VALUES('test', 'test')"
Set rs = db.Execute(sql)
msgbox "ok"

Read（読み取り）

view.vbs

Dim cd
Dim sh
Dim db
Dim sql
Set sh = CreateObject("WScript.Shell")
cd = sh.CurrentDirectory
Set db = CreateObject("ADODB.Connection")
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cd & "\test.mdb;"
sql = "SELECT * FROM blog_data"
Set rs = db.Execute(sql)
Do Until rs.EOF
    msgbox "id:" & rs(0) & " title:" & rs(1) & " body:" & rs(2) & " mod:" & rs(3)
    rs.MoveNext
Loop

Update（更新）

edit.vbs

Dim cd
Dim sh
Dim db
Dim sql
Set sh = CreateObject("WScript.Shell")
cd = sh.CurrentDirectory
Set db = CreateObject("ADODB.Connection")
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cd & "\test.mdb;"
sql = "UPDATE blog_data SET title='test2', body='test2' WHERE id=2"
Set rs = db.Execute(sql)
msgbox "ok"

Delete（削除）

del.vbs

Dim cd
Dim sh
Dim db
Dim sql
Set sh = CreateObject("WScript.Shell")
cd = sh.CurrentDirectory
Set db = CreateObject("ADODB.Connection")
db.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & cd & "\test.mdb;"
sql = "DELETE FROM blog_data WHERE id=1"
Set rs = db.Execute(sql)
msgbox "ok"