ADODB Example

spudw2k · November 24, 2009

Man, seems like I've been waiting for days for the site to come back up so I could post this.
I'm putting together a Scan Engine tool that collects system info (via WMI, Reg, etc...). It will collectively store the data on either a SQL server, or local MDB. Here's a simple UDF I made to handle the ADODB stuff. I'll share the rest (Scan Engine Builder) when it's ready.

_ADODB.au3

#Region - Constants and Variables
Global $objConnection = ObjCreate("ADODB.Connection")
Global $objRecordSet = ObjCreate("ADODB.Recordset")
Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")
Global $adCurrentProvider = 0
Global $adCurrentDataSource = 0

Const $adOpenForwardOnly = 0
Const $adOpenKeyset = 1
Const $adOpenDynamic = 2
Const $adOpenStatic = 3
Const $adLockReadOnly = 1
Const $adLockPessimistic = 2
Const $adLockOptimistic = 3
Const $adLockBatchOptimistic = 4
Const $adProviderSQLOLEDB = "SQLOLEDB"
Const $adProviderMSJET4 = "Microsoft.Jet.OLEDB.4.0"
Const $adProviderMSJET12 = "Microsoft.ACE.OLEDB.12.0"
#EndRegion

#Region - Functions, Subs, Methods
Func _AddColumn($varTableName,$varColumnName,$varColumnType)
    If Not IsObj($objConnection) Then Return -1
    $strAddCol = "ALTER TABLE " & $varTableName & " ADD " & $varColumnName & " " & $varColumnType
    Return $objConnection.Execute($strAddCol)
EndFunc

Func _CloseConnection() 
    Return $objConnection.Close
EndFunc

Func _CloseRecordSet()
    Return $objRecordSet.Close
EndFunc

Func _CreateDatabase($varDatabaseName=0)
    If $adCurrentProvider = $adProviderMSJET4 Then
        $objADOXCatalog = ObjCreate("ADOX.Catalog")
        $strProvider = "Provider=" & $adCurrentProvider & ";Data Source=" & $adCurrentDataSource
        $objADOXCatalog.Create($strProvider)
        $objADOXCatalog = 0
        Return 0
    ElseIf $varDatabaseName Then
        $objConnection.Execute("CREATE DATABASE " & $varDatabaseName)
        Return $objConnection.Execute("USE " & $varDatabaseName)
    EndIf
    Return 0
EndFunc

Func _CreateTable($varTableName,$arrFields)
    If Not IsObj($objConnection) Then Return -1
    If Not IsString($varTableName) Then Return -2
    If Not IsArray($arrFields) Then Return -3
    $varFields = ""
    $varFieldCount = Ubound($arrFields)-1
    For $x = 0 to $varFieldCount
        $varFields &= $arrFields[$x]
        If $x < $varFieldCount Then $varFields &= " ,"
    Next
    Return $objConnection.Execute("CREATE TABLE " & $varTableName & "(" & $varFields & ")")
EndFunc

Func _DropColumn($varTableName,$varColumnName)
    If Not IsObj($objConnection) Then Return -1
    $strDropCol = "ALTER TABLE " & $varTableName & " DROP COLUMN " & $varColumnName
    Return $objConnection.Execute($strDropCol)
EndFunc

Func _DropDatabase($varDatabaseName=0)
    If Not IsObj($objConnection) Then Return -1
    If $adCurrentProvider = $adProviderMSJET4 Then
        _CloseConnection()
        If MsgBox(4+16,"Are you sure?","Are you sure you want to delete" & @CRLF & $adCurrentDataSource & " ?" & @CRLF & @CRLF & "This Cannot Be Undone!") = 6 Then 
            Return FileDelete($adCurrentDataSource)
        EndIf
    Else
        $objConnection.Execute("USE master")
        Return $objConnection.Execute("DROP DATABASE " & $varDatabaseName)
    EndIf
EndFunc

Func _DropTable($varTableName)
    If Not IsObj($objConnection) Then Return -1
    Return $objConnection.Execute("DROP TABLE " & $varTableName)
EndFunc

Func _GetRecords($varTable,$arrSelectFields,$arrWhereFields=0)
    If Not IsObj($objConnection) Then Return -1
    If Not IsObj($objRecordSet) Then Return -2
    _OpenRecordset($varTable,$arrSelectFields,$arrWhereFields)
    If Not $objRecordSet.RecordCount Or ($objRecordSet.EOF = True) Then Return -3
    Dim $arrRecords
    $arrRecords = $objRecordSet.GetRows()
    _CloseRecordSet()
    Return $arrRecords
EndFunc

