Visual LISP to RDBMS (Oracle/SQL Server).

Hi,

Most of us limit the Auto/Visual Lisp Functionalities to only for AutoCAD.
If we want to perform operations like interacting with RDBMS, DBMS (.mdb), connect to external applications like Microsoft excel/word we choose to go for either AutoCAD VBA or ObjectARX.
The interesting thing is we can perform all these activities using Visual LISP itself. Since Visual Lisp was developed using ActiveX technology. We can now perform all the operations in visual LISP those were possible in VBA only.

In this article we will tell you how to connect to Oracle using Visual LISP.

To connect to Oracle in VBA first will go to references add the ado dll then we will write the connection functions query functions etc. like wise in Visual Lisp also we need to import the dll file then we have to write the required functions.

• Vlax-import-type-library
  By using this method we can import any valid dll functionalities in to Visual Lisp. It takes 4 arguments.
  1. :tlb-filename path and name of the dll/tlb file which needs to be imported.
  2. :Methods-prefix this will concatenate the string given by user to all the ‘Methods’ available in the dll.
  3. :Properties-prefix this will concatenate the string given by user to all the ‘Properties’ available in the dll.
  4. :Constants-prefix this will concatenate the string given by user to all the ‘Constants’ available in the dll.
  
  
  
  
• import dll Code.
  (defun ImportAdoDll ()
  (setq FileName "C:\\Program Files\\Common Files\\System\\ado\\msado15.dll")
  (cond ((not LISPADOC-adAddNew)
  (if (findfile FileName)
  (vlax-import-type-library :tlb-filename FileName :methods-prefix "LISPADOM-" :properties-prefix "LISPADOP-" :constants-prefix "LISPADOC-"
  )
  )
  )
  (t t)
  )
  )
  Remember one thing that we have to import dll only once per a AutoCAD session.
  
• Connect to Oracle code:
  
  (Defun ConnectOracle (DataSource Username Password / adoConn)
  (if (not lispadop-get-version)
  (ImportAdoDll)
  )
  (cond ((setq adoConn (vlax-create-object "Adodb.Connection"))
  (cond ((and DataSource Username Password)
  (Setq conString (strcat "Driver={Microsoft ODBC for Oracle};Server=" DataSource ";Uid=" Username ";Pwd=" Password))
  )
  )
  (vlax-invoke-method adoConn "Open" conString nil nil -1)
  (if (= (LISPADOP-get-State adoConn) 1)
  adoConn
  )
  )
  )
  )
  
  Note: In the above code ‘DataSource’ is service name.
  
