dbc connection string to SQL

[robnowa]

Hello!

I'm using a SQL database, I have in VFP9 the tables as remote views and a (Project Manager>Data>Databases>varu>)Connection called Upsize with a connectionstring (below). It's stored in my Data\Varu.dbc file.
Is there any way to refer to my Upsize connectionstring like this or similar?

lcConnString = varu.upsize.connectionstring

Info on my dbc:
c:\varu\data\varu.dbc - UPSIZE.Connection String:
Description=VaruSQL;DRIVER=SQL Server Native Client 10.0;SERVER=CT123\SQLEXPRESS;UID=testuser;PWD=testpass;APP=Microsoft Visual FoxPro;WSID=SRV-02;DATABASE=Varu;LANGUAGE=us_english;

I'd rather not have like this below since I already have the info in my project.

lcConnString = "DRIVER=SQL Server Native Client 10.0;" ;
				+ "SERVER=CT123\SQLEXPRESS;" ;
				+ "UID=testuser;" ;
				+ "PWD=testpass;" ;
				+ "DATABASE=Varu;"
	lnHandle=SQLSTRINGCONNECT(lcConnString)

Thanks!

 

[Mike Lewis]

Yes. Instead of using SQLSTRINGCONNECT(), use SQLCONNECT(), passing the name of the connection object ("Upsize" in your case). In other words:

lnHandle=SQLCONNECT("Upsize")

Be sure that the DBC (Varu) is open when you issue this command.

That said, if you are only using remote views, you won't need either SQLCONNECT() or SQLSTRINGCONNECT(). You specify the connection name in the CREATE SQL VIEW command or interactively when opening the view designer.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[robnowa]

Hello!
Thanks for your reply!
In this case I was just trying one function to update 10000+ records and I found that using SQLEXEC() was much much faster then regular update/replace in a remove view and simpler then a stored procedure.

Question: How do I ensure that my dbc is open?

 

[Mike Lewis]

In that case, you will indeed need either SQLSTRINGCONNECT() or SQLCONNECT(). As you probably know, you pass the connection handle (lnHandle, in your case) to SQLEXEC().

To open the database, do this:

OPEN DATABASE Varu

If the database is already open:

SET DATABASE TO Varu

To find out if the database is already open:

IF DBUSED("Varu")
  ....
ENDIF

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

To use a connection object in a DBC, you could define a view not querying any data via a query like SELECT * FROM sometabl WHERE .F., which would also open the connection, and then use ASQLHandles() to find out the connection handle for use in SQLExec(). If you typically are in a state any remote view already was used, you can also find out connection handles open via ASQLHandles() in the first place. SQLGetProp then can tell you, with which connection string a connection handle was made, to check the connection handle is not for any other database.

Then you can also get at the connection string from DBGETPROP():

OPEN DATABASE your.dbc
lcConnString = DBGETPROP("connectionname","CONNECTION","ConnectString")
gnConnHandle = SQLStringConnect(lcConnString)
* and then
SQLExec(gnconnHandle,...)

Then there also is ADBOBJECTS(), which can give you an array for tables, views, or also connections of a DBC, ADBOBJECTS(laConnections,"CONNECTION") will give all connections info in an array laConnections, one array row per connection object of the DBC.

By the way: In code samples of SQLStringConnect you'll find the help stores the handle returned in a variable of exactly that name "gnConnHandle", while the help says SQL(String)Connect returns a statement handle, also the first parameter of SQLExec is specified in the help as nStatementhandle. The two terms statement handle and connection handle are really interchangable and indeed most of the world talks about connection handles.

Of course you better put this into something else than a public variable, but that's up to you. If you then begin to use remote views you don't have to care about them opening further connections. If you defined the views to share connections, they will also reuse the connection you manually opened. VFP determines, if a connection already is made in the same way ASQLHandles determines handles. I'm not 100% sure and don't want to setup a dbc with a connection object and remote views to verify that, but you can simply check it by issuing a SQLStringConnect, using a remote view and then check, whether ASQLHandles only returns one handle.

Bye, Olaf.