Jump to content

LISP to query MS Access Database return results as list of strings


Recommended Posts

In Feb fixo helped me to write aSQLCon function. I used the function tooquery SQL databases. I have tried towrite one enabling me to query an access database in the same way. But I am at a total loss.

(defun SQLControl ( query_statement / adocmd ADOConn adorst  data field fields n sqlQuery 
 tmp data1 data2)
;; Fixo 
;; http://www.cadtutor.net/forum/showthread.php?84548-LISP-to-query-MS-SQL-Server-Database-return-results-as-list-of-strings
(defun tostring (received / temp ) 
; Function Syntax: (tostring received)
; received:   Variable to be converted to a string
  ((= (type received) 'STR) received)            
  ((= (type received) 'INT) (itoa received))          
  ((= (type received) 'REAL) (rtos received))          
  ((= (type received) 'LIST)               
       (setq temp "")            
       (foreach listitem received         
        (setq temp 
         (strcat temp " " 
          (datatostring listitem)
       (setq temp (substr temp 2))         
       (setq received temp)          
(setq SqlCon "Provider=sqloledb;Data Source=SQL\\DESIGNSQL;Initial Catalog=engineering;user id=DraftingDesign;Password=12345"
 ADOConn(vlax-create-object "ADODB.Connection")
 ADORst (vlax-create-object "ADODB.Recordset")
 data1 (list )
 data2 (list )
(vlax-invoke-method ADOConn 'Open SqlCon nil "" -1)
(setq sqlQuery query_statement
 ADOcmd (vlax-create-object "ADODB.command")
(vlax-put-property ADOcmd "ActiveConnection" ADOConn)
; optional
; (vlax-put-property cmd2 "CommandTimeout" 30)
(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:
(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
(foreach line data
 (foreach item line
  (setq data1
   (append data1
      ((= item nil)"")
      ((= (type item) 'SYM)(substr (vl-symbol-name item) 7))
      ((/=(type item) 'STR)(tostring item))
      (T item)
 (setq data2 (append data2 (list data1))
  data1 (list )

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...