• Code to get all the table names available in the connected database:
  
  (defun GetTables (object / adoxCat retlst tables)
  (cond ((setq adoxCat (vlax-create-object "ADOX.Catalog"))
  (vlax-put-property adoxCat "ActiveConnection" object)
  (cond ((and (Setq tables (vlax-get-property adoxCat "Tables"))
  (setq tabsCnt (vlax-get-property tables "Count"))
  (> tabsCnt 0)
  )
  (setq ind 0)
  (Setq retlst '())
  (while (< ind tabsCnt)
  (setq tab (vlax-get-property tables "Item" ind))
  (Setq tabtype (vlax-get-property tab "Type"))
  (cond ((and (= tabtype "TABLE")
  (Setq tabName (vlax-get-property tab "Name"))
  (not (wcmatch tabName "*$*"))
  (not (wcmatch tabName "*_*"))
  )
  (setq retlst (append (list tabName) retlst))
  )
  )
  (Setq ind (1+ ind))
  )
  )
  )
  )
  )
  retlst
  )
• Code to execute to Query
  
  (Defun ExecuteQuery (Connection QueryString)
  (Setq retval (vlax-invoke-method Connection "Execute" QueryString nil -1))
  )
  
• To get The Record Set from the Connection based on the Query String Provided
  
  (Defun GetRecordSet (Connection QueryString)
  (cond ((Setq adoRecSet (vlax-create-object "ADODB.Recordset"))
  (vlax-put-property adoRecSet "CursorLocation" LISPADOC-adUseClient)
  (vlax-put-property adoRecSet "ActiveConnection" Connection)
  (vlax-put-property adoRecSet "LockType" LISPADOC-adLockOptimistic)
  (vlax-put-property adoRecSet "CursorType" Lispadoc-adOpenDynamic)
  (vlax-put-property adoRecSet "Source" QueryString)
  (vlax-invoke-method adoRecSet "Open" nil nil nil nil -1)
  (if (> (vlax-get-property adoRecSet "RecordCount") 0)
  adoRecSet
  )
  )
  )
  )
  
  
• To Get the Column Names from the Recordset:
  (Defun getRecordSetFields (RecordSet / Fields ind maXInd retList field fieldName)
  (cond ((setq Fields (vlax-get-property RecordSet "Fields"))
  (Setq ind 0)
  (Setq maXInd (vlax-get-property Fields "Count"))
  (Setq retList '())
  (while (< ind maXInd)
  (Setq field (vlax-get-property Fields "Item" ind))
  (Setq fieldName (vlax-get-property field "Name"))
  (if retList
  (Setq retList (append retList (list fieldName)))
  (Setq retList (list fieldName))
  )
  (Setq ind (1+ ind))
  )
  )
  )
  retList
  )
  
• To Get all the Data in the Recordset along with the Field Names:
  
  (Defun GetDataFromRecordSet (RecordSet / rcsFields retvals)
  (cond ((and (Setq rcsFields (GETRECORDSETFIELDS RecordSet))
  (setq retvals (getRecordSetValues RecordSet))
  )
  (append (list rcsFields) retvals)
  )
  )
  )
  
  
• To Get all the Rows Data in the Recordset
  
  (Defun getRecordSetValues (RecordSet / rows ReturnValue retList)
  (cond ((And (= (vlax-get-property RecordSet "State") 1)
  (/= (vlax-get-property RecordSet "EOF") :vlax-true)
  (Setq rows (vlax-invoke-method RecordSet "GetRows" lispadoc-adGetRowsRest lispadoc-adBookmarkCurrent nil))
  (setq ReturnValue (vlax-safearray->list (vlax-variant-value rows)))
  )
  (setq retList (apply 'mapcar
  (cons 'list
  (mapcar '(lambda (InputList) (mapcar '(lambda (Item) (GetVariantvalue Item)) InputList))
  ReturnValue
  )
  )
  )
  )
  )
  )
  retList
  )
  
  
  
  
  
  
• To Get the Database Data types related to AutoCAD data types
  
  (defun GetVariantvalue (VariantItem / VariantType)
  (cond ((or (= vlax-vbCurrency (setq VariantType (vlax-variant-type VariantItem)))
  (= vlax-vbDecimal VariantType)
  )
  (vlax-variant-value (vlax-variant-change-type VariantItem vlax-vbDouble))
  )
  ((= vlax-vbDate VariantType) (1900BasedJulianToCalender (vlax-variant-value VariantItem)))
  ((= vlax-vbBoolean VariantType)
  (if (= :vlax-true (vlax-variant-value VariantItem))
  "True"
  "False"
  )
  )
  ((or (= vlax-vbInteger VariantType)
  (= vlax-vbDouble VariantType)
  (= vlax-vbString VariantType)
  (= vlax-vbSingle VariantType)
  (= vlax-vbLong VariantType)
  )
  (vlax-variant-value VariantItem)
  )
  ((= vlax-vbArray VariantType) (vlax-safearray->list (vlax-variant-value VariantItem)))
  ((= vlax-vbnull VariantType) "")
  ((/= vlax-vbnull VariantType)
  (setq VariantItem (vlax-variant-change-type VariantItem vlax-vbString))
  (vlax-variant-value VariantItem)
  )
  )
  )
  
  
  (defun floor (number /)
  (if (> number 0)
  (fix number)
  (fix (- number 1))
  )
  )
  
  
  
• Closing the Connection
  
  (defun CloseConnection (Object)
  (cond ((= (vlax-get-property Object "State") 1) (vlax-invoke-method Object "Close")))
  (if (/= Object :vlax-null)
  (vlax-release-object Object)
  )
  )

Regards,

GIS Programmers.