Jump to content

ADODB Example


spudw2k
 Share

Recommended Posts

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 by spudw2k
Link to comment
Share on other sites

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 by Alomax
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

  • 2 months later...

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 :D

Edited by Yogui
Link to comment
Share on other sites

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 :D

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 by spudw2k
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

  • 4 years later...

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 by Kinshima
Link to comment
Share on other sites

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 by spudw2k
Link to comment
Share on other sites

  • 1 year later...

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:

  1. Rename Existing "Data Sources (ODBC)" to "Data Sources (ODBC) 64-bit (x64)".
    • Target: %windir%\system32\odbcad32.exe
    • Start in: %windir%\system32
  2. 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

 

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...