Connecting to MySQL from LISP

[Bill Tillman]

I've been looking around in searches for information on this and found this code which looks like it should get me started on connected to and getting values from a MySQL database using VLISP.

(vl-load-com)
(defun mysqlQuery
      (sQuery / $acad dbConn dbConnString dbResult field item)
;connect to autocad application object
 (setq $acad (vlax-get-acad-object))
;connect to the mysql dll object
 (setq dbConn (vla-GetInterfaceObject $acad "ADODB.Connection"))
;open the mysql database
 (setq dbConnString
 (strcat "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=10.0.0.254; DATABASE=my_database; UID=my_id; PWD=my_passwd; OPTION=3")
 )
 (vlax-invoke dbConn "Open" dbConnString)
;run a query on the database
 (setq dbResult (vlax-invoke dbConn "Execute" sQuery))
;find the field, item, and value of the first object in the recordset
 (setq field (vlax-get-property dbResult "Fields"))
 (setq item (vlax-get-property field "Item" 0))
 (setq value (vlax-get-property item "Value"))
;release the autocad application and mysql object
 (vlax-invoke-method dbConn "Close")
 (vlax-release-object dbConn)
 (vlax-release-object $acad)
;prompt the result
 (prompt (vlax-variant-value value))
 (princ)
)

(defun C:test ()
 (mysqlQuery "select * from my_table")
); end function

This doesn't work, hits an error really quick.

; error: no function definition: VLAX-GET-ACAD-OBJECT

Anyone have a good source of info on using LISP to get data from MySQL? Also, is there a separate dll file which I will need for this?

[Hippe013]

When I saw that you were getting the error "; error: no function definition: VLAX-GET-ACAD-OBJECT" I immediately thought about (vl-load-com). Then I look at your code and there it is. I cannot, for the life of me, think of what else would cause this. Are you certain that (vl-load-com) is actually being initialized? I do not have any examples SQL databases so I am unable to fully test your code. Though I don't get the error "; error: no function definition: VLAX-GET-ACAD-OBJECT". I do get a driver error though.

 

"; error: Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified"

 

regards,

 

Hippe013

[Bill Tillman]

This one is a mystery to me as well. I'm just getting back to this issue today so hopefully soon I will have the breakthrough I need and will post the results. Thanks.

[Bill Tillman]

I'm revisiting this one again today. I received an email from a gentleman who posted the code but he said he can't remember how it was done. Anyway, another post I found here but this one has to do with the Common Lisp Language, not AutoLISP. So can anyone tell me if it's possible to make this work. I copied and pasted the code into the IDE but it is reporting six different errors.

 

I am afraid that like working with Excel from LISP, working with MySQL from LISP is going to be too slow, compared with using .NET. But I'm willing to try and I like learning new stuff. If anyone has any advice on this I'd love to hear about it.

[lzucco]

Hello Bill,

I know it's been a while since your post here but, I wonder if you found a proper code to connect mysql using Autolisp (save and read data). 

So, if you have it please share with us.

Thanks in advance + regards

[Lee Mac]

You may find this thread of interest: SQLite for AutoLISP