SQL Connection on the Fly 2

[jgarry]

Hi, thanks in advance.

I am using vfp6 to attach to Sql 7/2000 databases. I can connect on my system using a connection string and do what I want. BUT

What I need to do is to be able to have the users connect to different Sql databases. The structure and format of the databases will be the same. Each user will have many different SQL Databases to connect to and work with.

Where I am having the problem is creating a command base connection string to access the SQL DBF.
In my project I have a database

Under the data Tab I have:

Databases
EmpConver
Tables
Table1
Tabel2
Etc…

Remote View
V_Per_master
V_val_ValTabel1
V_val_tabTable2
Etc..

Connections
EmpConnections

Here is the connection string :
DRIVER=SQL Server;SERVER=XXXsql;UID=CONVERSION;PWD=123456;APP=Microsoft® Visual FoxPro®;WSID=JAG;DATABASE=SJIMGTEST

Should I set this up using command line in my startup program instead of using the project manager?

Is there a way to use this connection string from a program. When I use it I always get the request for the password even if the connection is has the button “Display ODBC Login prompts” set to never

What I now want to do is change the connection on the fly to a new database. It will always have the same table structure and layout just different names.

I could use variables for the Server, data name, password and users

Thanks for any help you can give I know Im making this harder then it is.



Jim

 

[bborissov]

At the begining you can always do:

OPEN DATA YourData
SET DATABASE TO YourData
DELETE CONNECTION EmpConnections

CREATE CONNECTION  EmpConnections CONNSTRING [ DRIVER=SQL Server;SERVER=RequiredServername;UID=CONVERSION;PWD=123456;APP=Microsoft® Visual FoxPro®;WSID=JAG;DATABASE=WantedDBName]
CLOSE DATABASE

I never work with CONNECTIONS builded in DB. I use SQLSTRINGCONNECT and SQLEXEC() to retreive the data, when I used VFP6. Now I am very happy with CursorAdapters in VFP8.

You can try to hide Login window with SQLSETPROP("DispLogin", 3)

Borislav Borissov

 

[Mike Lewis]

Jim,

You are basically on the right track. All you need to do to switch to a different back end database is to add a CONNSTRING clause to the USE statement that you use to open the remote views.

When you create the views, specify the connection object, as you have been doing. This will then be the default connection for the views, and will give access to whichever server-based database you specified in the connection string.

When you add CONNSTRING <connection string> to the USE, this will override the connection specified in the connection object. This new connection can point to any other database, and can include the login and password if required.

The string specified in CONNSTRING can be a variable, which means that you can add the database name, password, etc, on the fly.

Of course, the new database must contain the same table and field names as the original one.

Hope this makes sense.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My sites:
Visual FoxPro (www.ml-consult.demon.co.uk)
Crystal Reports (www.ml-crystal.com)

 

[jgarry]

Sorry it took so long to get back to both of you Its working great thanks for your assiance
Here is what I have done!

PUBLIC nSQL
nSQL = SQLSTRINGCONNECT('DRIVER=SQL SERVER=DEVSQL;UID=CONVERSION;PWD=123456;APP=Microsoft® Visual FoxPro®;WSID=JAG;DATABASE=DBFS')

then to run the connection

strSql = "Select * from " + trim(a_valTable[c_run,1])
sqlexec(nsql,strsql,"c_"+trim(a_valTable[c_run,1]))

What Im doing is grabbing the data from SQL server and creating a cursor. works great so far.

oh use this is also going through a list of table to make into cursors.



thanks for all your help.

Jim Garry