Visual LISP connect to SQL Server 2012 database

[JBerns]

Community,

I would like to read information from a database managed by SQL Server 2012. Read-only required at this time.

I have searched many forums for days and most results are many years old. I have attempted to use ADOlisp and SQL_Lite, but without success. No clear solutions found after all this searching.

I have an existing LSP file (500+ lines of code) that I would prefer not to convert to VBA or VB.NET. By the way, I have been able to connect to the server and database using VBA to prove that I can connect and read data from the database from within AutoCAD 2019. I have also been able to connect to the database and read a table using DBCONNECT. I just want to automate this read using Visual LISP.

Eventually, the goal is to read the CompanyName from the dbo.Customers table. Place this list into a dialog for the user to select a single customer. From there, the data populates a block for use by CNC software.

Is it possible to connect to a SQL Server database using AutoCAD 2019 and Visual LISP? What additional software is required? If I have overlooked an AutoCAD2019+VisualLISP+SQL_Server resource, please point me in that direction.

Thanks for your time. I look forward to your replies.

Regards,
Jerry

[vladimir_michl · Accepted solution]

You can list all available OLEDB providers with ASILISP (make sure your asilisp.arx is loaded). See

https://adndevblog.typepad.com/autocad/2013/03/find-all-available-oledb-data-providers-supported-by-...

Then you can connect to and select from your SQL database with the ASILISP functions.

Vladimir Michl, www.cadstudio.cz - www.cadforum.cz

[JBerns]

@vladimir_michl,

Thank you very much for the information and quick reply. The link should prove to be a helpful resource.

I wonder what other treasures are hiding in plain sight in all the ARX files.

Kind regards,

Jerry

[JBerns]

Community,

I have successfully connected to the SQL database in a read-only manner using Visual LISP code. I conclude the read-only connection because the date/time stamp on the MDF and LDF files do not change after reading the data.

If it is of benefit to others, here are my connection string and open statements:

(setq currConnectionString
    "Provider=SQLNCLI11;Server=ENGR\\PRONEST;Database=PRONEST13;Persist Security Inf=False;Integrated Security = SSPI"
)

(vlax-put-property
    ADOConnect
    "ConnectionString"
    currConnectionString
)
(vlax-invoke-method
    ADOConnect "Open" currConnectionString "" "" -1
)

Now I am trying to connect to the same database using VBA from within Autodesk Inventor.

I have connected and read successfully, but not read-only. The MDF and LDF file date/time stamp are changed.

I have posted on the Inventor forum hoping to find a solution.

Hope this has been helpful.

Regards,

Jerry

[kpennell]

Hi Jerry,

Sine this is somewhat of a recent post, I was wondering if you could help.  I've tried your connection strings and open statements, but to no avail.  Thanks for offering and sharing your experiences by the way.  And the link that was provided, I tried the 'getprov' command, but it's an unknown command.  I did find the ARX and I loaded it manually, and returned a successful load.

Any help would be great.

[JBerns]

@kpennell,

As I recall, I tried several connection string combinations before I was successful.

For example:

• You know your server name, correct?
• You know the database management software (SQL, MS OLE, etc.) in use, correct?

During my code development, I used a desktop with SQLEXPRESS and a laptop with Autodesk Vault. 

;;;DESKTOP
"Provider=SQLOLEDB; Data Source=JERRY-HP017\SQLEXPRESS; Initial Catalog=ProNest13; Integrated Security=SSPI;"

;;;LAPTOP
"Provider=SQLOLEDB; Data Source=JERRY-LAPTOP01\AUTODESKVAULT; Initial Catalog=ProNest13; Persist Security Inf=False; Integrated Security = SSPI"

The provider, data source, and catalog tend to be the easy parts for the connection strings. It's the remainder of the string that can be challenging.

Once I knew DB management software, I searched many a site looking for sample strings to try.

Therefore, the connection strings above would only work on my computers. You must edit the lines to match your provider, data source, and database (catalog). That's where the GetProv command can be helpful if your database is hosted on your computer. If not, you will have to use other tools to get the software used on the server to which you are trying to connect.

Regarding the GetProv, that is AutoLISP code that must be loaded. It is not a native AutoCAD command.

Try this:

1. Start AutoCAD.
2. Type VLIDE.
3. Create a new document.
4. Copy and then paste the AutoLISP code from the page on the link above.
5. Tools menu > Load Text in Editor (CTRL + ALT + E).
6. Return to AutoCAD.
7. At Command prompt, type GETPROV.

You should now be able to list the providers by typing 'N' (Next) until nothing is left to fetch.

If you still get an unknown command, then there must have been a problem during the copy/paste/load process.

I tested the code this morning successfully.

I hope this has been helpful. If you still need assistance, please post your server name and DB management software. We should be able to find connection string examples.

Regards,

Jerry

[JBerns]

@kpennell,

I meant to include these links for connection string examples:

https://www.connectionstrings.com/microsoft-ole-db-provider-for-sql-server-sqloledb/ 

