ReadSQL with a/vlisp

iLisp · 发表于 2016-9-15 14:30:39

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

您需要登录才可以下载或查看，没有账号？立即注册

×

;;____________________________________________________;;
(defun C:SQ (/ adocmd ADOConn adorst data field fields n sqlQuery tmp)
(vl-load-com)
(setq ADOConn (vlax-create-object "ADODB.Connection")
ADORst (vlax-create-object "ADODB.Recordset")
)
(vlax-invoke-method
ADOConn
'Open ;|SqlAutoCAD is database name|;
;; may use Data Source=ServerName\\SQLEXPRESS; instead :
"Provider=SQLOLEDB.1;Data Source=.\\SQLEXPRESS;Initial Catalog=SqlAutoCAD;Integrated Security=SSPI"
nil
""
-1
)
;|Blocks is table name|;
;; enclose table name and field name in squared brakets:
(setq sqlQuery "SELECT * FROM [Blocks] WHERE [Hdl] IS NOT NULL")
;;[Hdl] is primary key, string (handle of block), see picture
(setq ADOcmd (vlax-create-object "ADODB.command"))
(vlax-put-property ADOcmd "ActiveConnection" ADOConn)
;; (vlax-put-property cmd2 "CommandTimeout" 30); optional
(vlax-put-property ADOcmd "CommandText" sqlQuery)
(vlax-put-property ADOcmd "CommandType" 1)
(setq ADORst (vl-catch-all-apply
'vlax-invoke-method
(list ADOcmd 'Execute nil 2 1)
)
) ;OK
(setq fields (vlax-get-property ADORst 'Fields))
(vl-catch-all-apply
'vlax-invoke-method
(list ADORst 'movefirst)
)
(while (not (equal :vlax-true (vlax-get-property ADORst 'eof)))
(setq tmp nil
n 0
)
(while (not (vl-catch-all-error-p
(vl-catch-all-apply
'vlax-get-property
(list fields 'item n)
)
)
)
(setq field (vlax-variant-value
(vlax-get-property
(vlax-get-property fields 'item n)
'value
)
)
)
(setq tmp (cons field tmp))
(setq n (1+ n))
)
(setq data (append data (list (reverse tmp))))
(vl-catch-all-apply
'vlax-invoke-method
(list ADORst 'movenext)
)
)
;; use garbage cleaner before of the closing connection:
(gc)
(vlax-invoke-method ADOConn 'Close)
(vlax-release-object ADORst)
(vlax-release-object ADOcmd)
(vlax-release-object ADOConn)
(alert (vl-princ-to-string data))
;; must use (vl-string-trim " " strvalue) for string values within the data list
;; --- next your job here --- ;;
(princ)
)

;;____________________________________________________;; 
(defun C:SQ (/ adocmd ADOConn adorst data field fields n sqlQuery tmp) 
  (vl-load-com) 
  (setq        ADOConn        (vlax-create-object "ADODB.Connection") 
        ADORst        (vlax-create-object "ADODB.Recordset") 
  ) 
  (vlax-invoke-method 
    ADOConn 
    'Open ;|SqlAutoCAD is database name|; 
    ;; may use Data Source=ServerName\\SQLEXPRESS; instead : 
    "Provider=SQLOLEDB.1;Data Source=.\\SQLEXPRESS;Initial Catalog=SqlAutoCAD;Integrated Security=SSPI" 
    nil 
    "" 
    -1 
   ) 
  ;|Blocks is table name|; 
  ;; enclose table name and field name in squared brakets: 
  (setq sqlQuery "SELECT * FROM [Blocks] WHERE [Hdl] IS NOT NULL") 
  ;;[Hdl] is primary key, string (handle of block), see picture 
  (setq ADOcmd (vlax-create-object "ADODB.command")) 
  (vlax-put-property ADOcmd "ActiveConnection" ADOConn) 
  ;; (vlax-put-property cmd2 "CommandTimeout" 30); optional 
  (vlax-put-property ADOcmd "CommandText" sqlQuery) 
  (vlax-put-property ADOcmd "CommandType" 1) 
 
  (setq        ADORst (vl-catch-all-apply 
                 'vlax-invoke-method 
                 (list ADOcmd 'Execute nil 2 1) 
               ) 
  )                                        ;OK 
 
  (setq fields (vlax-get-property ADORst 'Fields)) 
  (vl-catch-all-apply 
    'vlax-invoke-method 
    (list ADORst 'movefirst) 
  ) 
  (while (not (equal :vlax-true (vlax-get-property ADORst 'eof))) 
    (setq tmp nil 
          n   0 
    ) 
    (while (not        (vl-catch-all-error-p 
                  (vl-catch-all-apply 
                    'vlax-get-property 
                    (list fields 'item n) 
                  ) 
                ) 
           ) 
 
      (setq field (vlax-variant-value 
                    (vlax-get-property 
                      (vlax-get-property fields 'item n) 
                      'value 
                    ) 
                  ) 
      ) 
      (setq tmp (cons field tmp)) 
      (setq n (1+ n)) 
    ) 
    (setq data (append data (list (reverse tmp)))) 
    (vl-catch-all-apply 
      'vlax-invoke-method 
      (list ADORst 'movenext) 
    ) 
  ) 
  ;; use garbage cleaner before of the closing connection: 
  (gc) 
 
  (vlax-invoke-method ADOConn 'Close) 
  (vlax-release-object ADORst) 
  (vlax-release-object ADOcmd) 
  (vlax-release-object ADOConn) 
  (alert (vl-princ-to-string data)) 
  ;; must use (vl-string-trim " " strvalue) for string values within the data list 
  ;; ---   next your job here   ---  ;; 
  (princ) 
)

dnbcgrass · 发表于 2016-9-16 08:07:57

回复学学习

HLCAD · 发表于 2016-9-16 15:21:38

百度了一下SQL,但不得要领，楼主能简单介绍一下吗

WhoCanSay · 发表于 2016-9-18 07:57:58

这个不错，值得收藏

kqqt6236 · 发表于 2016-9-27 09:30:05

建议加精

8142556 · 发表于 2018-2-22 16:01:28

堪称经典，不错

账号		自动登录	找回密码
密码			立即注册

[精彩文萃] ReadSQL with a/vlisp

马上注册，结交更多好友，享用更多功能，让你轻松玩转社区。

评分