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

[WPerciful]

Modded code:

 

(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=SQLNCLI;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")
     )
   )
 

 

 

Returns this error:

; error: Automation Error. Provider cannot be found. It may not be properly installed.

[rodrigo_sjc_sp]

ok , return to Provider=SQLOLEDB;

you can create a username and password with privileges and test?

 

I use sqlserver 2008

Use with user name and password, and this error does not happen here.

 

create a username and password ( all privileges - full )

Try changing the connection string with username and password

 

 

Tomorrow when you arrive at work I'll see your response

[rodrigo_sjc_sp]

see this website

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/4537aedb-a98f-4a4f-8d51-27ce77866b8c/microsoft-sql-server-native-client-100-invalid-authorization-specification?forum=sqldatabaseengine

 

Besides verifying the username and password, see if that helps website.

 

This bug is already something of connection to the database, user and password.

 

Solving this you will return data from routine.

[WPerciful]

I'm working trying to get that to work.

 

This is the code as it stands:

(progn
(vl-load-com)
(if adoConnect
	(if (= 1 (vlax-get-property ADOConnect "State"))
		(vlax-invoke-method ADOConnect "Close")
	)
	)
(setq adoconnect nil
	ADOConnect (vlax-create-object "ADODB.Connection")
	currConnectionString "Provider=SQLNCLI;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)
		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)
)

 

But I was able to connect to the database using the dbConnection Manager. I can even view the data. So I'm getting closer...

[WPerciful]

ADOLISP_Library.lsp

 

I've been doing some research and I came across the ADOLISP_Library.lsp by the fleming group. It's attached.

 

 

So I tried to use this:

(defun getMySqlData ( / )
(setq ;pm Name
	SQLCon "Provider=SQLNCLI;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 = pm"
	Connectionobject (adolisp_connecttodb SQLCon "RSHENERGY\wayne.perciful" "")
)
(if ConnectionObject
	(progn
		(setq Result 
			(adolisp_dosql
				ConnectionObject
				SQLStatementToProcess
			)
		)
		(if Result
			(progn
				(princ "db!")
				(prompt "\nSQLStatement Processed!!")
			)
			(progn
				(prompt "**Error Writing to Database")
				(setq badstatement SQLStatement2)
			)
		)
        (adolisp_disconnectfromdb ConnectionObject)
        (setq ConnectionObject nil)
     )
     (progn
        (prompt "\nCould Not Connect to Database***")
     )
  )
(princ)
)

 

But it could not connect to the database:(

[WPerciful]

Ok so using the ADOLISP_Library I was able to connect to the database. With this

 

(adolisp_connecttodb "Provider=sqloledb;Data Source=OurServer;Initial Catalog=engineering;Integrated Security=SSPI;" "" "")

 

But I'm still trying to process the SQL, this returns nil:

 

(setq SQLCon "Provider=sqloledb;Data Source=OurServer;Initial Catalog=engineering;Integrated Security=SSPI;"
ConnectionObject (adolisp_connecttodb SQLcon "" "")
SQLStatement "SELECT * FROM LineList"
)
(adolisp_dosql ConnectionObject SQLStatement)

[fixo]

Try this code (tested on SQL 2008R2) Win7

See piccy too

ReadSQLTable.LSP

[WPerciful]

fixo, thank you VERY MUCH ! Your code works GREAT! I was able to get the data.

 

 

 

rodrigo_sjc_sp thank you for all of your help as well.

[fixo]

Glad it helps

Cheers