ado.vb
option explicit
global dbg_ as dbg
sub main(connectionString as variant) ' {
set dbg_ = new dbg
dim fileWriter As New dbgFileWriter
fileWriter.init environ$("TEMP") & "\" & format(now, "yyyy-mm-dd_hhnn") & ".txt", flushImmediately := true
dbg_.init fileWriter
dim conn as new adodb.connection
debug.print("connection string = " & connectionString)
conn.open connectionString
createTestTable conn
insertData conn
selectData conn
debug.print "finished."
end sub ' }
sub createTestTable(conn as adodb.connection) ' {
on error resume next
conn.execute "drop table vba_ado_test"
on error goto 0
conn.execute _
"create table vba_ado_test ( " & _
" num number ( 2) primary key, " & _
" txt varchar2(20) not null, " & _
" is_prime char(1) not null check (is_prime in ('y', 'n'))," & _
" dat date null " & _
")"
end sub ' }
sub insertData(conn as adodb.connection) ' {
dim insStmt as new adoStatement
insStmt.init conn
insStmt.sql "insert into vba_ado_test values(:num, :txt, :is_prime, :dat)"
insStmt.defineParameters _
adInteger, _
adVarchar, 20, _
adVarchar, 1, _
adDate
conn.beginTrans
insStmt.exec 1, "one" , "n", dateSerial(2001, 1, 1)
insStmt.exec 2, "two" , "y", dateSerial(2002, 2, 2)
insStmt.exec 3, "three", "y", dateSerial(2003, 3, 3)
insStmt.exec 4, "four" , "n", null
insStmt.exec 5, "five" , "y", null
insStmt.exec 6, "six" , "n", dateSerial(2006, 6, 6)
insStmt.exec 7, "seven", "y", dateSerial(2007, 7, 7)
insStmt.exec 8, "eight", "n", null
insStmt.exec 9, "nine" , "n", dateSerial(2009, 9, 9)
insStmt.exec 10, "ten" , "n", dateSerial(2010, 10, 10)
conn.commitTrans
end sub ' }
sub selectData(conn as adodb.connection) ' {
dim selStmt as new adoStatement
selStmt.init conn
selStmt.sql "select * from vba_ado_test where is_prime = :is_prime order by num"
selStmt.defineParameters adVarchar, 1
dim rowNum as long
selStmt.exec "y"
while selStmt.record ' {
rowNum = rowNum + 1
if rowNum = 1 then ' {
if selStmt("NUM" ) <> 2 then msgBox "failure 1.num"
if selStmt("TXT" ) <> "two" then msgBox "failure 1.txt"
if selStmt("IS_PRIME") <> "y" then msgBox "failure 1.is_prime"
if selStmt("DAT" ) <> dateSerial(2002, 2, 2) then msgBox "failure 1.dat"
end if ' }
if rowNum = 2 then ' {
if selStmt("NUM" ) <> 3 then msgBox "failure 2.num"
if selStmt("TXT" ) <> "three" then msgBox "failure 2.txt"
if selStmt("IS_PRIME") <> "y" then msgBox "failure 2.is_prime"
if selStmt("DAT" ) <> dateSerial(2003, 3, 3) then msgBox "failure 2.dat"
end if ' }
if rowNum = 3 then ' {
if selStmt("NUM" ) <> 5 then msgBox "failure 3.num"
if selStmt("TXT" ) <> "five" then msgBox "failure 3.txt"
if selStmt("IS_PRIME") <> "y" then msgBox "failure 3.is_prime"
if not isNull(selStmt("DAT" )) then msgBox "failure 3.dat"
end if ' }
if rowNum = 4 then ' {
if selStmt("NUM" ) <> 7 then msgBox "failure 4.num"
if selStmt("TXT" ) <> "seven" then msgBox "failure 4.txt"
if selStmt("IS_PRIME") <> "y" then msgBox "failure 4.is_prime"
if selStmt("DAT" ) <> dateSerial(2007, 7, 7) then msgBox "failure 4.dat"
end if ' }
if rowNum > 4 then msgBox "failure: rowNum = " & rowNum
wend ' }
selStmt.exec "n"
while selStmt.record ' {
rowNum = rowNum + 1
if rowNum = 5 then ' {
if selStmt("NUM" ) <> 1 then msgBox "failure 5.num"
if selStmt("TXT" ) <> "one" then msgBox "failure 5.txt"
if selStmt("IS_PRIME") <> "n" then msgBox "failure 5.is_prime"
if selStmt("DAT" ) <> dateSerial(2001, 1, 1) then msgBox "failure 5.dat"
end if ' }
if rowNum = 6 then ' {
if selStmt("NUM" ) <> 4 then msgBox "failure 7.num"
if selStmt("TXT" ) <> "four" then msgBox "failure 7.txt"
if selStmt("IS_PRIME") <> "n" then msgBox "failure 7.is_prime"
if not isNull(selStmt("DAT" )) then msgBox "failure 7.dat"
end if ' }
if rownum > 10 then msgBox "failure: rowNum = " & rowNum
wend ' }
if rowNum <> 10 then msgBox "failure: rowNum = " & rowNum
end sub ' }
ado.wsf {
<job>
<script language="VBScript" src="VBS-MS-Office-App-Creator\create-MS-Office-app.vbs" />
<script language="VBScript">
option explicit
if wscript.arguments.unnamed.count < 1 then
wscript.echo("Expected: OLE DB connection string")
wscript.quit(-1)
end if
dim connectionString
connectionString = wscript.arguments.unnamed(0)
wscript.echo connectionString
' wscript.quit(-1)
dim app
dim xls
set xls = createOfficeApp("excel", currentDir() & "created.xlsm")
if xls is nothing then ' {
wscript.echo("Could not create excel worksheet.")
wscript.quit(-1)
end if ' }
set app = xls.application
addReference app, "{B691E011-1797-432E-907A-4D8C69339129}", 6, 1 ' ADODB
insertModule app, currentDir() & "../../../dbg/dbg.vb" , "dbg" , 2
insertModule app, currentDir() & "../../../dbg/dbgWriter.vb" , "dbgWriter" , 2
insertModule app, currentDir() & "../../../dbg/dbgFileWriter.vb" , "dbgFileWriter" , 2
insertModule app, currentDir() & "../../../Database\ADO\adoStatement.vb" , "adoStatement" , 2
insertModule app, currentDir() & "ado.vb" , "adoTest" , 1
app.run "main", connectionString
xls.save
wscript.echo("The end")
'
' Bring created Excel Workbook to the front:
'
createObject("WScript.Shell").appActivate(app.caption)
</script> </job>