Sign in to follow this  
Followers 0
momitty

ODBC / ADO AutoIt Questions

3 posts in this topic

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

Share this post


Link to post
Share on other sites



@momitty

Maybe this can get you started.

;===============================================================================
;
; 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()

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
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
Sign in to follow this  
Followers 0