Author Topic: Visual LISP connect to SQL Server 2012 database  (Read 5655 times)

0 Members and 1 Guest are viewing this topic.

jberns

  • Mosquito
  • Posts: 5
Visual LISP connect to SQL Server 2012 database
« on: May 28, 2019, 03:59:54 PM »
Community,

I would like to read information from a database managed by SQL Server 2012.

I have attempted to use ADOlisp and SQLlite, but without success. I have searched many forums for the past four days, with no clear solutions found.

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 VB to prove that I can connect and read data.

Is it possible to connect to a SQL Server database using AutoCAD 2019 and Visual LISP. What additional software is required?

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


Regards,
Jerry

kpblc

  • Bull Frog
  • Posts: 396
Re: Visual LISP connect to SQL Server 2012 database
« Reply #1 on: May 28, 2019, 04:52:22 PM »
I think main trouble is to define correct provider. For SQL Server I use somethinkg like this:
Code - Auto/Visual Lisp: [Select]
  1. (setq cs  "Provider = SQLNCLI11; Data Source=MyServer; Initial Catalog=MyDataBaseName; Persist Security Inf=False; Integrated Security = SSPI")
  2. (setq conn (vlax-create-object "ADODB.Connection"))
  3. (setq rec_set (vlax-create-object "ADODB.Recordset"))
  4. (setq sql "selet * from mytable")
  5. (vlax-invoke-method rec_set "Open" sql conn 1 3 1)
  6. (setq fld (vlax-get-property rec_set "fields"))
  7.                                         (while (= (vlax-get-property rec_set "BOF") (vlax-get-property rec_set "EOF") :vlax-false)
  8.                                           (setq pos -1
  9.                                                 tmp nil
  10.                                                 ) ;_ end of setq
  11.                                           (repeat (vla-get-count fld)
  12.                                             (setq item (vlax-get-property fld 'item (setq pos (1+ pos)))
  13.                                                   tmp  (cons (vlax-variant-value (vlax-get-property item 'value)) tmp)
  14.                                                   ) ;_ end of setq
  15.                                             ) ;_ end of repeat
  16.                                           (setq res (cons (reverse tmp) res))
  17.                                           (vlax-invoke-method rec_set "MoveNext")
  18.                                           )
  19. (foreach item (vl-remove nil (list rec_set conn))
  20.            (vl-catch-all-apply (function (lambda () (vlax-invoke-method item "close"))))
  21.            (vl-catch-all-apply (function (lambda () (vlax-release-object item))))
  22.            ) ;_ end of foreach
  23.          (gc)
  24.          (reverse res)
This code is just for example. I can't guarantee it will be work correct. You can use not SQLNCLI* provider but SQLOLEDB or MSOLEDBSQL or womething else. I never worked with sqllite, so you have to look for correct connection method by yourself :(
Sorry for my English.

Lee Mac

  • Seagull
  • Posts: 12905
  • London, England
Re: Visual LISP connect to SQL Server 2012 database
« Reply #2 on: May 28, 2019, 06:07:40 PM »

jberns

  • Mosquito
  • Posts: 5
Re: Visual LISP connect to SQL Server 2012 database
« Reply #3 on: May 30, 2019, 10:08:56 AM »
I have been struggling to find the right Connection String to connect with the intended SQL database.

I have installed the sample NORTHWIND and PUBS databases available from Microsoft. https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/downloading-sample-databases

What would the connection string be to connect to these? I am testing first on my local computer before connecting to the actual server. My local workstation is Windows 10 Pro with several Autodesk products installed, including Autodesk Vault Client. I have also installed Microsoft SQL Server Management Studio 18 (MSSMS) to confirm that I can see and view the tables in the various databases.

When starting MSSMS, the Server type = Database Engine, the Server name = JERRY-HP-WIN10\SQLEXPRESS, and Authentication = Windows Authentication.

Is there other info you may need to help me build the correct Connection String? Eventually, I will need to build the connection string to the actual SQL server. Details on that can follow if needed.

Thank you for your time and attention. I look forward to your replies.


Regards,
Jerry

jberns

  • Mosquito
  • Posts: 5
Re: Visual LISP connect to SQL Server 2012 database
« Reply #4 on: May 30, 2019, 04:08:27 PM »
kpblc,

In your code example, you set the variable, cs:
Code: [Select]
(setq cs  "Provider = SQLNCLI11; Data Source=MyServer; Initial Catalog=MyDataBaseName; Persist Security Inf=False; Integrated Security = SSPI")

Where in your code does cs get used?

I am certain this is related to why I am unable to connect to the SQL database.

I look forward to your reply.


Regards,
Jerry


kpblc

  • Bull Frog
  • Posts: 396
Re: Visual LISP connect to SQL Server 2012 database
« Reply #5 on: May 30, 2019, 05:32:59 PM »
There are many resources to create connection string. First of them is oficcial MS docs :) https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax
Aso you can use _.dbconnect to connect to your SQL server and check connection string...
Sorry for my English.

jberns

  • Mosquito
  • Posts: 5
Re: Visual LISP connect to SQL Server 2012 database
« Reply #6 on: May 31, 2019, 08:29:03 AM »
kpblc / Lee,

I was successful at setting up ADOLisp (by the Fleming Group) and my connection string. I am able to connect to the database and get data. Thanks for all the links and help.  :-D

Next challenge, how do I open the database Read-Only? In your (kpblc) code example, you have an Open statement containing numbers (1 3 1):
Code: [Select]
(vlax-invoke-method rec_set "Open" sql conn 1 3 1)
I trust these have something to do with CursorType, LockType, and Options.  (https://docs.microsoft.com/en-us/sql/ado/reference/ado-api/open-method-ado-recordset?view=sql-server-2017

I could not find a similar line in the ADOLisp_Example.lsp or in the ADOLisp_Library.lsp file. I suspect the database is being opened as read-write because the date-time stamp is changing on the database (MDF and LDF) files.

Thanks again. I look forward to the replies.


Regards,
Jerry

kpblc

  • Bull Frog
  • Posts: 396
Re: Visual LISP connect to SQL Server 2012 database
« Reply #7 on: June 02, 2019, 10:18:44 AM »
I think these situations should be solved by server rights and user roles.
Sorry for my English.

Donalind

  • Mosquito
  • Posts: 2
Re: Visual LISP connect to SQL Server 2012 database
« Reply #8 on: November 07, 2021, 12:59:31 PM »
I searched many forums for days, and most of the results are years old. I tried ADOlisp and SQL_Lite, but without success. N O clear solutions found after all these searches.
I have an existing LSP file (over 500 lines of code), which I'd instead not convert to VBA or VB.NET. By the way, I connected to the server and database using VBA to prove that I can click and read data from the database from AutoCAD 2019. I was also able to connect to the database and read the table using SQL studio. I want to automate this reading using Visual LISP.
Ultimately the goal is to read the CompanyName from the dbo.Customers table. Put this list in a dialog box so that the user can select a single customer. From there, the data fills the block for use by the CNC software.
« Last Edit: November 08, 2021, 07:29:13 AM by Donalind »

It's Alive!

  • Retired
  • Needs a day job
  • Posts: 8659
  • AKA Daniel
Re: Visual LISP connect to SQL Server 2012 database
« Reply #9 on: November 07, 2021, 05:32:17 PM »
I searched many forums for days, and most of the results are years old. I tried ADOlisp and SQL_Lite, but without success.

I have some SQLite stuff here in case it interests you, for SQL Server ADOlisp may work, provided you have the correct drivers.
some time ago Microsoft dropped jet drivers (since added them back), so I not longer trust using providers lol