Shared SQL Connections and Expense

[stanlyn]

Hi,

Got a few VFP/SQL usage questions that I need clarity on...

1. Who are explicitly shared sql connections going to the same database shared with?
a. all users connected to the database, whether local or remote,
b. all connections coming from multiple vfp apps on a single workstation,
c. all connections created by a specific user on a specific workstation running a single vfp app uses,
d. all connections only ????

2. What are the boundaries of when a connection is shared or not.

3. Am I safe to assume a connection is shared ONLY if the sqlsetprop(nConnection, 'Shared') was issued? What is the default for shared setting as the docs fails to say?

4. What is the difference in using a DSNLess connection via an ODBC driver or directly thru "SQL Server" of SQL Server Native Clients". I've always thought they were the same, however I've seen docs that suggests otherwise, hence the question.

5. What is the fastest (least expensive in sql time and sql resources) in vfp9 to use for testing if the sql connection is alive? I would like to find out without tripping to the server which would be the fastest with zero cost to the server...
a. is there a way to find out without trying to make a trip to the server?
b. if a trip has to be made, do something like

select pk from [database].{dbo}.[SingleRowTable] option(fast 1)

c. wrap the needed query in a try-catch block and deal with it there?

Thanks,
Stanley

 

[stanlyn]

Advice, whitepapers from a VFP developer perspective ????

 

[atlopes]

Stalyn,

1. You share connections inside an application that is running. Two instances of an application that are running on different computers or on the same computer do not share connections.

2. You share connections if you need to limit the number of connections made to a server.

3. No. You declare that a connection is shared by explicitly passing .T. for the Shared or Sharable parameter in SQLCONNECT() or SQLSTRINGCONNECT() functions. As documented, this parameter defaults to false. SQLSETPROP(nConnection, 'Shared') is read-only, so you don't use it to establish shared status.

4. "SQL Server" and "SQL Native Client XX" are names of Microsoft ODBC drivers to MSSQL Servers.

5. There are ODBC API calls to verify if a connection is alive, but without poking the server there are no guarantees that the server itself is alive and responding to requests. The simplest verification statement for an SQL server is a comment:

--

You can refer to the Help file for documentation on shared connections.

 

[stanlyn]

Hi atlopes,

Thanks for answering, very helpful...
Answers 1-4 is clear now.

For answer 5, what would the statement look like using the "--" or is there something missing there? I just googled it and everything relating to comments is about commenting script code and I see no mention on querying for it.

Thanks, Stanley

 

[atlopes]

Stanlyn,

If you send the statement

--

to an SQL server, the server will reply "OK, got it". That's what you need to know to be sure there is a live connection to the server.

 

[stanlyn]

Hi atlopes,

I need some help here as

Do 'CreateConnection'
	
TEXT to m.lcSqlStr textmerge NOSHOW PRETEXT 15
	--
ENDTEXT

lnSqlResults = SQLExec(gnConnectionHandle, m.lcSqlStr)
llDisconnected = SQLDisconnect(0)

If lnSqlResults < 0
	Messagebox('A SQL connection error occurred...', 0, 'SQL Error', 10000)
	*Quit
Else
	Select 'sqlresult'
	Go Top
Endif

The value of lnSqlResults is 1 even after I closed the connection. I also get error "Alias 'SQLRESULT' is not found when it hits the "Select 'sqlresult' line after the Else line...

It appears the -- command is successful. It also appears a cursor (sqlresult) is not generated, so what result are you looking for and where?

What should the sql command look like?

How are you implementing this from VFP?

Thanks, Stanley

 

[atlopes]

Stanley,

The statement

--

does not produce any cursor, so SQLResult won't be created. The relevant information is the value of the lnSqlResults variable. In this case, the value 1 means success, and therefore it signals that you're connected.

 

[stanlyn]

atlopes,

Notice that immediately after I send, I disconnect (induced outage) and it still thinks the connection is good when it isn't.

So a simple test like

 if SQLExec(gnConnectionHandle, '--') > 0
    * connection is good
else
    * connection is bad
