Jump to content

LISP to query MS SQL Server Database return results as list of strings

WPerciful

By WPerciful
in AutoLISP, Visual LISP & DCL

 Share

Recommended Posts

WPerciful Enthusiast

WPerciful

Posted
Posted

LISP to query MS SQL Server Database return results as list of strings.

 

(defun getMySqlData ( Name / )
(setq pm Name
	SQLCon "Server=OurServer;Database=engineering;Trusted_Connection=True"
	SQLStatement1 "USE enginering SELECT ID FROM Projects WHERE ProjectName = pm"
	SQLStatement2 "USE enginering SELECT * FROM LineList WHERE ProjectID = ResultsID"
)	
;	I'm trying to use SQLStatement1 to get the project ID.
;	So that I can use it to run this SQLStatement2 
;	Then return the results of the above as a list of strings.
)

  • Replies 28
  • Created
  • Last Reply

Top Posters In This Topic

  • WPerciful

    14

  • rodrigo_sjc_sp

    13

  • fixo

    2

Popular Days

Top Posters In This Topic

Popular Days

Posted Images

rodrigo_sjc_sp Community Regular

rodrigo_sjc_sp

Posted
Posted

In this example I bring a recordset

the str_FIELD_INTABLE variable receives the 0 position of the query. The first field.

 


 (vl-load-com)
 (if adoConnect
   (if (= 1 (vlax-get-property ADOConnect "State"))
     (vlax-invoke-method ADOConnect "Close")
     )
   )
 (setq adoconnect nil)
 (setq ADOConnect (vlax-create-object "ADODB.Connection"))
 (setq currConnectionString " ENTER A  STRING CONNECTION ")
 (vlax-put-property ADOConnect "ConnectionString" currConnectionString)
 (vlax-invoke-method ADOConnect "Open" currConnectionString "" "" -1)
 (setq cmd2 (vlax-create-object "ADODB.command"))
 (vlax-put-property cmd2 "ActiveConnection" ADOConnect)
 (vlax-put-property cmd2 "CommandTimeout" 30)
 (vlax-put-property cmd2 "CommandText" (strcat "select FIELD_INTABLE from TABLE"))
 (setq rs2 nil)     
 (setq rs2 (vlax-create-object "ADODB.Recordset"))
 (vlax-invoke-method rs2 "OPEN" cmd2 nil nil nil nil)
 (setq campos (vlax-get-property rs2 "Fields"))

 (while (not (equal :vlax-true (vlax-get-property rs2 "EOF")))

    (setq item11 (vlax-get-property campos "item" 0))
    (setq str_FIELD_INTABLE (vlax-variant-value (vlax-get-property item11 "value")))              
       ;;;;;   --------------------------
       ;;;;;   --------------------------
       ;;;;;  in this point use the field result  str_FIELD_INTABLE  , ou create more field results( "item" 1 , 2 , 3 ...)
       ;;;;;   --------------------------
       ;;;;;   -------------------------- 
    (vlax-invoke-method rs2 "movenext")
 )
 (setq rs2 nil)
 (setq cmd2 nil)
 (if adoConnect
   (if (= 1 (vlax-get-property ADOConnect "State"))
     (vlax-invoke-method ADOConnect "Close")
     )
   )
 (setq adoconnect nil)

WPerciful Enthusiast

WPerciful

Posted
  • Author
Posted
LISP to query MS SQL Server Database return results as list of strings.

 

(defun getMySqlData ( Name / )
(setq pm Name
	SQLCon "Server=OurServer;Database=engineering;Trusted_Connection=True"
	SQLStatement1 "USE enginering SELECT ID FROM Projects WHERE ProjectName = pm"
	SQLStatement2 "USE enginering SELECT * FROM LineList WHERE ProjectID = ResultsID"
)	
;	I'm trying to use SQLStatement1 to get the project ID.
;	So that I can use it to run this SQLStatement2 
;	Then return the results of the above as a list of strings.
)

 

I modded it to this:

 

(progn
(vl-load-com)
 (if adoConnect
   (if (= 1 (vlax-get-property ADOConnect "State"))
     (vlax-invoke-method ADOConnect "Close")
     )
   )
 (setq adoconnect nil)
 (setq ADOConnect (vlax-create-object "ADODB.Connection"))
 (setq currConnectionString "Server=OurServer;Database=engineering;Trusted_Connection=True")
 (vlax-put-property ADOConnect "ConnectionString" currConnectionString)
 (vlax-invoke-method ADOConnect "Open" currConnectionString "" "" -1)
 (setq cmd2 (vlax-create-object "ADODB.command"))
 (vlax-put-property cmd2 "ActiveConnection" ADOConnect)
 (vlax-put-property cmd2 "CommandTimeout" 30)
 (vlax-put-property cmd2 "CommandText" (strcat "USE enginering SELECT * FROM LineList"))
 (setq rs2 nil)     
 (setq rs2 (vlax-create-object "ADODB.Recordset"))
 (vlax-invoke-method rs2 "OPEN" cmd2 nil nil nil nil)
 (setq campos (vlax-get-property rs2 "Fields"))

 (while (not (equal :vlax-true (vlax-get-property rs2 "EOF")))

    (setq item11 (vlax-get-property campos "item" 0))
    (setq str_FIELD_INTABLE (vlax-variant-value (vlax-get-property item11 "value")))              
       ;;;;;   --------------------------
       ;;;;;   --------------------------
       ;;;;;  in this point use the field result  str_FIELD_INTABLE  , ou create more field results!
       ;;;;;   --------------------------
       ;;;;;   -------------------------- 
    (vlax-invoke-method rs2 "movenext")
 )
 (setq rs2 nil)
 (setq cmd2 nil)
 (if adoConnect
   (if (= 1 (vlax-get-property ADOConnect "State"))
     (vlax-invoke-method ADOConnect "Close")
     )
   )
 (setq adoconnect nil)
)

 

