Here's an example script that uses ADO COM to do some Access database manipulations. The script by itself does not do anything particularly useful, but it does demonstrate several basic operations. Before running the script you may need to adjust the three filename values at the top to match your environment. The script requires
CoHelper.
Code:
/*
Sample script using ADO COM that will create an Access database, create a
table in the database, populate the table, read binary data from a
file into a database field, and then write a file with binary data from
a database field. Error checking has been ommitted.
*/
#NoEnv
#SingleInstance
#Include CoHelper.ahk
CoInitialize()
;Change these values for your specific environment. Only file required to
;exist at the start is sFileIn. The others are created if they don't exist.
sFileIn = c:\windows\system32\setup.bmp ;can use any file as input
sFileOut = %A_Temp%\test_out.bmp ;output file, will be overwritten if exists
sDbFile = %A_Temp%\test_db.mdb ;database to be created, deleted if exists at start
;Some useful constants
Jet3x:=4, Jet4x:=5
adOpenKeyset:=1, adLockOptimistic:=3, adTypeBinary:=1
adSaveCreateOverWrite:=2, adInteger:=3, adKeyPrimary:=1
VT_I4:=0x3, VT_BSTR:=0x8, VT_DISPATCH:=0x9, VT_UI1:=0x11, VT_ARRAY:=0x2000
;Delete the database if it exists so we start fresh each time
IfExist, %sDbFile%
FileDelete, %sDbFile%
;::::::::::::::::::::::::::::::::::::
; Create a new database
;::::::::::::::::::::::::::::::::::::
;Define the connection string
sConn := "Provider=Microsoft.Jet.OLEDB.4.0; Jet OLEDB:Engine Type=" . Jet4x . "; Data Source=" . sDbFile . ";"
;Create a new database using ADOX.Catalog
pcat := ActiveXObject("ADOX.Catalog")
pDbase := Invoke(pcat, "Create", sConn)
Release(pDbase)
Release(pcat)
;Create a new Connection object and open a connection to the database
pcn := ActiveXObject("ADODB.CONNECTION")
Invoke(pcn, "Open", sConn)
;Create a table in the new database
sCreate := "CREATE TABLE Photos(PhotoId INTEGER NOT NULL
,Title TEXT(40) NOT NULL
,Location TEXT(40)
,Photo_1 OLEOBJECT)"
Invoke(pcn, "Execute", sCreate)
;Store some values into the table
Invoke(pcn, "Execute", "INSERT INTO Photos VALUES (1,'Grand Canyon', 'Arizona','')")
;At this point if we were finished we would close the connection
;and clean things up, but instead we will reuse the connection
;to open the recordset below.
;::::::::::::::::::::::::::::::::::::::::::::::::::
; Read binary data from a file into the database
;::::::::::::::::::::::::::::::::::::::::::::::::::
;Create a recordset object and open it with a query the database
prs := ActiveXObject("ADODB.RECORDSET")
Invoke_(prs, "Open", VT_BSTR,"SELECT * FROM Photos WHERE PhotoID=1;"
, VT_DISPATCH,pcn
, VT_I4,adOpenKeyset
, VT_I4,adLockOptimistic)
;Create a binary stream object to hold the data
pmstream := ActiveXObject("ADODB.STREAM")
Invoke(pmstream, "Type=", adTypeBinary)
Invoke(pmstream, "Open")
;Read the disk file into the stream object
Invoke(pmstream, "LoadFromFile", sFileIn)
pSafearray:=Invoke(pmstream, "Read")
;Set the value of the Photo_1 field to the binary data
pFields := Invoke(prs, "Fields")
pField := Invoke(pFields, "Item", "Photo_1")
Invoke_(pField, "Value=", VT_ARRAY|VT_UI1,pSafeArray)
Invoke(prs, "Update")
;Cleanup, conservative - release every IDispatch
DllCall("oleaut32\SafeArrayDestroy", "Uint", pSafeArray)
Release(pField)
Release(pFields)
Invoke(pcn, "Close")
Invoke(prs, "Close")
Release(pmstream)
Release(prs)
Release(pcn)
;::::::::::::::::::::::::::::::::::::::::::::::::::
; Write binary data from the database into a file
;::::::::::::::::::::::::::::::::::::::::::::::::::
;We could have reused the connection, recordset, and stream objects
;from above, but for this example we will create new objects.
;Create and open a connection
pcn := ActiveXObject("ADODB.CONNECTION")
Invoke(pcn, "Open", sConn)
;Create and open the recordset with a query
prs := ActiveXObject("ADODB.RECORDSET")
Invoke_(prs, "Open", VT_BSTR,"SELECT * FROM Photos WHERE PhotoID=1;"
, VT_DISPATCH,pcn
, VT_I4,adOpenKeyset
, VT_I4,adLockOptimistic)
;Create and open a binary stream
pmstream := ActiveXObject("ADODB.STREAM")
Invoke(pmstream, "Type=", adTypeBinary)
Invoke(pmstream, "Open")
;Write the value of the Photo_1 field to the stream object and then save it to a file with overwrite
pFields := Invoke(prs, "Fields")
pField := Invoke(pFields, "Item", "Photo_1")
pSafearray:=Invoke(pField, "Value")
Invoke_(pmstream, "Write", VT_ARRAY|VT_UI1,pSafearray)
Invoke(pmstream, "SavetoFile", sFileOut, adSaveCreateOverWrite)
;Cleanup, conservative - release every IDispatch
DllCall("oleaut32\SafeArrayDestroy", "Uint", pSafeArray)
Release(pField)
Release(pFields)
Invoke(pcn, "Close")
Invoke(prs, "Close")
Release(pmstream)
Release(prs)
Release(pcn)
CoUninitialize()
Msgbox, Done!
ExitApp
Login
Register
FAQ
Search