endif

will do it?

Where did you find the "--" query thing?

Thanks, Stanley

 

[atlopes]

Stanley,

You must test the connection whenever it's relevant for your application. In any case, what you'll have is just a snapshot in time. Whatever status you'll get from your test, the next millisecond it may change.

 

[atlopes]

Where did you find the "--" query thing?

Nowhere. It's a foundational feature in our company's framework.

 

[stanlyn]

Hi,

Nowhere. It's a foundational feature in our company's framework.

Confused... Found nowhere and is in your company's framework, suggests proprietary, so, why would it work on our system, which also suggests its not proprietary and documented somewhere, and I was just wondering where?

No, my short version won't work either. As soon as I disconnect, the connection becomes invalid, therefore do a quick connection test using

if SQLExec(gnConnectionHandle, '--') > 0

fails with the invalid error.

I'll keep looking for a solution that can run a quick connection test immediately before I query for needed data. May be the best to forget about it and wrap the needed data query in a for x = 1 to 3 plus a try/catch structure and if errors, reconnect and retry (reason for the for x structure)

Thanks, Stanley

 

[vernpace]

stanlyn,

While defensive coding of a server outage is reasonable, the probabilty of it occuring (between the time of SQLSTRINGCONNECT and SQLEXEC) is extremely low, unless you are intentially keeping the connection open for some reason - which is not good design. Furthermore, SQLEXEC returns -1 (see vfp help) if there is a connection error - that should be all you need. SQL Server CRUD functions should be as short and sweet as possible. See example below:

FUNCTION GetLBHandleDocument(tlOpenTransaction AS Logical) AS Integer
   LOCAL lcConnectString, liHandle

   SQLSETPROP(0, "DispLogin", 3)
   lcConnectString = "Driver={SQL Server Native Client 11.0};Server=TestSite;Database=LBDocuments;Trusted_Connection=Yes"
   liHandle = SQLSTRINGCONNECT(lcConnectString)

   IF liHandle <= 0
      RETURN -1
   ENDIF

   IF tlOpenTransaction
      SQLSETPROP(liHandle, "Transactions", 2)
      SQLSETPROP(liHandle, "DisconnectRollback", .T.)
   ENDIF

   RETURN liHandle

ENDFUNC

FUNCTION DeleteDocument(tiKeyDocument AS Integer) AS Logical
   LOCAL lcCmdDel, liHandle, IsCommitted

   liHandle = GetLBHandleDocument(.T.)

   IF liHandle <= 0
      RETURN .F.
   ENDIF

   lcCmdDel = "DELETE FROM lbdocument WHERE ikey_document = ?tiKeyDocument"

   IF SQLEXEC(liHandle, lcCmdDel) = 1
      IsCommitted = (SQLCOMMIT(liHandle) = 1)
   ENDIF

   IF NOT IsCommitted
      SQLROLLBACK(liHandle)
   ENDIF

   SQLDISCONNECT(liHandle)

   IF IsCommitted
      BackupLog("LBDocuments")
   ENDIF

   RETURN IsCommitted

ENDFUNC

 

[vernpace]

Concerning shared connections: The only case I have seen where this is usefull is where many Cursor Adapters are employed in an application. Each Cursor Adapter connection can be set to the shared handle. Even then, it can be problematic because the connection handle must persist somewhere - as a public memory variable or as an object property.

 

[atlopes]

Stanley,

Found nowhere and is in your company's framework, suggests proprietary, so, why would it work on our system, which also suggests its not proprietary and documented somewhere, and I was just wondering where?

Everywhere we looked for a minimum ODBC connection test involved a Select statement. We just found out that a comment would be enough. We don't think too much of it.

As soon as I disconnect, the connection becomes invalid

Querying for a real-time state requires real-time verification. You can't store a state in a variable and assume it will perdure.

I'll keep looking for a solution that can run a quick connection test immediately before I query for needed data

We always test for a connection before using it. It minimizes the impact of abrupt disconnections. In our case, it even allows us to handle reconnection transparently, but that will depend on how one builds the connection layer.