Func _GetTablesAndColumns($varSystemTables=0)
    If Not IsObj($objConnection) Then Return -1
    $objADOXCatalog = ObjCreate("ADOX.Catalog")
    $objADOXCatalog.ActiveConnection = $objConnection
    Dim $arrTables[1][2]=[['Table Name','Columns Array']]
    Dim $arrColumns[1][2]=[['Column Name','Column Type']]
    For $objTable In $objADOXCatalog.Tables
        Local $varSkipTable = 0
        If Not $varSystemTables Then
            If StringInstr($objTable.Type,"SYSTEM") or StringInstr($objTable.Name,"MSys")=1 Then $varSkipTable = 1
        EndIf
        If Not $varSkipTable Then
            ReDim $arrTables[UBound($arrTables)+1][2]
            $arrTables[UBound($arrTables)-1][0] = $objTable.Name
            ReDim $arrColumns[1][2]
            For $objColumn in $objTable.Columns
                ReDim $arrColumns[UBound($arrColumns)+1][2]
                $arrColumns[UBound($arrColumns)-1][0] = $objColumn.Name
                $arrColumns[UBound($arrColumns)-1][1] = $objColumn.Type
            Next
            $arrTables[UBound($arrTables)-1][1] = $arrColumns
        EndIf
    Next
    $objADOXCatalog = 0
    Return $arrTables
EndFunc

Func _InsertRecords($varTableName,$arrFieldsAndValues)
    If Not IsObj($objConnection) Then Return -1
    If Not IsArray($arrFieldsAndValues) Then Return -2
    For $y = 1 To UBound($arrFieldsAndValues)-1
        $strInsert = "INSERT INTO " & $varTableName & " ("
        $varFieldCount = UBound($arrFieldsAndValues,2)-1
        For $x = 0 To $varFieldCount
            $strInsert &= $arrFieldsAndValues[0][$x]
            If $x < $varFieldCount Then $strInsert &= ","
        Next
        $strInsert &= ") VALUES ("
        For $x = 0 To $varFieldCount
            $strInsert &= "'" & $arrFieldsAndValues[$y][$x] & "'"
            If $x < $varFieldCount Then $strInsert &= ","
        Next
        $strInsert &= ");"
        $objConnection.Execute($strInsert)
        If $errADODB.number Then
            If Msgbox(4+16+256,"Insert Record Error","Statement failed:" & @CRLF & $strInsert & @CRLF & @CRLF & "Would you like to continue?") <> 6 Then Return -3
        EndIf
    Next
    Return 1
EndFunc

Func _OpenConnection($varProvider,$varDataSource,$varTrusted=0,$varInitalCatalog="",$varUser="",$varPass="")
    If Not IsObj($objConnection) Then Return -1
    $adCurrentDataSource = $varDataSource
    $adCurrentProvider = $varProvider
    If $adCurrentProvider = $adProviderMSJET4 Then
        If Not FileExists($adCurrentDataSource) Then
            If MsgBox(4+16,$adCurrentDataSource & " does not exist.","Would you like to attempt" & @CRLF & "to create it?") = 6 Then
                _CreateDatabase()
            Else
                Return 0
            EndIf
        EndIf
    EndIf
    $strConnect = "Provider=" & $adCurrentProvider & ";Data Source=" & $adCurrentDataSource & ";"
    If $varTrusted Then $strConnect &= "Trusted_Connection=Yes;"
    If $varUser Then $strConnect &= "User ID=" & $varUser & ";"
    If $varPass Then $strConnect &= "Password=" & $varPass & ";"
    $objConnection.Open($strConnect)
    If $varInitalCatalog Then
        Return $objConnection.Execute("USE " & $varInitalCatalog)
    Else
        Return 1
    EndIf
EndFunc

Func _OpenRecordset($varTable,$arrSelectFields,$arrWhereFields=0,$varCursorType=$adOpenForwardOnly,$varLockType=$adLockReadOnly)
    If Not IsObj($objConnection) Then Return -1
    If Not IsObj($objRecordSet) Then Return -2
    $strOpen = "SELECT "
    $varFieldCount = UBound($arrSelectFields)-1
    For $x = 0 to $varFieldCount
        $strOpen &= "[" & $arrSelectFields[$x] & "]"
        If $x < $varFieldCount Then $strOpen &= ", "
    Next
    $strOpen &= " FROM " & $varTable
    If IsArray($arrWhereFields) Then
        $strOpen &= " WHERE "
        $varFieldCount = UBound($arrWhereFields)-1
        For $x = 0 to $varFieldCount
            $strOpen &= $arrWhereFields[$x]
            If $x < $varFieldCount Then $strOpen &= ", "
        Next
    EndIf
    Return $objRecordSet.Open($strOpen,$objConnection,$varCursorType,$varLockType)
