用LISP通过ADO读取access 数据库

XDSoft

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.

[pcode=lisp,true](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-paCommandObject)))
      (vlax-release-object aCommandObject)
)
(if (and RecordSetObject (null (vlax-object-released-pRecordSetObject)))
      (vlax-release-object RecordSetObject)
)
(if (and ConnectionObject (null (vlax-object-released-pConnectionObject)))
      (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)
[/pcode]

GTJ116600

好东西，老大就是老大，可真给力呀{:soso_e113:}

cuyongping

支持，谢谢分享

dnbcgrass

谢谢分享！:lol

zteykmgscqh

想学习一下这方面的，先收藏一下

快乐小子春天

想学习，但现在看不懂

革天明

谢谢楼主分享

凝思

正需要，支持楼主大人了！

lmchgs

cad和数据库现实工作密不可分，很好的，可惜自己不会

20180318

真能這樣!那不錯,能自訂資料庫嗎?

mocheng

谢谢楼主分享
来自: 微社区