http://www.sqlstrings.com/sql-server-connection-strings.htm 

https://www.dofactory.com/connection-strings 

I hope they are helpful.

Regards,

Jerry 

[kpennell]

Away from the office, put your post has not fallen on deaf ears.  Thank you so much for the response.  And of course need to adjust the connection to my environment, but reading down through your post, I might be off in the right direction.  I’ll certainly keep you posted.

[kpennell]

Well, there is good news to report.  The 'GetProv' command works.  My colleague tells me that either 'SQLNCL11' or 'SQLOLEDDB' should work, but it still returns a 'bad argument type: VLA_OBJECT'.

Here is what I think the connection string should be:

"Provider=SSISOLEDB;Server=DARKLIGHTER\STCSANDBOX;Database=193;Persist Security Inf=False;Integrated Security = SSPI"

[JBerns]

@kpennell,

During my research, I found this page which discusses how to use AutoLISP to read an Access database via ADO?

https://adndevblog.typepad.com/autocad/2013/04/using-autolisp-to-read-an-access-database-via-ado.htm... 

I used the code example to build my routine to read our SQL database. The code below retrieves the company names from the 'Customers' table in the database.

;;; ----- Function to get Company (Customer) Names from a SQL database and return the names as a list
;;;
(defun JB:SqlDbCustomerNames->Lst (/
				    ADOConnect
				    currConnectionString
				    Field1
				    FieldCnt
;;;				    FieldsObj
				    lst_CompanyNames
;;;				    RecCnt
				    RecSet
				    sqlcmd
				    str_Field_InTable
				   )
  
  ;; load ASIlisp
  (if (null asi_connect)
    (arxload "asilisp")
  )

  (progn
    (vl-load-com)
    ;; test for an open connection, close if found
    (if	ADOConnect
      (if (= 1 (vlax-get-property ADOConnect "State"))
	(vlax-invoke-method ADOConnect "Close")
      )
    )
    ;; create database connection
    (setq ADOConnect nil)
    (setq ADOConnect (vlax-create-object "ADODB.Connection"))
    ;; set database connection string
    (setq currConnectionString 
       "Provider=SQLNCLI11; Server=YourServerNameHere; Database=YourDatabaseHere; Persist Security Inf=False; Integrated Security = SSPI"
    ) ;_setq
    ;; send connection string to database
    (vlax-put-property
      ADOConnect
      "ConnectionString"
      currConnectionString
    ) ;_vlax-put-property
    (vlax-invoke-method
      ADOConnect "Open"	currConnectionString ""	"" -1)
    ;; send commands to SQL database
    (setq sqlcmd (vlax-create-object "ADODB.command"))
    (vlax-put-property sqlcmd "ActiveConnection" ADOConnect)
    (vlax-put-property sqlcmd "CommandTimeout" 30)
    (vlax-put-property
      sqlcmd
      "CommandText"
      (strcat "SELECT * FROM Customers")
    ) ;_vlax-put-property
    ;; retrieve a recordset
    (setq RecSet nil)
    (setq RecSet (vlax-create-object "ADODB.Recordset"))
    (vlax-invoke-method RecSet "OPEN" sqlcmd nil nil nil nil)
    ;; determine number of fields in record
    (setq FieldCnt (vlax-get-property RecSet "Fields"))
    ;; loop to End Of File
    (while (not (equal :vlax-true (vlax-get-property RecSet "EOF")))
      ;; get first field
      (setq Field1 (vlax-get-property FieldCnt "item" 1))
      ;; get field value, which is the company name
      (setq str_FIELD_INTABLE
	     (vlax-variant-value
	       (vlax-get-property Field1 "value")
	     ) ;_vlax-variant-value
      ) ;_setq
      ;; append new company name to list
      (setq lst_CompanyNames
	     (append lst_CompanyNames
		     (list (strcase str_FIELD_INTABLE))
	     ) ;_append
      ) ;_setq
      ;; get other record info
;;;      (setq FieldsObj (vlax-get-property RecSet 'Fields))
;;;      (vlax-dump-object FieldsObj)
;;;      (getstring "\nPaused after Dump FieldsObj...")
;;;      (setq reccnt (vlax-get-property FieldsObj 'Count))
;;;      (princ reccnt)
;;;      (getstring "\nPaused after Dump reccnt...")
      ;; get next record
      (vlax-invoke-method RecSet "movenext")
    ) ;_while
;;;    (setq RecSet nil)
;;;    (setq sqlcmd nil)
    (if	RecSet						;;2019-06-26 added
      (if (= 1 (vlax-get-property RecSet "State"))
	(vlax-invoke-method RecSet "Close")
      ) ;_if
    ) ;_if
    (if	ADOConnect
      (if (= 1 (vlax-get-property ADOConnect "State"))
	(vlax-invoke-method ADOConnect "Close")
      ) ;_if
    ) ;_if
    (setq ADOConnect nil)
  ) ;_progn
  (if lst_CompanyNames
    (progn
      ;; sort the list
      (setq lst_CompanyNames
	     (cons "Select Customer Name..."				;; prepend select prompt
		   (vl-sort 						;; sort the list
		     (vl-remove (strcase "<none>") lst_CompanyNames)	;; remove <none>
		     '<
		   ) ;_vl-sort
	     ) ;_cons
      ) ;_setq
    ) ;_progn
  ) ;_if
  ;; return the list of company names
  lst_CompanyNames
) ;_NMC:SqlDbCustomerNames->Lst

Requirements:

You must have ASILISP available on a support path.

For example, using the FINDFILE function, should return a result:

   Command: (findfile "asilisp.arx")

   "C:\\Program Files\\Autodesk\\AutoCAD 2019\\asilisp.arx"

Substitute:

• Your connection string (server, database, etc.)
• Your table name (E.g. Customers)

Usage:

  (setq lstCustNames (JB:SqlDbCustomerNames->Lst))	;;get the customer names from the SQL database
  
  (setq mnCustNames (nth 0 lstCustNames))		;;preset Customer Name selection

I hope this will be helpful to you and others. I look forward to hearing of your success.

Regards,

Jerry

[kpennell]

So I missed the variable ADOConnect, now I am getting different results.

The 

(vlax-put-property ADOConnect "ConnectionString" currConnectionString)

returns 'nil'

The

(vlax-invoke-method ADOConnect "Open" currConnectionString "" "" -1)
returns ; error: Automation Error. Invalid connection string attribute

[jberns-newell]

@kpennell,

When I step through my code, those lines also return nil for me.

According to the Help file, the vlax-put-property functions will return nil if successful, otherwise an error will occur.

For vlax-invoke-method, return values vary depending on method invoked. Nil is a possible result.

The first 'non-nil' return value I see is after the line:

   (setq sqlcmd (vlax-create-object "ADODB.command"))

   which returns a VLA_Object value.

More vlax-put-property statements follow which return nil.

Then the vlax-create-object function returns the next VLA_Object value.

The following lines, inside the while loop, are when I start to see values from the database:

(setq str_FIELD_INTABLE
	     (vlax-variant-value
	       (vlax-get-property Field1 "value")
	     ) ;_vlax-variant-value
      ) ;_setq

I hope this helps.

Regards,

Jerry

[kpennell]

SUCCESS

You'd never believe why I couldn't connect in the first place.  I had the server name as DARKLIGHTER\STCSANDBOX as opposed to DARKLIGHTER\\STCSANDBOX

as you and/or anyone else would know, this opens up a whole new world.

[JBerns]

@kpennell ,

Great to hear of your success!

Glad you found the problem- I didn't notice the missing backslash either.

Enjoy the new world of possibilites. 🙂

Regards,

Jerry

[kpennell]

You've been a huge help.

So I did return a column in a table to a list, of about 1800 entries and it was rather quick.  As you know, SQL tables have been known to be rather large.  But I'm thinking if I can pare down the list in the SQL syntax, it wouldn't be so much of a pig.  During my travels, I thought I read somewhere how one should write the SQL 'command text' in lisp.

this part of the code:

(strcat "SELECT * FROM \"193\".dbo.EQUIP_tblEquipmentClasses")

I'll eave you alone after this one.  Somehow I think you have a link stowed away somewhere. 🙂

[JBerns]

@kpennell,

You can try these examples which use the WHERE clause to apply more filters:

SQL Wildcard Characters 

SQL | String functions - GeeksforGeeks 

SQL Server LIKE Operator By Examples 

Matching character strings in the WHERE clause 

SQL WHERE LIKE, SELECT WHERE LIKE Wildcard - with Examples - Dofactory.com 

For more examples, just search the internet for these terms: SQL, SELECT, FROM, WHERE, LIKE, IN, BETWEEN

Let us know if that helps.

Regards,

Jerry

[kpennell]

yeah, I use those sources a lot too.  What I like to do, is have MSQuery build the CommandText for me.  When I copied and pasted the CommandText from the connection details to the lisp file, it didn't turn out that well.  I'll keep playing with it.  Hopefully I'll yield the results I want.  The driver for those connections to the database, from say Excel, is 'SQL Server'.  Wondering if the driver made the difference or not, with respect to the syntax.

Understanding of course, some tweaking is required if characters like quotations are in the CommandText, for a proper conversion for lisp..... \".

thanks for everything Jerry.

[JBerns]

@kpennell,

Yes, getting the command string correct can be tricky. I recall using SQL Server Management Studio to test my "SELECT" strings. Of course, my goal was to get all customer names from our database. No other filtering beyond that. But at the time, I did explore the various filter tools.

https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server... 

Yes, handling quotation marks and backslashes can get tricky in AutoLISP. You can always try the CHR function.

Regards,

Jerry

Regar

[bob_basques]

I just got burned by this one too, same double backslash miss.  I did manage to get ADOLisp and Postgres to connect though.

bobb

[JBerns]

@bob_basques,

Great to hear of your success.

Regards,

Jerry