EndFunc

Func _ErrADODB()
    Msgbox(0,"ADODB COM Error","We intercepted a COM Error !"      & @CRLF  & @CRLF & _
             "err.description is: "    & @TAB & $errADODB.description    & @CRLF & _
             "err.windescription:"     & @TAB & $errADODB.windescription & @CRLF & _
             "err.number is: "         & @TAB & hex($errADODB.number,8)  & @CRLF & _
             "err.lastdllerror is: "   & @TAB & $errADODB.lastdllerror   & @CRLF & _
             "err.scriptline is: "     & @TAB & $errADODB.scriptline     & @CRLF & _
             "err.source is: "         & @TAB & $errADODB.source         & @CRLF & _
             "err.helpfile is: "       & @TAB & $errADODB.helpfile       & @CRLF & _
             "err.helpcontext is: "    & @TAB & $errADODB.helpcontext _
            )
    Local $err = $errADODB.number
    If $err = 0 Then $err = -1
EndFunc
#EndRegion

Demo Script

#include <_ADODB.au3>
#include <Array.au3>   ;Only needed for This Demo Script

$adCurrentProvider = $adProviderMSJET4   ;Microsoft.Jet.OLEDB.4.0
$adCurrentDataSource = @ScriptDir & "\TEMPDB.MDB"

;Establish ADO Connection
_OpenConnection($adCurrentProvider,$adCurrentDataSource)  

;Create Table
Dim $arrFields[3]=["Firstname VARCHAR(50)","Lastname VARCHAR(50)","AnotherField VARCHAR(50)"]  ;Init Fields for Table Creation
_CreateTable("TestTable",$arrFields)

;Insert Record
Dim $arrFieldsandValues[3][3]=[["Firstname","Lastname","AnotherField"],["John","Smith","Rulez"],["Dave","Thomas","Rulez"]]   ;Init Fields and Values for Insert
_InsertRecords("TestTable",$arrFieldsandValues)

;Retrieve Records from Table
Dim $arrSelectFields[3]=["Firstname","Lastname","AnotherField"]   ;Init Select Fields for Recordset
$arrRecords = _GetRecords("TestTable",$arrSelectFields)
_ArrayDisplay($arrRecords)

;Retrieve Records from Table with Where Clause
Dim $arrWhereFields[1]=["Firstname = 'Dave'"]
$arrRecords = _GetRecords("TestTable",$arrSelectFields,$arrWhereFields)
_ArrayDisplay($arrRecords)

;Capture Tables and Colums
$tables = _GetTablesAndColumns(1)   ;Param causes display if SYSTEM tables.  Default (no param) hides these tables
_ArrayDisplay($tables)   ;Display Tables
For $x = 1 to UBound($tables)-1   ;Display Table Columns
    _ArrayDisplay($tables[$x][1],"Table Name: " & $tables[$x][0])
Next

;Add Column to Table
_AddColumn("TestTable","Field2","VARCHAR(10)")

;Drop Column from Table
_DropColumn("TestTable","Field2")

;Drop Table
_DropTable("TestTable")

;Drop Database
_DropDatabase()   ;Closes ADO Connection first if using MS.JET.OLEDB.4.0

;Close ADO Connection
;_CloseConnection()

edit: Made a few small changes.

I added the ability to create JET DB Files (.MDB) so it is a bit more useful for demo purposes.
Added Where Clause to Example

Sooner or later I'll modify the GetTablesAndColumns to translate the field types into the literal data types (text,int,date,etc...) and make a more meaningful demo (Multiple tables and what ever else I can think of or someone recommends).

edit: Made a few changes based on inputs from Kinshima in post #10

edit: changed _GetRecords() func based on input from @‌kvcinu

Edited May 10, 2018 by spudw2k

saromman · November 30, 2009

Good,It's very kind of you!

spudw2k · December 2, 2009

Made some updates. Demo script no longer has existing MDB file dependency.

Alomax · December 7, 2009

Thanks for this, spudw2k. This is very handy.

The _GetRecords function throws a COM error if it attempts to get records from a table that has no rows. How can I handle that error? I'm just learning how to work with COM objects.

Edited December 7, 2009 by Alomax

spudw2k · December 7, 2009

Thanks for this, spudw2k. This is very handy.

The _GetRecords function throws a COM error if it attempts to get records from a table that has no rows. How can I handle that error? I'm just learning how to work with COM objects.

I made a generic error handler for this script. It simply returns the error message.

