Connection string to MS SQL

The place to discuss the rollout of the VPME 9.1 Client-Server SQL Toolkit. The VPME 9.1 Client-Server SQL Toolkit adds specialized tools to VPME 9.1 to facilitate the creation of a frontend to access MS SQL Server and MySQL backend databases.

Connection string to MS SQL

Postby stecenko » Tue Oct 16, 2012 1:31 pm

I'm just about to install a new application using VPME 9.1.12 and MS SQL (SQL Server Express 2012).

In my database containers for the remote views I created a connection using a connection string; like this...

DRIVER=SQL Server;Trusted_connection=True;DATABASE=icsSmdBudget;SERVER=RICHARD-PC\SQLEXPRESS

Works just fine on my machine.

Now I want to install everything at my clients and of course the server there is not called RICHARD-PC.

I can include the data dictionary in my application and manually change the server name in the database. That's not a big deal.

But I wonder if there is some way to do that programmically.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: Connection string to MS SQL

Postby Bob » Wed Oct 17, 2012 9:13 am

How about the SQLStringConnect() fumction in VFP?
Bob
 
Posts: 718
Joined: Mon Jun 14, 2010 6:53 am
Location: Near Albany, NY

Re: Connection string to MS SQL

Postby stecenko » Wed Oct 17, 2012 9:47 am

Here's what I'm trying to avoid...

For SqlExec, I put the string in a table and added a custom method to all my forms that uses the string to create a connection if one is required (usually for a report).

But in the remote views database, there is a string that is used to make the connection. I can change that string at the clients site using the datamanager. It's just takes about 10 seconds.

If I have to make some changes to the application that involves the views (highly likely), then I have to de-bug here and remember to change the string in the dbc there. And I know I'll forget.

I'm sort of looking for something that I can put in my startUp routine. I can test in the startUp.prg that I'm at the clients and then I'd like to at least check that the string for the remote views dbc is the one in the table or if I'm testing at my office, then I want the string to be the one for my server.

Also, I had a case a while back that an auditor walked up to the network administrator and insisted that all the passwords had to be changed. With the string in the table, the administrator can easily change the password and the startup routine will change the string in the dbc (if I knew how to change the string in the dbc).

Mostly, I'm looking for ideas, different approaches that people have used for connecting to a SQL server.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: Connection string to MS SQL

Postby Bob » Thu Oct 18, 2012 5:04 pm

Perhaps you can use the VPM code that reads and writes ini files. Retrun the dbc with an empty string there and let the startup routine test that anad grab it from the ini file.
Bob
 
Posts: 718
Joined: Mon Jun 14, 2010 6:53 am
Location: Near Albany, NY

Re: Connection string to MS SQL

Postby stecenko » Thu Oct 18, 2012 5:26 pm

Yes, that's one way of determining where I am: either here or at my client's.

But I still need to change the connection string in the DBC.

This kind of works...

set safety off
open database fredViews
create connection ;
MsSQLIntoFred ;
constring ;
'DRIVER=SQL Server;SERVER=RICHARD-PC\SQLEXPRESS;UID=Richard;Trusted_Connection=Yes;APP=Microsoft Visual FoxPro;WSID=RICHARD-PC;DATABASE=icsAdmin'
close databases all


But, not quite. If I do that then I get connection errors when I open any view in fredViews.

The errors go away, if I use the datamanger and open fredViews, edit the connection, and click verify.

I'm still working on it.

But not too much. After all, what I'm trying to do is safe guard my application from my own forgetfulness.

If I use fredViews.Dbc and browse for ObjectType='Connection', then I can see in the properties memo field, the connection string. But, there's a bunch of what looks like compiled code there as well.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: Connection string to MS SQL

Postby Bob » Thu Oct 18, 2012 6:08 pm

Perhaps dbGetProp() and dbSetProp() should be able to read and set these. You can try form the command line.

With the DBC open:

? DBGETPROP('<connection name>','Connection','Database')
Bob
 
Posts: 718
Joined: Mon Jun 14, 2010 6:53 am
Location: Near Albany, NY

Re: Connection string to MS SQL

Postby stecenko » Thu Oct 18, 2012 6:56 pm

That's it? Thanks, Bob.

OPEN DATABASE icsAdmin
STORE DBGETPROP('icsAdmin', 'Connection', 'ConnectString') TO dlcConnString

OPEN DATABASE test
STORE DBSETPROP('icsAdmin', 'Connection', 'ConnectString', dlcConnString) TO dllResult
? dllResult
CLOSE DATABASES
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: Connection string to MS SQL

Postby stecenko » Fri Oct 19, 2012 3:35 pm

This works nicely.

I created a table called sDataConnections with one column called 'Here'.

I created one record with the appropriate connection string for each connection here and there.

Then at startup, I check to see if the dbc has a different connection str using dbGetProp and mkae it correct with dbSetProp.

The only problem that might occur will be if I convert the VPME security tables to Ms SQL. The startup runs after login.

Working with Tek Hong, I've converted the audit, activity, and error tables so that they are in Ms SQL. But, not the security. Yet. More than likely I can put my connection changing routine in the Load of the login form. Or someplace ProApp.

Anyway, for now, I'm happy with this. Thanks again.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia

Re: Connection string to MS SQL

Postby Bob » Sat Oct 20, 2012 9:41 am

In the oApp.startup() method, there are a number of methods called before the login that you could use to do the checking. These are best seen in the vpm.oApp class library, but you would likely put code in a proApp method before a doDefault().
Bob
 
Posts: 718
Joined: Mon Jun 14, 2010 6:53 am
Location: Near Albany, NY

Re: Connection string to MS SQL

Postby stecenko » Sat May 11, 2013 10:26 am

Is any one else working with a project entirely in Remote Views?

I created in my project a DBC that contains only remote views. I used a connection string in the DBC to connect to the SQL engine.

That connect string needs to be changed at the client's location to refer to the SQL engine there.

That's not a problem, I can do it with DBSETPROP.

But, normally I include the view only DBC files in the executable, but that makes the DBC read-only. To change the connection string at the client's the DBC has to be writeable.
Richard Stecenko
Interactive Computer Services Inc.
Victoria, British Columbia
204.453.2052
stecenko
 
Posts: 822
Joined: Mon Jun 21, 2010 8:06 pm
Location: Saanich, British Columbia


Return to VPME 9.1 Client-Server SQL Toolkit

Who is online

Users browsing this forum: No registered users and 1 guest

cron