VLISP通过ADO连接ACCESS数据库

marting

Using AutoLISP to read an access database via ADO

By Xiaodong Liang

Issue
How can you access the data in a database rows using Visual LISP functions ?

Solution
Although there are no direct Visual LISP functions to read databases, you can use Microsoft's ActiveX ADO technology from AutoLISP to do this.

The following code illustrates how to read the Customer table from the NorthWind sample database in the Office products using either the Microsoft Access drivers or ODBC.

[C++] 纯文本查看 复制代码

(vl-load-com)

(defun ADOreadNorWndDB (/              MDB_File            SQLStatement
                        ADO_DLLPath   ConnectionObject
                        aCommandObject                    supp
                        rsState              recno            RecordSetObject
                        aBOF              aEOF            fieldsObj
                        reccnt              afield            i
                        fvalue              coState
                       )
  ;; You'll Need To Change the Path to the Northwind.mdb file 
  ;;  L:\Program Files\Microsoft Office\Office\Samples  
;;; Set MDB_File to a locatable Access Database file -> 
  (if (setq MDB_File (findfile "c:\\Northwind.mdb"))
    (setq MDB_File "c:\\Northwind")
    (progn (princ "\nNorthWind Database was not found, Exiting.")
           (exit)
    )
  )
;;; Setup a SQL statement -> 
  (setq SQLStatement "SELECT * FROM CUSTOMERS")
;;; Normal path to the ADO DLL, change as needed: 
  (setq ADO_DLLPath "c:\\program files\\common files\\system\\ado\\")
  (if (null adom-Append)
    (if        (findfile (strcat ADO_DLLPath "msado15.dll"))
      (vlax-import-type-library
        :tlb-filename
        (strcat ADO_DLLPath "msado15.dll")
        :methods-prefix
        "adom-"
        :properties-prefix
        "adop-"
        :constants-prefix
        "adok-"
      )
      (progn (alert (strcat "Exiting - Can't find \""
                            ADO_DLLPath
                            "msado15.dll\""
                    )
             )
             (exit)
      )
    )
  )
;;; Proceed if Type Library has been loaded. 
  (if adom-Append
    (progn
;;; Create A Connection Object 
      (setq ConnectionObject (vlax-create-object "ADODB.Connection"))
;;; Check the mode of the Connection Object 
      (setq coMode (vlax-get-property ConnectionObject 'Mode)) ; = 0 
;;; Valid Mode values are: 
;;;  adok-adModeUnknown = 0 
;;;  adok-adModeRead = 1 
;;;  adok-adModeWrite = 2 
;;;  adok-adModeReadWrite = 3 
;;;  adok-adModeShareDenyRead = 4 
;;;  adok-adModeShareDenyWrite = 8 
;;;  adok-adModeShareExclusive = 12 
;;;  adok-adModeShareDenyNone = 16 
      (setq coState (vlax-get-property ConnectionObject 'State))
      (if (= coState adok-adStateClosed)
        (vlax-put-property
          ConnectionObject
          'Mode
          adok-adModeReadWrite
        )
      )
;;; Use the direct Microsoft Access Drivers Creates a LDB Locking file here after this executes 
;;;    (vlax-invoke-method 
;;;     ConnectionObject 
;;;    "Open" 
;;;    (strcat "Driver={Microsoft Access Driver (*.mdb)};DBQ=" 
;;;       MDB_File 
;;;    ) 
;;;    "admin" 
;;;    "" 
;;;    adok-adConnectUnspecified 
;;;    ) 
;;; Or Use a ODBC Connection, but set it up first -> 
      (vlax-invoke-method
        ConnectionObject     "Open"                  "DSN=myMDB;"
        "admin"                     ""
        adok-adConnectUnspecified
       )
;;; Create the RecordSet Object 
      (setq RecordSetObject (vlax-create-object "ADODB.RecordSet"))
;;; Set the CursorType, and Locking Type 
      (vlax-put-property
        RecordSetObject
        'CursorType
        adok-adOpenDynamic
      )
;;; Where the property values are: 
;;;    adok-adOpenForwardOnly = 0 
;;;    adok-adOpenKeyset = 1 
;;;    adok-adOpenDynamic = 2 
;;;    adok-adOpenStatic = 3 
      (vlax-put-property
        RecordSetObject
        'LockType
        adok-adLockOptimistic
      )
;;; Where the property values are: 
;;;  adok-adLockReadOnly = 1 
;;;  adok-adLockPessimistic = 2 
;;;  adok-adLockOptimistic = 3 
;;;  adok-adLockBatchOptimistic = 4 
;;; The following code will not work, do not use it. 
;(setq RecordSetObject 
;;; The following fails with a "Type mismatch" error 
;      (vlax-invoke-method 
;        ConnectionObject "Execute" SQLStatement 'RecordsAffected 
;        adok-adCmdText 
;       ) 
;  ) 
;;; Create a Command Object and Connect it to the Connection Object 
      (setq aCommandObject (vlax-create-object "ADODB.Command"))
      (vlax-put-property
        aCommandObject
        'ActiveConnection
        ConnectionObject
      )
;;; Setup the Command Object, and Execute the SQL query 
      (vlax-put-property aCommandObject 'CommandText SQLStatement)
      (vlax-put-property
        aCommandObject
        'CommandType
        adok-adCmdText
      )
;;; adok-adCmdText = 1 
;;; adok-adCmdTable = 2 
;;; adok-adCmdTableDirect = 512 
;;; adok-adCmdStoredProc = 4 
;;; adok-adCmdUnknown = 8 
;;; adok-adCommandFile = nil 
;;; adok-adExecuteNoRecords = 182 
      (setq RecordSetObject
             (vlax-invoke-method
               aCommandObject "Execute"        nil nil        nil)
      )
      (setq supp (vlax-invoke-method
                   RecordSetObject
                   'Supports
                   adok-adAddNew
                 )
      )
;;; Check to see that the RecordSet is available here -> 
      (setq rsState (vlax-get-property RecordSetObject 'State))
;;;   adok-adStateClosed = 0 
;;;   adok-adStateOpen = 1 
;;;   adok-adStateConnecting = 2 
;;;   adok-adStateExecuting = 4 
;;;   adok-adStateFetching = 8 
      (if (= rsState adok-adStateOpen)
        (progn
          (setq recno 0)
          (while
            (and (= (setq aBOF (vlax-get-property RecordSetObject 'BOF))
                    :vlax-false
                 )
                 (= (setq aEOF (vlax-get-property RecordSetObject 'EOF))
                    :vlax-false
                 )
            )
             (setq fieldsObj (vlax-get-property RecordSetObject 'Fields))
             (setq reccnt (vlax-get-property fieldsObj 'Count)
                   i          0
             )
             (princ (strcat "\nRecord " (itoa recno) ":\n"))
             (while (< i reccnt)
               (setq afield (vlax-get-property fieldsObj 'Item i))
               (princ (setq fvalue (vlax-variant-value
                                     (vlax-get-property afield 'Value)
                                   )
                      )
               )
               (princ "\t")
               (setq i (1+ i))
             )
             (princ "\n")
             (setq recno (1+ recno))
             (vlax-invoke-method RecordSetObject 'MoveNext)
          )
        )
        (princ
          (strcat "\nRecord Set NOT Available, Record Set State = "
                  (itoa rsState)
          )
        )
      )
;;; Close only works if it's Opened !, this can usually be tested by checking if 
      a
      LDB
      file
      exists.
;;; Check to see that the Connection Object is open here -> 
      (setq coState (vlax-get-property ConnectionObject 'State))
      (if (and (findfile (strcat MDB_File ".ldb"))
               (= coState adok-adStateOpen)
          )
        (vlax-invoke-method ConnectionObject "Close")
        (princ
          "\nConnection Object is not Open, please delete the LDB file if 
needed.")
      )
      (Clean_Up)
    )
;;; Exit if Type Library has NOT been loaded. 
    (progn (alert (strcat "Exiting - Type Library was Not Loaded: \""
                          ADO_DLLPath
                          "msado15.dll\""
                  )
           )
           (exit)
    )
  )
)


(defun Clean_Up        ()
  (if (and aField (null (vlax-object-released-p aField)))
    (vlax-release-object aField)
  )
  (if (and fieldsObj (null (vlax-object-released-p fieldsObj)))
    (vlax-release-object fieldsObj)
  )
  (if (and aCommandObject
           (null (vlax-object-released-p aCommandObject))
      )
    (vlax-release-object aCommandObject)
  )
  (if (and RecordSetObject
           (null (vlax-object-released-p RecordSetObject))
      )
    (vlax-release-object RecordSetObject)
  )
  (if (and ConnectionObject
           (null (vlax-object-released-p ConnectionObject))
      )
    (vlax-release-object ConnectionObject)
  )
  (setq        ConnectionObject nil
        RecordSetObject        nil
        aCommandObject nil
        fieldsObj nil
        aField nil
  )
  (princ "\n\nRemoving and Releasing Connection Objects.")
  (princ)
)

(princ
  "\nADOreadNorWndDB loaded, type (ADOreadNorWndDB) to run."
)
(princ)