ADO (Visual Fox Pro oledb) - read .dbf with tcom

SV (2006-06-06)

Requirements:

  • latest MDAC from Microsoft
  • vfpoledb from the same source
  • latest tcom package (recent version 3.9)

set About {based on ex09ado.tcl - ADO (ActiveX Data Objects) handling,
this program has been published into the public domain.}
## tested on: WindowsXP Home, ActiveTcl 8.4.5
 
## logging window
pack [scrollbar .sb -orient vertical -command ".t yview"] -fill y -side right
pack [text .t -yscrollcommand ".sb set"] -expand yes -fill both -side left
bind all <F1> {tk_messageBox -title about -message $About}
bind all <Escape> "destroy ."
proc log {{s ""}} {.t insert end "$s\n"; .t see end}
 
package require tcom
 
## format an adDate as "YYYY-MM-DD HH:MM:SS". maybe broken.
proc fmtdate adotv {
    # adDate - days since from 1900-01-01 UTC.
    # Tcl clock - seconds since from 1970-01-01 UTC.
    set tcltv [expr ($adotv - 25569) * 86400]
    scan $tcltv %dl tcltv
    clock format $tcltv -format "%Y-%m-%d %H:%M:%S" -gmt true
}
 
proc getdata {obj} {
    global sqlquery
    ## exec an SQL, and yield a new recordset
    set rs [$obj Execute $sqlquery]
    
    ## navigate the recordset
    for {$rs MoveFirst} {![$rs EOF]} {$rs MoveNext} {
        ## enumerate a Fields Collection
        tcom::foreach fld [$rs Fields] {
            switch -- [$fld Type] {
                133   {set value [fmtdate [$fld Value]]}
                default {set value [$fld Value]}
            }
            log [format "%s: %s" [$fld Name] $value]
        }
        log
    }
    $rs Close
}

## default path after full install of vfpoledb
set dbdir {C:\Program Files\Microsoft Visual FoxPro OLE DB Provider\Samples\Northwind}
## valid vfpoledb statement
set sqlquery "select * from Employees"

if {[catch {
    ## get an ADO Connection instance
    set conn [::tcom::ref createobject ADODB.Connection]
    ## conn setup in 'DSN-less' style
    $conn Provider "VFPOLEDB.1"
    $conn Open $dbdir
} errmsg]} {
    log $errmsg
} else {
    getdata $conn
    catch {$conn Close}
}