The Err Event is defined at the begging of the UDF script. [Global $errADODB = ObjEvent("AutoIt.Error","_ErrADODB")]

And the _ErrADODB is at the bottom of the script.

Yogui · February 11, 2010

Hi

Can you give an exemple for _GetRecords with a Where

I tested that

$arrSelectFields[3]=["Firstname","Lastname","AnotherField"]

$arrWhereFields[1]=["Fistname = 'Dave'"]

_GetRecords("TestTable",$arrSelectFields,$arrWhereFields=0)

but don't work

Edited February 11, 2010 by Yogui

spudw2k · February 11, 2010

Hi

Can you give an exemple for _GetRecords with a Where

I tested that

$arrSelectFields[3]=["Firstname","Lastname","AnotherField"]
$arrWhereFields[1]=["Fistname = 'Dave'"]
_GetRecords("TestTable",$arrSelectFields,$arrWhereFields=0)

but don't work

Watch your spelling

$arrWhereFields[1]=["Fistname = 'Dave'"]

Should Be

$arrWhereFields[1]=["Firstname = 'Dave'"]

and

$arrRecords = _GetRecords("TestTable",$arrSelectFields,$arrWhereFields)

I modified the demo script in the first post.

Edited February 11, 2010 by spudw2k

Yogui · February 12, 2010

Thanks for this and sorry ... for my inattention in my code

spudw2k · February 12, 2010

Thanks for this and sorry ... for my inattention in my code

No prob; we've all made mistakes...hopefully we're attentive at learning from those mistakes.

Kinshima · September 24, 2014

Thanks for this, this script is wonderfull.

Works perfectly with access 2010.

Provider "Microsoft.ACE.OLEDB.12.0" for Access 2007-2010

Provider "Microsoft.Jet.OLEDB.4.0" for Access 2003

If you change in _OpenRecordset:

$strOpen &= $arrSelectFields[$x]

into

$strOpen &= '['&$arrSelectFields[$x]&']'

Then you can use spaces in your Field Names in the tables.

If you add in _GetRecords:

Dim $arrRecords[1][1]

If $objRecordSet.EOF = False Then

...

Endif

_CloseRecordSet()

Then you won't get COM Errors when trying to get data from an empty table.

Edited September 26, 2014 by Kinshima

spudw2k · September 24, 2014

Glad you found use of it, and I appreciate your inputs. I changed the first post to incorporate them:

Added 12.0 Provider
Added surrounding field brackets
Added .EOF check (If Not $objRecordSet.RecordCount Or ($objRecordSet.EOF = True) Then Return -3)

Edited September 24, 2014 by spudw2k

Page2PagePro · October 23, 2015

FYI. If working in a 64-bit (x64) environment and using Office x86 (32-bit) which is very common, be sure you specify NOT to compile 64-bit.

Otherwise you may experience COM Errors "Provider cannot be found. It may not be installed properly."

#Region
#AutoIt3Wrapper_UseX64=N
#EndRegion

If you do NOT have two Data Sources shortcuts in Control Panel --> Administrative Tools, I'd recommend doing the following:

Rename Existing "Data Sources (ODBC)" to "Data Sources (ODBC) 64-bit (x64)".
- Target: %windir%\system32\odbcad32.exe
- Start in: %windir%\system32
Copy "Data Sources (ODBC) 64-bit (x64)" to "Data Sources (ODBC) 32-bit (x86)".
- Target: %windir%\syswow64\odbcad32.exe
- Start in: %windir%\syswow64

(YES! The default "System32" is 64-bit. The "SysWow64" is 32-bit)

Using the above shortcuts, you'll notice that if you try to "Create a New Data Source" under 64-bit, Access (Jet) will not be listed.

Hope this makes sense.

References:

https://msdn.microsoft.com/en-us/library/cc645931.aspx

https://support.microsoft.com/en-us/kb/942976

http://www.samlogic.net/articles/32-64-bit-windows-folder-x86-syswow64.htm

cramaboule · June 27

Hello thanks for this script,

What about the 'Update' with the returned affected rows ?
Do you have an exemple ?

C.

cramaboule · June 27

5 hours ago, cramaboule said:

Hello thanks for this script,

What about the 'Update' with the returned affected rows ?
Do you have an exemple ?

C.

Still not working in AutoIt from here:

Sign In

ADODB Example

Recommended Posts

spudw2k

saromman

spudw2k

Alomax

spudw2k

Yogui

spudw2k

Yogui

spudw2k

Kinshima

spudw2k

Page2PagePro

cramaboule

cramaboule

Create an account or sign in to comment

Create an account

Sign in

Recently Browsing 0 members

Browse

AutoIt Resources

Release

Beta