In Feb fixo helped me to write a SQLCon function. I used the function to query SQL databases. I have tried to write one enabling me to query an access database in the same way. But I am at a total loss. Any help would be GREAT!
(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
(cond
((= (type received) 'STR) received)
((= (type received) 'INT) (itoa received))
((= (type received) 'REAL) (rtos received))
((= (type received) 'LIST)
(progn
(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:
(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
(foreach line data
(foreach item line
(setq data1
(append data1
(list
(cond
((= 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 )
)
)
data2
)