I got this error:

 

; error: Automation Error. [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

WPerciful Enthusiast

WPerciful

Posted
  • Author
Posted

No, I do not know what that is. Where can I go to get it?

rodrigo_sjc_sp Community Regular

rodrigo_sjc_sp

Posted
Posted

See my string

 

Provider=SQLOLEDB;Server=MYSERVER;Database=MYDATAB ASE;Uid=USER;Pwd=PASSWORD;

rodrigo_sjc_sp Community Regular

rodrigo_sjc_sp

Posted
Posted

I need

:version of your windows and version of your sqlserver.

rodrigo_sjc_sp Community Regular

rodrigo_sjc_sp

Posted
Posted

you need download and execute this file SQLNCLI.msi

however need to know the version of your sqlserver.

WPerciful Enthusiast

WPerciful

Posted
  • Author
Posted

The Server management studio I used to make the database is:

Microsoft SQL Server Management Studio 11.0.3128.0

WPerciful Enthusiast

WPerciful

Posted
  • Author
Posted

Awesome!

 

But it's not in English, do you have a link for one in English?

 

Or does it not matter?

rodrigo_sjc_sp Community Regular

rodrigo_sjc_sp

Posted
Posted

Insert in your stringConnection this (Provider=SQLOLEDB;)

 

Provider=SQLOLEDB;Server=OurServer;Database=engineering;Trusted_Connection=True

WPerciful Enthusiast

WPerciful

Posted
  • Author
Posted

Awesome, thank you very much. I am installing it now.

rodrigo_sjc_sp Community Regular

rodrigo_sjc_sp

Posted
Posted

after installing, restart the computer and test the routine again in autocad.

 

Insert in your stringConnection this -----Provider=SQLOLEDB------

 

Provider=SQLOLEDB;Server=OurServer;Database=engine ering;Trusted_Connection=True

 

check ( server , database , and type of login in database )

WPerciful Enthusiast

WPerciful

Posted
  • Author
Posted

I get an error with this line:

 

(vlax-put-property cmd2 "ActiveConnection" ADOConnect)

 

The error:

; error: Automation Error. Invalid authorization specification

 

The modded code I'm using:

 

(progn
(vl-load-com)
 (if adoConnect
   (if (= 1 (vlax-get-property ADOConnect "State"))
     (vlax-invoke-method ADOConnect "Close")
     )
   )
 (setq adoconnect nil)
 (setq ADOConnect (vlax-create-object "ADODB.Connection"))
 (setq currConnectionString "Provider=SQLOLEDB;Server=OurServer;Database=engineering;Trusted_Connection=True")
 (vlax-put-property ADOConnect "ConnectionString" currConnectionString)
 (vlax-invoke-method ADOConnect "Open" currConnectionString "" "" -1)
 (setq cmd2 (vlax-create-object "ADODB.command"))
 (vlax-put-property cmd2 "ActiveConnection" ADOConnect)
 (vlax-put-property cmd2 "CommandTimeout" 30)
 (vlax-put-property cmd2 "CommandText" (strcat "USE enginering SELECT * FROM LineList"))
 (setq rs2 nil)     
 (setq rs2 (vlax-create-object "ADODB.Recordset"))
 (vlax-invoke-method rs2 "OPEN" cmd2 nil nil nil nil)
 (setq campos (vlax-get-property rs2 "Fields"))

 (while (not (equal :vlax-true (vlax-get-property rs2 "EOF")))

    (setq item11 (vlax-get-property campos "item" 0))
    (setq str_FIELD_INTABLE (vlax-variant-value (vlax-get-property item11 "value")))              
       ;;;;;   --------------------------
       ;;;;;   --------------------------
       ;;;;;  in this point use the field result  str_FIELD_INTABLE  , ou create more field results!
       ;;;;;   --------------------------
       ;;;;;   -------------------------- 
    (vlax-invoke-method rs2 "movenext")
 )
 (setq rs2 nil)
 (setq cmd2 nil)
 (if adoConnect
   (if (= 1 (vlax-get-property ADOConnect "State"))
     (vlax-invoke-method ADOConnect "Close")
     )
   )
 (setq adoconnect nil)
)

rodrigo_sjc_sp Community Regular

rodrigo_sjc_sp

Posted
Posted (edited)

Try change the provider

 

Provider=SQLNCLI;

 

 

The ideal would be to create a username and password in sqlserver

with full privileges and make a test using the same.

 

Other way :

 

Try to update ( Microsoft® SQL Server® 2012 Service Pack 1 (SP1) )

http://www.microsoft.com/en-us/download/details.aspx?id=35575

 

 

I find this text

http://support.microsoft.com/kb/2652582/en-us

 

 

 

I believe the problem will be solved with a registered user

in the database, with every privilege.

 

You have to generate a user?

Edited by rodrigo_sjc_sp

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
 Share
Go to topic listing
×
×
  • Create New...