ODBC / ADO AutoIt Questions

[momitty]

My most pressing problem is trying to get a list of schemas on a database. Anybody know how to do this?

I've collected code I've found around the forums and tried something like this, which connects okay but errors out :

$sqlCon = ObjCreate("ADODB.Connection")

$sqlCon.Mode = 16; shared
$sqlCon.CursorLocation = 3; client side cursor

; http://ns7.webmasters.com/caspdoc/html/syn...ion_strings.htm
; where [ip_address] is the IP address of the database server, [port_number] is the port for the database server,
; [database_name] is the name of the database,
; and [username] and [password] are the username and password required for accessing the database.
ConsoleWrite("start" & @CRLF);

$sqlCon.Open ("DSN=Database; UID=user; PWD=pass")
If @error Then
    MsgBox(0, "ERROR", "Failed to connect to the database")
    Exit
EndIf

ConsoleWrite("cxn opened" & @CRLF);
; See also Catalog "ADOX Catalog Example.au3"

$sqlRs = ObjCreate("ADODB.Recordset")
If Not @error Then
    $sqlRs = $sqlCon.OpenSchema('adSchemaCatalogs')
     ConsoleWrite("schemas read" & @CRLF);
    If Not @error Then
    ;Loop until the end of file
        While Not $sqlRs.EOF
        ;Retrieve data from the following fields
                $OptionName = $sqlRs.Fields ('TABLE_SCHEMA' ).Value
            $sqlRs.MoveNext
        WEnd
        $sqlRs.close
    EndIf
EndIf

$sqlCon.Close

This next code chunk gets me a list of tables, but I can't get the schemas.

$adoxConn = ObjCreate("ADOX.Catalog")
$sqlCon = ObjCreate("ADODB.Connection")

$sqlCon.Open ("DSN=Database; UID=user; PWD=pass")
$adoxConn.activeConnection = $sqlCon
    
$sqlCon.CursorLocation = 3; client side cursor


for $table in $adoxConn.tables
   ConsoleWrite($table.name & @CRLF)
next

[ptrex]

@momitty

Maybe this can get you started.

expandcollapse popup

;===============================================================================
;
; Function Name:    _adoListTables()
; Description:      List the tables in an MSAccess (*.mdb) file
; Syntax:   adoListTables ($adSource)
; Parameter(s):     $adSource  - The full path/filename of the database to be listed
; Requirement(s):   None.
; Return Value(s):  Success - Returns a "|" delimited string of table names
;                                       Failure Sets @Error
;                                           1 = unable to create connection
;                                           3 = no matching tables located (returns a blankString)
; Author(s):        GEOSoft, 2tim3_16
; Note(s):          None.
;
;===============================================================================
;
Func _adoListTables()
    Local $oList = ''
    If IsObj($oADO) = 0 Then Return SetError(1)
    $oRec = $oADO.OpenSchema($adSchemaTables)
    
    While NOT $oRec.EOF
        If StringLen( $oRec("TABLE_TYPE").value) > 5 Then;; Skip the hidden internal tables
            $oRec.movenext
            ContinueLoop
        EndIf
        $oList = $oList & $oRec("TABLE_NAME").value & '|'
        $oRec.movenext
    Wend
    
    If $oList <> '' Then
        Return '|' & StringTrimRight($oList,1)
    Else
        SetError(3, 0, 0)
        Return $oList
    EndIf
EndFunc    ;<===> _adoListTables()

[momitty]

It might, but I'm guessing there is some more to this code. Like what is opening the connection and where does $adSchemaTables get set? Also, is there a schema attribute when doing this loop? like TABLE_SCHEMA ? I need to get a unique list of schemas.