Topic: connect vlisp to SQLEXPRESS

[Amsterdammed]

Hello there,

i tried to get this done before but had not really luck with it

I would like to  read (and write if possible) from / to this dbase. But i can't get the connectionstring working, sometimes it tells me the data base is in use or some other error.

Server=(local)\SQLEXPRESS;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=TechlineSQL

Anybody done this here before? And yes, i looked at MR .Flemmings site already but i can't figure it out.

Thanks in Advance

Bernd

[irneb]

Have you tried using a different ADO/ODBC driver instead?

As a tip, I first connect to the DB through Excel which then generates the connection string for me from a "nice" wizard interface. Can also test there if it actually works. Then I copy-n-paste that instead of trying to rehash one from scratch - which seldom works, as the documentations are usually incomplete and/or WRONG!

[Amsterdammed]

You lost me here! Throug Ecxel? Tell me, how does that work?

Thanks Bernd

[irneb]

Open Excel in a new workbook. (I've got 2007 so I can only describe the ribbon for you).

Open the Data tab on the ribbon, then the 1st button (Get External Data) has a drop-down arrow which opens a set of sources (Access, Web, Tex, Other), choose the Other. There should be a From SQL connection, OLE DB, etc. Try some of them to connect to your DB server, there should also be some SQL server drivers under the OLE DB and even under the OLDE DB - ODBC drivers (try until you find one which works well). If it works then it should extract a selected table into the current sheet.

After that, under the Data ribbon tab should be a "Connections" group with a "Connections" button. This opens a dialog with all the connections into the current workbook. Select the one which worked, then click the properties button. In the Connection Properties dialog open the Definition tab. Select the text in the "Connection string" field and copy ... then paste this into your Lisp.

Edit: Remember to escape stuff like double quotes and backslashes. Easiest way I know of is to use a call to getstring and then paste that when asked. The result should show the escaped string which can directly be used in lisp. E.g. type the following into the lisp console in VLIDE:

Code: [Select]

(getstring t "Paste: ")Then paste the connection string there & press Enter. Then select the result in the console and copy-n-paste into your lisp file.

[Amsterdammed]

wow,

if that isn't a classic case of a backdoor. Well, it works. Now i need to figure out how to retrieve the data I need.

Thanks a lot!!
 
Bernd

[irneb]

You're welcome! Don't know if it's a "backdoor ", but I've been using it for a while 

[irneb]

Now i need to figure out how to retrieve the data I need.

BTW, if you mean you need to extract only some portion(s) of the data, then you're probably referring to queries. Instead of needing to learn yet another programming language (SQL - Structured Query Language) Excel may be of service as well.

In that same spot where you connect to a DB you can choose the "Microsoft Query" option instead. Then you'll be presented with a list of tables and their fieldnames to include in the query. It basically opens a query builder similar to what you get inside of Access: I.e. point & click, drag & link, etc. Then you could simply click on the SQL toolbar button to shee the equivalent SQL code to copy into your lisp.