Author Topic: Visual LISP connect to SQL Server 2012 database  (Read 281 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: 306
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.


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: 306
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: 306
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.